Please enable Javascript to correctly display the contents on Dot Net Tricks!

Stored Procedure Plan Recompilation and Performance Tuning

  Author : Shailendra Chauhan
Updated On : 24 Sep 2016
Total Views : 122,028   
Support : SQL Server 2005,2008,2012
 

The main advantage of stored procedure is, to execute T-SQL statements in less time than the similar set of T-SQL statements is executed individually. The reason to take less time is that the query execution plan for the stored procedures is already stored in the "sys.procedures" system defined view.

The recompilation process of stored procedure is like as compilation process and also reduce Sql Server performance. Stored procedure with recompilation option was introduced in Sql Server 2005. We should recompile stored procedure in following cases

  1. Changing to the schema (means adding/dropping columns, constraints, rules, index, trigger etc) of the tables or referenced table(s) or view(s).

  2. Updating the statistics used by the execution plan of stored procedure

We have two options for stored procedure recompilation

  1. Recompile option at the time of Creation

    In this we create a stored procedure with RECOMPILE option. When we call this procedure than every time this procedure will be recompile before executing.

     CREATE PROCEDURE usp_InsertEmployee
    WITH RECOMPILE
    @flag bit output,-- return 0 for fail,1 for success
    @EmpID int,
    @Name varchar(50),
    @Salary int,
    @Address varchar(100)
    AS
    BEGIN
     BEGIN TRANSACTION 
     BEGIN TRY
     Insert into Employee(EmpID,Name,Salary,Address) Values(@EmpID,@Name,@Salary,@Address);
     set @flag=1;
     commit TRANSACTION;
     END TRY 
    BEGIN CATCH
     rollback TRANSACTION;
     set @flag=0;
     END CATCH
    END 
     Declare @flag bit
    --Now Execute this procedure. Every time this procedure will be recompiled
    EXEC usp_InsertEmployee @flag output,1,'Deepak',14000,'Noida'
    if @flag=1
     print 'Successfully inserted'
    else
     print 'There is some error' 
  2. Recompile option at the time of Execution

    In this we call a stored procedure with RECOMPILE option. Hence this stored procedure will be compiled only when we use RECOMPILE option at the time of calling. This is the best option for stored procedure recompilation.

     CREATE PROCEDURE usp_InsertEmployee
    @flag bit output,-- return 0 for fail,1 for success
    @EmpID int,
    @Name varchar(50),
    @Salary int,
    @Address varchar(100)
    AS
    BEGIN
     BEGIN TRANSACTION 
     BEGIN TRY
     Insert into Employee(EmpID,Name,Salary,Address) Values(@EmpID,@Name,@Salary,@Address);
     set @flag=1;
     commit TRANSACTION;
     END TRY 
    BEGIN CATCH
     rollback TRANSACTION;
     set @flag=0;
     END CATCH
    END 
     Declare @flag bit
    --Now Execute this procedure with RECOMPILE option, if you want to recompile its execution plan
    EXEC usp_InsertEmployee @flag output,2,'Jitendra',15000,'Noida' WITH RECOMPILE
    if @flag=1
     print 'Successfully inserted'
    else
     print 'There is some error' 

Note

  1. Creating the stored procedure by using "WITH RECOMPILE" option force the SQL Server to recompile the stored procedure every time when it is called.

  2. Call the stored procedure by using "WITH RECOMPILE" option in the EXEC command.

  3. Altering the procedure will cause the SQL Server to create a new execution plan

  4. If SQL Server is restarted or stopped then all the execution plans will be flush from server cache and recreated when the stored procedure is executed after restarting the server.

  5. The "Sp_recompile" system defined stored procedure can be called to refresh the query execution plan for a particular stored procedure

Summary

In this article I try to explain the Sql Server Stored Procedure with RECOMPILE option with example. I hope after reading this article you will be able to understand recompilation plan of stored procedure in Sql Server. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.

YOU MIGHT LIKE
Free Interview Books
 
COMMENTS (0)
14 DEC
ASP.NET MVC with AngularJS Development (online)

MON-FRI 07:30 AM- 09:00 AM IST

Know More
5 DEC
AngularJS Development (online)

Mon - Fri     6:30 AM-7:30 AM IST

3 DEC
AngularJS Development (offline)

SAT,SUN     11:00 AM-12:30 PM IST

3 DEC
MEAN Stack Development (offline)

Sat, Sun     (09:30 AM-11:00 AM IST)

26 NOV
ASP.NET MVC with AngularJS Development (offline)

(SAT,SUN)     03:30 PM-05:00 PM IST

24 NOV
ASP.NET MVC with AngularJS Development (online)

MON-FRI     09:30 PM-11:00 PM IST

12 NOV
ASP.NET MVC with AngularJS Development (offline)

SAT,SUN     08:00 AM-09:30 AM

3 NOV
ASP.NET MVC with AngularJS Development (online)

MON-FRI     07:30 AM-09:00 AM IST

25 OCT
.NET Development (offline)

Mon-Fri     9:00 AM-11:00 AM IST

BROWSE BY CATEGORY
 
RECENT ARTICLES
SUBSCRIBE TO LATEST NEWS
 
LIKE US ON FACEBOOK
 

Professional Speaks

+