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

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:

SQL
Copy
1CREATE PROCEDURE MyProcedure
2WITH RECOMPILE
3AS
4BEGIN
5    SELECT * FROM MyTable;
6END;

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:

SQL
Copy
 1-- Before
 2CREATE PROCEDURE MyProcedure
 3WITH RECOMPILE
 4AS
 5BEGIN
 6    SELECT * FROM MyTable;
 7END;
 8
 9-- After
10ALTER PROCEDURE MyProcedure
11AS
12BEGIN
13    SELECT * FROM MyTable OPTION(RECOMPILE);
14END;

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.
Categories

Performance Rules, Code Smells

Additional Information
Example Test SQL
SQL
Copy
 1CREATE PROCEDURE dbo.uspProductByVendor @Name varchar(30) = '%'
 2WITH RECOMPILE, ENCRYPTION
 3AS
 4    SET NOCOUNT ON;
 5    SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'
 6    FROM Purchasing.Vendor AS v 
 7    JOIN Purchasing.ProductVendor AS pv 
 8      ON v.BusinessEntityID = pv.BusinessEntityID 
 9    JOIN Production.Product AS p 
10      ON pv.ProductID = p.ProductID
11    WHERE v.Name LIKE @Name;
Analysis Results
  Message Line Column
1 SA0149 : Consider using RECOMPILE query hint instead of WITH RECOMPILE option. 2 5
See Also

Other Resources

Analysis Rules