SQL Server Index Tuning Wizard Tips

When you run the Index Tuning Wizard, you have the option to choose if you want a fast, medium, or thorough analysis (only fast or thorough in 7.0). When analyzing large Profiler traces, your choice makes a significant different in how fast the analysis is done. But don’t try to cut corners here, you should always choose a thorough analysis. After all, you want the optimum indexes for your database, so why would you want to do a less thorough analysis and have a less than optimum set of indexes for your database? [7.0, 2000] Updated 4-4-2005

*****

In most cases, using the GUI interface for the Index Tuning Wizard is the best way to perform index analysis. But if you are running a lot of Index Tuning Wizard analysis, you may want to automate this task by using the itwiz command line utility. You can use this utility, along with the proper command line options, to complete any analysis you want, just as with the GUI interface.

In addition, you can use the SQL Server Agent Job Scheduler to schedule any analysis jobs to run at convenient times of the day when users will be less affected. See the SQL Server Books Online to learn how to use this command. [7.0, 2000] Updated 4-4-2005

*****

When using the Index Tuning Wizard to identify potential new indexes, it ignores triggers on any of your tables when they are executed. While the Profiler has the ability to capture trigger code using the SQL:StmtCompleted Event, the Index Tuning Wizard is not able to use it for index analysis on trigger code. This means that you must manually tune all indexes used by your trigger manually. [7.0, 2000] Updated 4-4-2005

]]>

Leave a comment

Your email address will not be published.