Parameter Sniffing(version SQL SERVER 2019)

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.
• 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

* sp_recompile generates a new plan on the next execution, but it does not maintain different plans for different parameter values.

** sp_recompile causes recompilation on the next execution, not on every execution.

Main Difference

The solutions can essentially be divided into three approaches.

1. Generate a plan for the current parameter value

OPTION (RECOMPILE)
WITH RECOMPILE

Current parameter
       ↓
Recompile
       ↓
New optimized plan

This handles extreme cardinality differences well because each execution can be optimized for its current parameter value.

The trade-off is additional compilation overhead.

2. Use one reusable plan

OPTIMIZE FOR
OPTIMIZE FOR UNKNOWN

Different parameter values
          ↓
   One cached plan
          ↓
       Reused

This preserves plan reuse and avoids repeated compilation.

However, when parameter values have dramatically different cardinalities, one plan may not be efficient for all of them.

3. Force a one-time new compilation

sp_recompile

Current cached plan
        ↓
Mark for recompilation
        ↓
Next execution
        ↓
New plan generated
        ↓
New plan cached and reused

sp_recompile is different from OPTION (RECOMPILE) and WITH RECOMPILE.

It does not recompile on every execution. It causes a new compilation on the next execution, after which the newly generated plan can again be cached and reused.

For this reason, sp_recompile can be useful for troubleshooting or resetting an unsuitable cached plan, but it does not by itself solve a recurring Parameter Sniffing problem.