SQL 2012 Database Tunning Advisor DTA - Procedure Cache

Hi Friends,

Its Sunday afternoon and prior to go out for shopping this evening I tought lets write a blog.

Here by in this blog I will be talking about a new cool feature of SQL Server 2012 Database Tunning Advisor - Plan Cache.

Untill SQL 2008 we were just having 3 option to provide work load to DTA in order to analyze and tune.

Note: If we directly open DTA and create a new session it will show 2 options only i.e. File and Table (as above pic). But if we open query analyzer and select the query that want to tune and then right click and then say "Analyze Query In Database Tunning Advisor" then it will show the third option "Query" (below pic).

This same feature "Query" is available there in SQL 2012 as well.

SQL 2012 Database Tunning Advisor Workload - Plan Cache

Starting from SQL 2012, in Database Tunning Advisor we can provide a new form of workload i.e. Plan Cache. This is pretty cool feature as it can analyze and provide recommendations on the workload which is there present in Plan Cache. This feature pretty good when you want to fix/optimize databases which are performing slow. We can directly use DTA an use Plan Cache workload option and find the recommendations.

Lets try using this feature:

Connect to database "AdventureWorks2012" and execute DBCC FreeProcCache to clear existing procedure cache entries.

Use AdventureWorks2012
Go
DBCC FREEPROCCACHEGo

Lets execute few queries against few of the tables to fill Procedure Cache.

Select

B.FirstName+' '+ B.MiddleName+' ''+B.LastName,
A.JobTitle,C.PhoneNumber,D.EmailAddress
From HumanResources.Employee A, Person.Person B, Person.PersonPhone C, Person.EmailAddress DWhere A.BusinessEntityID=B.BusinessEntityIDAnd A.BusinessEntityID=C.BusinessEntityIDAnd A.BusinessEntityID=D.BusinessEntityIDGo

Select

B.FirstName+' '+ B.MiddleName+' '+B.LastName,
A.JobTitle,C.PhoneNumber,D.EmailAddress
From HumanResources.Employee A, Person.Person B, Person.PersonPhone C, Person.EmailAddress DWhere A.BusinessEntityID=B.BusinessEntityIDAnd A.BusinessEntityID=C.BusinessEntityIDAnd A.BusinessEntityID=D.BusinessEntityID

And JobTitle Like '%Manager%'Go

Lets check the execution plan for the 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 ('AdventureWorks2012')

Lets try using DTA with Plan Cache as a workload.

Step 1 - Open DTA and go to General Tab and select

           - Workload option as "Plan Cache"

           - Database for workload analysis as "AdventureWorks2012"

           - Select database and tables to tune as "AdventureWorks2012"

Step 2 - Click on "Tunning Options" tab and select

        

           - Physical Design Structures (PDS) to use in database, I selected Indexes option only. You

              can use option as per your requirement.

           - Partitioning strategy to employe, I kept it default i.e. No Partiotioning.

           - Physical Design Structures (PDS) to keep in database, I selected "Keep all existing PDS" 

             option so that all existing structures remain intact. 

Step 3 - Click on "Advanced Options" button on Tunning Options tab and select

           - Include plan cache events from all databases. (will discuss this option in a while).
           - Generate online recommendations where possible.

Step 4 - Click on Start Analysis button. Once analysis completes it will provide recommendations as 
              below.

DTA Utility

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

dta -E -D AdventureWorks2012 -ip -ipf -n 1100 -s TuneUsingPlanCache

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
        1100.
        
Note:

* By default Plan Cache will use first 1000 event from the plan cache to use for analysis.
   We cannot change this limit in GUI, the only option to change it using DTA command line utility.

* Pausing Database Tunning Advisor is not supported. Once we click on "Start Analysis" the only 
   options we have is to "Stop Analysis" or "Stop Anlysis (with recomendations)".

        Stop Analysis : stops the tunning session without generating any recommendation.
        Stop Analysis (with recommendations) : stops the tunning session and prompts you to decide
        whether you want Database Engine Tunning Advisor to generate recommendations based on the
        analysis done till that point.


Include plan cache events from all databases option

As shown above we selected this option by click on "Advanced Option" button. The reason for selecting this button is:

Suppose we have a query which joins other tables of some different database and we want to analyze that query using DTA then this option will be helpful as it will use all the events from plan cache for analysis that will give good and more accurate results.

Hopefully this blog will be helpful. Please post your comments.

Regards
Gurpreet Singh Sethi