Specifying Physical Design Structures
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. |
Indexed views |
Add only indexed views. |
Include filtered indexes |
Consider adding filtered indexes. This option is available when you select the Indexes and indexed views option, the Indexes option, or the Nonclustered indexes option. |
Indexes |
Add only indexes. |
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 2008 and 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.