Spotlight on the Index Tuning Wizard

The Index Tuning Wizard is a tool that accepts a workload of activity against your SQL Server and makes recommendations about creating appropriate indexes for the workload. The input workload can be a trace file or table, among other options. In "Boost Performance with the Index Tuning Wizard," April 2000, I discussed the wizard in detail. Here, I'd like to mention some significant new options available with the Index Tuning Wizard that comes with SQL Server 2000. One of the wizard's most important new features is that it can make recommendations about creating indexed views, which are new in SQL Server 2000. The wizard can also recommend how to create the views themselves.

The Index Tuning Wizard in SQL Server 2000 also lets you choose from three possible tuning modes: Fast, Medium, and Thorough. These modes give you more flexibility in controlling how much load the wizard will put on the server, how long the tuning process will take, and how efficient the wizard's recommendations will be.

Also, the wizard displays the number of actual rows in each table you're tuning and lets you specify the number of projected rows. Then, if you're tuning a system in which some tables don't reflect the number of rows that you expect in the production system, you can specify the number of projected rows and the wizard can give you more accurate recommendations.

Another new and important feature is that you can fully automate the index-tuning process by using the command-line utility itwiz.exe. The itwiz.exe utility accepts command-line arguments in which you can specify all the tuning options you're interested in, and it runs in batch mode. You can even use a SQLServerAgent job to schedule the invocation of itwiz.exe on a recurring basis, say once a month, and examine the index recommendations saved in the .sql output script file. SQL Server Books Online (BOL) fully documents the itwiz.exe utility along with its arguments and gives some examples for running it.