| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | helpviewer_keywords | |
|---|---|---|---|---|---|---|---|---|---|
Using the dta Command Prompt Utility |
Learn about the functionality that the dta command-prompt utility offers in addition to that provided by the SQL Server Database Engine Tuning Advisor. |
markingmyname |
maghan |
03/14/2017 |
sql |
performance |
how-to |
|
[!INCLUDE SQL Server] 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 [!INCLUDEssNoVersion] 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
[!INCLUDE article-uses-adventureworks]
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 the [!INCLUDE sssampledbobject-md] database.
- Install SQL Server 2017 Developer Edition.
- Download AdventureWorks sample database.
Instructions for restoring databases in SSMS are here: Restore a database.
Note
This tutorial is meant for a user familiar with using SQL Server Management Studio and basic database administration tasks.
-
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
| 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.
-
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 AdventureWorks2022 -if MyScript.sql -s MySession2 -of MySession2OutputScript.sql -ox MySession2Output.xml -fa IDX_IV -fp NONE -fk NONEwhere
-Sspecifies the name of your server and the [!INCLUDEssNoVersion] instance where the [!INCLUDEssSampleDBobject] 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 [!INCLUDEtsql] 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 [!INCLUDEssManStudioFull] 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 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.