SQL Server 2012: Database Tuning Advisor enhancements January 14th, 2013

Vinod Kumar

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.

image

  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.

Share this article

Tags: , , , , , , ,

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.


10 Responses to “SQL Server 2012: Database Tuning Advisor enhancements”

  1. THenrich says:

    “I am not a big fan of DTA tool”. What are the disadvantages of using DTA? It’s a good starting point and one can always review its recommendations. Every time I see a comment like this, the DBA says that developers depends on it for tuning recommendations without understanding the SQL or implications. WELL.. is this DTA’s fault or the users fault from not knowing?? It’s a helping tool.. not a magic bullet which will fix every single performance issue.

    • Vinod Kumar says:

      The idea here was not to offend perse the tool or its capability, but I am the sort of person who wants to dig into the workload, usage pattern, data distribution, statistics, partitioning, file placements, deployment topology, execution plans etc and then get into the Indexing strategy recommendations. Though DTA does a decent and a fairly good job, I work differently. Hence that statement.

      I agree to your point, though DTA is a guideline – the developer / DBA needs to evaluate it and then apply on their system to fix performance issues. Thanks for your note.

  2. Jency says:

    Hi,

    Please can you let me know if there is a script or powershell command to perform the SQL 2012 Upgrade Advisor to check for compatability issues on previous versions of SQL Server database objects and DTS packages. There are a large number of servers in this regard and hence looking for a solution to automate which results in a faster remediation procedure

    I forgpt to mention Upgrade Advisor in the previous comment

    Regards,
    Jency

  3. Dinesh says:

    Hi ,
    I am getting Workload into two patrs:-1) file 2) Table
    Plan cache is not available.Which sql version you are using ?

    Rds,
    Dinesh

  4. Dinesh says:

    hi,
    37% of Consumed workload has syntax errors.Check tunning log for more information.
    Can tell what to do ??
    Regards,
    Dinesh

  5. Manoj Sahoo says:

    Thanks Vinod

    for such a good article……

Leave a Reply



 

Email
Print