SQL SERVER – Understanding WITH RECOMPILE in Stored Procedures

Recently, while working with a client on a Comprehensive Database Performance Health Check, we stumbled upon a stored procedure behaving unpredictably. The culprit? Parameter sniffing. The solution? You guessed it: WITH RECOMPILE.

This handy keyword can make a huge difference when dealing with stored procedure performance issues. But what does it do? Why should you use it? And how do you use it? Let’s explore this in simple terms.

What is WITH RECOMPILE?

Every time SQL Server executes a stored procedure, it creates and stores an execution plan. This plan is reused for subsequent calls to save time. But sometimes, this “one-size-fits-all” plan doesn’t work for varying inputs.

For example, let’s say you have a stored procedure that filters customers based on their CustomerID. If the procedure is first executed for a very common ID, SQL Server optimizes the plan for that scenario. Now, if you pass an uncommon ID, the same plan might perform poorly.

This is where WITH RECOMPILE comes in. It tells SQL Server to throw away the cached plan and generate a new one for each execution. The result? A plan tailored to your current inputs.

Why Use WITH RECOMPILE?

There are a few specific scenarios where WITH RECOMPILE can save the day:

1. Parameter Sniffing Problems: When a cached plan optimized for one input performs poorly for others.
2. Dynamic Queries: When query patterns vary significantly based on parameter values.
3. Ad-hoc Queries: When the stored procedure runs infrequently and you want optimal performance for every run.

How to Use WITH RECOMPILE

Let’s dive into how you can use WITH RECOMPILE in different ways. Don’t worry—it’s simpler than it sounds!

1. Add WITH RECOMPILE to the Stored Procedure Definition

The easiest way to apply WITH RECOMPILE is to include it when you create or alter your stored procedure. This ensures every execution of the procedure generates a fresh plan.

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

Evry time you call usp_GetCustomerData, SQL Server will ignore any cached plan and create a new one.

2. Use OPTION (RECOMPILE) for Specific Queries

What if you want to recompile just one query in a procedure instead of the entire procedure? You can use the OPTION (RECOMPILE) query hint.

CREATE PROCEDURE usp_GetCustomerData
    @CustomerID INT
AS
BEGIN
    SELECT * 
    FROM Customers
    WHERE CustomerID = @CustomerID
    OPTION (RECOMPILE);
END;

In this case, only the SELECT statement will be recompiled, leaving the rest of the procedure unaffected. This is a great way to reduce the overhead of recompilation.

3. Use WITH RECOMPILE at Execution Time

Sometimes, you don’t want to modify the procedure itself. Instead, you can specify WITH RECOMPILE when executing the stored procedure.

EXEC usp_GetCustomerData @CustomerID = 42 WITH RECOMPILE;

This approach is useful when you suspect a plan issue for a specific execution. It ensures that SQL Server creates a fresh plan for this execution only, without changing the procedure definition.