Specifying Physical Design Structures

Updated: 5 December 2005

You can specify which physical design structures that the Database Engine Tuning Advisor graphical user interface (GUI) or the dta command prompt utility consider during tuning. Either through the GUI or the command prompt, users can specify the types of indexes and partitioning for Database Engine Tuning Advisor to consider in a recommendation, and whether to keep or consider dropping existing indexes and partitioning.

Users must make two choices before they start a tuning operation:

  • What feature set is the user willing to consider in the database?
  • Which subset of existing physical design structures in the database must be part of the Database Engine Tuning Advisor recommendation?

To specify the feature set, you must provide the class of objects to consider and how they are to be partitioned. The following sections describe the options that are available with the two different Database Engine Tuning Advisor interfaces.

The Database Engine Tuning Advisor GUI supports most of the tuning options that the dta command-prompt utility supports. However, the command prompt utility provides more options and greater flexibility because it fully supports the user-specified configuration feature. For more information, see Exploratory Analysis Using Database Engine Tuning Advisor. The following tables describe the tuning options that are available on the Tuning Options tab of the GUI.

Physical Design Structures to Use in Database

Tuning option Causes Database Engine Tuning Advisor Recommendations to …

Indexes and indexed views

Add clustered indexes, nonclustered indexes, and indexed views.

Indexes

Add only indexes.

Indexed views

Add only indexed views.

Nonclustered indexes

Add only nonclustered indexes.

Evaluate utilization of existing PDS only

Evaluate the existing physical design structures only, but not to consider adding any new structures.

Partitioning Strategy to Employ

Tuning Option Causes Database Engine Tuning Advisor to …

No partitioning

Not use partitioning in its recommendation.

Full partitioning

Use partitioning in its recommendation.

Aligned partitioning

Only recommend partitions that are aligned with existing partitions of underlying tables or views. (Selecting this option enhances manageability.)

Physical Design Structures to Keep in the Database

Tuning Option Causes Database Engine Tuning Advisor to …

Do not keep existing PDS

Drop any existing physical design structure if appropriate. Select the Evaluate utilization of existing PDS option with this option to set Database Engine Tuning Advisor to the drop-only mode, which only evaluates current physical design structures to determine whether they can be dropped.

Keep indexes only

Drop all structures except indexes if appropriate.

Keep all existing PDS

Keep all existing structures.

Keep clustered indexes only

Drop all structures except clustered indexes if appropriate.

Keep aligned partitioning

Drop all structures except aligned partitions if appropriate.

You can specify the feature set objects that you want Database Engine Tuning Advisor to include in the recommended configuration by using the –fa and –fp options, respectively. The values you can specify for these options are listed in the following two tables, Feature Set Objects (-fa) and Feature Set Partitioning (-fp).

Feature Set Objects (-fa)

Value Description

IDX_IV

Indexes and indexed views.

IDX

Indexes only (default).

IV

Indexed views only (indexed views apply to all editions of SQL Server 2005, and to SQL Server 2000 Enterprise Edition).

NCL_IDX

Nonclustered indexes only.

Feature Set Partitioning (-fp)

Value Description

NONE

No partitioning (default).

FULL

Full partitioning (choose to enhance performance).

ALIGNED

Fully aligned. This indicates that a table and all indexes on it are partitioned similarly. (Selecting this option enhances manageability.)

To specify which existing physical design structures in the database must be part of a Database Engine Tuning Advisor recommendation, you can use the -fk option. The values you can specify for this option are listed in the following table.

Design Structures to Keep (-fk)

Value Description

ALL

Everything (default).

NONE

Nothing.

CL_IDX

Clustered indexes only.

IDX

Clustered and nonclustered indexes only.

ALIGNED

All partition-aligned structures only.

Drop-only Mode (-fx)

To specify the drop-only mode, use the -fx option. When this option is used, it specifies that Database Engine Tuning Advisor only consider dropping existing physical design structures. No new physical design structures are considered. Database Engine Tuning Advisor evaluates the usefulness of existing physical design structures and recommends dropping seldom used structures. This option takes no values. It cannot be used with -fa, -fp, or -fk ALL.

Release History

5 December 2005

Changed content:
  • Changed title from "Available Tuning Options" to "Specifying Physical Design Structures"

Community Additions

ADD
Show: