Understanding the Architecture of the Index Tuning Wizard

The Index Tuning Wizard takes as input a workload on a specified database. The tool iterates through several alternative sets of indexes called configurations, and chooses the configuration that results in the lowest cost for the given workload. Evaluating a configuration by materializing it physically is not practical because this approach requires adding and dropping indexes, which can be resource-intensive and affect operational queries on the system. Therefore, the Index Tuning Wizard must simulate a configuration without materializing it. SQL Server 7.0 has been extended to support the ability to simulate a configuration and estimate the cost of evaluating a query for a simulated configuration. The illustration shows the architectural overview of the Index Tuning Wizard and its interaction with SQL Server.

During the course of its execution, the Index Tuning Wizard may have to evaluate the cost of many alternative configurations.

• The Cost Evaluation module exploits the commonality among configurations to reduce the server work needed to estimate expected cost. The basic search module that picks the set of indexes has an iterative structure. The iterative structure arises from the consideration of multicolumn indexes of increasing width until wider indexes exceed the width threshold or fail to provide any additional reduction in the cost of the workload.
• The Candidate Index Selection module examines each query in the workload and helps eliminate from further consideration a large number of indexes from the current set that provide no tangible benefit for any query in the workload. The resulting candidate indexes provide significant improvement potentially to one or more queries in the workload.
• The Configuration Enumeration module uses a search algorithm to intelligently search the space of candidate indexes and pick a configuration with low total cost.
• The Multicolumn Index Generation module determines the initial set of multicolumn indexes to consider in the next iteration, based on indexes chosen by the configuration enumeration module in the current iteration.

In summary, the Index Tuning Wizard works with the query processor to determine the viability of a configuration. The wizard uses workload information and is therefore able to tune the selection of indexes to the expected usage of the system. By taking into account the space of multicolumn indexes, the wizard finds indexes appropriate for index-only access. Finally, the wizard has been designed to be scalable and can handle large schemas as well as large workloads by staging its execution steps appropriately.