Holi Sale. Get upto 40% OFF on Job-oriented Training! Offer Ending in
D
H
M
S
Get Now
Browse Tutorials
Stored Procedure Plan Recompilation and Performance Tuning

Stored Procedure Plan Recompilation and Performance Tuning

18 Mar 2024
Advanced
7.73K Views
8 min read

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.

FAQs

Q1. What really happens when you recompile a saved procedure?

When a procedure is first compiled or recompiled, the query plan is optimized to reflect the current state of the database and its objects. If a database's data or structure changes significantly, recompiling the process updates and optimizes the query plan to reflect those changes.

Q2. What is the definition of SQL performance tuning?

SQL performance tuning, often known as performance tuning, is the process of identifying and fixing issues that may have an impact on a SQL database's efficiency. The key to good performance is ensuring that queries run quickly and with minimal resources.

Q3. How can I enhance SQL procedure performance?

Choosing appropriate data types, avoiding nulls and unnecessary clauses, strategically employing indexes, minimizing cursors and temp tables, and creating efficient stored procedures are all important ways. By adhering to these principles, you may ensure that your database works optimally for your applications.

Q4. What is the reason why stored procedures and functions enhance performance?

Stored procedures are compiled once and saved in executable format, making procedure calls rapid and efficient. Executable code is automatically cached and shared between users. This reduces memory needs and invocation overhead.

Take our free sqlserver skill challenge to evaluate your skill

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

GET CHALLENGE

Share Article
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Accept cookies & close this