Analyzing Index Tuning Wizard Output

The most important output from the Index Tuning Wizard is a set of recommended indexes. The Index Recommendations dialog box displays the list of these indexes, indicating the assigned index name, the order of columns in the index, whether the index is clustered, and whether the index exists. The wizard also produces an estimate of expected improvement in the execution of the workload compared to the existing configuration. The Index Tuning Wizard uses the optimizer component of the query processor to project the above estimate. Because the optimizer's projection is based on statistical information, the actual change in performance may be different from the projected estimate.

The Index Tuning Wizard recommendations are supplemented by a variety of reports that provide further analysis of the recommendations and their quantitative impact. These reports affect the decision about whether to accept or reject the recommendations. All the reports can be saved as text files for further analysis. In the Index Recommendations dialog box, click Analyze to view these report options:

• The Index Usage Report option (recommended configuration) presents information about the expected relative usage of the recommended indexes and their estimated sizes.
• The Query Cost Report option indicates to the user the estimated reduction or increase in the cost for execution of the most expensive, 100 Transact-SQL statements in the workload file if the recommended configuration is accepted.
• The Table Analysis Report option provides information about the relative hits of the queries in the workload by tables in the database.
• The Workload Analysis Report option provides information about the relative frequencies of SELECT, INSERT, UPDATE, and DELETE queries and their relative impact on total cost of the workload.
• The Tuning Summary Report option provides important summary information about the execution of the Index Tuning Wizard. In particular, this report indicates if the recommendations suggest dropping indexes. The report also indicates the total number of queries in the workload that were considered by the Index Tuning Wizard.

Finally, the Index Tuning Wizard allows the scheduling of a task to update the existing index configuration. The index creation/alteration step can be initiated immediately or can be scheduled to occur at a specific date and time. In addition, a script to perform the index update can be created. This is particularly useful because the index recommendations can be ported from the test computer to production computers by using the script. Furthermore, the script makes it easy to identify the indexes that will be dropped if the recommendations of the Index Tuning Wizard are accepted. Examining the script identifies two essential components of index tuning: a set of indexes and a set of statistics. Executing the recommendations to create a set of statistics is vital to harnessing the full benefits of indexing. This is because the query processor exploits statistical information during query optimization to determine whether to use an index.