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.
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!!!
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
I didn’t expect anything special. Before proceeding to treatment, I passed all the necessary tests. In addition, the instructions say that the exact mechanism of https://skincarepillsshop.com action of the pill has not yet been clarified. Two weeks later, having again passed the tests, they were good, and I calmly continued to undergo treatment.
It’s very good that author add such many illustrations. All readers will understand what they should do.