SQL SERVER – Introduction to Database Engine Tuning Advisor (a.k.a. DTA)

While talking to one of the attendees after my session at GIDS, I realized that I don’t have much information available on my blog about the usage of one of the fantastic performance tuning tool called Database Engine Tuning Advisor. This is also called as Database Tuning Advisor or DTA in short.

The good thing about this tool is that it is part of the product itself. Once SQL Server Client Tools are installed, DTA is installed along with that. This tool has the capability to suggest index and statistics recommendations for a query given as input. You need not be an expert about query optimization to use this tool.

There are various ways to provide workload input. For a quick demonstration, I would use sample database “AdventureWorksDW2012” and tune a query. If you are not aware about this sample database, then you can read the information provided in my earlier blog.

SQL Authority News – Download and Install Adventure Works 2014 Sample Databases

Once AdventureWorksDW2012 is restored, let’s assume that we need to tune below query.

SELECT [ProductKey] ,[DateKey] ,[MovementDate] ,[UnitCost] ,[UnitsIn] ,[UnitsOut] ,[UnitsBalance] FROM [AdventureWorksDW2012].[dbo].[FactProductInventory] WHERE [MovementDate] =  '2005-07-06'

 

There are multiple ways, we can provide input to DTA tool. The simplest way is to use management studio query windows and right click over there to choose “Analyze Query in Database Engine Tuning Advisor” as shown below.

SQL SERVER - Introduction to Database Engine Tuning Advisor (a.k.a. DTA) DTA-01

Once that is clicked, the tool would open like below.

SQL SERVER - Introduction to Database Engine Tuning Advisor (a.k.a. DTA) DTA-02

We need to choose the proper database in “Database for workload analysis:” and “Select databases and tables to tune”. In our example, we are using AdventureWorksDW2012 database so we will choose that in both the places. Once the database is selected we can directly use “Start Analysis” under “Actions” menu or press the button as well.

SQL SERVER - Introduction to Database Engine Tuning Advisor (a.k.a. DTA) DTA-03

It would take some time to perform the analysis

SQL SERVER - Introduction to Database Engine Tuning Advisor (a.k.a. DTA) DTA-04

…and finally provide recommendation(s).

SQL SERVER - Introduction to Database Engine Tuning Advisor (a.k.a. DTA) DTA-05

One of the interesting piece which is highlighted in above image is “Estimated Improvements”. This the example which we have selected, we are seeing 99% improvement possible. The recommendation provided is to “create” an index.

To get that improvement, we can get the recommendations from Menu “Action” and choose either “Apply Recommendations” or “Save Recommendations”

SQL SERVER - Introduction to Database Engine Tuning Advisor (a.k.a. DTA) DTA-06

Here is the recommendations file which I have saved and opened in SSMS.

SQL SERVER - Introduction to Database Engine Tuning Advisor (a.k.a. DTA) DTA-07

Here is the comparison of query plan before and after recommendations.

Plan before Index

SQL SERVER - Introduction to Database Engine Tuning Advisor (a.k.a. DTA) DTA-08

And here is the plan after index. We can see that clustered index scan has changed to non-clustered index seek.

 SQL SERVER - Introduction to Database Engine Tuning Advisor (a.k.a. DTA) DTA-09

In future blogs, I would show some more example and ways to tune the workload by using database engine tuning advisor.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
Interview Question of the Week #019 – How to Reset Identity of Table
Next Post
SQL SERVER – Identify Page Splits Using Extended Events in SQL Server

Related Posts

No results found.

8 Comments. Leave new

  • One little warning to the DTA. I had it crash on me once while running it was doing analysis. It left all the recommendations it was testing on the data base which included indexes views which are schema bound so they don’t allow changes to any table in the view. I had to find a drop a large number of objects created by the DTA.

    Reply
  • Creating indexes through DTA is a fast-track way to ending up with 80+ indexes on a table and wondering why your INSERT, UPDATE and DELETE statements now take 50x longer than before.

    It should be used with caution!

    Reply
  • Any suggested indexes given by the query tuning advisor should be tested and renamed before implementation. Using it exclusively runs a high risk of having largely duplicative indexes.

    Reply
  • Any indexes created by the query tuning advisor should be tested and renamed prior to implementation. It tends to run a high risk of creating duplicative indexes.

    Reply
  • Christian Reich
    November 17, 2017 2:29 pm

    Got the same situation, the DTA was crashed while executing. After implementing recommendations and deleting the results of the DTA I’ve found a lot of orphan statistics, indexes and views.

    I’ve done a little research and found this, worked for me.

    –DROP DTA Statistics–
    DECLARE @dtaStats AS TABLE(Id INT IDENTITY(1,1), StatName VARCHAR(255),TableName VARCHAR(255))
    DECLARE @currentId AS INT
    DECLARE @lastId AS INT
    DECLARE @statName VARCHAR(255)
    DECLARE @tableName VARCHAR(255)

    INSERT INTO @dtaStats(StatName,TableName)
    SELECT name,OBJECT_NAME(OBJECT_ID) AS TABLENAME
    FROM sys.stats
    WHERE name like ‘_dta_stat_%’
    SET @currentId=NULL
    SET @lastId=0

    SELECT @currentId=MIN(Id)
    FROM @dtaStats
    WHERE Id>@LastId
    WHILE (@currentId IS NOT NULL)
    BEGIN
    SELECT @statName=StatName,
    @tableName=TableName FROM @dtaStats WHERE Id=@currentId
    PRINT ‘DROP STATISTICS ‘ + @tableName + ‘.’ + @statName
    EXEC(‘DROP STATISTICS ‘ + @tableName + ‘.’ + @statName)
    SET @lastId = @currentId
    SET @currentId=null
    SELECT @currentId=MIN(Id)
    FROM @dtaStats
    WHERE Id>@LastId
    END

    –DROP DTA Indexes–
    DECLARE @dtaIndex AS TABLE(Id INT IDENTITY(1,1), IndexName VARCHAR(255),TableName VARCHAR(255))
    DECLARE @currentId AS INT
    DECLARE @lastId AS INT
    DECLARE @IndexName VARCHAR(255)
    DECLARE @tableName VARCHAR(255)

    INSERT INTO @dtaIndex(IndexName,TableName)
    SELECT name, OBJECT_NAME(OBJECT_ID) AS TABLENAME
    FROM sys.indexes
    WHERE name like ‘_dta_index_%’

    SET @lastId=0
    SET @currentId=NULL
    SELECT @currentId=MIN(Id)
    FROM @dtaIndex
    WHERE Id>@LastId
    PRINT @currentId
    WHILE (@currentId IS NOT NULL)
    BEGIN
    SELECT @IndexName=IndexName,@tableName=TableName FROM @dtaIndex WHERE Id=@currentId
    PRINT ‘DROP INDEX ‘ + @IndexName + ‘ ON ‘ + @tableName
    EXEC(‘DROP INDEX ‘ + @IndexName + ‘ ON ‘ + @tableName)
    SET @lastId = @currentId
    SET @currentId=NULL
    SELECT @currentId=MIN(Id)
    FROM @dtaIndex
    WHERE Id>@LastId
    END

    Source:
    https://www.codeproject.com/Articles/123750/SQL-Cleaning-Up-After-the-Database-Tuning-Engine-A

    Reply

Leave a Reply

Menu