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 Cache
DBCC FREEPROCCACHE
GO
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]
GO
-- 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

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.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

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

  1. Hello Kishor,

    Output parameter is best way to get a value into a variable out of a stored procedure.
    For example if you want to return a value like “YourName” from a stored procedure into a variable @YourName. Here you have three options:
    1. In the stored procedure store the value in some user table and out of stored procedure select the stored value in variable.
    2. Use an insert into statement with a table variable and insert the result of EXEC yourSP and then set the value in your variable from table variable.
    3. Easiest is assign the value to an output parameter.

    Regards,
    Pinal dave

  2. Pinal Dave,

    Can you explain what is meant by “compiled”? Does it simply mean the execution plan is cached? The stored procedure is not compiled in the way that I’d consider a C# program compiled, right?

    Thanks.

  3. Hi Pinal.

    is there a way to enforce that the objects related in a stored procedure exists?

    for example, i want that this script fail when i execute it. Not when runs the stored procedure the first time.

    CREATE PROCEDURE TEST_SP_OBJECT_DOESNT_EXIST
    AS
    BEGIN

    SELECT * FROM TABLE_THAT_DOESNT_EXIST
    END
    GO

    Thank you

    • I believe till 2008R2 there is not any way to enforce the existence of the DB objects inside a stored procedure.

      The other way around is, you can check using IFExist() for any DB objects and proceed accordingly.

      Thanks
      Manish

  4. Pingback: SQL SERVER – Cases When Stored Procedure RECOMPILE – Quiz – Puzzle – 14 of 31 « SQL Server Journey with SQL Authority

    • Hi Pinal,
      Thanks for the explanation. But what would happen if the stored procedure is removed from the cache? Would it compile the stored procedure again?

  5. Hello Pinal,

    I have gone through on this article and reach one point where i seek your guidance.

    In my environment, while running the particular stored procedure say “GetPersonsInfo” for the first time it takes “22secs” to complete and after that when i execute the same stored procedure again it takes only “8secs” to complete. It means that the stored procedures take a long time to run the first time they’re executed, but then they run very fast for subsequent executions.

    As per this article, the performance improvement when i run the stored procedure the second time is due to the execution plan being set already and data has been already stored in buffer memory. Now, what i want is for the stored procedures to run fast, even on the first time. I have done many research on the same but didn’t find any useful solution.

    Is there any way to keep buffer data ready even for the first time of execution?

    Any advice or workaround would be greatly appreciated.

    Thanks,
    Ashish

  6. Pingback: SQL SERVER – Beginning New Weekly Series – Memory Lane – #002 « SQL Server Journey with SQL Authority

  7. How Can i , have SP that just Compile for the first time ??
    if i write down the ” with recompile ” in My Sp it will be compile every time , Right???
    Please answer these 2 question .

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #007 « SQL Server Journey with SQL Authority

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #007 « SQL Server Journey with SQL Authority

  10. Hi Pinal,

    If we restart the db service all the cached plans will be flushed out . In that case it will behave like a brand new sp right?

  11. Hi Pinal,
    Firstly I can’t thank you enough for all the help your blogs have been for me, during all these years.

    Talking of SP compile, I see that if I change the input parameters to one of my Stored Procedures, then it will compile it the first time. So every time I change the values, it will compile. Do you know what could be the reason?

    One additional information: With the SP a temp table is created and an index created and dropped on that Temp table. I am not sure if this is a good design but that’s the way the vendor app is designed.

    And in profiler traces, I see that most of the SQL Query compile messages include this temporary table.

  12. Hi, One of my customer says, the ram utilization of sql server is high on particular day but on next day sql is utilizing very normal.
    how to find previous days transactions or sp’s or quiries which took high cpu time or ram executed on the sql server?

  13. Hi,
    Our application send data to Data Ware House daily, we create Snapshot of the database every time. We have a Stored Procedure and it takes around 20-30 sec (first time), there after 4 sec to execute, since the snapshot is created every time the execution plan is created and sometimes the SP times out, do we have any workaround so that SP is precompiled and available in the Snapshot.

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