Vinod Kumar (Blog home)

The most important change in Database Tuning Advisor (DTA) is, SQL Server 2012 will allow you to use the query plan cache as a DTA workload. By doing this, we can avoid having to manually create a workload from a script or trace file. When we specify the plan cache as the DTA workload, the Database Engine Tuning Advisor selects the top 1,000 events to use for analysis. Currently, the number of events cannot be changed via the DTA graphical interface (DTASHELL.EXE), but can be changed using the –n option at the command line of DTA.EXE.

1. Launch Database Engine Tuning Advisor, and log into an instance of SQL Server.
2. On the General tab, enter a name in Session name to create a new tuning session.
3. Select Plan Cache as the workload option. Database Engine Tuning Advisor selects the top 1,000 events from the plan cache to use for analysis.
4. Select the database or databases that you want to tune, and optionally from Selected Tables, choose one or more tables from each database. To include cache entries for all databases, from Tuning Options, click Advanced Options and then check the Include plan cache events from all databases check box.
5. Check the Save tuning log check box to save a copy of the tuning log. Clear the check box if you do not want to save a copy of the tuning log.
6. You can view the tuning log after analysis by opening the session and selecting the Progress tab.
7. Click the Tuning Options tab and select from the options listed there.
8. Click Start Analysis.

Final words

Personally, I am not a big fan of DTA tool but have felt a lot of people use this tool because they don’t understand how SQL Server works when it comes to creating indexes. In such cases, this is a great start and a good tool to guide us. Use the recommendation given from DTA, but analyze why this recommendation and look at your workload. Make sure the recommendation are not skewed because of month end reporting workload or some batch process that runs once in a while. I am sure with the plan cache option, there will be lesser friction to generate workload with SQL Server for DTA.

Tags: InterOp, ITPro, Management, Misc, Performance, SQL Server, Technology, Tips and Tricks

This entry was posted on Monday, January 14th, 2013 at 09:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.