Parameter Sniffing

Parameter Sniffing is a SQL Server behavior where, during query compilation, the optimizer uses the actual parameter value passed to a stored procedure or parameterized query to create an execution plan.

The generated execution plan is then stored in the plan cache and can be reused by subsequent executions with different parameter values.

This is normally beneficial because SQL Server can use the actual parameter value together with table statistics to create an efficient execution plan.

The problem appears when different parameter values have significantly different cardinalities.

For example:

@CustomerID = 1001  →          5 rows
@CustomerID = 1     →  1,000,000 rows

If the procedure is first compiled with:

@CustomerID = 1001

SQL Server may create a plan suitable for retrieving only a few rows, such as an Index Seek with Key Lookups.

That plan is cached.

Later, the same procedure may be executed with:

@CustomerID = 1

which returns 1,000,000 rows.

SQL Server may reuse the previously cached plan even though a completely different execution strategy would be more efficient for such a large number of rows.

The opposite situation is also possible: a plan compiled for a high-cardinality parameter can later be reused for a low-cardinality parameter.

Therefore, Parameter Sniffing itself is not a problem. It becomes a performance problem when a plan generated for one parameter value is reused for another parameter value with a substantially different data distribution or cardinality.

First parameter value
        ↓
SQL Server sniffs the value
        ↓
Optimizer creates a plan for that value
        ↓
Plan is cached
        ↓
Different parameter value
        ↓
Cached plan is reused
        ↓
Plan may no longer be efficient

Resolution

There is no single solution for every Parameter Sniffing problem. The appropriate approach depends on the query, data distribution, execution frequency, and SQL Server version.

Common solutions include:

• OPTION (RECOMPILE) — generates a new execution plan for the current parameter value each time the statement executes.
• WITH RECOMPILE — recompiles the stored procedure for each execution.
• OPTIMIZE FOR — instructs SQL Server to optimize the query for a specified parameter value.
• OPTIMIZE FOR UNKNOWN — prevents optimization for the currently sniffed parameter value and instead uses a more general statistical estimate.
• Query Store hints / plan control — can influence or stabilize query behavior without changing application code.
• Parameter Sensitive Plan (PSP) optimization — available in modern SQL Server versions and allows SQL Server to maintain multiple plan variants for different parameter-value ranges.

Each solution has advantages and disadvantages. For example, recompilation can produce a plan appropriate for the current parameter value, but frequent compilation also consumes CPU resources.

The correct solution should therefore be selected only after identifying how different parameter values affect the execution plan.

Parameter Sniffing Solutions — Advantages and Disadvantages

Quick Comparison