SQL SERVER – Stored Procedure are Compiled on First Run – SP taking Longer to Run First Time

During the PASS summit, one of the attendees asked me the following question.

Why the Stored Procedure takes long time to run for first time?

The reason for the same is because Stored Procedures are compiled when it runs first time. When I answered the same, he replied that Stored Procedures are pre-compiled, and this should not be the case. In fact, Stored Procedures are not pre-compiled; they compile only during their first time execution.

There is a misconception that stored procedures are pre-compiled. They are not pre-compiled, but compiled only during the first run. For every subsequent runs, it is for sure pre-compiled.

If you create any SP, you will find that there is no cache entry for the execution of that SP.

After running the SP for the first time, the entry for the cache is made in the system.

If we see the following script, we can notice the different of cache when SP was created and SP was executed.

/* Exeercise to verify if stored procedure pre-compiled */USE AdventureWorks
GO-- Clean CacheDBCC FREEPROCCACHE
GOIF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[CompSP]') AND typeIN (N'P', N'PC'))DROP PROCEDURE [dbo].[CompSP]
GO-- Create New Stored ProcedureCREATE PROCEDURE CompSPAS
SELECT *FROM HumanResources.Department
GO-- Check the Query Plan for SQL Batch
-- You will find that there is no ObjectName with CompSPSELECT cp.objtype AS PlanType,OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,cp.refcounts AS ReferenceCounts,cp.usecounts AS UseCounts,st.TEXT AS SQLBatch,qp.query_plan AS QueryPlanFROM sys.dm_exec_cached_plans AS cpCROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qpCROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st;GO/* Execute Stored Procedure */EXEC CompSP
GO-- Check the Query Plan for SQL Batch
-- You will find that there is one entry with name ObjectName with name CompSPSELECT cp.objtype AS PlanType,OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,cp.refcounts AS ReferenceCounts,cp.usecounts AS UseCounts,st.TEXT AS SQLBatch,qp.query_plan AS QueryPlanFROM sys.dm_exec_cached_plans AS cpCROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qpCROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st;GO

The result set of above query is as following.

The above script to find out the cache is taken from the white paper SQL SERVER – Plan Caching in SQL Server 2008 by Greg Low. You can also read my follow up article SQL SERVER – Plan Caching and Schema Change – An Interesting Observation, where I have given an interesting conversation with Greg Low.