Stored Procedure Plan Recompilation and Performance Tuning

Stored Procedure Plan Recompilation and Performance Tuning: An Overview

Stored Procedure Plan Recompilation and Performance Tuning are essential to optimizing database operations. The main advantage of the stored procedure is its ability to execute T-SQL statements in less time than a similar set of T-SQL statements executed individually. This efficiency is achieved because the query execution plan for stored procedures is already stored in the "sys.procedures" system-defined view. If you want to learn more about optimizing SQL Server performance, consider checking out a SQL Server Tutorial or enrolling in a SQL Server Certification Course.

What is Stored Procedure Plan Recompilation and Performance Tuning?

The recompilation process of the stored procedure is like a compilation process and also reduces Sql Server performance. A stored procedure with a recompilation option was introduced in Sql Server 2005. We should recompile the stored procedure in the 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.

Options for Stored Procedure Recompilation

We have two options for stored procedure recompilation

1. Recompile option at the time of Creation

In this, we create a stored procedure with the RECOMPILE option. When we call this procedure then every time this procedure will be recompiled 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 

This stored procedure 'usp_InsertEmployee' adds employee information into a database table, setting a flag to 1 for successful insertion and 0 for failure, with error handling and transaction management built in.

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' 

This example declares a flag variable and calls the stored function usp_InsertEmployee, which adds employee information and sets the flag appropriately. If the insertion is successful (flag = 1), it displays "Successfully inserted"; otherwise, it displays "There is some error".

2. Recompile option at the time of Execution

In this, we call a stored procedure with the RECOMPILE option. Hence this stored procedure will be compiled only when we use the 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 

This stored procedure, usp_InsertEmployee, adds employee information into a database table, setting a flag to 1 for successful insertion and 0 for failure, while also managing transactional integrity and error conditions.

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' 

This code declares a flag variable and calls the stored method usp_InsertEmployee with the RECOMPILE option to recompile the execution plan. It then checks the flag to see if the insertion was successful, and outputs the appropriate message.

Note

1. Creating the stored procedure by using the "WITH RECOMPILE" option forces the SQL Server to recompile the stored procedure whenever it is called.

2. Call the stored procedure by using the "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 flushed from the 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 the RECOMPILE option with an example. I hope after reading this article you will be able to understand the recompilation plan of the stored procedure in the Sql Server. I would like to have feedback from my blog readers. Please post your feedback, questions, or comments about this article.

Take our Sqlserver skill challenge to evaluate yourself!

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET FREE CHALLENGE