www.TuneSqlServer.com - info@TuneSqlServer.com - Call Chris Dickey 858-274-6909

What About SQL Server 2000 Index Tuning Wizard?

It is only a first step towards increased performance. Keep going to speed up SQL Server!

Many people believe that Index Tuning Wizard is able to tune SQL Server 2000 so that it will perform at full potential. Once they have used the tool, they feel they have done enough. If the system is still slow, which it usually is, then the solution must be more hardware. Wrong! Index Tuning Wizard is free. Sometimes it works OK. It certainly can't solve your tough performance problems unless you happen to be extremely lucky. If you are unlucky, you may even end up with very detrimental clustered index choices that it added for you. Anybody who has spent time becoming an expert at SQL Server tuning knows that Index Tuning Wizard usually says that nothing can improve a situation when the facts are that a lot can be done.

Kimberly Tripp, a well known SQL Server expert, was asked about Index Tuning Wizard in a recent interview. She reminded the interviewer about a query that she had discussed where she had reduced the time from 5 minutes down to seconds. She said she tried out Index Tuning Wizard on that query to see what it might suggest. It had no suggestion for improvement! Yet she was able to make a huge improvement using her own tuning skills. I have also found this to be the case. I used to try out the Index Wizard but gave up on it. I found that I could do much better by studying Query Analyzer graphical query plans and using my best judgement based on experience. I was always suprised when I could speed up a query by a factor of 10, and yet Index Tuning Wizard hadn't given any index recommendations. Eventually I realized that the Wizard is much more limited in its capabilities than what people expect after reading all the articles about it. To be honest, there is a lot of misinformation and confusion about SQL Server 2000 performance tuning published on the Internet. It is difficult for DBA's, developers and technical managers, who don't have the time to be tuning experts, to figure out what really works.

I just worked on a project where the client really needed a performance boost. There were busy periods on his company's web site when SQL Server 2000 was hitting its head hard on the CPU ceiling. Page response times were falling below acceptable levels. What to do? As usual, my client was thinking that the only solution might be to buy more hardware. He had just upgraded to 8GB of RAM trying to outrun his performance headaches. And, the system already had 4 fast hyperthreaded Xeon processors running SQL Server 2000 Enterprise Edition. My client had tried using Index Tuning Wizard. It did add indexes and made improvements. But then the wizard gave up saying it had done all that could be done. It had no more recommendations to offer.

After Index Tuning Wizard was done, the company gave me the chance to work on the system to see what I could do. I increased SQL Server performance by over 500%. Guess what? I added a lot of indexes that were needed. Better indexes are the easiest fix to implement with no application changes required. Effective indexing made the huge difference in performance. But, there is no silver bullet. It isn't easy to figure out what the best indexes will be. It still takes knowledge of lots of performance details and experience to formulate effective improvements. Many of the new indexes I chose reduced durations of common queries from 200-500ms to 0-50ms. Some of the big improvements in overall system performance were also the result of better clustered index choices. The index changes reduced CPU utilization dramatically because of the combination of less data being scanned in RAM, not as many locks being held and fewer hash joins. The end result: My client got a much faster responding system that Index Tuning Wizard didn't have a clue about!

It still takes brainpower, tedious work and experimentation to make SQL Server 2000 run like the fast racecar that it is. If you have questions about the details of SQL Server 2000 Performance Tuning, please give me a phone call to get accurate answers. I think you will be suprised at how fast SQL Server 2000 can run after being tuned by an expert performance consultant. Your customers and management will notice the big difference!

Chris Dickey

DotNetRocks interview with Kimberly Tripp discussing the complexities, pitfalls and realities of SQL Server 2000 performance tuning.