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)
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….
[...] Server Interview Questions and Answers ISBN: 1466405643 Page#99 Stored Procedure are Compiled on First Run – SP taking Longer to Run First Time Recompile Stored Procedure at Run Time Encrypted Stored Procedure and Activity Monitor Stored [...]
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?
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
what does pre-compile actually means? we say sp are pre-compile
[...] Stored Procedure are Compiled on First Run – SP is taking Longer to Run First Time A myth is quite prevailing in the industry that Stored Procedures are pre-compiled and they should always run faster. It is not true. Stored procedures are compiled on very first execution of it and that is the reason why it takes longer when it executes first time. In this blog post I had a great time discussing the same concept. If you do not agree with it, you are welcome to read this blog post. [...]
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 .
[...] Stored Procedure are Compiled on First Run – SP taking Longer to Run First Time Is stored procedure pre-compiled? Why the Stored Procedure takes a long time to run for the first time? This is a very common questions often discussed by developers and DBAs. There is an absolutely definite answer but the question has been discussed forever. 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. Read the entire article for example and demonstration. [...]
[...] Stored Procedure are Compiled on First Run – SP taking Longer to Run First Time Is stored procedure pre-compiled? Why the Stored Procedure takes a long time to run for the first time? This is a very common questions often discussed by developers and DBAs. There is an absolutely definite answer but the question has been discussed forever. 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. Read the entire article for example and demonstration. [...]
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?