Datase Tuning Advisor

Database Engine Tuning Advisor Mechanisms

This tool can help identify an optimal set of indexes and statistics for a given workload without requiring an expert understanding of the database schema, workload, or SQL Server internals.

Running DTA

You can run the DTA from the command line or within SQL Server Management Studio.

Running from the command line

  1. Click Start
  2. Type run
  3. Type cmd
  4. From the cmd you now access the dta command line tool

Running from SQL Server Management Studio

  1. Click Tools
  2. Choose Database Tuning Advisor

You can also tune a specific query from SMSS by right-clicking and choosing Database Tuning Advisor.

Database Tuning Advisor

Database Engine Tuning Advisor Examples

Tuning a Query

You can use the Database Engine Tuning Advisor to recommend indexes for a complete database by using a workload that fairly represents all SQL activities. You can also use it to recommend indexes for a set of problematic queries.

To learn how you can use the Database Engine Tuning Advisor to get index recommendations on a set of problematic queries, say you have a simple query that is called rather frequently. Because of the frequency, you want a quick turnaround for some tuning. This is the query:

SELECT  soh.DueDate,
        soh.CustomerID,
        soh.Status
FROM    Sales.SalesOrderHeader AS soh
WHERE   soh.DueDate BETWEEN '1/1/2008' AND '2/1/2008';

To analyze the query, right-click it in the query window and select Analyze Query in the Database Engine Tuning Advisor.

Tuning a Workload Using the Plan Cache

You can also specify the plan cache as a workload to tune. In this case, the DTA will select the top 1,000 events from the plan cache based on total elapsed time of the query (that is, based on the total_elapsed_time column of the sys.dm_exec_query_stats DMV)

Let’s try an example, and to make it easy to see the results, let’s clear the plan cache and run only one query in Management Studio:s

 -- let’s clear the plan cache and run only one query in Management Studio:
DBCC FREEPROCCACHE
GO
SELECT SalesOrderID, OrderQty, ProductID
FROM dbo.SalesOrderDetail
WHERE CarrierTrackingNumber = 'D609-4F2A-9B'

After the query is executed, most likely, it will be kept in the plan cache. Open a new DTA session. In the Workload option, select Plan Cache and specify AdventureWorks2012 as both the database to tune and the database for workload analysis. Click the Start Analysis button. After the analysis is completed, you can select the Recommendations tab and select Index Recommendations, which will include the following recommendations (which you can see by looking at the Definition column):

-- after the analysis is completed, you can select the Recommendations tab
CREATE NONCLUSTERED INDEX [_dta_index_SalesOrderDetail_5_807673925__K3_1_4_5]
ON [dbo].[SalesOrderDetail]
(
[CarrierTrackingNumber] ASC
)
INCLUDE ([SalesOrderID],
[OrderQty],
[ProductID]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
ON [PRIMARY]

-- finally, drop the table you just created by running the following statement:
DROP TABLE dbo.SalesOrderDetail

Offload of Tuning Overhead to Test Server

One of the most interesting and perhaps less known features of the DTA is that you can use it with a test server to tune the workload of a production server.

The DTA can gather the database metadata and statistics from the production server and use it to create a similar database, with no data, on a different server. This database is called a shell database.

Database Engine Tuning Advisor Limitations

The Database Engine Tuning Advisor recommendations are based on the input workload. If the input workload is not a true representation of the actual workload, then the recommended indexes may sometimes have a negative effect on some queries that are missing in the workload.

But most importantly, in many cases, the Database Engine Tuning Advisor may not recognize possible tuning opportunities. It has a sophisticated testing engine, but in some scenarios, its capabilities are limited