Stored procedure compilation and caching

In this post I will explain when the actual caching (the stored procedure is placed in the procedure cache ) takes place.

Many people think that the execution plan of a stored procedure is cached in the procedure cache when the stored procedure is created. That is a mistake.

You can use any edition of SQL Server of 2012/2008/2005 you want for this example. SQL Server Express edition will do just fine.Download the Express edition, here .

In my case I will use Developer edition of 2012 for these example.

I connect to the SQL Server instance.

Type (copy and paste but make sure you review it) and execute the code below

USE AdventureWorks2012
GO

DBCC FREEPROCCACHE
GO

CREATE PROCEDURE PeopleinSeattle
AS
SELECT [AddressID]
,[AddressLine1]
,[AddressLine2]
,[City]
,[StateProvinceID]
,[PostalCode]
FROM [AdventureWorks2012].[Person].[Address]
WHERE [City] = 'Seattle'

SELECT cpl.objtype AS PlanType,
OBJECT_NAME(stl.objectid,stl.dbid) AS ObjectName,
cpl.refcounts AS ReferenceCounts,
cpl.usecounts AS UseCounts,
stl.text AS SQLBatch,
qpl.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cpl
CROSS APPLY sys.dm_exec_query_plan(cpl.plan_handle) AS qpl
CROSS APPLY sys.dm_exec_sql_text(cpl.plan_handle) AS stl;
GO

Let me explain what I do with the code above. I clear the procedure cache in line 4.Do not do that in a production environment.

Then in lines 8-17 I create a stored procedure.In lines 19-28 I use 1 DMV and 2 DMFs to get information from the procedure cache.

I get information such as the Plan Type,Use counts(how many times the plan was used),the actual query and its query plan.

When I execute lines 8-17 I get the result you see in the picture below.

It is clear that there is no stored procedure in the cache.Let’s execute the stored procedure and run the code with the DMO (DMV+DMFs) again

exec PeopleinSeattle

SELECT cpl.objtype AS PlanType,
OBJECT_NAME(stl.objectid,stl.dbid) AS ObjectName,
cpl.refcounts AS ReferenceCounts,
cpl.usecounts AS UseCounts,
stl.text AS SQLBatch,
qpl.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cpl
CROSS APPLY sys.dm_exec_query_plan(cpl.plan_handle) AS qpl
CROSS APPLY sys.dm_exec_sql_text(cpl.plan_handle) AS stl;
GO

The result follows in the picture below

Only when the stored procedure is executed the Query optimiser generates a plan, compiles it and places it in the procedure cache.No plan is generated when the stored procedure is created.

Execute the code below

exec PeopleinSeattle
GO 40

SELECT cpl.objtype AS PlanType,
OBJECT_NAME(stl.objectid,stl.dbid) AS ObjectName,
cpl.refcounts AS ReferenceCounts,
cpl.usecounts AS UseCounts,
stl.text AS SQLBatch,
qpl.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cpl
CROSS APPLY sys.dm_exec_query_plan(cpl.plan_handle) AS qpl
CROSS APPLY sys.dm_exec_sql_text(cpl.plan_handle) AS stl;
GO

We execute the stored procedure 40 times and we check again to see the contents of the procedure cache.

Have a look at the picture below to see the results

Now we can see that the compiled plan that was compiled and placed in the procedure cache for this particular stored procedure will be used every time this procedure is executed.That is great performance boost.

That is one of the main advantages of stored procedures.

Hope it helps!!!

About these ads