Tuesday 7 February 2012

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

 

 

 

 

 

 

9 comments:

  1. After go through this article, I think there is some confusion between Compile and Execution of Stored Procedure.

    ReplyDelete
    Replies
    1. Thanking you for viewing this article
      Please follow the example and you must get your answer.
      -- Joydeep Das

      Delete
  2. So they are actually pre-compiled and all the DBAs are correct. I honestly don't see a big difference between compiled when created or compiled when first time run. What's the point?

    ReplyDelete
  3. See also the option WITH RECOMPILE

    ReplyDelete
  4. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    (2 row(s) affected)
    Msg 208, Level 16, State 1, Procedure up_SampleSP, Line 4
    Invalid object name 'Tbl_Sample'.

    (4 row(s) affected)

    ReplyDelete
  5. Hi Joy your article is superb...i have one doubt let's suppose if input value changes then on sql server will it create new execution plan and because in execution of sql statement with different values it creates new caching...Waiting for your reply

    ReplyDelete
  6. Lets say i have a single query, which joins to 2 other tables. In addition it also has 2 self joins.
    I want to execute this query from a java cron scheduler every 10 minutes indefinitely.
    Will it be good to go for a Stored Procedure and the cron job calls this Stored Proc from a java backend ?
    Or should we avoid Stored proc and write the preparedStatement in java which executes the query every 10 mins?

    ReplyDelete