What’s new in DTA – SQL Server 2012

DTA stands for Database Tuning Advisor which has introduced from SQL Server 2005. This utility helps the end user to tune their query and give them suggestions for better performance. It takes T-SQL query or profiler trace as workload, based on this it will analyze and give you the recommendation. Now in SQL Server 2012 it has been extended to include plan cache as workload, which means now you can directly point your db plan cache for analysis.

This is a small change to this utility but it might help most of them. DTA will give you recommendations and suggestions which you have to analyze a little bit and pick up the proper recommendation. Now let’s step in and see how can we tune a plan cache

• Open Database Engine Tuning Advisor and then connect to SQL Server where you want to tune your database. Once you connected you will get similar screen as below

• Provide “Session name” , select “Plan Cache” as workload and then the db which you want to do the performance analysis. In my case I’ve chosen AdventureWorks db.
• That’s it you are done with the pre-requisites, now just click on “Start Analysis” button on toolbar. If you want advanced mode before tuning then go to “Tuning Options” tab and select necessary parameters and the go with analysis

• You can view the progress under “Progress” tab, once the analysis is over you will get a similar screen.  When you choose plan cache as workload, DTA will check for all the plans related to this db in plan cache and will start tune all of them. In my case same thing happened but ended up with warning because most of the objects which is used in the query doesn’t exist at the time of tuning.
• I tested this on a test machine where there is no heavy workload so there is no recommendations for me so I’m moving to “Reports” tab

• From the above image you could see the analysis report. In my case there are around 502 plans related to this db & DTA tried to tune all of them. In addition to this you will get a good report in the bottom pane. If you can see the red oval shape drop down box I’ve requested to show the statement based on cost report, it has shown me the details and the percent improvement. Like this you have so many other reports which will be helpful to understand the workload on the db.

So to conclude DTA is a good tool to start with performance tuning for the beginner which is now extended to execution plans to optimize the db. DTA will result with many recommendations however it’s sensible to analyze those recommendations and choose only the required recommendations, so don’t blindly implement all the recommendations instead use the recommendations as first step in performance tuning.