Recompile a stored procedure

Problem: How to recompile a stored procedure for better performance without restarting SQL Server.

Solution: Recompile a Stored Procedure.

Recompile is a QUERY HINT which allows us to recompile the query plan and optimize the query, it is recommended to use when there is a data change underlying the tables which are used inside a stored procedure. As the plan which was initially created by SQL server can behave differently when there is data change, its preferred to recompile the query or procedure for optimal performance.

Another good reason to recompile a stored procedure is to counteract the “parameter sniffing”, If parameter values on the procedure are frequently atypical, forcing a recompile of the procedure and a new plan based on different parameter values can improve performance.

Also there is an option to recompile a specific statement inside the procedure.

Recompiling a stored procedure can be done in three ways:

1.        WITH RECOMPILE
2.        RECOMPILE statement Level
3.      sp_recompile (system procedure),this does not execute the procedure but marks the procedure to recompile.

Example:

In this article I am going to show you how we can recompile a stored procedure.

Method 1: To create a procedure WITH RECOMPILE explicitly

IF OBJECT_ID ( 'dbo.EmployeebyEmployeeName', 'P' ) IS NOT NULL  

    DROP PROCEDURE dbo.EmployeebyEmployeeName; 

GO 

CREATE PROCEDURE dbo.EmployeebyEmployeeName @Name varchar(50) = '%' 

WITH RECOMPILE 

AS 

    SET NOCOUNT ON; 

    SELECT E.Name AS 'Employee name', C.Name AS 'Company name' 

    FROM [dbo].[Employees] AS E

    JOIN dbo.Company AS C  

      ON C.Company_ID =E.Company_ID  

    WHERE E.Name LIKE @Name; 

 

Method 2: To execute the procedure WITH RECOMPILE.

EXEC dbo.EmployeebyEmployeeName  WITH RECOMPILE

Method 3: To mark the procedure to recompile using system procedure sp_recompile.

EXEC sp_recompile 'dbo.EmployeebyEmployeeName'