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.
The tutorial uses the AdventureWorks2012 sample database. For security reasons, the sample databases are not installed by default. To install the sample databases, see Installing SQL Server Samples and Sample Databases.
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.
To start the dta command prompt utility and view Help
On the Start menu, point to All Programs, point to Accessories, and then click Command Prompt.
At the command prompt, type the following, and press ENTER:
dta -? | moreThe | 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.
To tune a simple workload by using the dta command prompt utility
At the command prompt, navigate to the directory where you have stored the MyScript.sql file.
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 AdventureWorks2012 -if MyScript.sql -s MySession2 -of MySession2OutputScript.sql -ox MySession2Output.xml -fa IDX_IV -fp NONE -fk NONEwhere -S specifies the name of your server and the SQL Server instance where the AdventureWorks2012 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.
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 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.
After You Finish This Tutorial
After you finish the lessons in this tutorial, refer to the following topics for more information about Database Engine Tuning Advisor:
Database Engine Tuning Advisor for descriptions of how to perform tasks with this tool.
dta Utility for reference material on the command prompt utility and the optional XML file you can use to control the operation of the utility.
To return to the start of the tutorial, see Tutorial: Database Engine Tuning Advisor.