SQL Server parameter sniffing: a good plan for one value, catastrophic for the next

A stored procedure that ran in 30 ms for weeks is now taking 8 seconds per call. Query text unchanged. Indexes unchanged. Statistics look fine. The application is timing out. The same query with literal values returns instantly. With OPTION (RECOMPILE) appended, it returns instantly. The cached plan is the problem.

This is parameter sniffing. The optimizer compiled the plan for the parameter values it saw first, cached the result, and every subsequent call reuses a plan that is wrong for the values it is now processing. On a table with skewed data, a plan sized for 12 rows is catastrophic when fed 12 million.

Parameter sniffing itself is not a bug. It is how the optimizer uses known values at compile time to pick a join strategy, memory grant, and access path. The failure is the mismatch between the cached plan and the runtime values. This article covers how to recognize the pattern, confirm it, and pick the right fix without making the underlying workload worse.

What this means

The SQL Server query optimizer sees parameter values at compilation time, not at runtime. When a parameterized query or stored procedure is compiled, the optimizer “sniffs” the parameter values, consults statistics and histograms, and produces a plan shaped for those specific values. That plan goes into the plan cache and is reused for every subsequent execution regardless of what parameter values the application sends.

When the data is uniform, this is invisible. Every parameter value has roughly the same cardinality, and one plan works for all of them. When the data is skewed, the first parameter values the optimizer sees determine the plan shape for every subsequent call. A plan compiled for a selective value (CustomerID = 7, 3 rows) might use nested loops with a tiny memory grant and no parallelism. When the same cached plan runs for CustomerID = 999 (800,000 rows), the nested loops become catastrophic. CPU surges. The undersized memory grant spills the sort to TempDB. I/O stalls climb. Queries that were not even involved queue behind workers consumed by this one plan.

The signature of the pattern is sharp: query text unchanged, regression sudden, a plan change visible in Query Store at the moment of regression, and OPTION (RECOMPILE) on the same text restores baseline performance. If any of those four is missing, you are probably looking at a different problem: statistics drift, schema change, blocking, or genuine resource exhaustion.

Common causes

CauseWhat it looks likeFirst thing to check
First execution after cache clear with atypical parameterRegression starts shortly after restart, failover, or DBCC FREEPROCCACHE (destructive: clears the entire plan cache, expect a temporary CPU spike as every procedure recompiles)Query Store plan creation time vs. regression onset
Statistics update triggering a recompileRegression coincides with an auto-stats update eventsys.dm_db_stats_info last update time vs. regression time
Memory pressure evicting the good planRegression correlates with buffer pool pressure; recompiled plan sniffs an atypical valuePLE drop and lazy writer activity around the regression
Application reusing one procedure for skewed distributionsProcedure accepts a wide range of selectivity (status = ‘open’ returns 10 rows, status = ‘all’ returns 10M)Histogram on the parameterized column

Quick checks

All read-only. Run them against the database you suspect, in order.

-- 1. Top queries by average duration in the last hour (Query Store, SQL 2016+)
SELECT TOP (20)
    q.query_id,
    p.plan_id,
    qt.query_sql_text,
    rs.avg_duration / 1000.0 AS avg_duration_ms,
    rs.avg_cpu_time / 1000.0 AS avg_cpu_ms,
    rs.avg_logical_io_reads,
    rs.avg_physical_io_reads,
    rs.count_executions,
    rs.last_execution_time
FROM sys.query_store_runtime_stats rs
JOIN sys.query_store_plan p ON rs.plan_id = p.plan_id
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
ORDER BY rs.avg_duration DESC;

-- 2. Queries with more than one plan variant (sign of plan instability)
SELECT
    q.query_id,
    qt.query_sql_text,
    COUNT(*) AS plan_count,
    MAX(p.last_execution_time) AS last_seen
FROM sys.query_store_plan p
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
GROUP BY q.query_id, qt.query_sql_text
HAVING COUNT(*) > 1
ORDER BY MAX(p.last_execution_time) DESC;

-- 3. Existing forced plans and their failure counts
SELECT
    q.query_id,
    p.plan_id,
    p.is_forced_plan,
    p.force_failure_count,
    p.last_force_failure_reason_desc,
    p.last_force_failure_time
FROM sys.query_store_plan p
JOIN sys.query_store_query q ON p.query_id = q.query_id
WHERE p.is_forced_plan = 1
ORDER BY p.last_force_failure_time DESC;

-- 4. Currently running memory grants (bad plans over- or under-request)
SELECT
    session_id,
    requested_memory_kb,
    granted_memory_kb,
    used_memory_kb,
    wait_time_ms,
    dop,
    query_cost
FROM sys.dm_exec_query_memory_grants
ORDER BY wait_time_ms DESC;

-- 5. Memory grants waiting (RESOURCE_SEMAPHORE is the symptom)
SELECT cntr_value AS memory_grants_pending
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Memory Grants Pending'
  AND object_name LIKE '%Memory Manager%';

-- 6. TempDB internal object growth (sort/hash spills from undersized grants)
USE tempdb;
SELECT
    SUM(internal_object_reserved_page_count) * 8 / 1024 AS internal_objects_mb,
    SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_mb,
    SUM(unallocated_extent_page_count) * 8 / 1024 AS free_space_mb
FROM tempdb.sys.dm_db_file_space_usage;

How to diagnose it

The flow below assumes Query Store is enabled (SQL 2016+). On older versions or with Query Store disabled, the same logic applies, but you lose historical plan data and confirmation becomes guesswork.

flowchart TD
    A[Regression reported] --> B{Query text unchanged?}
    B -- No --> Z[Not parameter sniffing]
    B -- Yes --> C{Regression sudden?}
    C -- No --> Z
    C -- Yes --> D{Plan change in Query Store at regression time?}
    D -- No --> Z
    D -- Yes --> E{OPTION RECOMPILE makes it fast?}
    E -- No --> Z
    E -- Yes --> F[Confirmed: parameter sniffing]
    F --> G[Compare ParameterCompiledValue vs ParameterRuntimeValue in plan XML]
    G --> H[Pick fix: force plan, OPTIMIZE FOR, RECOMPILE, or redesign]

  1. Identify the regressed query in Query Store. Use the top-by-duration query above. Note the query_id and plan_id of the currently active plan.

  2. Pull the plan history for that query_id. A query that switched plans at the moment the regression started is the strongest possible signal.

-- Returns one row per runtime_stats_interval per plan. Multiple rows per plan_id is normal.
SELECT
    p.plan_id,
    p.query_plan_hash,
    rs.first_execution_time,
    rs.last_execution_time,
    rs.avg_duration / 1000.0 AS avg_duration_ms,
    rs.avg_cpu_time / 1000.0 AS avg_cpu_ms,
    rs.avg_logical_io_reads,
    rs.count_executions,
    p.query_plan
FROM sys.query_store_plan p
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE p.query_id = <your_query_id>
ORDER BY rs.first_execution_time DESC;

  1. Compare the two plans. The regressed plan usually differs in one of: join type (loops to hash or vice versa), parallelism (serial to parallel or vice versa), missing index usage, or memory grant size. Open the graphical plan or read the XML.

  2. Confirm with OPTION (RECOMPILE). Run the same query text with OPTION (RECOMPILE) appended. If it returns to baseline duration, the cached plan was the problem. If it does not help, you do not have parameter sniffing; you have a statistics, blocking, or genuine resource problem.

  3. Read the parameter values from the plan XML. Open the regressed plan as XML and look for the <ParameterList> element. Each <ColumnReference Column="@YourParam"> entry carries a ParameterCompiledValue (the value the plan was built for) and a ParameterRuntimeValue (the value of this specific execution). When the compiled value is highly selective and the runtime value is broad, or vice versa, you have direct evidence.

Metrics and signals to monitor

SignalWhy it mattersWarning sign
Query Store avg_duration per plan_idThe only reliable per-plan historical latency signalPlan with a sudden 10x+ avg_duration jump
Memory Grants Pending counterBad plans with oversized grants starve other queriesSustained nonzero during the regression
TempDB internal objects MBUndersized grants spill sorts and hashesSpike coincident with the regression
RESOURCE_SEMAPHORE wait timeAggregate time spent waiting for a grantBecomes a top-5 wait during the regression
PAGEIOLATCH_* waitsBad nested-loop plans do random I/OSpikes when the bad plan runs
SQL Compilations/sec to Batch Requests/sec ratioExcessive RECOMPILE hints show up hereSustained ratio above 10%
Plan count per query_id in Query StorePlan instability is a leading indicatorSustained more than 1 active plan for one query

Fixes

Pick the smallest fix that holds. None of these is free.

Force the known-good plan in Query Store

The fastest, most surgical fix. Identify the query_id and plan_id of the last-known-good plan, then force it.

-- SQL 2016+. Forces a specific plan for a specific query.
EXEC sp_query_store_force_plan @query_id = <query_id>, @plan_id = <plan_id>;

Tradeoffs: the forced plan is frozen. If statistics or schema change in a way that would benefit a new plan, you will not get the new plan until you unforce. Check force_failure_count and last_force_failure_reason_desc in sys.query_store_plan periodically. A forced plan can fail to apply after a schema change.

To unforce:

EXEC sp_query_store_unforce_plan @query_id = <query_id>, @plan_id = <plan_id>;

Automatic Plan Correction

SQL Server 2017+ can detect plan regressions from Query Store history and force the last-known-good plan automatically.

-- View queries SQL Server thinks should have their plan forced
SELECT
    r.name,
    r.reason,
    r.state_desc,
    JSON_VALUE(r.details, '$.planForceDetails.queryId') AS query_id,
    JSON_VALUE(r.details, '$.planForceDetails.regressedPlanId') AS regressed_plan_id,
    JSON_VALUE(r.details, '$.planForceDetails.recommendedPlanId') AS recommended_plan_id
FROM sys.dm_db_tuning_recommendations r;

To enable automatic forcing database-wide:

ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

Tradeoffs: same as manual forcing, plus you are trusting the engine’s regression detection. Review the recommendations before enabling in production the first time.

OPTIMIZE FOR hint

Tell the optimizer which value to compile for, regardless of what was sniffed.

-- Compile for a specific literal value
SELECT ... WHERE Status = @status OPTION (OPTIMIZE FOR (@status = 'open'));

-- Compile for the average (uses statistics, not a sniffed value)
SELECT ... WHERE Status = @status OPTION (OPTIMIZE FOR UNKNOWN);

Tradeoffs: OPTIMIZE FOR UNKNOWN produces a middle-of-the-road plan that is mediocre for both selective and broad values. Useful when no single value dominates. OPTIMIZE FOR (@param = 'value') works when you know the dominant case.

RECOMPILE hint

Discard the plan after every execution. Every call gets a fresh plan optimized for its actual parameter values.

-- Per-query
SELECT ... WHERE Status = @status OPTION (RECOMPILE);

-- Procedure level
CREATE PROCEDURE dbo.GetOrders @Status varchar(10)
WITH RECOMPILE
AS ...

Tradeoffs: CPU cost goes up. On a high-frequency OLTP query this is the wrong fix and will show up as elevated SQL Compilations/sec. Best for infrequently-called but high-variance queries such as reports and admin functions.

Disable parameter sniffing database-wide

Last-resort hammer. Equivalent to OPTIMIZE FOR UNKNOWN for every query in the database.

-- SQL 2016+
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;

Tradeoffs: removes the benefit of parameter sniffing for every query, including the ones that were benefiting from it. Reserve for databases full of poorly-designed parameterized queries that you cannot fix individually.

Newer engine features: PSPO and OPPO

  • Parameter Sensitive Plan optimization (PSPO): introduced in SQL Server 2022 at database compatibility level 160. The engine can cache multiple plan variants for a single query (small, medium, and large result-set variants) and dispatch at runtime based on parameter cardinality. Documented limitations include restrictions on predicate types; confirm the full list against current docs before relying on PSPO.
  • Optional Parameter Plan Optimization (OPPO): introduced in SQL Server 2025 at database compatibility level 170. Targets the optional-parameter pattern WHERE col = @p OR @p IS NULL.

Per-query hints to disable these features when they cause problems:

-- SQL 2022+: disable PSPO for this query
OPTION (USE HINT('DISABLE_PARAMETER_SENSITIVE_PLAN'));

-- SQL 2025+: disable OPPO for this query
OPTION (USE HINT('DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION'));

Redesign

When none of the above holds, the underlying issue is usually one query being asked to serve wildly different selectivity. Split it into two procedures, one for the selective path and one for the broad path. Add an index that makes the broad path cheap. Or materialize a summary table for the broad case. This is the only durable fix for some workloads.

Prevention

  • Enable Query Store on every production database. It is on by default for newly-created databases in SQL Server 2022+, but upgraded or migrated databases need it enabled manually: ALTER DATABASE CURRENT SET QUERY_STORE = ON;.
  • Establish a baseline before the next incident. Without historical Query Store data, confirming a regression is guesswork. Let Query Store collect a week of normal workload before you need it.
  • Track plan count per query. A query with a stable plan count of 1 is not regressing. A query whose plan count grows week over week is a candidate for forcing or hinting.
  • Alert on sudden duration regressions for known-critical queries. Query Store exposes per-plan runtime stats. Alert when avg_duration for a tracked query jumps more than 2x baseline.
  • Keep statistics current. Auto-update is on by default, but for skewed tables consider asynchronous auto-update statistics to avoid compile-time penalties during peak hours.

How Netdata helps

  • Per-second visibility into RESOURCE_SEMAPHORE and memory grants pending. A parameter sniffing regression frequently shows up as a sudden spike in memory grant waiters before users report timeouts.
  • TempDB internal object size trended over time. Sort and hash spills from undersized grants show up here, and the timing correlation with a query regression is direct evidence of the pattern.
  • PAGEIOLATCH wait time as a proportion of total waits. Bad nested-loop plans against large sets drive this up sharply. Correlating the spike with a Query Store plan change pinpoints the regressed query.
  • Batch requests/sec against compilations/sec ratio. Over-applied OPTION (RECOMPILE) makes this ratio climb, trading one CPU problem for another. The trend catches the overcorrection.

Netdata’s Microsoft SQL Server monitoring brings these signals together with per-second metrics and ML anomaly detection.