Hi friends,

Just came across Database Tuning Advisor new feature in SQL Server 2012. It can now tune the events from the plan cache directly. This is a very good option when tuning an inherited database or when one has to come up with quick optimization fixes for a slow performing database.

It selects top 1000 events from plan cache to tune.  This can be increased using –n option of Database Tuning Advisor command line utility.

The code below clears up the plan cache and executes queries to generate plans to be tuned.

The code below returns the cached plan from the above queries.

Let’s now configure the DTA to tune the plan cache.

Step 1: Open up DTA and under the “General” tab select

Workload: Plan Cache

Database for work load analysis: AdventureWorks2008R2

– Database and tables to tune: relevant database and table you want to tune. In my case I selected all tables under AdventureWorks2008R2 database.


Step 2: Under the “Tuning Options” tab select

Physical Design structures to use in database: I selected indexes. Other options can be selected as appropriate.

– Leave everything else as default.


Step 3: Click on “Advanced option” button to set few other options.

– Include plan cache events from all databases to tune all databases.


That’s it. Click on “Start Analysis” and it gives you recommendations as shown below.


The above analysis can also be done using the dta command line utility as shown below.

dta -E -D AdventureWorks2008R2 -ip -ipf -n 1500 -s MytuningSession

Where in the option

ip:  is for plan cache workload

ipf: is to analyze plan cache events for all database.

-n: is to increase the number of cache events to be analyzed for from the default value of 1000 to 1500.

Happy tuning!!!



Ahmad Osama

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook