Sunday, September 19, 2004

Index Tuning Wizard - Man's Best Friend

Index Tuning Wizard is a handy tool for any ole Mr. Clean Project. Basically it is a great tool to improve the performance of your databases.

First off, there is only 1 way to access it via Query Analyzer, I believe the option is not available easily, otherwise.

My method of running it, is to pick a slow running coldfusion page, and look for queries taking more than 100 ms.

Then you plop it into query analyzer, and make sure you enable Show Execution Plan, for additional information.

Then you click on Query, then Index Tuning Wizard. This is where the options are simple but the consequences can be great.

First of all, make sure you make up a backup, and that the site is down, not creating any more database interactions while you make these changes.

Also if you are working on big tables, the actual process of automatically selecting and adding indexes can take much longer, so be prepared to give it the time and resources it needs.

Then I start with the medium level of index seeking, and i keep checked 'Keep Current Indexes', so that it doesn't drop anything.

Then it will take it's time to come up with indexes, and just be patient, and let it happen, and then you have some additional choices.

Such as whether you have a dev sql server, you may want to save the scripts it creates to take action to then be run on dev, after your production database is up to snuff.

All of this is what I just did for my current job, this Saturday. To help improve performance.

All Of what I try to teach is to help any of us, have a stable and scalible platform of database,coding, and standards.

Maybe this is boring, because it isn't the hip or trendy xml, java, but how relevent is it to pursue that, if your platform for your sites, isn't great.

Cover your bases, and make sure you are optimal before you explore anything non-related to coldfusion's main purpose.