DTA Command-Line Utility

dta is the command line version of the graphical Database Engine TunningAdvisory. They both provide performance recommendations based on the workload provided to them.
The workload to tune would either be a workload file or a workload table. The workload filemust be either a profiler trace file or an SQL script or SQL Server trace file(.log).
We will use the T-SQL below which is stored in a workload file named c:\dtascript.sql.


USE AdventureWorks
Go
Select * from HumanResources.Employee
where gender = 'M'

To demonstrate the use of dta, lets look at this example.

C:\>dta -S pavilion -E -D AdventurewORKS -if c:\dtascript.sql -s Mysession -of c:\dtaOutputscript.sql -F

Microsoft (R) SQL Server Microsoft SQL Server Database Engine Tuning Advisor command line utilityVersion 9.00.3042.00Copyright (c)
Microsoft Corporation. All rights reserved.

Tuning session successfully created. Session ID is 6.
Total time used: 00:00:01Workload consumed: 100%, Estimated improvement: 47%

Tuning process finished.Successfully generated recommendations script: c:\dtaOutputscript.sql.

The output of the dtaoutputscript.sql will contain the following performance recommendation:

use [AdventureWorks]
go
CREATE NONCLUSTERED INDEX [_dta_index_Employee_14_869578136__K9_1_2_3_4_5_6_7_8_10_11_12_13_14_15_16] ON [HumanResources].[Employee]
( [Gender] ASC)
INCLUDE ( [EmployeeID],
[NationalIDNumber],
[ContactID],[LoginID],
[ManagerID],[Title],[BirthDate],
[MaritalStatus],
[HireDate],
[SalariedFlag],
[VacationHours],
[SickLeaveHours],
[CurrentFlag],
[rowguid],
[ModifiedDate]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

The dta ouput recommends that a new non-clustered index be created on the Gender column in the Employee table.