DF074: The WITH RECOMPILE option is used.
Last modified: June 12, 2025
The topic describes the DF074 T-SQL code analysis rule.
Category
PERFORMANCE
Message
The WITH RECOMPILE
option is used.
Description
Consider using the RECOMPILE query hint instead of the WITH RECOMPILE
option.
Additional information
Using WITH RECOMPILE
on a stored procedure causes the entire procedure to be recompiled every time it runs, which can lead to unnecessary overhead. In contrast, the RECOMPILE hint query hint allows you to recompile only specific queries within the procedure. This provides more granular control, helps avoid performance issues caused by parameter sniffing, and ensures that execution plans reflect current data distribution.
Noncompliant code example
CREATE PROCEDURE dbo.DemoProc
@Id INT
WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON;
SELECT c.FirstName, c.LastName
FROM dbo.Customer c
WHERE c.Id = @id;
END
GO
Compliant solution
CREATE PROCEDURE dbo.DemoProc
@Id INT
AS
BEGIN
SET NOCOUNT ON;
SELECT c.FirstName, c.LastName
FROM dbo.Customer c
WHERE c.Id = @id
OPTION(RECOMPILE);
END
GO
Want to find out more?
Overview
Take a quick tour to learn all about the key benefits delivered by dbForge Studio for SQL Server.
All features
Get acquainted with the rich features and capabilities of the tool in less than 5 minutes.
Request a demo
If you consider employing this tool for your business, request a demo to see it in action.