New Database Engine Tuning Advisor

  • Article

In SQL Server 2005, Database Engine Tuning Advisor replaces the Index Tuning Wizard in previous releases of SQL Server. Database Engine Tuning Advisor has been enhanced to improve the quality of recommendations, types of events evaluated, and the types of physical design structures considered. Physical design structures include clustered indexes, nonclustered indexes, indexed views, and partitioning.

In SQL Server 2005, Database Engine Tuning Advisor provides two user interfaces: a graphical user interface (GUI) based tool, and a command-line utility, dta.exe, which allows you to use Database Engine Tuning Advisor functionality in applications and scripts.

Database Engine Tuning Advisor Features

Database Engine Tuning Advisor can be used to tune SQL Server 2000 and SQL Server 2005 databases. For information about which tuning options are not supported for SQL Server 2005 and SQL Server 2000, see Unsupported Tuning Options.

The following sections describe the new tuning features that Database Engine Tuning Advisor provides.

Using Database Engine Tuning Advisor to Tune SQL Server 2000

If you want to tune SQL Server 2000 databases, Database Engine Tuning Advisor provides the following new tuning features:

  • Time bound tuning. You can control how much time the Database Engine Tuning Advisor spends analyzing a workload. The quality of the recommendations improves as the amount of time increases. For more information, see Limiting Tuning Duration and Events.
  • Tune across multiple databases. You can tune a workload that involves multiple databases. The Database Engine Tuning Advisor can recommend indexes, indexed views, or partitioning on any of the databases in the workload. For more information, see Tuning Multiple Databases.
  • Tune a broader class of events and triggers. You can include workloads with the following classes of events:
  • Tuning log. Database Engine Tuning Advisor writes to the tuning log all events that it cannot tune and provides a message that gives a reason why the event could not be tuned. You can view the log during the tuning session to determine whether Database Engine Tuning Advisor can tune the events in your workload. For more information, see About the Tuning Log.
  • What-if analysis. The Database Engine Tuning Advisor user-specified configuration feature supports what-if analysis. The user-specified feature allows you to specify a configuration of existing and hypothetical physical design structures in an XML input file. Then you can use Database Engine Tuning Advisor to evaluate the effects of these physical design structures without incurring the overhead of implementing the configuration before you begin tuning. For more information, see Exploratory Analysis Using Database Engine Tuning Advisor.
  • More control over tuning options. Database Engine Tuning Advisor allows you to specify a greater variety of tuning options. For example, you can specify that Database Engine Tuning Advisor consider adding nonclustered indexes or keep all existing clustered indexes when it generates a recommendation. For more information, see Available Tuning Options.
  • XML support. Database Engine Tuning Advisor can take an XML file as input or generate recommendations in XML. A Database Engine Tuning Advisor XML schema is published and available at the following URL:
    https://schemas.microsoft.com/sqlserver/

Using Database Engine Tuning Advisor to Tune SQL Server 2005

If you want to tune SQL Server 2005 databases, Database Engine Tuning Advisor provides the following new tuning features in addition to those that are listed for SQL Server 2000:

  • Partitioning recommendations. Database Engine Tuning Advisor can also recommend partitioning when appropriate to improve performance and manageability of large tables. For more information, see Available Tuning Options.
  • Supports using a test server to reduce the production server tuning load. Database Engine Tuning Advisor tunes a database on a production server by offloading most of the tuning load onto a test server. It does this by using the production server hardware configuration information and without actually copying the data from the production server to the test server. Database Engine Tuning Advisor does not copy actual data from the production server to the test server. Instead, it only copies the metadata and necessary statistics. For more information, see Reducing the Production Server Tuning Load.
  • Members of the db_owner fixed database role can tune their databases. In addition to members of the sysadmin fixed server role, members of the db_owners fixed database role can use Database Engine Tuning Advisor to tune databases that they own. For more information, see Permissions Required to Run Database Engine Tuning Advisor.

See Also

Other Resources

Differences Between Database Engine Tuning Advisor and Index Tuning Wizard
Database Engine Tuning Advisor Features
Tuning the Physical Database Design
dta Utility

Help and Information

Getting SQL Server 2005 Assistance