jump to navigation

Stored procedure compilation and caching July 17, 2012

Posted by fofo in general .net, Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags: , ,
trackback

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!!!

Comments»

1. Dot Net Rules : Stored procedure compilation and caching - July 17, 2012

[…] procedure is cached in the procedure cache when the stored procedure is created. That is a mistake.(read more) Share Posted: Τρίτη, 17 Ιουλίου 2012 5:13 πμ από το μέλος […]


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

%d bloggers like this: