Yes, Stored Procedures are Pre-Complied after its first run. The practical example to prove this is:
Stored procedure are not pre-compiled, but compiled only during the first run. For every subsequent runs, it is for sure pre-compiled.
When we create a Stored procedure, first time and we will find that there are not casing entry for the executions of the Stored procedure.
After running the stored procedure for the first time, the entry for the cache is made in the system.
That's why Stored Procedure takes long time to run for first time.
Here are some SQL statements to see the casing options of stored procedure.
Run below code once......
-- First Clean Cache
DBCC FREEPROCCACHE
GO
IF EXISTS (SELECT * FROM sys.sysobjects WHERE TYPE = 'P' AND NAME = 'up_SampleSP')
BEGIN
DROP PROCEDURE up_SampleSP
END
GO
-- Create the Stored Procedure
CREATE PROCEDURE up_SampleSP
AS
SELECT *
FROM TableName -- Replace the table name which you have in your database
GO
-- Check the Query Plan for SQL Batch
-- [ Result -- You will find that there is no ObjectName with the name of up_SampleSP ]
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 up_SampleSP
GO
-- Check the Query Plan for SQL Batch
-- [ Result -- You will find that there is one entry with name ObjectName with name up_SampleSP ]
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
Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif
Kanakaraju_Msc, if this helps please login to Mark As Answer. | Alert Moderator