DOT NET RULES

Looking into the contents of Plan Cache in SQL Server August 9, 2012

In this post I will be demonstrating with hands-on examples how to look into the contents of Plan Cache.  I will be explaining what the Plan Cache (Procedure Cache) is, the process of query optimization and the generation of execution plans in detail. Then I will use some DMVs to create a custom view that will return the contents of the plan cache.

I will be using SQL Server 2012 Developer edition in this post but feel free to use SQL Server 2005,2008 versions.I will be using the AdventureWorks2012 database.You can download it from the Codeplex website.

Let me talk a bit about query optimisation and T-SQL. T-SQL is declarative by nature. When we write T-SQL statements in a query window in SSMS and execute them,we just say to SQL Server “I want these results back”. We do not provide any details on how the results will be returned.If there was nothing else between our T-SQL code and the SQL Server Database engine, we simply would not get any results back.Luckily for us there is a very important component, the Query Optimizer that generates an imperative plan. By saying imperative I mean detailed. This plan that is called execution plan is what is actually executed by the relational engine.The query optimiser will not look for a perfect plan.It is a cost-based optimiser that must find an efficient plan.The optimiser when deciding upon the execution plan will take in to consideration the type of operations,statistics (must always be up to date),indexes,hardware resources (number of CPUs ,available memory),SQL Server edition,number of active concurrent connections and query hints.

The following steps  take place upon query receival from SQL Server:

• Parser
• Algebriser
• Optimiser
• Execution plan generation – caching
• Query execution = execution plan execution

The Parser checks the an incoming query validating it for correct syntax.The query is terminated if a syntax error is detected.On validating a query the parser generates the parse tree.

The Algebriser takes the parse tree and resolves all the names of the different objects and data types.The output of all these verifications and resolutions is a binary set of data called a query processor tree.

Based on the query tree the Optimiser finds the optimal solution to execute the query.

Plans are then cached and reused so you do not have to create a new plan every time a query is executed.That will improve the performance significantly since execution plans are costly in both time and resources.So, the first time a query is executed the plan is created,compiled and placed in the procedure-plan cache. When there are subsequent requests for that query,the query plan will be used.

Connect to an instance of SQL Server. Type (copy paste) the code below and execute it. Make sure you include the execution plan ( CTRL+M )

USE [AdventureWorks2012]

GO

SELECT  Sales.SalesOrderDetail.OrderQty ,
Sales.SalesOrderDetail.SpecialOfferID ,
Sales.SalesOrderDetail.UnitPrice ,
Sales.SalesOrderDetail.UnitPriceDiscount ,
Sales.SalesOrderHeader.Status ,
Sales.SalesOrderHeader.AccountNumber
FROM    Sales.SalesOrderDetail
INNER JOIN Sales.SalesOrderHeader ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
ORDER BY Sales.SalesOrderDetail.UnitPrice DESC

Ηave a look at the picture below to see the results returned with the execution of the statement above along with the execution plan.

I will not be looking into the various components of this execution plan and what they mean. You can hover over each of these components and get information like IO cost and CPU cost.

Most SQL Server developers and administrators I know, strongly believe that DMVs are the best feature that has been added to SQL Server since 2005.I feel exactly the same way.Please have a look at this post of mine that I talk about new DMVs and DMFs in SQL Server 2008 SP1 and SQL Server 2012.

Wouldn’t it be nice to be able to look into the contents of the cache?We can do that by using various DMVs.

In a new query window type the following.Do not execute anything yet.

USE AdventureWorks2012
GO
--DO NOT USE THIS COMMAND IN A PRODUCTION SYSTEM

DBCC FREEPROCCACHE

SELECT * FROM sys.dm_exec_cached_plans

SELECT * FROM Person.Person

SELECT * FROM sys.dm_exec_cached_plans

select * from sys.dm_exec_query_plan(0x060005003A0E56049009B9FF0100000001000000000000000000000000000000000000000000000000000000);

select * from sys.dm_exec_sql_text(0x060005003A0E56049009B9FF0100000001000000000000000000000000000000000000000000000000000000)

Execute line 1-5 first. I clear the contents of plan cache with the DBCC FREEPROCCACHE command.

Then (in line 7) I use the sys.dm_exec_cached_plans dynamic view that returns a row for each query plan that is cached by SQL Server.

Please have a look in this link for more information about the sys.dm_exec_cached_plans view.

At this time I do not expect to see anything in the plan cache.Then in line 9 I execute a simple SELECT statement..

An execution plan is generated. I run again the sys.dm_exec_cached_plans (line 11) view and I get back a row for this cached plan. I find out the relevant handle.

In my case is “0x060005003A0E56049029B9FF0100000001000000000000000000000000000000000000000000000000000000”

In your case it will be different so make sure you use yours instead.

Now I want to find the actual execution plan.We can use the sys.dm_exec_query_plan dynamic view.This view returns the plan in XML format for the batch specified by the plan handle.

Please have a look at this link for more information about the sys.dm_exec_query_plan view.I need to provide the handle as an input parameter.

I execute line 13 and I get the query plan in XML format for the sql query “Select * from Person.Person”

Then I want to find the SQL statement that created that plan.I will use the sys.dm_exec_sql_text dynamic view.

Please have a look at this link for more information about the sys.dm_exec_sql_text dynamic view.I need to provide the handle as an input parameter.

I execute the statement in line 15.The results I get is something like this

dbid    objectid    number    encrypted    text
5              NULL          NULL                0              SELECT * FROM Person.Person

So I managed to get the handle of the SQL query and then get the execution plan for this query and finally the text for the query itself.

Well that seems like a lot of work and I will demonstrate how to combine all these views into a custom-helper view.

In the same query window type (copy paste) and execute the following.

CREATE FUNCTION ExecPlanAndSqlText2 (@planhandle varbinary (MAX))
RETURNS TABLE
AS
RETURN SELECT EST.text,ECP.objtype,ECP.cacheobjtype,ECP.usecounts,ECP.size_in_bytes,EQP.query_plan FROM sys.dm_exec_sql_text(@planhandle) AS EST CROSS JOIN
sys.dm_exec_query_plan(@planhandle) AS EQP
JOIN sys.dm_exec_cached_plans AS ECP
ON ECP.plan_handle = @planhandle;
GO

SELECT * FROM ExecPlanAndSqlText(0x060005003A0E56049029B9FF0100000001000000000000000000000000000000000000000000000000000000);

I create a helper function first using all the dynamic management views and their relevant columns and then I pass as an input parameter the handle.

When I run the last SELECT statement I get the following results.

As you can see now I have all the information I need in one function.

In the same query window type (copy and paste) the code below.Execute the code

CREATE VIEW LookIntoPlanCache
AS
SELECT  EPAST.*
FROM    sys.dm_exec_cached_plans AS ECP
CROSS APPLY ExecPlanAndSqlText(ECP.plan_handle) AS EPAST

SELECT * FROM LookIntoPlanCache

I create a view (LookIntoPlanCache) that collects all the plan handles and passes them to our user defined function. Finally I execute a simple SELECT to see the contents of the view.

When I do that  I get the following results.

Finally I have the SQL query text,the execution plan and the times this plan was used in one view.

Make sure you use this view to look into the contents of your Plan Cache.It works for every system.

Hope it helps!!!!