SQL Server 2008 R2 DTA CLI Tool
dta is the command-line version of the graphical DB Engine Tuning Advisor. Both the command-line utility and graphical tool provide performance recommendations based on the workload provided to them. The syntax for dta is as follows:
Dta [ -? ] |
[
[ -S server_name[ \instance ] ]
{
{ -U login_id [-P password ] }
I "E }
{ -D DB_name [ ,...n ] }
[-d DB_name ]
[ -T1 table_list | -Tf table_list_file ]
{ -if workload_file | -it workload_trace_table_name }
{ -s session_name | -ID session_ID }
[ -F ]
[ -of output_script_file_name ]
[ -or output_xml_report_file_name ]
[ -ox output_XML_file_name ]
[ -rl analysis_report_list [ ,...n ] ]
[ -ix input_XML_file_name ]
[ -A time_for_tuning_in_minutes ]
[ -n number_of_events ]
[ -m minimum_improvement ]
[ -fa physical_design_structures_to_add ]
[ -fp partitioning_strategy ]
[ -fk keep_current_option ]
[ -fx drop_only_mode ]
[ -B storage_size ]
[ -c max_key_columns_in_index ]
[ -C max_columns_in_index ]
[ -e I -e tuning_log_name ]
[ -N online_option]
[ -q ]
[ -u ]
[ -x ]
[ -a ]
An extensive number of options is available with this utility, but many of them are not necessary to do basic analysis. At a minimum, you need to use options that provide connection info to the DB, a workload to tune, a tuning session identifier, and the location to store the tuning recommendations. The connection options comprise -S for the server name, -D for the DB, and either -E for a trusted connection or -U and -P, which can be used to stipulate the user and password. The workload to tune is either a workload file or workload table. The -if option is used to stipulate the workload file location, and the -it option is used to stipulate a workload table. The workload file need to be a Profiler trace file (.trc), SQL script (.sql) that contains T-SQL commands, or SQL Server trace file (.log). The workload table is a table that contains output from a workload trace. The table is specified in the form DB_name. owner_name. table_name. The tuning session need to be identified with either a session name or session ID. The session name is character based and is specified with the -s option. If the session name is not provided, a session ID need to be provided instead. The session ID is numeric and is set using the - ID option. If the session name is specified instead of the session ID, the dta generates an ID anyway.
The last options necessary for a basic dta execution identify the destination to store the dta performance recommendations, which can be stored in a script file or in XML. The -of option is used to stipulate the output script filename. XML output is produced when the -or or -ox option is used. The -or option generates a filename if one is not specified, and the -ox option requires a filename. The -F option can be used with any of the output options to force an overwrite of a file with the same name, if one exists. To illustrate the use of dta with basic options, let's look at an example of tuning a simple CHOOSE statement against the AdventureWorks2008R2 DB. To begin, you use the subsequent T-SQL, which is stored in a workload file named c: \myScript. sql:
USE AdventureWorks2008R2 ;
choose *
from Production.transactionHistory
where TransactionDate = '9/1/04'
The subsequent example displays the basic dta execution options that can be used to acquire performance recommendations :
dta -S xpvirtuall -E -D AdventureWorks2O08R2 -if c:\MyScript.sql
-s MySessionX -of C:\MySessionOutputScript.sql –F
The preceding example utilizes a trusted connection against the AdventureWorks2008R2 DB, a workload file named c: \MyScript. sql, and a session named MySessionX, and it outputs the performance recommendations to a text file named c: \MySessionOutputScript. sql. The -F option is used to force a replacement of the output file if it already exists. The output file contains the subsequent performance recommendations:
se [AdventureWorks2008R2]
CREATE NONCLUSTERED INDEX [_dta_index_TransactionHistory_5]
ON [Production].[TransactionHistory]
(
[TransactionDate] ASC
)
COMPRISE ( [TransactionID],
[ProductID],
[ReferenceOrderlD],
[ReferenceOrderLinelD],
[TransactionType],
[Quantity],
[ActualCost],
[ModifiedDate])
WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_CURRENT = OFF, ONLINE = OFF) ON [PRIMARY]
In short, the dta output recommends that a new index be generated on the TransactionDate column in the TransactionHistory table. This is a viable recommendation, considering that there was no index on the TransactionHistory .TransactionDate column, and it was used as a search argument in the workload file. Many other options (that go beyond basic execution) can be used to manipulate the way
dta makes recommendations. As an example, a list can be provided to limit which tables the dta looks at during the tuning process. Options can be set to limit the amount of time that the dta tunes or the number of events. These options go beyond the scope of this , but you can increase further insight into them by looking at the graphical DTA, which contains many of the same types of options. You can refine your tuning options in the DTA, export the options to an XML file, and use the -ix option with the dta utility to import the XML options and run the analysis.