DTA "command line tool

This series of articles focuses on SQL Server database optimization command line tools, such as "DTA" command line tools. We will pay attention to its usage and corresponding graphical tools. We will analyze and provide some suggestions based on the given load. In addition, we will learn about the "tablediff" command line tool, which allows you to compare the content of two tables.

"DTA" command line tool

"DTA" is the command line version of the Graphic Database Engine Tuning consultant. Both the command line tool and graphical tool provide performance optimization suggestions based on the loads they face. The syntax of "DTA" is as follows:

DTA [-? ] | [ [-S SERVER_NAME [\ instance] { {-U login_id [-P Password]} |-E} {-D database_name [,... n]} [-D database_name] [-Tl 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_existing_option] [-Fx drop_only_mode] [-B storage_size] [-C max_key_columns_in_index] [-C max_columns_in_index] [-E |-e tuning_log_name] [-N online_option] [-Q] [-U] [-X] [-A] ]

This tool has a large number of parameters available, but many of these parameters do not require basic analysis. But at least, you need to use parameters that provide connection to the database, load tuning, session identifiers optimization, and storage optimization recommendations. The connection involves the following parameters: "-s" specifies the service name, "-d" specifies the database, and "-e" specifies the trusted connection, "-u" and "-P" can be used to specify the user name and password.

The load to be optimized can be either a load file or a load table. The "-If" parameter is used to specify the location of the Load file, and the "-it" parameter is used to specify the load table. The Load file must be a Profiler trace file (. TRC), an SQL script that contains the T-SQL command (. SQL), or an SQL Server trace file (. Log ). A load table is a table that contains SQL output from a load trace. This table is specified in the form of "database name. Owner name. Table name.

The optimized session must be explicitly specified. You can specify the session name or session ID. The session name is character-based and can be specified with the "-s" parameter. If the session name is not provided, the session ID must be provided as an alternative. The session ID must be numeric. It is set with the "-ID" parameter. If the session name is specified but the session ID is not used, "DTA" generates an ID in any case.

The last parameter requires a basic "DTA" execution to specify the target location for storing "DTA" performance recommendations. It can be stored as a script file or XML. The "-of" option is used to specify the output script file name. To generate an XML output file, you must specify the "-or" or "-Ox" parameter. If the file name is not specified, the "-or" parameter will generate a file name, and the "-Ox" parameter requires a file name. The "-F" parameter can be used with any output parameter to forcibly overwrite an existing file of the same name (if any ).

To illustrate the usage of the basic "DTA" parameter, let's look at an example of optimizing a simple "select" Statement, which uses the "adventureworks2008r2" database. First, you need to use the following T-SQL, which is stored in the load file named "C: \ myscript. SQL:

Use adventureworks2008r2; Go Select * From production. transactionhistory Where transactiondate = '2014/1/04'

The following example shows the basic "DTA" execution parameters, which can be used to obtain performance recommendations:

DTA-s xpvirtual1-e-d adventureworks2008r2-if C: \ myscript. SQL -S mysessionx-of c: \ mysessionoutputscript. SQL-F

The preceding example uses a trusted connection to the "adventureworks2008r2" database. The load file name is "C: \ myscript. SQL, the session name is "mysessionx", It outputs performance recommendations to a name named "C: \ mysessionoutputscript. SQL text file. The "-F" option is used to overwrite the output file (if a file already exists ). The output file contains the following performance recommendations:

Se [adventureworks2008r2] Go Create nonclustered index [_ dta_index_transactionhistory_5] On [production]. [transactionhistory] ( [Transactiondate] ASC ) Include ([transactionid], [Productid], [Referenceorderid], [Referenceorderlineid], [Transactiontype], [Quantity], [Actualcost], [Modifieddate]) With (sort_in_tempdb = OFF, ignore_dup_key = off, Drop_existing = OFF, online = OFF) on [primary] Go

In short, we recommend that you create an index on the "transactiondate" column of the "transactionhistory" table. This is a feasible suggestion, considering that there is no index in the "transactionhistory. transactiondate" column, it is used as a search parameter in the load file.

Many other parameters (not just basic execution functions) can control "DTA" to provide recommended methods. For example, you can provide a list to define which tables will be viewed by "DTA" during debugging. You can also set the "DTA" Tuning limit time or the number of events. These parameters are beyond the scope of this chapter, but you can learn more by viewing the graphic "DTA". There are many parameters of the same type in the graphic "DTA. In "DTA", you can optimize your tuning parameters, export the parameters to an XML file, and then use "-IX" in the "DTA" tool to import these XML parameters, then run the analysis.