Stored Procedure Plan Recompilation and Performance Tuning

Estimated study time: 8 minutes. Keep cached plans efficient and predictable.

When a stored procedure runs for the first time, SQL Server compiles an execution plan and caches it, so later calls can reuse that plan instead of paying the compilation cost again. Sometimes, though, that cached plan gets thrown away and rebuilt — this is plan recompilation.

Common Causes of Recompilation

Schema changes: Adding, dropping, or altering a column or index used by the procedure.
Statistics updates: When enough rows change in a referenced table, SQL Server updates statistics, which can invalidate the existing plan.
Mixing DDL and DML: Creating a temp table and then querying it in the same procedure often forces a recompile.
SET option changes: Different SET options (like ANSI_NULLS) between the session that created the plan and the one calling it.
Explicit recompile hints: Using WITH RECOMPILE or sp_recompile.

Forcing a Recompile

-- Recompile just this execution
EXEC GetEmployeeByID @EmpID = 101 WITH RECOMPILE;

-- Mark the whole procedure for recompilation on next call
EXEC sp_recompile 'GetEmployeeByID';

The Parameter Sniffing Problem

SQL Server builds its first execution plan based on the parameter values passed in on that first call — a behavior called parameter sniffing. If later calls use very different values (say, a highly selective ID versus one that matches most of the table), the cached plan can perform poorly for the atypical case.

Fixing Parameter Sniffing Issues

-- Option 1: Force recompilation on every execution
CREATE PROCEDURE GetOrdersByStatus
  @Status VARCHAR(20)
WITH RECOMPILE
AS
BEGIN
  SELECT * FROM Orders WHERE Status = @Status;
END;

-- Option 2: Use OPTIMIZE FOR to hint a representative value
SELECT * FROM Orders WHERE Status = @Status
OPTION (OPTIMIZE FOR (@Status = 'Pending'));

-- Option 3: Use local variables to avoid sniffing entirely
CREATE PROCEDURE GetOrdersByStatus
  @Status VARCHAR(20)
AS
BEGIN
  DECLARE @LocalStatus VARCHAR(20) = @Status;
  SELECT * FROM Orders WHERE Status = @LocalStatus;
END;

Monitoring Recompilations

The sql_statement_recompile Extended Events session, or the older SQL Profiler SP:Recompile event, can be used to observe when and why recompilations happen in a running system.