SQL Server 2012 “Denali” Database Tuning Advisor (DTA): Tune a database using Plan cache.

Hi friends,

Just came across DTAs 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 dta command line utility.

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

USE AdventureWorks2008R2
GO
DBCC FREEPROCCACHE
GO
Select	 pp.FirstName
		,pp.LastName
		,pe.EmailAddress
		,pph.PhoneNumber
		,pa.AddressLine1
		,pa.AddressLine2
		,pa.City
FROM Person.Person pp 
		Join Person.EmailAddress pe ON pp.BusinessEntityID=pe.BusinessEntityID
		Join Person.PersonPhone pph ON pp.BusinessEntityID=pph.BusinessEntityID
		Join Person.Address pa ON pp.BusinessEntityID=pa.AddressID
GO
Select	 pp.FirstName
		,pp.LastName
		,pe.EmailAddress
		,pph.PhoneNumber
		,pa.AddressLine1
		,pa.AddressLine2
		,pa.City
FROM Person.Person pp 
		Join Person.EmailAddress pe ON pp.BusinessEntityID=pe.BusinessEntityID
		Join Person.PersonPhone pph ON pp.BusinessEntityID=pph.BusinessEntityID
		Join Person.Address pa ON pp.BusinessEntityID=pa.AddressID
		WHERE pa.City like 'Sea%'
GO
Select prd.Name,prd.ProductNumber,prd.DaysToManufacture,prd.Color,prd.Weight
from Production.Product prd where prd.DaysToManufacture>0 and prd.Color is not null

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

SELECT [text], usecounts
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N'Compiled Plan' 
and dbid=db_id('AdventureWorks2008R2')

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!!!

If you liked this post, do like us on FaceBook at http://www.FaceBook.com/SQLServerGeeks

Thanks,

@ahmad_4u