What is RECOMPILE in SQL Server?

In SQL Server, RECOMPILE is an option that forces the query optimizer to not reuse a previously cached execution plan and instead generate a new execution plan every time the query or stored procedure runs.

Why Use RECOMPILE?

Normally, SQL Server caches execution plans to improve performance by avoiding the overhead of recompiling queries every time.

But sometimes, the cached plan is not optimal for all parameter values — this is especially true when:

• Parameter sniffing causes a bad plan to be reused.
• Data distribution changes significantly.
• Query performance varies widely depending on input parameters.

In these cases, using RECOMPILE forces SQL Server to create a fresh execution plan tailored to the current parameter values, which can improve query performance.

How to Use RECOMPILE

1. At the query level (using a query hint):

SELECT * FROM Orders WHERE CustomerID = @CustomerID
OPTION (RECOMPILE);

2. At the stored procedure level:

CREATE PROCEDURE GetOrders
    @CustomerID INT
WITH RECOMPILE
AS
BEGIN
    SELECT * FROM Orders WHERE CustomerID = @CustomerID;
END

3. Using the system stored procedure sp_recompile:

EXEC sp_recompile 'GetOrders';

Pros and Cons

Pros	Cons
Gets a fresh, optimized plan each time	Extra CPU overhead for recompilation
Avoids poor performance from bad cached plans	May slow down queries if overused

Summary

RECOMPILE forces SQL Server to generate a fresh execution plan each time, helping to avoid performance problems caused by parameter sniffing or stale plans.

Use it when query performance varies widely with different parameters, but be careful since it increases CPU usage.