Lesson 3: Using the dta Command Prompt Utility
The dta command-prompt utility offers functionality in addition to that provided by the Database Engine Tuning Advisor.
You can use your favorite XML tools to create input files for the utility by using the Database Engine Tuning Advisor XML schema. This schema is installed when you install SQL Server and can be found at: C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\schemas\sqlserver\2004\07\dta\dtaschema.xsd.
The Database Engine Tuning Advisor XML schema is also available online at this Microsoft Web site.
The Database Engine Tuning Advisor XML schema provides more flexibility to set tuning options. For example, it enables you to perform "what-if" analysis. "What-if" analysis involves specifying a set of existing and hypothetical physical design structures for the database you want to tune, and then analyzing it with the Database Engine Tuning Advisor to find out whether this hypothetical physical design will improve query processing performance. This type of analysis provides the advantage of evaluating the new configuration without incurring the overhead of actually implementing it. If your hypothetical physical design does not provide the performance improvements you want, it is easy to change it and analyze it again until you reach the configuration that produces the results you need.
In addition, using the Database Engine Tuning Advisor XML schema and the dta command-prompt utility, you can incorporate Database Engine Tuning Advisor functionality into scripts and use it with other database design tools.
Using the XML input functionality of Database Engine Tuning Advisor is beyond the scope of this lesson.
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 AdventureWorks2017 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 complete this tutorial, you need SQL Server Management Studio, access to a server that's running SQL Server, and an AdventureWorks database.
Instructions for restoring databases in SSMS are here: Restore a database.
This tutorial is meant for a user familiar with using SQL Server Management Studio and basic database administration tasks.
Access DTA command prompt utility help menu
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 -? | more
| morepart 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.
Tune simple workload 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 NONE
-Sspecifies the name of your server and the SQL Server instance where the AdventureWorks2012 database is installed. The setting
-Especifies 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
-Dspecifies the database that you want to tune,
-ifspecifies the workload file,
-sspecifies the session name,
-ofspecifies the file to which you want the tool to write the Transact-SQL recommendations script, and
-oxspecifies 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_IVspecifies that Database Engine Tuning Advisor should only consider adding indexes (both clustered and nonclustered) and indexed views;
-fp NONEspecifies that no partition strategy should be considered during analysis; and
-fk NONEspecifies 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.
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.