Home > Articles > Data > SQL Server

SQL Server Reference Guide

Hosted by

Toggle Open Guide Table of ContentsGuide Contents

Close Table of ContentsGuide Contents

Close Table of Contents

Performance Monitoring Tools: SQL Server 2000 Index Tuning Wizard

Last updated Mar 28, 2003.

We're still in the second phase of the performance tuning process, and we need a method to be able to evaluate the indexes and their efficacy on a system. Indexes have one of the highest impacts on performance within the database, followed by proper design. As I explained in a previous tutorial, indexes speed up reads, but they slow down writes, so it’s often a juggling act to move the bottlenecks around. That's where the design issue comes into play. You should ensure that the data is spread across the tables in the most optimized fashions for reads and writes. If the table is written to and read the same amount, the decision for indexes becomes far more difficult. You just can’t have it both ways.

This situation reminds me of a kid who tried to have it both ways when he started in a job bagging groceries at a gourmet food market. The manager watched him and admired how quickly he could adapt to the difficult shoppers the store had. In one instance, a demanding elderly lady told him she only wanted half a head of lettuce. "Why certainly, madam." he replied. Taking the lettuce, he walked over to the produce manager. He said "Some old biddy wants half a head of lettuce." Turning around, he noticed the lady had followed and was standing and glaring at him. "And this young lady" he said, "would like the other half." The manager was so impressed that he brought the boy into his office. "Where are you from, son?" he asked. "Minnesota, sir." he replied with a grin. "Home of the ugliest women and the great hockey players in the world." The manager frowned. "I’ll have you know my wife is from Minnesota." "Is that that right?" the young man asked, not missing a beat. "What team is she on?"

In this tutorial I’ll show you how to determine the best indexes to pick for your tables using a tool that Microsoft provides right inside SQL Server, called the Index Tuning Wizard. I'm focusing here on SQL Server version 2000, since SQL Server 2005 has a different tool that evaluates not only the indexes but other database objects as well. I'll cover that in another tutorial.

Using the Index Tuning Wizard is fairly straightforward. You simply run it, supply it a representative set of queries (called a workload) and examine the results. The Wizard will suggest changes or additions to your indexes to speed the queries. Since it has direct access to the real activity within the server, it can accurately predict the best performance. I'll walk you through a very simple outline of that process here.

Using this wizard isn’t a panacea, however. You’ll need to provide an accurate workload, which is not always an easy task. It also isn’t very useful for applications that allow a lot of ad-hoc queries, since there’s no easy way to predict what those will ask for.

Another assumption is that your query is properly tuned to begin with. No amount of indexes can make up for a poorly written query. Most queries can benefit from one question: Is this query asking for the proper data, in the proper way?

So let’s get started. I normally Index Tuning Wizard on a test system, but I get the workload from the production system, since it has actual users working on it. I don’t want to change anything on the production system without going through the proper procedure, which includes testing, and that's why I run the workload on a test system.

So why not run this on the development system? Isn’t that the "proper" procedure I’ve talked about before? The reason I don’t do that is that in the environments I work in the development system is much smaller than the testing and production systems. Also, indexes aren’t really part of the development effort per se, but part of the administration of the system. I know a few developers will disagree with me on that, but that’s OK. If your shop has you follow another procedure, just ensure that you evaluate the indexes to ensure that they add the right value.

To begin, open Enterprise Manager, drill down to the database in question and select Tools | Wizards and then Management | Index Tuning Wizard. After the introduction screen, the first set of choices involves the current indexes and the depth of analysis.

The first checkbox asks whether you want to keep the current indexes. If the system is brand new and hasn’t been tested yet, de-select this box. If the workload isn’t representative or the developers put a lot of thought into performance, however, you wouldn’t necessarily do this. You might still do it if the workload is representative, just to make sure that the Wizard agrees with the developers.

The radio box below that selects the level of depth the system will use to evaluate the indexes. If you’ve got the luxury of time select Thorough, but if the workload is complex select Medium. The Fast option is less useful, since it doesn't evaluate the indexes very deeply.

After you click Next, select the workload you want the server to evaluate. There are two types. The first is a SQL script file that has the queries you plan to run, and the second is a SQL Trace, gathered from SQL Profiler. I normally recommend using the SQL Trace file, since it’s almost impossible to the get the right order and amount of statements to get an accurate load any other way. You can also set some advanced options on this panel that deal with what will and won’t be included on the evaluation.

After you click Next, the system evaluates the indexes based on the information you’ve supplied. When that completes, the Wizard presents a reporting panel, where you can see the results of all its work. I usually pull down each of the categories and save the results out to a file. I do this so that I can evaluate what the system found, and so I can learn more about the best way to tune my indexes. I also save these reports in the development tracking system so that our group has a history of the work that has been done.

Finally you're asked whether you want to apply what the system has suggested or save the results out to a file. I always save this out to a file, so that I can evaluate the reports I collected a moment ago to make sure this is what I want. I also save that file in the development system, since I want to track every single change that is made to any database I’m responsible for. Not only that, it provides a back-out strategy in case things don’t go exactly like I expected.

The Index Tuning Wizard is simple to run, effective, and very useful. That simplicity is sometimes a deceptive, but if you remember the caveats about getting a representative workload and carefully evaluating the before and after performance in the real-world, It can be incredibly helpful.

Informit Articles and Sample Chapters

I’ve got an excerpt from my SQL Server 2000 Administration handbook on SQL Profiler here.

Online Resources

Microsoft has a great article on the Wizard here. The SQL Server 7 version is here.