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 [...]