Have you ever wondered why a stored procedure takes a long time to run for the first time? This is a common question that many SQL Server users have. The answer lies in the compilation process of stored procedures.

Contrary to popular belief, stored procedures are not pre-compiled. They are compiled only during their first execution. This misconception often leads to confusion among users who expect stored procedures to be pre-compiled.

Let’s take a closer look at the compilation process of stored procedures. When a stored procedure is created, there is no cache entry for its execution. It is only when the stored procedure is executed for the first time that an entry is made in the system’s cache.

To demonstrate this, let’s consider the following script:

USE AdventureWorks

-- Clean Cache
DBCC FREEPROCCACHE

IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[CompSP]') AND type IN (N'P', N'PC'))
    DROP PROCEDURE [dbo].[CompSP]

-- Create New Stored Procedure
CREATE PROCEDURE CompSP
AS
    SELECT * FROM HumanResources.Department
GO

-- Check the Query Plan for SQL Batch
-- You will find that there is no ObjectName with CompSP
SELECT 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 QueryPlan
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;
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 CompSP
SELECT 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 QueryPlan
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;
GO

In the above script, we first clean the cache using the DBCC FREEPROCCACHE command. Then, we check the query plan for the SQL batch before and after executing the stored procedure CompSP.

Before executing the stored procedure, you will notice that there is no ObjectName with the name CompSP in the query plan. However, after executing the stored procedure, you will find that there is one entry with the name CompSP in the query plan.

This clearly demonstrates that stored procedures are not pre-compiled. They are compiled on the fly during their first execution and then cached for subsequent runs.

Understanding the compilation process of stored procedures can help you optimize the performance of your SQL Server. By knowing that the first execution may take longer due to compilation, you can plan accordingly and take steps to minimize the impact on your application’s performance.

For more in-depth information on plan caching in SQL Server, I recommend reading the white paper “SQL SERVER – Plan Caching in SQL Server 2008” by Greg Low. Additionally, you may find my follow-up article “SQL SERVER – Plan Caching and Schema Change – An Interesting Observation” interesting, where I discuss an intriguing conversation with Greg Low.

Remember, stored procedures are not pre-compiled, but they are compiled during their first execution. Understanding this concept will help you optimize the performance of your SQL Server and ensure smooth execution of your stored procedures.