Diagnosing and Fixing Parameter Sniffing

1. What parameter sniffing is and why it surprises people

Parameter sniffing describes the behavior of a query planner that builds the execution plan for a parameterized query based on the parameter values passed at first compilation, and then reuses that plan for every subsequent call with different values. The name comes from the optimizer "sniffing" the actual values on the first call and basing its cost estimate on them, instead of planning fresh for every call. This is fundamentally sensible behavior, because it avoids repeated compilation of the same query structure and thereby saves CPU time.

Parameter sniffing becomes a problem when the data distribution is highly skewed and the first call happens to supply an atypical value. A plan optimized for a very rare category with ten rows might use an index seek, but the same plan becomes catastrophically slow when applied to a very common category with a million rows, because an index seek followed by a key lookup per row is orders of magnitude more expensive at a million hits than a simple table scan. This regularly surprises developers, because the query itself stays unchanged, only the passed values differ.

2. Symptoms: same query, drastically different runtime

The most conspicuous symptom of parameter sniffing is a query that runs in a few milliseconds ninety percent of the time, but occasionally, seemingly at random, takes several seconds or even minutes, even though neither the query nor the overall data volume has changed. Unlike classic query optimization problems, where a query is consistently slow, parameter sniffing shows up as an intermittent problem that is hard to reproduce because it depends on the concrete parameter values of the particular call.

A second symptom often appears after a database server restart, a plan cache flush, or a failover: right after that, a previously unremarkable query suddenly runs consistently slow, because the first call after the restart happened to carry an atypical parameter value, and the resulting plan now applies to every subsequent call until the plan gets recompiled for some other reason. This pattern, correlated with a restart timestamp, is a strong indicator of parameter sniffing as the cause.

3. How the optimizer ends up with a bad plan

The mechanism behind parameter sniffing lies in the optimizer's cost estimation. During first compilation of a parameterized query, the optimizer looks at the statistics of the affected columns and estimates, based on the concretely passed parameter value, how many rows the query is likely to return. That estimate determines the chosen join strategy, the choice between index seek and table scan, and the join order for more complex queries. The resulting plan gets stored in the plan cache and reused for an identical query structure, regardless of the actual parameter values, on subsequent calls.

The core problem is that this cost estimate is only correct for the specific data distribution of the first parameter value. With evenly distributed data this barely matters, because every parameter value leads to a similar row count and the cached plan stays close to optimal for all values. With heavily skewed data, for example a status field with ninety percent "completed" and ten percent "in progress", the same plan can be brilliant for one value and catastrophic for another, because the optimal strategy fundamentally depends on the expected row count.

-- Highly skewed distribution: 2 rows for one status, 2 million for another
-- SELECT COUNT(*) FROM orders GROUP BY status;
--   status='cancelled'  -> 2 rows
--   status='completed'  -> 2,000,000 rows

CREATE PROCEDURE GetOrdersByStatus @Status VARCHAR(20)
AS
BEGIN
    SELECT * FROM orders WHERE status = @Status;
END;

-- First call with the RARE value compiles a plan optimized for 2 rows
EXEC GetOrdersByStatus @Status = 'cancelled';   -- compiles: Index Seek plan

-- Same cached plan gets reused for the COMMON value -> catastrophic
EXEC GetOrdersByStatus @Status = 'completed';   -- reuses Index Seek plan
-- -> 2,000,000 key lookups instead of a single table scan

4. Diagnosis: comparing the plan cache against execution statistics

The most reliable diagnosis for parameter sniffing starts with comparing the estimated execution plan stored in the plan cache against a freshly compiled plan for the currently problematic parameter value. In SQL Server, sys.dm_exec_query_stats together with sys.dm_exec_query_plan exposes the currently cached plan along with the parameter values it was originally compiled with, visible through the ParameterCompiledValue property in the XML plan.

In PostgreSQL the same underlying problem manifests differently, because PostgreSQL by default plans simple queries fresh for every call, but for prepared statements switches to a generic, parameter independent plan after the fifth execution, provided that plan is not estimated significantly more expensive than the individual plans. Diagnosis here happens via EXPLAIN ANALYZE with the actually problematic parameter values, compared against PREPARE and EXPLAIN EXECUTE, to contrast the generic plan against the specific one.

-- SQL Server: inspect the cached plan and the parameter values it was compiled for
SELECT
    qs.execution_count,
    qs.total_worker_time / qs.execution_count AS avg_cpu_time,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.plan_handle IN (
    SELECT plan_handle FROM sys.dm_exec_cached_plans
    WHERE cacheobjtype = 'Compiled Plan'
);
-- Look for "ParameterCompiledValue" in the plan XML to see the sniffed value

-- PostgreSQL: compare the generic plan against a plan for the specific value
PREPARE order_lookup (varchar) AS SELECT * FROM orders WHERE status = $1;
EXPLAIN ANALYZE EXECUTE order_lookup('completed');
-- vs. a plain, non-prepared query for the same value:
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'completed';

5. Fix: OPTIMIZE FOR, query hints and plan guides

The most direct countermeasure in SQL Server is the hint OPTION (OPTIMIZE FOR (@Status = 'completed')), which instructs the optimizer to always compile the plan for a specific, representative value, regardless of what was actually passed on the first call. Alternatively, OPTION (RECOMPILE) forces recompilation on every call, which eliminates parameter sniffing entirely but incurs CPU overhead for the repeated compilation, which can be uneconomical for very frequently called, simple queries.

A third, more granular option is OPTION (OPTIMIZE FOR UNKNOWN), which instructs the optimizer to use average selectivity across all values instead of specializing for a concrete one. This variant produces a compromise plan that is not optimal for any single value, but robust across the whole set of calls, especially useful when no single category clearly dominates. PostgreSQL offers a similar option per session with plan_cache_mode = force_custom_plan, always planning individually instead of switching to a generic plan.

-- SQL Server: force the plan to always assume the common value's distribution
SELECT * FROM orders WHERE status = @Status
OPTION (OPTIMIZE FOR (@Status = 'completed'));

-- Force recompilation on every execution (removes sniffing, adds CPU overhead)
SELECT * FROM orders WHERE status = @Status
OPTION (RECOMPILE);

-- Use average selectivity instead of the sniffed value
SELECT * FROM orders WHERE status = @Status
OPTION (OPTIMIZE FOR UNKNOWN);

-- PostgreSQL: force per-call custom planning instead of a generic plan
SET plan_cache_mode = force_custom_plan;

6. Fix: improving statistics and histograms

An often overlooked but durable approach against parameter sniffing is improving the underlying statistics themselves. Standard statistics summarize the data distribution into a limited number of histogram buckets, which leads to inaccurate estimates for heavily skewed data. A finer resolution, in SQL Server through CREATE STATISTICS with an increased sample rate or FULLSCAN, improves the accuracy of the cost estimate for each individual parameter value and thereby reduces the likelihood of extremely bad plans.

In PostgreSQL, the column setting ALTER TABLE table ALTER COLUMN SET STATISTICS 500 controls the number of histogram buckets collected for that column, up to a maximum of ten thousand compared to the default of one hundred. For columns with known heavy skew in their distribution, like a status field with a few dominant values, this increase is worth applying deliberately, because it sharpens the optimizer's cost estimate without requiring any change to the query itself or the application code.

7. Fix: query rewriting and local variables

A structural fix against parameter sniffing is rewriting the query so that it uses different, specialized execution paths for strongly diverging cases. Instead of a single parameterized query that covers every possible status value, a conditional branch in application code or in a stored procedure can provide a separate, dedicated path with its own, independently compiled plan for known outlier values.

A related trick, common in SQL Server, is assigning the parameter to a local variable inside the stored procedure before using it in the WHERE clause. Since the optimizer does not know a concrete value for local variables, it falls back to average selectivity, similar to OPTIMIZE FOR UNKNOWN. This trick is unofficial and should be used deliberately and documented, because it reads like a side effect to another developer reading the code later without knowing the background.

8. When parameter sniffing actually helps

It is worth emphasizing that parameter sniffing is not a fundamental design flaw, in the vast majority of cases it actually improves performance. With evenly distributed data, or queries where every possible parameter value leads to a similar row count, the cached, parameter specific plan delivers consistently good results while also saving the CPU cost of repeated compilation. The problem only arises with heavily skewed data distributions combined with queries whose optimal strategy strongly depends on the expected row count.

The practical consequence: blanket applying OPTION (RECOMPILE) to every query to avoid parameter sniffing is, in most cases, the wrong solution, because it negates the actual benefit of plan caching, reduced compilation cost, for the vast majority of unproblematic queries. Applying the techniques from sections five through seven in a targeted way only to the concretely affected queries is the more economical approach.

9. Comparing countermeasures

The following table compares the common countermeasures by CPU overhead and suitability for different scenarios.

For most production systems, combining improved statistics as a durable baseline protection with targeted OPTIMIZE FOR hints for individually known problematic queries is the most economical path, without resorting to expensive recompiling across the board.

10. Summary

Parameter sniffing arises because the optimizer compiles an execution plan based on the first parameter value and reuses that plan for every subsequent call, regardless of whether their data distribution looks similar. With heavily skewed data, this leads to intermittently catastrophically slow queries, while the same query stays unremarkable for other parameter values. Diagnosis succeeds through comparing the cached plan against a freshly compiled plan for the problematic value.

The most effective countermeasures range from targeted query hints like OPTIMIZE FOR, through improved statistics, to structural query rewriting for known outliers. Blanket recompiling on every call fully avoids parameter sniffing but causes unnecessary CPU overhead for the vast majority of unproblematic queries and should therefore be applied deliberately rather than universally.

11. FAQ: Parameter Sniffing