RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Reap the Benefits of SQL Server's New Database Tuning Advisor

The Database Tuning Advisor (DTA) is an improvement over the old Index Tuning Wizard. Learn how to access the advanced features of DTA from the command line with a custom configuration.


atabase Tuning Advisor (DTA), the SQL Server 2005 replacement for the Index Tuning Wizard (ITW), has two entry points:

  1. A GUI that practically drives itself
  2. A command line interface with all its traditional gnarly-ness

The behavior under each interface is a little different. Using the command line, you can load arguments to the DTA utility with an XML file. The GUI does not provide the flexibility of XML to deliver startup information for analysis (but it can be imported and exported), nor does it allow you to ape a production instance from system tables. Instead, you can use the GUI to save a DTA session. In doing so, you create a skeletal XML file, which you can edit to add personal tuning enhancements to the session. This is analogous to using Profiler as a macro recorder; it enables you to watch the command syntax as it is generated, and learn how to do it from T-SQL instead of point-and-clicking.

In addition to using an XML file to deliver arguments to the DTA utility, the command line interface lets DTA ape a production instance's environment. This is achieved not by moving tables around, but by using a copy of the production instance's index statistics and metadata. This effectively recreates a target environment without the need to migrate a volume of data onto a development server or laptop. (If you need the real thing, see "Create a Developer's Extract of a Production Database"). From the database and server, DTA creates a shell database that you can tune by using the TestServer sub-element and the TuningOptions element.

Don't get me wrong; the GUI is great. I simply needed to discuss its limitations.

As a consequence of XML informing DTA of the user's assumptions, a simple, powerful (always a great combination) advantage materializes—and it springs from XML's nature. XML can express a nested relationship, or, viewed another way, it can store multiple values of each instance of something, just like rows in a table. Because XML can express these nests, it has a natural advantage over the command line in that it is the ideal vehicle to deliver alternate scenarios for DTA's evaluation. Because the structure of the input file supports nested objects, it lets DTA evaluate alternative physical structures (indexes or partitions) in its analysis. It looks like the partitioning feature supported by SQL Server 2005 Enterprise Edition and up, so I cannot test it.

Any way you look at it, DTA is an improvement over ITW. The tuning activities using the GUI or the command line interface are the same, and they mirror the traditional approach. Set up a workload and the tool entertains one assumption at a time. It is up to the tester to introduce and manage variations to the test plan.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date