Querying the Plan Cache

The first time a SQL Server statement or stored procedure is executed, a query plan is compiled and placed in memory into the plan cache. Compiling a plan is an expensive operation, hence the plan is re-used from cache for subsequent invocations of the same statement to avoid having to compile the plan again. Here is an example: –


USE AdventureWorks2012;

EXECUTE dbo.uspGetBillOfMaterials
@StartProductID = 893,
@CheckDate = '2015-11-05T00:00:00';

We can then query the plan cache via theĀ sys.dm_exec_cached_plans dynamic management view to show the compiled plan just inserted: –


SELECT ObjectName = OBJECT_NAME(st.objectid, st.[dbid]) ,
cp.plan_handle,
cp.usecounts ,
cp.size_in_bytes ,
cp.cacheobjtype ,
cp.objtype ,
qp.query_plan ,
st.[text]
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE st.[dbid] = DB_ID(N'AdventureWorks2012')
AND st.objectid = OBJECT_ID(N'AdventureWorks2012.dbo.uspGetBillOfMaterials');


Plan cache 1 invocation
A subsequent invocation of the same stored procedure with different parameters, like so: –

USE AdventureWorks2012;

EXECUTE dbo.uspGetBillOfMaterials
 @StartProductID = 518,
 @CheckDate = '2004-07-14T00:00:00';

Results in the query plan being re-used. We can demonstrate this via the same sys.dm_exec_cached_plans query above.

 

Plan cache 2 invocationSee the usecounts value is now two. This is optimal behaviour, but is not without potential issues. If we look at the parameter values from actual execution plan of the second invocation, we see: –

Parameter values

This demonstrates the second invocation used a plan that was compiled using the parameter values from the first invocation. This might well be fine, but can lead to a concept known as parameter sniffing that will be explained in a future blog post.

As an appendix, to clear a specific plan from cache for this demonstration, I used the following: –

DECLARE @plan_handle VARBINARY(64);

SELECT TOP 1
 @plan_handle = cp.plan_handle
FROM sys.dm_exec_cached_plans AS cp
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE qp.[dbid] = DB_ID(N'AdventureWorks2012')
 AND qp.objectid = OBJECT_ID(N'AdventureWorks2012.dbo.uspGetBillOfMaterials');

DBCC FREEPROCCACHE (@plan_handle);
Advertisements

2 thoughts on “Querying the Plan Cache

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s