19 December,2019 by Tom Collins
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)
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |