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.
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)