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
Very nice stuff…
thanks buddy for sharing this inforamtion.
Nice explanation sir.
All the best for Pune Session.
Is dynamic Query in SP complied Everytime ?
how it complied the SP when it take Parameter ?
I have the same question as Rakesh…. Please can someone explain?
Cheers – DV
Hi Pinal
What is the advantage of Output Parameter ?
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
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.
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 in SQL SERVER.
Thanks
Manish
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
Camilo, you can ask if the table exists direcly. This is the same approach for every object in the db….
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
Hello Ashish,
i am having the same question.
Did you got any reply on your query?
Thanks,
Raj
You can always run the queries by yourself during your start up process so you can always get optimal performance when you reach to your query first time.
what does pre-compile actually means? we say sp are pre-compile
Thanks for the explanation. SP are really tough to work with
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 .
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?
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.
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?
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.
what is reason behind to say first time compilation to store procedure…..
what is reason behind that in Function cannot use Store Procedure but in Store Procedure use..
Hi Pinal your article was superb…I agree that stored procedures are not pre-compiled in sql but after the first execution for other subsequent call they are pre-compiled…I have one doubt let’s suppose i have an stored procedure with select statement with where condition, every time i change the value in where condition will it create a new execution plan in sql caching and also do you have any article in using sql profiler…because i’m trying to understand sql profiler…Thanks