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….
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?
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
Pingback: SQL SERVER – Beginning New Weekly Series – Memory Lane – #002 « SQL Server Journey with SQL Authority
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 .
Pingback: SQL SERVER – Weekly Series – Memory Lane – #007 « SQL Server Journey with SQL Authority
Pingback: SQL SERVER – Weekly Series – Memory Lane – #007 « SQL Server Journey with SQL Authority
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.