Tuning Indexes - Professional SQL Server 2005 Performance Tuning - page 365

Section 2: Tuning with DTA
The Database Tuning Advisor is a physical database design tool that replaces and builds on the
technology in the Index Tuning Wizard in SQL Server 2000. It accepts as input a workload in the form
of a T-SQL script containing a set of SELECT, DELETE, and UPDATE statements or a SQL Profiler trace,
and will output a T-SQL script consisting of recommendations for the creation, dropping and partitioning
of indexes, indexed views, and statistics. It will also give you an estimated performance improvement if
you implement the recommendations. Figure 11-2 shows a high-level architecture of DTA.

Applications

Workload

"What-if"

DTA

SQL Server

Recommendation

Apply

SQL Server Profiler

Figure 11-2

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

DTA does this either by analyzing individual queries from SQL Management Studio or a SQL Server Pro-
filer 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 DTA will only tune 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 will instead simply look at each query and provide recommendations to improve