O'Reilly logo

Professional Microsoft® SQL Server® 2008 Administration by Steven Wort, Ross LoForte, Wayne Snyder, Ketan Patel, Brian Knight

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required