Exploratory Analysis Using Database Engine Tuning Advisor
Database administrators can use Database Engine Tuning Advisor to perform exploratory analysis. Exploratory analysis involves using a combination of hand tuning and tool-assisted tuning. To perform exploratory analysis with Database Engine Tuning Advisor, use the user-specified configuration feature. The user-specified configuration feature allows you to specify for tuning configurations of existing and hypothetical physical design structures, such as indexes, indexed views, and partitioning. The benefit of specifying hypothetical structures is that you can evaluate their effects on your databases without incurring the overhead of implementing them first.
Although both the Database Engine Tuning Advisor graphical user interface (GUI) and the dta command-line utility support exploratory analysis, dta provides more flexibility because it can use an XML input file. This XML input file uses the Database Engine Tuning Advisor XML schema, a published schema that you can download by going to Database Engine Tuning Advisor Schema.
After navigating to this URL, scroll down the table rows until you find the link to the Database Engine Tuning Advisor Schema. The exact URL to this schema is in the adjacent column. This XML schema supports specifying for analysis hypothetical configurations that are evaluated in isolation, or hypothetical configurations that are evaluated relative to the current configuration. In comparison, the Database Engine Tuning Advisor graphical user interface only fully supports evaluating a subset of structures from a Database Engine Tuning Advisor-generated recommendation.
Two Modes of Exploratory Analysis
Exploratory analysis can be performed in either of the following two modes with Database Engine Tuning Advisor:
Evaluate mode
In evaluate mode, Database Engine Tuning Advisor compares the cost of the current configuration (C) with that of a user-specified configuration (U), for the same workload. C is always a real configuration because it consists of physical design structures that currently exist in the database. In comparison, U is a configuration that consists of real and hypothetical physical design structures. If Database Engine Tuning Advisor reports that the cost of U is lower than the cost of C, it is likely that the physical design of U will perform better than C.
For example, the evaluate mode is useful in the following situations:
An administrator wants to determine the performance impact of adding a nonclustered index to a table.
An administrator just finished using Database Engine Tuning Advisor to tune a database and received the recommendation (R). After reviewing R, the administrator would like to fine tune R by modifying it. For example, she wants to add two nonclustered indexes and delete one nonclustered index that was part of R. After modifying R, the administrator uses the modified recommendation as input to Database Engine Tuning Advisor and tunes again to measure the performance impact of her modifications.
Tune mode
In tune mode, a database administrator already knows that a part of the database physical design should be fixed, but he wants Database Engine Tuning Advisor to recommend the best physical design structures for the rest of the configuration.
For example, the tune mode is useful in the following situations:
A database administrator knows that a fact table must be partitioned because it is too large. The administrator must choose between partitioning it by month or by quarter. Either way of partitioning the table would work, but the administrator wants to choose the partitioning method that provides the best performance for a given workload. To determine which partitioning method is best, the administrator can use Database Engine Tuning Advisor to tune the workload twice. First, the administrator tunes the workload by means of a user-specified configuration with the table hypothetically partitioned by month. Then the administrator tunes the workload with the table hypothetically partitioned by quarter. After the workload has been tuned with both of the hypothetical configurations, the administrator can compare the percentage of improvement to determine which partitioning method should provide the best performance.
An Orders table must have a clustered index on its ship_date column. The database administrator wants to determine the best set of nonclustered indexes for the Orders table. The database administrator can partially fix the physical database design by specifying a user-specified configuration that has a clustered index on the ship_date column in the Orders table. Then the administrator can use Database Engine Tuning Advisor in the tune mode to determine the performance effects of the user-specified configuration.
About Comparing Two Configurations for a Particular Workload
A common scenario is comparing two different configurations for a particular workload. For example, Database Engine Tuning Advisor recommends a certain configuration, and the administrator wants to see the impact if a slightly different configuration is used. To compare the new configuration with the original using the same workload, it is important to use the dta command-line utility instead of the graphical user interface, because the command-line utility enables you to specify how many workload events that Database Engine Tuning Advisor should tune (using the -n option). If you specify the same number of events that Database Engine Tuning Advisor should tune for each configuration, you can be sure that the two tuning sessions are equivalent and are thus comparable. This is important because the entire workload may not be tuned by Database Engine Tuning Advisor within the specified time. For more information, see dta Utility and How to: Tune a Database by Using the dta Utility.
Considerations for Using User-specified Configuration
Note the following:
User-specified configurations can consist of the following physical design objects: indexes, indexed views, partitioning of indexes and indexed views, and statistics.
You can use the Database Engine Tuning Advisor GUI to import a user-specified configuration into a tuning session. To import a configuration, on the File menu, click Import Session Definition.
You can also use the Database Engine Tuning Advisor GUI to select a subset of indexes from one of its recommendations and launch a new session with this selected subset of the recommended configuration for evaluation.
User-Specified Configuration Limitations
User-specified configurations have the following limitations:
The configuration specification should conform to the Database Engine Tuning Advisor XML schema DTAschema.xsd, which can be found at this Microsoft Web site.
The configuration should not specify adding an already-existing index or indexed view to the database.
The configuration should not contain any views on which indexes cannot be created.
The configuration should not attempt to drop a nonexistent index or an index enforcing a constraint (for example, a unique or primary key constraint).
The configuration cannot create and drop the same index. For example, you cannot create index I on table T and then drop it. Nor can you do the opposite: drop index I on table T and then create it again.