SQL Server Index Tuning Wizard Tips

Don't run the SQL Server Profiler or the Index Tuning Wizard on your production SQL Servers. Both tools use SQL Server resources that are best left to your users. Ideally, run them on a workstation connected to the server via your network. In one instance, I was running the Index Wizard on a database with over 800 tables. When the Index Wizard was running, it used over 1GB of virtual memory, greatly slowing my computer. Fortunately, I was using a desktop for the analysis, not my SQL Server. If I had run this same analysis on my production server, my users would have complained loudly. [7.0, 2000] Updated 2-11-2005

*****

Even if you run the Index Tuning Wizard from a computer other than the one where the database you are analyzing resides, running the Index Tuning Wizard still puts a load on your production server. Because of this, you should only run the Index Tuning Wizard when your production database is less busy. Another option is to restore the production to a non-production server, and then run the Index Tuning Wizard against the backup database on the non-production server. [7.0, 2000] Updated 3-15-2005

*****

Once you have tuned your indexes using the Index Tuning Wizard, don’t assume that you are set for life. The type of queries, along with the data often change over time, and you should periodically rerun the Index Tuning Wizard to see if it recommends any new changes based on the mix of queries that change over time. [7.0, 2000] Updated 3-15-2005

*****

Don’t blindly accept every recommendation made by the Index Tuning Wizard. Personally review each recommendation, and based on your knowledge of the database and how it is used, then either accept or not accept the recommendations on a recommendation-by-recommendation basis. For example, the Index Tuning Wizard might recommend adding an index to a table that you know is subject to a tremendous number of INSERTS and UPDATES. Adding an index to such a table may or may not be a good idea.

Also, before blindly taking the Index Tuning wizards recommendations, review the queries that hit the table that the Index Tuning Wizard is recommending adding an index, and see if perhaps the queries themselves are the problem. Perhaps instead of needing a new index, you really need to rewrite one or more queries.

The Index Tuning Wizard may also recommend you drop one or more indexes. Always carefully review this recommendation before removing any indexes. Remember, the Index Tuning Wizard makes its recommendations based on the trace data you provided it. It is very possible that the trace that was used may not include all relevant data. For example, perhaps you run long reports at night that need certain indexes, and this information was not captured in the trace you created. If you were to delete an index needed for these reports, these reports then may take forever to run because they are missing their needed indexes.

Furthermore, don't rely on the Index Tuning Wizard to recommend all of your table's indexes. You should make the original selection of indexes for your tables based on the types of queries you expect to be run against your data. Only use the Index Tuning Wizard as an adjunct to your original work in order to help fine-tune it. [7.0, 2000] Updated 3-15-2005

*****

Sometime, the Index Tuning Wizard will not recommend an index, even if you know that one is needed. This can happen if the queries are complex, or they are part of a larger stored procedure. If you run into this situation, consider breaking up the complex query or stored procedure into smaller queries, and then run these individually through the Index Tuning Wizard. [7.0, 2000] Added 9-1-2000

*****

When the Index Tuning Wizard runs, it creates what are called hypothetical indexes in the sysindexes table. The names of these indexes start with "hind_%". These tables are used by the Index Tuning Wizard to help determine if new indexes should be added to your tables.

Normally, these hypothetical tables are deleted when the Index Wizard is completed, but if the Index Wizard is interrupted before it is completed, it may leave these hypothetical indexes in the sysindexes table.

In some cases, the existence of these tables can lead to an unusual performance problem. What can happen is that some stored procedures may be forced to recompile every time they run, even if they should not be recompiled.

The best way to ensure that you don't have any unnecessary "hind_%" tables in your sysindexes table is to run a script provided by Microsoft, that can be found at http://support.microsoft.com/support/kb/articles/Q293/1/77.ASP. You can also delete these tables manually from the sysindexes table if you desire.  [7.0, 2000] Updated 4-4-2005