Is STORED PROCEDURE is PRE-COMPILED

 

 

I saw many of the DBA have the wrong concept related to stored procedure. They think that the stored procedures are pre-compiled.

I this article, I am trying to resolve this issue.

First of all we have to understand, what a stored procedure is. The stored procedure is nothing but a bundle of T-SQL statement executing all together. So, why it is so important to making stored procedure? We can execute all the T-SQL statement one by one without using any stored procedure. Suppose we have a stored procedure contains 10 T-SQL statements.  We can run the stored procedure to execute them all or we can run each T-SQL statement separately. So what the differences? Yes there are certain differences here in this example. If we run the stored procedure it builds a single execution plan and if we run then 10 T-SQL statements separately it builds 10 separate execution plan.

Now, I am explaining related to stored procedure pre-compile options.

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.

 

-- 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   Tbl_Sample

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

 

I think the information of the article is quite informative and thanking you to provide your valuable time on it.

 

Posted by: MR. JOYDEEP DAS