Because the index recommendations are made with respect to a workload file, the single most important prerequisite is to pick a workload that is representative of the database system’s usage. Although tools such as SQL Server 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 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 statistical summary of data. Therefore, the actual decrease or increase in cost can diverge from the estimation. It is advisable to reexecute 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: Why does the Index Tuning Wizard take so long to complete index recommendations?
A: You can reduce the execution time of the Index Tuning Wizard in several ways. First, make sure that Perform Thorough Analysis in the Select Server and Database dialog box is not selected. Next, consider tuning only a subset of the tables in the database. Finally, reduce the size of the workload file that you are using to significantly speed up the execution of the Index Tuning Wizard.
Q: I ran SQL Server Query Analyzer and want to accept the recommendations. How can I schedule the actual creation of indexes to occur at a later time?
A: You can save the script generated by SQL Server Query Analyzer and schedule that task at a convenient time.
Q: How can I determine the indexes that will be dropped if I accept the recommendations?
A: Select Save script file in the Schedule Index Update Job dialog box instead of applying the proposed changes immediately. By examining the script file, you can determine the indexes that will be dropped if you decide to accept the recommendations. You can edit the script to customize the recommendations if you want.
Q: An error occurred when I invoked Perform Index Analysis from SQL Server Query Analyzer on a query buffer consisting of multiple valid Transact-SQL statements. How do I avoid this?
A: Make sure that the query buffer consists purely of a consecutive set of Transact-SQL statements separated by blank lines. In particular, there should be no GO commands separating the Transact-SQL statements.
Q: Why do SQL Server Query Analyzer and the Index Tuning Wizard give different recommendations for the same query?
A: SQL Server Query Analyzer does not consider the option of building a clustered index. The Index Tuning Wizard may consider building a clustered index if none already exists. Therefore, the recommendations from the two tools may differ even for the same query.