Professional Microsoft® SQL Server® 2008 Administration

15.6. Database Tuning Advisor

It used to be that the DBA had to spend a lot of time reviewing the database design, learning about data distribution, finding and examining in detail the main queries, and then manually tuning indexes to try to find the best set of indexes to suit individual queries. With the DTA, that slow and laborious process is no longer needed. You can use the DTA to tune individual queries as they are being developed, and to tune entire workloads as they become available.

The DTA does this either by analyzing individual queries from SQL Management Studio, or with a SQL Server Profiler trace file. The workload should contain at least one example of each query called, but it doesn't need to contain repeated calls to the same procedure, as you would expect to see in a trace from a production system. This is because the DTA only tunes each unique query; it isn't going to look at the interaction of all the queries in the result set and provide a balanced set of indexes to suit a mix of INSERT, UPDATE, and DELETE statements. It simply looks at each query and provides recommendations to improve that query, so the DBA still has some work to do in deciding which indexes to implement to get the best compromise between insert, update, and delete performance.

Now we'll jump straight into using the DTA to create some indexes.

15.6.1. Using the DTA to Tune Individual Queries

Imagine the following scenario: As a developer DBA, you are writing queries for a new database that ...