Indexes the easy way with the MS SQL Server Index Tuning Wizard

I’ve created a quick (4-minute) Flash screen-capture video that shows you how to quickly make indexes to speed up your queries under MS SQL Server. Obviously, it’s aimed not so much at DBAs, but at web developers who aren’t DBAs but tend to have to do all the database work anyway.

Transcript follows:

As if web geeks weren’t busy enough, our user base is quickly getting used to the new AJAX-ified web where data magically appears with sub-second response times. This means that we now have to actually do our jobs and make things run at ludicrous speed. In most web apps, the choke point is the database server, primarily because the queries aren’t optimized and there aren’t any indexes. I can’t tell you how many contracting jobs I’ve started where the first few days was spent just making indexes. I’m not going to get into the wonders of set theory and relational database theory, but I will show you the quickest way to speed things up by an order of magnitude on a SQL Server.

Anyone who has used the Microsoft OLAP services knows that the system has a neat feature that logs all of the queries against it and can then review those queries and optimize its own indexes. SQL Server doesn’t have quite the polish on it that the OLAP tuner does, but you can accomplish basically the same thing. Let’s start with a moderately complex query like this one. When you are in the Query Analyzer, it has a spiff feature called the Index Tuning Wizard. Select the query you want to tune, then run the wizard from the Query menu. I normally change the mode to thorough, then go into the Advanced options and remove the query limit. The default workload selection in this case is the SQL Query Analyzer Selection, which is what we want to tune.

Select all tables and let the tuning begin. After a bit, sometimes a while depending on your query and your existing indexes, you’ll get back a list of recommended indexes. You can drill down into the analysis if you want, but I recommend that at the bare minimum you at least take a look at the columns that are about to be indexed and decide if they make sense for you. It’s a little counter-intuitive, but the check marks DO NOT mean that it’s a new index, the little yellow stars do. If you decide you like the recommendations, continue on and you can shoose to apply the changes now or later. It’s that simple.

Of course, that’s all fine and good for static queries. But what about systems where the queries are dynamic, sometimes extemely so? In that case, your best bet is to run a trace using the SQL Profiler. Most of the options for creating a new trace are straightforward. One thing you probably want to do is limit the filters to only catch the queries you want to tune, such as the ones coming in from the web server. You can catch ColdFusion queries by setting the Application Name to ColdFusion. Aternatively, if your CF server uses a unique login name, you can filter on that, too. Once you run it, it will quietly log all the activity to the server. Let it run for an hour or a day or whatever you think is appropriate.

When you’re ready to tune, be sure to stop the trace first. After that, it’s almost the same as last time. Find your source table, set up all of your options, and have it create your indexes.

Of course, having SQL server do the heavy lifting is nice, but you should still review the indexes that it suggests. Also, I hate to say it but you are SOL if your SQL Server is in a shared hosting environment. Unless you have SA rights, you can’t run the trace or the index tuning wizard. Your best bet at that point is to run the wizard against your development server and have it script out the new indexes, which you can then apply to your production server.

Happy indexing, everyone!

By Rick Osborne

I am a web geek who has been doing this sort of thing entirely too long. I rant, I muse, I whine. That is, I am not at all atypical for my breed.