Lesson 3: Using the dta Command Prompt Utility
Applies To: SQL Server 2016
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 lesson provides an introduction to the basic dta command-prompt utility syntax, how to access help, and practice for tuning simple workloads.
It contains the following topic:
- Starting the dta Command Prompt Utility and Tuning a Workload