AEJ Software

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');

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.

See 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: –

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);