How to: Tune a Database by Using the dta Utility

  • Article

The dta utility provides a command prompt executable file that you can use to tune databases. It enables you to use Database Engine Tuning Advisor functionality in batch files and scripts. The dta utility takes trace files, trace tables, and Transact-SQL scripts as workloads. It also takes XML input that conforms to the Database Engine Tuning Advisor XML schema, which is available at this Microsoft Web site.

Consider the following before you begin tuning a workload with the dta utility:

  • When using a trace table as a workload, that table must exist on the same server that Database Engine Tuning Advisor is tuning. If you create the trace table on a different server, then move it to the server that Database Engine Tuning Advisor is tuning.
  • Make sure that tracing has stopped before using a trace table as a workload for Database Engine Tuning Advisor. Database Engine Tuning Advisor does not support using a trace table to which trace events are still being written as a workload.
  • If a tuning session continues running longer than you had anticipated it would run, you can press CTRL+C to stop the tuning session and generate recommendations based on the analysis dta has completed up to this point. You will be prompted to decide whether you want to generate recommendations or not. Press CTRL+C again to stop the tuning session without generating recommendations.

For more information about dta utility syntax and usage examples, see dta Utility.

To tune a database using dta utility default settings

  1. Determine the database features (indexes, indexed views, partitioning) you want Database Engine Tuning Advisor to consider adding, removing, or retaining during analysis. For more information, see About Workloads and Considerations for Using Database Engine Tuning Advisor.

  2. Create a workload. For more information, see How to: Create Workloads.

  3. From a command prompt, enter the following:

    dta -E -D DatabaseName -if WorkloadFile -s SessionName

    where -E specifies that your tuning session uses a trusted connection (instead of a login ID and password), -D specifies the name of the database you want to tune. By default, the utility connects to the default instance of Microsoft SQL Server on the local computer. (Use the -S option to specify a remote database as shown in the following procedure, or to specify a named instance.) The -if option specifies the name and path to a workload file (which can be a Transact-SQL script or a trace file), and -s specifies a name for your tuning session.

    The four options shown here (database name, workload, connection type, and session name) are mandatory.

To tune a remote database or a named instance for a specific duration

  1. Determine the database features (indexes, indexed views, partitioning) you want Database Engine Tuning Advisor to consider adding, removing, or retaining during analysis. For more information, see About Workloads and Considerations for Using Database Engine Tuning Advisor.

  2. Create a workload. For more information, see How to: Create Workloads.

  3. From a command prompt, enter the following:

    dta -S ServerName\Instance -D DatabaseName -it WorkloadTableName 
-U LoginID -P Password -s SessionName -A TuningTimeInMinutes

    where -S specifies a remote server name and instance (or a named instance on the local server) and -D specifies the name of the database you want to tune. The -it option specifies the name of the workload table, -U and-Pspecify the login ID and password to the remote database, -s specifies the tuning session name, and -A specifies the tuning session duration in minutes. By default, the dta utility uses an 8-hour tuning duration. If you would like Database Engine Tuning Advisor to tune a workload for an unlimited amount of time, specify 0 (zero) with the -A option.

To tune a database using an XML input file

  1. Determine the database features (indexes, indexed views, partitioning) you want Database Engine Tuning Advisor to consider adding, removing, or retaining during analysis. For more information, see About Workloads and Considerations for Using Database Engine Tuning Advisor.

  2. Create a workload. For more information, see How to: Create Workloads.

  3. Create an XML input file. See How to: Create XML Input Files.

  4. From a command prompt, enter the following:

    dta -E -S ServerName\Instance -s SessionName -ix PathToXMLInputFile

    where -E specifies a trusted connection, -S specifies a remote server and instance, or a named instance on the local server, -s specifies a tuning session name, and -ix specifies the XML input file to use for the tuning session.

  5. After the utility finishes tuning the workload, you can view the results of tuning sessions with the Database Engine Tuning Advisor graphical user interface (GUI). As an alternative, you can also specify that the tuning recommendations be written to an XML file with the -ox option. For details, see dta Utility.

Security

For important information about the permissions required to use Database Engine Tuning Advisor, see Initializing Database Engine Tuning Advisor.

See Also

Concepts

Permissions Required to Run Database Engine Tuning Advisor
Considerations for Using Database Engine Tuning Advisor
Exploratory Analysis Using Database Engine Tuning Advisor
Unsupported Tuning Options

Other Resources

Using Database Engine Tuning Advisor
XML Input File Reference (DTA)

Help and Information

Getting SQL Server 2005 Assistance