Click or drag to resize

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

The topic describes the SA0149 analysis rule.

Message

Consider using RECOMPILE query hint instead of WITH RECOMPILE option

Description

The rule checks that stored procedures do not use WITH RECOMPILE procedure option.

The OPTION(RECOMPILE) is the preferred method when a recompile is needed.

The WITH RECOMPILE procedure option instructs the Database Engine does not cache a plan for this procedure and the procedure is compiled at run time.

To instruct the Database Engine to discard plans for individual queries inside a stored procedure, use the RECOMPILE query hint. Use the RECOMPILE query hint when a typical or temporary values are used in only a subset of queries that belong to the stored procedure.

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.

Categories

Performance Rules

Additional Information
Example Test Script
 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

 MessageLineColumn
1SA0149 : Consider using RECOMPILE query hint instead of WITH RECOMPILE option.25
See Also

Other Resources