SQL Server Database Engine Tuning Advisor (DTA)

The Database Engine Tuning Advisor analyzes a "workload" that you create. The workload can be created from a SQL Profiler trace or from T-SQL statements.

Key points:

  1. DTA can be used either by launching the graphical user interface or by running dta.exe from a command line.

  2. DTA must be run by someone who is a sysadmin on the database server.

  3. To analyze a workload, a user must be a member of the db_owner database role.

  4. The workload can be:

    1. A .trc file created by a SQL Profiler trace

    2. A .sql file containing whatever SQL commands you have created in int

    3. A table created by a SQL Profiler trace

    4. If pulling from a table, the table must be on the SQL Server you are running DTA on.

  5. After analyzing a workload, Database Engine Tuning Advisor can recommend that you add, remove, or modify physical design structures in your databases. The advisor can also recommend what statistics should be collected to back up physical design structures. The physical design structures include clustered indexes, nonclustered indexes, indexed views, and partitioning. Database Engine Tuning Advisor recommends a set of physical design structures that reduces the query optimizers estimated cost of the workload.

  6. You can optionally offload the tuning process to a test server by running the DTA from a command line. The command line accepts parameters from an XML file. In the XML file, you specify the name of the test server. See http://technet.microsoft.com/en-us/library/ms190389(SQL.100).aspx