Parameter Sniffing — When the Same Plan Works for One Parameter but Not Another

2026-11-10 · 2 min

Parameter sniffing is not inherently a bug.

When compiling a stored procedure, SQL Server can use parameter values to choose an execution plan. Problems arise when the data distribution is skewed.

Example

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
CREATE OR ALTER PROCEDURE dbo.GetOrdersByCustomer
    @CustomerId int
AS
BEGIN
    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId;
END;
GO

If one customer has 5 orders and another has 5 million, a single plan may not suit both.

Common symptoms

  • The procedure is fast on one execution and very slow on another.
  • Restarting or recompiling helps, but only temporarily.
  • The plan changes after a deployment or failover.
  • Execution time depends on the parameter value used for the first compilation.
  • Query Store shows considerable variation.

RECOMPILE

1
OPTION (RECOMPILE);

A new plan is generated, at the cost of recompilation.

OPTIMIZE FOR

1
OPTION (OPTIMIZE FOR UNKNOWN);

This is not a universal solution. It must fit the actual data distribution.

Parameter Sensitive Plan

In SQL Server 2022, with an appropriate database compatibility level, the engine may use Parameter Sensitive Plan (PSP) optimization to maintain multiple plan variants for different parameter-value ranges.

Takeaway

If a procedure sometimes performs well and sometimes badly, do not start by restarting the server.

Inspect the parameter values, data distribution, and plan history.


Tags: SQLServer , ParameterSniffing , Performance , QueryTuning , DBA

Marcin Pytlik
LinkedIn · GitHub

Marcin Pytlik

LinkedIn · GitHub