How to use Database Tuning Advisor via command line

19 December,2019 by Jack Vamvas

Question: Is it possible to use SQL Server Database Tuning Advisor through the command line?

Answer: On a server with SQL Server installed you can start the SQL Server Database Tuning Advisor In the command line type "dta".That will return something like :

Microsoft (R) SQL Server Microsoft SQL Server Database Engine Tuning Advisor command line utility
Version 14.0.800.90 ((SQL_Main).170711-1339)
Copyright (c) 2015 Microsoft. All rights reserved.

 

Additionally - you can use : dta -? and that will give you extra usage details along with descriptions per switch 

Usage:
DTA.EXE [-S ServerName[\Instance]] [-U LoginId] [-P Password] [-E] [-d DatabaseName] [-D DatabaseName[, DatabaseName]] [-Tl TableName[, TableName]] [-Tf TableListFileName] [-if WorkloadFileName] [-it WorkloadTableName] [-ip] [-iq] [-ipf] [-s SessionName] [-of [ScriptFileName]] [-or [ReportFileName]] [-rl Report[, Report]] [-ox [OutputXmlFileName]] [-F] [-ID SessionID] [-ix InputXmlFileName] [-A TuningTime] [-n NumberOfEvents] [-m MinimumImprovement] [-fa PhysicalDesignStructure] [-fp PartitionStrategy] [-fk PhysicalDesignStructure] [-fx] [-fi] [-fc] [-B StorageSize] [-c MaxKeyColumnsInIndex] [-C MaxColumnsInIndex] [-k MaxTotalIndexes] [-K MaxIndexesPerTable] [-ie] [-M MinPercentImpPerIndex] [-p NumProcessorsToUse] [-cl Mode] [-clt CleanupTimeout] [-l LastNHoursOfQueriesToTune] [-e TuningLogTable] [-N OnlineOption] [-q] [-u] [-x] [-a] [-?]

Here is a simple example of how to utilise DTA through the command line 

Step 1 -

Create a file with the sql query - example - SQLQuery1.sql

Step 2-

- In the command line construct a valid command set utilising dta. An example:

dta -S SERVER1\Instance -E -D MYDB -if SQLQuery1.sql -s MySession2 -of MySession2OutputScript.sql -ox MySession2Output.xml -fa IDX_IV -fp NONE -fk NONE

In this example command line: 

-E = security 

-if = input file 

-s  = unique session name 

-of = output file

-ox = output xml file 

-fk = physical design structure

Step 3 -

 

Review output file 

 

This is a straightforward example . Do not just accept the recommendations made by the Database Tuning Advisor . Blindly implementing can lead to unexpected consequences - just as slower sql performance 

 

Read more on automatic tuning 

Automatic Index Tuning by default on Azure sql server

Database Tuning Advisor Performance (SQL Server DBA)


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to use Database Tuning Advisor via command line


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer