Command-line utilities for SQL Server database tuning, comparing tables

Get an inside look at a command line utility for SQL Server database tuning. See how the dta utility does an analysis on its given workload and provides performance recommendations. And read about tablediff, which allows for comparing tables.

Microsoft SQL Server 2008 R2 Unleashed

Chapter 5: SQL Server Command-Line Utilities

This section focuses on command-line utilities for SQL Server database tuning, such as the dta command-line utility and how it, along with its counterpart, the graphical Database Engine Tuning Advisor, does an analysis on a given workload and provides recommendations. And we’ll look at the tablediff command-line utility, which lets you compare the contents of two tables.

Table of Contents

Editor's Note: Please see the chapter in PDF form for formatted syntax conventions.

The dta Command-Line Utility

dta is the command-line version of the graphical Database 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 ] }
  | –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 ]

An extensive number of options is available with this utility, but many of them are not required to do basic analysis. At a minimum, you need to use options that provide connection information to the database, a workload to tune, a tuning session identifier, and the location to store the tuning recommendations. The connection options include –S for the server name, –D for the database, and either –E for a trusted connection or –U and –P, which can be used to specify the user and password.

The workload to tune is either a workload file or workload table. The –if option is used to specify the workload file location, and the –it option is used to specify a workload table. The workload file must 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 database_name.owner_name.table_name.

The tuning session must 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 must 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 required 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 specify the output script filename. XML output is generated 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 SELECT statement against the AdventureWorks2008R2 database. To begin, you use the following T-SQL, which is stored in a workload file named c:\myScript.sql:

USE AdventureWorks2008R2 ;
select *
   from Production.transactionHistory
   where TransactionDate = ‘9/1/04’
The following example shows the basic dta execution options that can be used to acquire performance recommendations:
dta -S xpvirtual1 -E -D AdventureWorks2008R2 -if c:\MyScript.sql
-s MySessionX -of C:\MySessionOutputScript.sql -F

The preceding example utilizes a trusted connection against the AdventureWorks2008R2 database, 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 following performance recommendations:

se [AdventureWorks2008R2]
CREATE NONCLUSTERED INDEX [_dta_index_TransactionHistory_5]
ON [Production].[TransactionHistory]
[TransactionDate] ASC
INCLUDE ( [TransactionID],


In short, the dta output recommends that a new index be created 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. For 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 chapter, but you can gain 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.

The tablediff Command-Line Utility

The tablediff utility enables you to compare the contents of two tables. It was originally developed for replication scenarios to help troubleshoot nonconvergence, but it is also very useful in other scenarios. When data in two tables should be the same or similar, this tool can help determine whether they are the same, and if they are different, it can identify what data in the tables is different.

The syntax for tablediff is as follows:
[ -? ] |
-sourceserver source_server_name[\instance_name]
-sourcedatabase source_database
-sourcetable source_table_name
[ -sourceschema source_schema_name ]
[ -sourcepassword source_password ]
[ -sourceuser source_login ]
[ -sourcelocked ]
-destinationserver destination_server_name[\instance_name]
-destinationdatabase subscription_database
-destinationtable destination_table
[ -destinationschema destination_schema_name ]
[ -destinationpassword destination_password ]
[ -destinationuser destination_login ]
[ -destinationlocked ]
[ -b large_object_bytes ]
[ -bf number_of_statements ]
[ -c ]
[ -dt ]
[ -et table_name ]
[ -f [ file_name ] ]
[ -o output_file_name ]
[ -q ]
[ -rc number_of_retries ]
[ -ri retry_interval ]5
[ -strict ]
[ -t connection_timeouts ]

The tablediff syntax requires source and destination connection information to perform a comparison. This information includes the servers, databases, and tables that will be compared. Connection information must be provided for SQL Server authentication but can be left out if Windows authentication can be used. The source and destination parameters can be for two different servers or the same server, and the tablediff utility can be run on a machine that is neither the source nor the destination.

To illustrate the usefulness of this tool, let’s look at a sample comparison in the AdventureWorks2008R2 database. The simplest way to create some data for comparison is to select the contents of one table into another and then update some of the rows in one of the tables. The following SELECT statement makes a copy of the AddressType table in the AdventureWorks2008R2 database to the AddressTypeCopy table:

select *
into Person.AddressTypeCopy
from Person.AddressType

In addition, the following statement updates two rows in the AddressTypeCopy table so that you can use the tablediff utility to identify the changes:

UPDATE Person.AddressTypeCopy
SET Name = ‘Billing New’ WHERE AddressTypeId = 1


UPDATE Person.AddressTypeCopy SET Name = ‘Shipping New’, ModifiedDate = ‘20090918’ WHERE AddressTypeId = 5

The tablediff utility can be executed with the following parameters to identify the differences in the AddressType and AddressTypeCopy tables:

tablediff -sourceserver “(local)” -sourcedatabase “AdventureWorks2008R2”
-sourceschema “Person”-sourcetable “AddressType”
-destinationserver “(local)” -destinationdatabase “AdventureWorks2008R2”
-destinationschema “Person” -destinationtable “AddressTypeCopy”
-f c:\TableDiff_Output.txt

The destination and source parameters are the same as in the previous example, except for the table parameters, which have the source AddressType and the destination AddressTypeCopy. The execution of the utility with these parameters results in the following output to the command prompt window:

User-specified agent parameter values:
-sourceserver (local)
-sourcedatabase AdventureWorks2008R2
-sourceschema Person
-sourcetable AddressType
-destinationserver (local)
-destinationdatabase AdventureWorks2008R2
-destinationschema Person
-destinationtable AddressTypeCopy
-f c:\TableDiff_Output

Table [AdventureWorks2008R2].[Person].[AddressType] on (local)
and Table [AdventureWorks2008R2].[Person].[AddressTypeCopy] on (local)
have 2 differences.
Fix SQL written to c:\TableDiff_Output.sql.
Err AddressTypeID Col
Mismatch 1 Name
Mismatch 5 ModifiedDate Name
The requested operation took 0.296875 seconds.

The output first displays a summary of the parameters used and then shows the comparison results. In this example, it found the two differences that are due to updates performed on AddressTypeCopy. In addition, the –f parameter used in the example caused the tablediff utility to output a SQL file that can be used to fix the differences in the destination table. The output file from this example looks as follows:

— Host: (local)
— Database: [AdventureWorks2008R2]
— Table: [Person].[AddressTypeCopy]
SET IDENTITY_INSERT [Person].[AddressTypeCopy] ON
UPDATE [Person].[AddressTypeCopy]
   SET [Name]=’Billing’
   WHERE [AddressTypeID] = 1
UPDATE [Person].[AddressTypeCopy]
   SET [ModifiedDate]=’2002-06-01 00:00:00.000’,
   [Name]=’Shipping’ WHERE [AddressTypeID] = 5
SET IDENTITY_INSERT [Person].[AddressTypeCopy] OFF

Keep in mind that several different types of comparisons can be done with the tablediff utility. The –q option causes a quick comparison that compares only record counts and looks for differences in the schema. The –strict option forces the schemas of each table to be the same when the comparison is run. If this option is not used, the utility allows some columns to be of different data types, as long as they meet the mapping requirements for the data type (for example, INT can be compared to BIGINT).

The tablediff utility can be used for many different types of comparisons. How you use this tool depends on several factors, including the amount and type of data you are comparing.

Download the chapter "SQL Server Command-Line Utilities" in PDF form.

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning