Using Database Engine Tuning Advisor

You can access Database Engine Tuning Advisor at the command prompt by using the dta.exe file, or through the application's graphical user interface (GUI). The command-line utility lets you incorporate Database Engine Tuning Advisor functionality into scripts and software programs. The dta utility also takes XML input. The Database Engine Tuning Advisor GUI makes it easy to view existing tuning sessions tuning recommendations.

Note

Database Engine Tuning Advisor stores tuning session data and other information in the msdb database. Implement an appropriate backup strategy for the msdb database to avoid the risk of losing tuning session data.

The following sections contain conceptual information about using both user interfaces of Database Engine Tuning Advisor.

In This Section

• Initializing Database Engine Tuning Advisor
  Contains information about how to initialize Database Engine Tuning Advisor immediately after installing SQL Server. A member of the sysadmin fixed server role must launch the application first before others can use it.

• Starting Database Engine Tuning Advisor
  Describes how to start Database Engine Tuning Advisor from the Start menu, from the Tools menu in SQL Server Management Studio, from the Query Editor in SQL Server Management Studio, and from the Tools menu in SQL Server Profiler.

• Determining Whether Events Can Be Tuned
  Contains information about the types of events that Database Engine Tuning Advisor can tune.

• Tuning a Database
  Contains information about the overall process of tuning the physical database design and provides links to the how-to topics that describe each task.

• Available Tuning Options
  Contains information about all the tuning options that Database Engine Tuning Advisor provides, such as limiting the tuning time, the amount of space consumed by recommendations, and so on.

• Choosing a Database Engine Tuning Advisor Report
  Contains information about each type of analysis report that Database Engine Tuning Advisor generates after consuming a workload.

• Reducing the Production Server Tuning Load
  Contains information about using a test server to offload tuning overhead from the production server.

• Tuning Multiple Databases
  Contains information about how to use Database Engine Tuning Advisor to tune multiple databases.

• Using an XML Input File for Tuning
  Contains information about using an XML input file for tuning, including information about specifying hypothetical configurations for Database Engine Tuning Advisor to analyze.

• Tuning Mode Precedence: When the dta Utility Overrides XML Tuning Input
  Contains information about what tuning options take precedence over others.

• Using Session Monitor to Evaluate Tuning Recommendations
  Contains information about how to use Database Engine Tuning Advisor sessions to compare and evaluate tuning results.