SA0149 : Consider using RECOMPILE query hint instead of WITH RECOMPILE option

Introduction

Avoid unnecessary compilation overhead in SQL Server stored procedures by misusing the WITH RECOMPILE option.

Description

When stored procedures are executed in SQL Server with the WITH RECOMPILE option, the procedure is not cached, resulting in re-compilation every time it runs. This can degrade performance as the extra work is required for repeated compilation.

For example:

CREATE PROCEDURE MyProcedure
WITH RECOMPILE
AS
BEGIN
    SELECT * FROM MyTable;
END;

In the given example, WITH RECOMPILE causes the above procedure to compile each time it is executed, which can be inefficient. Instead, the OPTION(RECOMPILE) query hint should be used selectively for individual queries within a procedure that may benefit from it.

Reduces the performance impact of frequent recompilations by allowing SQL Server to cache execution plans for subsequent use.
Facilitates optimized query execution by applying recompilation only when necessary, such as for queries with atypical, temporary, or volatile inputs.

How to fix

Avoid unnecessary recompilation overhead in SQL Server stored procedures by replacing the WITH RECOMPILE option with the OPTION(RECOMPILE) query hint when applicable.

Follow these steps to address the issue:

1.Identify stored procedures where WITH RECOMPILE is used. This forces a recompilation every time the procedure is executed, which may degrade performance.

2.Evaluate each query within these stored procedures to determine which ones, if any, would benefit from recompilation due to atypical, temporary, or volatile input data.

3.Replace the WITH RECOMPILE option in the procedure definition with OPTION(RECOMPILE) query hints for specific queries that require it. This allows SQL Server to cache execution plans for other queries, optimizing overall performance.

4.Use ALTER PROCEDURE to update the stored procedure without the WITH RECOMPILE option.

For example:

-- Before
CREATE PROCEDURE MyProcedure
WITH RECOMPILE
AS
BEGIN
    SELECT * FROM MyTable;
END;

-- After
ALTER PROCEDURE MyProcedure
AS
BEGIN
    SELECT * FROM MyTable OPTION(RECOMPILE);
END;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

13 minutes per issue.

Additional Information

Recompile a Stored Procedure - Recommendations

Example Test SQL

CREATE PROCEDURE dbo.uspProductByVendor @Name varchar(30) = '%'
WITH RECOMPILE, ENCRYPTION
AS
    SET NOCOUNT ON;
    SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'
    FROM Purchasing.Vendor AS v
    JOIN Purchasing.ProductVendor AS pv
      ON v.BusinessEntityID = pv.BusinessEntityID
    JOIN Production.Product AS p
      ON pv.ProductID = p.ProductID
    WHERE v.Name LIKE @Name;

Analysis Results

	Message	Line	Column
1	SA0149 : Consider using RECOMPILE query hint instead of WITH RECOMPILE option.	2	5

Analysis Rules