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.
Once that is clicked, the tool would open like below.
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.
It would take some time to perform the analysis
…and finally provide recommendation(s).
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”
Here is the recommendations file which I have saved and opened in SSMS.
Here is the comparison of query plan before and after recommendations.
Plan before Index
And here is the plan after index. We can see that clustered index scan has changed to non-clustered index seek.
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)
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.
Thanks for the caution Tim. Do you have any script to identity and drop such indexes?
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!
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.
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.
sqlzane – Thanks for sharing your thoughts
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