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.


  1. Anonymous1:13 AM

    Is the Index Tuning Wizard some general purpose tool that will work with any database?

  2. Anonymous5:24 AM

    I never read about that Index Tunning Wizard is the perfect solution for performance tunning. Plus index tunning wizard is not handy tool at all, As a DEVELOPER role you have to know about what things will be queried, some of colums needs fillfactor 80% and some are 100% or 0%. Plus you have to view the sql server setting detail SP_CONFIGURE. how much minimun ram is dedicated for queries, and alot more things before you go for index tunning wizard. Please Read MS SQL SERVER Manual dont post like these things, whatever are suited to you, BLOG is very important for Learning Developers , SO things should be mentioned in a way that if developers following them, then they get the best results. One more thing is bad in index tunning wizard that creates a statistic, that have problem on server, REINDEX and INDEX DEFRAGMENTATION. Please must read about creating statistics and above all capital words details.

  3. Well it's sort of a cheat, an easier way than having to manually go thru every table, and examine for possible relationships.

    It is just much faster to have it figure out what fields to be indexed. Of course no solution is perfect, but when you have a database that is having performance issues, i follow the following steps:

    1. I make sure all joined tables in the queries are indexed in their joining fields. To automate this I drop the query into Query Analyzer and use Index Tuning Wizard, which is only available in SQL Server 2000, so far.
    2. I look thru any looping queries, or queries with select *, and either clean up the queries, or move some nasty queries into views or stored procedures.
    3. I also look to use dts to automatically either move or delete data older than x days. After a while, even with a tuned database, can be a performance hog with millions of records. So we always have to plan to only have essential data on the production sql server, and move older data to an archival sql server.

    Anyways that's my thoughts, share yours.

  4. Anonymous2:56 PM

    For pity's sake would you PLEASE LEARN TO USE COMMAS CORRECTLY? Whether anyone reading this agrees or disagrees with the content of your posts, your horrendous use of punctuation make these entries sound like they are being written by a 10 year old.

  5. Anonymous8:54 PM

    Hmm, comma use aside, you got soem valid points. For those of us in the other half of the DB hemisphere using Oracle we have explain plan. If you are using TOAD, as you really should be :), you can get nice plan output and easily build indicies for your table. Oh, don't forget hints too. Always loved that one, give the Oracle a hint. ha!