Index Tuning Wizard for Microsoft SQL Server 2000
Summary: This article describes the Index Tuning Wizard in Microsoft SQL Server 2000. The Index Tuning Wizard simplifies the task of identifying which indexes to create in a table and also optionally generates scripts to create them by analyzing a user-supplied workload. Various modes are offered for tuning both indexes and indexed views. (9 printed pages)
Table of Contents
Why Is Index and Indexed View Selection Difficult?
Using the Index Tuning Wizard
Customizing the Index Tuning Wizard
Analyzing Index Tuning Wizard Output
Starting the Wizard
Getting the Most from the Index Tuning Wizard
Understanding the Architecture of the Index Tuning Wizard
For More Information
Identifying an appropriate set of indexes for a database system is a complex undertaking. For a given database, there are many possible indexes (such as single and multi-column) and modern query processors exploit indexes in sophisticated ways. While the number of considered indexes is very large, the number of indexed views (materialized views) that must be considered for a database is even larger. In principle, an indexed view of any subset of the tables referenced in a query may be useful in answering the query. Furthermore, for any given subset of tables in the query, we can define an indexed view containing any subset of the conditions in the query on that subset of tables. The number of considered indexed views is further increased since an indexed view may also contain grouping columns and aggregation expressions (that is, SUM). Finally, as with tables, it is possible to define a clustered index on a view, as well as multiple non-clustered indexes on the view.
The choice of indexes and indexed views cannot be done in isolation of each other. One reason for this is that the presence of an index can make an indexed view more attractive to the optimizer, and vice versa. Ignoring such interactions can result in poor quality recommendations. Furthermore, since both indexes and indexed views may be useful for a given query, picking them separately can lead to redundant recommendations that incur high storage and update overhead. Finally, while tuning the physical design of a database, we must trade off the performance requirements of a diverse set of queries and updates that the database system must support. Therefore, identifying an appropriate physical design is a challenging task.
The Index Tuning Wizard in Microsoft® SQL Server™ 2000 can help you avoid these problems. It recommends the right mix of indexes and indexed views for the given workload of queries and updates. To make its decision, it employs usage statistics, it consults the query processor in evaluating the usefulness of indexes and indexed views, and it searches the space of possible indexes and indexed views.
To use the Index Tuning Wizard, the server name and the database name must be specified. In addition, the wizard requires a workload (specified as a file or a table) as its input. The goal of the Index Tuning Wizard is to recommend a physical design that best optimizes the aggregate performance of the queries and updates in the workload. Any SQL Profiler trace can be used as a workload. SQL Profiler is a graphical SQL Server client tool that makes it possible to monitor and record engine events in a file or table.
A typical entry in such a workload may consist of a variety of fields: event-class, text of the event (for example, text of the Transact-SQL query), start-time, and duration of the event. The Index Tuning Wizard can extract engine relevant events (such as Transact-SQL statements) and fields from a SQL Profiler trace automatically. Alternatively, any file that contains a set of Transact-SQL statements can also be used as a workload. Such files may contain customer benchmarks for which the index selection must be tuned. Because the Index Tuning Wizard does not execute any batch of queries in the workload during analysis, it cannot provide recommendations for a batch that references temporary objects.
The Index Tuning Wizard produces a recommendation for physical design and optionally generates the necessary script to implement it. It also estimates the expected improvement that may accrue from implementing the recommendation. Furthermore, the recommendation is accompanied by a range of reports that provide further insight into the impact of the recommendations.
The Index Tuning Wizard provides the following set of options to customize index selection:
Keep all existing indexes
This option can be exercised on the Server and Database Choice dialog box. By selecting this option, the user instructs the wizard not to drop any of the existing indexes and indexed views. This allows conservative use of the tool and incremental changes in the design. Unless the user is experienced, it is recommended that this mode of operation be used.
Add indexed views
This option is enabled when connecting to an Enterprise or Developer Edition version of SQL Server 2000, and is checked by default. This option determines whether or not indexed views, in addition to indexes, will be considered by the Index Tuning Wizard.
For large workload files and large databases, tuning may require a significant amount of time and resources. This option allows the user to trade off the running time of the Index Tuning Wizard with the thoroughness of the analysis. The Fast mode consumes the least amount of time and resources and produces a quick recommendation that is based on query analysis and limited interaction with the query processor. (Indexed views are not proposed in the Fast mode.) The Medium mode, which is the default, proposes indexes and indexed views and is significantly faster than the Thorough mode for large workloads. Although the Medium mode of operation searches fewer possibilities, in many cases it is able to provide a respectable set of recommendations. The Thorough mode consumes the maximum amount of time and resources, but it also gives the highest quality recommendation for the workload. Below is a comparison of the running times and expected improvements of the Index Tuning Wizard (ITW) in different tuning modes for a sample workload on a 1.2 GB database:
|Server Version||Features Tuned||Tuning Mode||ITW running time||Expected Improvement|
|SQL Server 7.0||Indexes Only||Thorough||40 min.|
|SQL Server 2000||Indexes Only||Fast||1 min.|
|SQL Server 2000||Indexes Only||Medium||3 min.|
|SQL Server 2000||Indexes and Indexed Views||Medium||4 min.|
|SQL Server 2000||Indexes Only||Thorough||16 min.|
|SQL Server 2000||Indexes and Indexed Views||Thorough||19 min.|
Additional customization options are presented in the Advanced Options screen:
Limit number of workload queries to sample
To improve scalability, the Index Tuning Wizard in SQL Server 2000 supports the ability to randomly sample queries from a workload and restrict tuning to the sampled queries. If this option is checked (which is the default), then the Index Tuning Wizard samples the specified number of queries from the workload. If the workload contains fewer than the specified number of queries, then all queries are tuned. If this option is not checked, then all queries in the workload are tuned. It should be noted that the Index Tuning Wizard considers the specified number of queries, not events. Specifically, events that are not considered by the Index Tuning Wizard to be queries are not counted towards the limit.
Maximum space for the recommendation
This parameter reflects the limit on the sum total of storage for all data and indexes in the chosen database. By default, this parameter is set to three times the size of the current data, or the available disk space on all attached drives, whichever is smaller. The current data is defined as all heaps and clustered indexes on tables and views. Indexes are defined as all non-clustered indexes on tables and views. In case the Keep all indexes option is selected, the specified limit also includes the storage required for existing indexes. Because databases grow over time, the administrator should adjust the parameter so that the assigned storage is appropriate for the current data size.
Maximum columns per index
This parameter can be tuned to influence the maximum width of indexes on tables as well as views. An index with few columns potentially can be used in many queries in a workload. An index with many columns may enable index-only access and eliminate data scans for some of the queries even though it requires more storage space than an index with fewer columns. Given the complexity of the trade-off, it is recommended that only experienced administrators tune this parameter.
Selecting tables to tune
Another significant way in which the Index Tuning Wizard can be customized is by restricting index tuning to only a subset of all tables by selecting the Select Tables to Tune option. This allows the user to focus the design on selected tables in the database without altering the indexes and indexed views for the remaining tables.
A new feature available in Index Tuning Wizard for SQL Server 2000 enables you to tune the workload for a database where the tables have different sizes than in the current database. In the Select Tables to Tune dialog box, the user can specify the projected number of rows for a table. When performing the tuning, the Index Tuning Wizard takes the projected sizes into account when recommending indexes and indexed views. Table scaling can be used to account for the future growth of the database as well as to facilitate tuning on a test server by porting the recommendations to the production server where the tables may be larger or smaller.
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 table or view on which the index is defined, the order and collation (ASC or DESC) of columns in the index, whether the index is clustered, and whether the index exists. The Index Recommendations dialog box also displays which indexes, if any, were recommended to be dropped by the Index Tuning Wizard. The wizard also produces an estimate of the expected improvement in the execution time of the workload compared to the existing configuration. The Index Tuning Wizard uses the optimizer component of the query processor to project this estimate. However, 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 augmented by a range of reports that provide further analysis of the recommendations and their quantitative impact. These reports affect the decision about whether the recommendations should be accepted or rejected. All the reports can be saved into files for further analysis. In the Index Recommendations dialog box, click Analysis to view these report options:
- Index Usage Report (recommended or current configuration) presents information about the expected relative usage of the recommended or current indexes and their estimated sizes.
- Query Cost Report indicates to the user the estimated reduction or increase in the cost for execution of the 100 most expensive Transact-SQL statements in the workload file, if the recommended configuration is accepted.
- Table Analysis Report provides information about the relative hits of the queries in the workload by tables in the database.
- View-Table Relations Report provides information about the tables referenced by each indexed view in the recommendation.
- Query-Index Relations Report provides information about the indexes and indexed views referenced by the 100 most expensive statements in the workload for the recommended or current set of indexes and indexed views.
- Workload Analysis Report provides information about the relative frequencies of SELECT, INSERT, UPDATE, and DELETE queries and their relative impact on the total cost of the workload.
- Tuning Summary Report provides important summary information about the execution of the Index Tuning Wizard. In particular, this report indicates the number of tables tuned, the number of new indexes and indexed views recommended, and the number of proposed indexes and indexed views to be dropped. The report indicates the total number of queries in the workload that were considered as well as the time taken for analysis by the Index Tuning Wizard.
Finally, the Index Tuning Wizard enables the scheduling of a task to update the existing index configuration. The index creation and 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 and indexed views 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 or an indexed view.
The Index Tuning Wizard can be started from SQL Server Enterprise Manager. On the Tools menu, select Wizards, and then select Management. The Index Tuning Wizard is one of the wizards in the Management group. Alternatively, it can be started from SQL Profiler on the Tools tab of the Profiler menu. After the wizard obtains the necessary user input on required parameters, it carefully begins searching the possible configurations. If the search for the index configurations is terminated during this time, the wizard returns the best available configuration that has been considered thus far. The SQL Server Query Analyzer provides another mode in which the Index Tuning Wizard can be started for a workload consisting of one or more Transact-SQL statements in a Query Analyzer buffer. This is accomplished by selecting one Transact-SQL statement in the buffer and then selecting the Perform index analysis option on the Query tab. The ability to invoke the index tuning capability from SQL Server Query Analyzer is useful, for example, for tuning an under-performing query in an otherwise well-tuned system.
In SQL Server 2000, index tuning can also be invoked from a command-line utility called itwiz. This utility enables scripting of the index tuning process. Tuning using itwiz is similar to tuning via the Index Tuning Wizard interface with the following exceptions: (a) itwiz allows a mode in which only indexed views are considered during tuning, that is, no new indexes on tables are considered; and (b) the Analysis Reports described above are not generated by itwiz. Instead, itwiz always generates a script file as output which contains the statements for implementing the recommendation and additional information about the tuning process, such as the expected improvement, the storage consumed, and the time taken for analysis. This script file can be executed to implement the recommendations.
Because the recommendations of the Index Tuning Wizard are made with respect to a workload file, the single most important step is to select a workload that is representative of the database system's usage. Although tools such as SQL Profiler can help the user record a workload by logging activity on the server over a specified period of time, it is important to ensure that the logged events are representative. Furthermore, the choice of the indexes and indexed views must be reevaluated periodically. In particular, if the data volume, the data distribution, or the queries against the system change, the Index Tuning Wizard must be executed to ensure that the choice of indexes remains sound. Another important aspect to consider is that the projected reduction in the cost of the workload estimated by the index selection tool is based on a statistical summary of data. Therefore, the actual decrease or increase in cost can diverge from the estimation. It is advisable to re-execute the workload with the new index configuration to verify the projected improvement before the index configuration update is applied to production servers.
The following questions and answers provide additional tips for working with the Index Tuning Wizard:
Q: How can I determine the indexes that will be dropped if I accept the recommendations?
A: The Index Recommendations dialog box shows the indexes that will be dropped if the recommendation is accepted. Alternatively, you can also select Save script file in the Schedule Index Update Job dialog box. By examining the script file, you can determine the indexes that will be dropped if you decide to accept the recommendations. If running itwiz, the script file will contain the indexes to be dropped. You can also edit the script to customize the recommendations. For example, you can change the names of the proposed indexes and indexed views.
Q: How can I disable sampling of the workload and ensure that all queries in the workload are tuned?
A: If you are using the Index Tuning Wizard UI, then go to the Advanced Options dialog box and uncheck the Limit number of workload queries to sample check box.
Q: Can I specify that the Index Tuning Wizard consider only indexed views?
A: Not if you use the Index Tuning Wizard UI. However, if you use itwiz you can achieve this by specifying the –f 2 option.
Q: How can I control the time taken by the tuning wizard for analysis?
A: The tuning wizard takes the least time when you set the tuning mode to Fast. It takes the longest time to analyze when the tuning mode is set to Thorough. Generally speaking, the longer time enables the wizard to produce a better recommendation for the physical design.
Q: For a given query, the Index Tuning Wizard suggested an index and I accepted the recommendation. However, when investigating the query plan, the index is not used. Why?
A: When the Index Tuning Wizard makes a recommendation, it uses random sampling to estimate how useful an index will be. However, once the index is created, more accurate statistics are generated with fullscan. The query processor may therefore find the index less useful than originally estimated and it may not use the index in the optimal query plan.
The Index Tuning Wizard takes as input a workload on a specified database. The tool iterates through several alternative sets of indexes and indexed views called configurations. It then 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 2000 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 below (Figure 1) shows the architectural overview of the Index Tuning Wizard and its interaction with SQL Server 2000.
Figure 1. Architectural diagram of the interaction of the Index Tuning Wizard with SQL Server 2000
The Index Tuning Wizard is comprised of the following key components:
- The Syntactic Structure Selection module. Proposes a set of indexes and indexed views that are potentially relevant for the given workload.
- The Candidate Index and Indexed View Selection module. Examines each query in the workload and then helps eliminate from further consideration a large number of indexes and indexed views from the current set that provide no tangible benefit for any query in the workload. The resulting candidate indexes and indexed views potentially provide significant improvements 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 indexed views and picks a configuration with low total cost.
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 and indexed views to the expected usage of the system. By considering indexes and indexed views together, the wizard is able to judiciously trade their choices. Finally, the wizard is scalable and can handle large schema as well as large workloads by staging its execution steps appropriately, and by exposing the tuning modes to enable the user to control the degree of analysis.
These resources can provide more information about the Index Tuning Wizard:
Index Tuning Wizard topic in Microsoft SQL Server 2000 Books Online.
Data Management, Exploration and Mining Group, Microsoft Research.
Agrawal, S., S. Chaudhuri, and V. Narasayya. "Automated Selection of Materialized Views and Indexes for SQL Databases." Proceedings of the Twenty-sixth International Conference on Very Large Databases. Cairo, Egypt, 2000.
Chaudhuri, S. and V. Narasayya. "An Efficient, Cost-driven Index Tuning Wizard for Microsoft SQL Server." Proceedings of the Twenty-third International Conference on Very Large Databases. Athens, Greece, 1997.
Chaudhuri, S. and V. Narasayya. "AutoAdmin What-If Index Analysis Utility." Proceedings of ACM SIGMOD 1998. Seattle, USA.
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS DOCUMENT.
©2000 Microsoft Corporation. All rights reserved.
Microsoft is a registered trademark of Microsoft Corporation in the United States and/or other countries.
Other trademarks and tradenames mentioned herein are the property of their respective owners.