I’m currently working on a property tax consulting startup, and I was noticing that some queries were running slowly. I’m familiar with database indexing, but I wanted to see what the new Database Tuning Advisor that comes with SQL Server 2005 could come up with. It supposedly helps database newbs tune their database without having to know how to create indexes, etc. Seasoned DBAs will probably gasp in horror (with good reason) at automated tuning processes, but what the heck…let’s take her for a spin.

Don’t Start Without a Trace

The first thing you want to do is create a trace file containing the SQL statements you want analyzed. So fire up SQL Server Profiler, click File->New Trace, and connect to your database. In the trace properties screen you’ll want to use the Tuning template, click Save to File, choose a filename, check Server processes trace data, and click Run. See the screenshot below for details.

Now you need to execute the SQL statements that are running slowly. In my case, I just run through a number of test cases in my web application, ensuring realistic database calls. But you could also run SQL statements directly from Query Analyzer or SQL Server Management Studio. You’ll need to ensure that your database command timeout is set high enough so your queries finish running instead of rolling back.

Once the SQL statements have finished, stop the trace and exit SQL Server Profiler. You now have a trace file containing all the SQL statements you want tuned. It’s a binary file, so don’t bother reading it in Notepad, although you can open it up and view it using the Profiler.

Generate Database Tuning Recommendations

Now it’s time to tune your database by starting a new Session in the Database Engine Tuning Advisor (DTA — don’t ask why the E is dropped). The DTA groups its recommendations into Sessions. Each session starts out with a Workload (a list of SQL statements to evaluate, e.g. from your trace file) and a set of databases & tables to tune. A Session can either be used to generate tuning recommendations (e.g. Indexes, Statistics, etc), or evaluate pre-specified recommendations and let you know how well they work.

So, open up the DTA — it’ll start making a new Session for you automatically. Connect to your database, select the trace file, choose the database to be tuned from the database dropdown, and put checkboxes next to the databases/tables you want to tune. I didn’t bother with the Tuning Options tab, but you can take a look out of curiosity.

Now click Start Analysis at the top, and wait a bit. The Tuning Advisor will run the SQL statements from the trace file, think a bit, and then spit out some recommendations. In my case (see below) it recommended some indexes and recomputing some statistics. It also gave a guesstimate on the speed improvement.

Using the Recommendations

Now you have a set of recommendations, and you have a few choices under the Actions menu item. You can Save Recommendations, which will generate a .sql script file you can apply to your database. You can Apply Recommendations, which just means the Tuning Advisor will apply them to your database, either now or at a specific time (like 2am) in the future. You can also Evaluate Recommendations, allowing you to try them out & see how much they improve performance, without permanently affecting your database. Before Saving, Applying, or Evaluating, you can uncheck any recommendations you want to skip.

I decided to evaluate the recommendations, which creates a new Session (11:32:46 AM) displayed a screen like the one below. Note how the tabs on the top allow you to switch between various Sessions. The tab on the left (the one from 11:08:53 AM) is the first Session that generated the recommendations.

Now that we’ve set up a “what-if” Session, go ahead and click Start Analysis again, and a Progress screen will come up. The Tuning Advisor will apply the changes, run some comparison tests, undo the changes, and display the results. In the reports tab, you can view reports like Statement detail report (see below) to see how much faster your queries are executing. You can also see how much disk space the indexes take up, and other information.

If you’re happy with the results, you can go ahead and Save or Apply the recommendations.

But let’s say that you want to try some of the recommendations but not others. If so, you can go back to the original set of recommendations (click its tab, or double click it in the Session Monitor window on the left), check/uncheck some recommendations you want to try, and Evaluate Recommendations again. The ability to play with different sets of recommendations and evaluate them without permanently affecting your database is handy and powerful.

Once I had a set of recommendations whose results I liked, I applied them to my database. I then fired up my application and run through a few test cases, and it ran much faster (as I suspected).

Final Thoughts

The SQL Server Database Tuning Advisor is a nice tool. It probably won’t out-tune a skilled DBA, but it does a decent job in recommending indexes. I also like the ability to try “what if” scenarios to evaluate different indexes.

I had a few beefs, however. I didn’t like the default names of the recommended indexes, but there was no way to edit them in the GUI. Similarly, there’s no way to use the DTA to evaluate your own recommendations — you can only evaluate recommendations that the DTA comes up with*.

Overall, though, it was a fun tool, and easy to use.

* – you can actually use the DTA with custom recommendations (i.e. ones that you came up with) by editing XML files and importing them into the DTA using a command line tool. You can also edit existing DTA recommendations by exporting them to XML, editing them, and re-importing them. I think that’s pretty lame that you can’t use the GUI for it, but oh well.