Specifying Physical Design Structures

Article
12/03/2008

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.

Before You Begin

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.

Tuning Options Through the GUI

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.

Tuning Options Through the dta Utility

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.

Change History

Release	History
5 December 2005	Changed content: Changed title from "Available Tuning Options" to "Specifying Physical Design Structures"

Share via