Professional SQL Server® 2005 Performance Tuning

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

Figure 11-2. 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 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 ...