Share via

  • Article

Starting the dta Command Prompt Utility and Tuning a Workload

This task guides you through starting the dta utility, viewing its Help, and then using it to tune a workload from the command prompt. It uses the workload, MyScript.sql, which you created for the Database Engine Tuning Advisor graphical user interface (GUI) practice Tuning a Workload. It also uses the AdventureWorks database, which is a new and larger sample database that can demonstrate the more complex features of SQL Server.

AdventureWorksDW is a related database that supports SQL Server Analysis Services. For security reasons, the sample databases are not installed by default. To install the sample databases, run Setup from Add or Remove Programs in Control Panel.

The following tasks guide you through opening a command prompt, starting the dta command prompt utility, viewing its syntax Help, and tuning a simple workload, MyScript.sql, which you created in Tuning a Workload.

Starting the dta command prompt utility and viewing Help

  1. On the Start menu, point to All Programs, point to Accessories, and then click Command Prompt.

  2. At the command prompt, type the following, and press ENTER:

    dta -? | more

    The | more part of this command is optional. However, using it enables you to page through the syntax help for the utility. Press ENTER to advance the help text by the line, or press the SPACEBAR to advance it by the page.

Tuning a simple workload with the dta command prompt utility

  1. At the command prompt, navigate to the directory where you have stored the MyScript.sql file.

  2. At the command prompt, type the following, and press ENTER to run the command and start the tuning session (note that the utility is case-sensitive when it parses commands):

    dta -S YourServerName\YourSQLServerInstanceName -E -D AdventureWorks -if MyScript.sql -s MySession2 -of MySession2OutputScript.sql -ox MySession2Output.xml -fa IDX_IV -fp NONE -fk NONE

    where -S specifies the name of your server and the SQL Server instance where the AdventureWorks database is installed. The setting -E specifies that you want to use a trusted connection to the instance, which is appropriate if you are connecting with a Windows domain account. The setting -D specifies the database that you want to tune, -if specifies the workload file, -s specifies the session name, -of specifies the file to which you want the tool to write the Transact-SQL recommendations script, and -ox specifies the file to which you want the tool to write the recommendations in XML format. The last three switches specify tuning options as follows: -fa IDX_IV specifies that Database Engine Tuning Advisor should only consider adding indexes (both clustered and nonclustered) and indexed views; -fp NONE specifies that no partition strategy should be considered during analysis; and -fk NONE specifies that no existing physical design structures in the database must be kept when Database Engine Tuning Advisor makes its recommendations.

  3. After Database Engine Tuning Advisor finishes tuning the workload, it displays a message indicating that your tuning session completed successfully. You can view the tuning results, by using Microsoft SQL Server Management Studio to open the files MySession2OutputScript.sql and MySession2Output.xml. Alternatively, you can also open the MySession2 tuning session in the Database Engine Tuning Advisor GUI and view its recommendations and reports in the same way that you did in Viewing Tuning Recommendations and Viewing Tuning Reports.

Summary

You have completed tuning a simple workload from the command prompt by using the dta utility. This tool provides many other tuning options. Refer to the tool Help (dta -?) and the reference topic dta Utility for more information.