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 (https://blog.sqlauthority.com)
37 Comments. Leave new
Hi Pinal, great article!! You’re very helpful as always, I do have one question though
I’ve created an SP which takes 40 sec aprox. to execute the 1st time and then 2 sec afterwards. However, I can’t find it within any of the sys.dm_exec_* tables (as expected) since I’m using “WITH RECOMPILE” statement at the beginning of SP’s body.
Yet execution times have huge difference between 1st and consecutive ones.
Is there any other table that might know for SPs using “WITH RECOMPILE” that are still cached but away from sys.dm_exec_* tables ?
Another question, we’ve requested to clean buffers with:
checkpoint
dbcc dropcleanbuffers
To get performance analysis when executing the same SP I’m referring to, but I’ve noticed (as expected) this causes long execution times as if it was the 1st execution (40 sec).
In your opinion, is this practice a suggested thing to do?
From what I understand, a similar situation (cleanbuffers) this will happen on a production environment only if the server gets rebooted, am I correct? or is it going to be the same if input parameters change? (going back to 40 sex execution times)
Thanks for your help!!
I would start looking at the query which is taking more time out of those 40 seconds.
select * query take more time for executing thousands of data.
give perfect solution for get data very fast.
I am not clear with the question. Are you asking way to do faster “select * from table”. get faster machine, I guess.
Hi Pinal
I have a stored procedure that takes 10 minutes the first time it runs and 20 seconds on the subsequent runs. Is there a way to force SQL server to “KEEP PLAN”, or something of that sort?
I think delay is due to the data fetching from disk. As I mentioned, nothing can be done other than moving to faster disks.
Hi Pinal,
This is really a great article.
When we say system create entry in the cache, does SQL cache the actual Query or Query Execution Plan or Query along with Data retrieved on the first execution ?.
Plan is made and saved in cache. Then execution begins and data is pulled from disk to memory (if its not found in memory) and this goes on till query is executed.
Nice article!
I wonder if Functions have the same behavior the first time they run?
Thank you.
Logically they will.
Hi Pinal,
I have an SP which is very slow on first run each day, when i stop and restart SP it completed within 20 minutes otherwise it take long hours to complete. What would be reason behind this.