The report ran in two seconds for tenant A and timed out at thirty for tenant B, on the same stored procedure, on the same afternoon. Restarting the instance fixed it until Thursday, when a different parameter happened to compile first and a different set of tenants started timing out. That is the parameter sniffing shape, and after you have been bitten twice you stop blaming the plan cache and start reading plans.
Sniffing is not a bug. It is a feature with sharp edges, and the right response is a ranked toolkit, not a single magic hint. Here is how I diagnose it and how I choose the fix on SQL Server 2016 through 2022, in the order that has cost my systems the least pain.
Why does parameter sniffing exist at all?
Because plan reuse is the entire economics of the cache: compiling is expensive, so SQL Server compiles a procedure once, sniffs the parameter values from that first compilation to drive row estimates out of the statistics histogram, and caches the resulting plan for everyone who calls it after. For uniformly distributed data this is free performance, and you never think about it. For skewed data it breaks, because the histogram says genuinely different things for different values.
The skewed tenant is the canonical case. Customer 41 has twelve orders, so a nested loops join with an index seek is perfect. Customer 9001 has four million orders, so the same plan does four million seeks and melts; what that parameter wants is a hash join over a scan. Whichever customer compiles first wins the cache, and the other one's users file tickets. The feature is doing exactly what it was designed to do. Your data is just not the shape the assumption needs.
How do I prove sniffing is my problem and not something else?
Look for plan-level variance: one statement whose minimum and maximum durations differ by orders of magnitude for the same query_hash, or two cached plans for the same query with very different shapes and very different average costs. sys.dm_exec_query_stats carries min and max columns for elapsed time, worker time, and rows, and a wide spread between them on a parameterized statement is the fingerprint. The query I run first:
SELECT TOP 20 qs.query_hash,
qs.execution_count,
qs.min_elapsed_time / 1000.0 AS min_ms,
qs.max_elapsed_time / 1000.0 AS max_ms,
qs.min_rows, qs.max_rows,
SUBSTRING(st.text, 1, 200) AS statement_start
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE st.text LIKE '%GetOrdersByCustomer%'
ORDER BY qs.max_elapsed_time - qs.min_elapsed_time DESC;Query Store makes the diagnosis even cleaner when it is on: join sys.query_store_plan to sys.query_store_runtime_stats, group by plan_id for the suspect query_id, and look for the telltale pair, one plan built on nested loops and a seek averaging 40 ms, one built on a hash and a scan averaging nine seconds. The final confirmation is running the procedure with the good and bad parameters under SET STATISTICS IO and watching logical reads go from fourteen to two million. At that point there is nothing left to argue about.
Which fix should I reach for first?
A better index, because if one plan shape is cheap for every parameter value, the sniffing problem evaporates instead of being managed. A covering index that makes seeks inexpensive at any selectivity is the most durable fix on this list: no hint debt, no query-text change, no behavior that surprises the next person. It is not always possible, which is why the rest of the toolkit exists, but it is always where I start. Ranked, the toolkit looks like this:
- Better index: converges the plans so there is nothing left to sniff wrongly. First choice, always.
- OPTIMIZE FOR UNKNOWN: estimates from the density vector average instead of the histogram, so the plan is mediocre for everyone and catastrophic for nobody. My default for mild skew. OPTIMIZE FOR a specific common value works when one value dominates traffic.
- Statement-level OPTION(RECOMPILE): compiles with the actual parameter values every execution, producing a perfect plan at the price of compile CPU per call. Fine for a dashboard that runs a few times a minute; ruinous at thousands of executions per second. Keep it at statement level, not whole-procedure WITH RECOMPILE.
- Dynamic SQL for wildly heterogeneous predicates: build the statement text per predicate shape with sp_executesql and proper parameters, so each shape gets its own cache entry. The honest escape hatch when the WHERE clause itself varies.
Notice what is not on the list: a server-wide setting, a cache clear, or a reboot. Those treat the symptom by destroying the evidence.
What does SQL Server 2022 Parameter Sensitive Plan optimization change?
PSP optimization lets one query hold several active plans at once, with a dispatcher plan that routes each execution to the right child plan based on which range the parameter value falls into, so the engine stops forcing a single plan onto skewed data. It requires database compatibility level 160, and you can see the dispatcher and its children in the plan cache and in Query Store, where the plan_type_desc column in sys.query_store_plan and the sys.query_store_query_variant view map the dispatcher to its variant plans.
It is genuinely good for the skewed-tenant classic, the single skewed predicate that used to force a choice between a bad-for-someone plan and OPTIMIZE FOR UNKNOWN. It is not a general solvent. Multi-parameter skew, plan-shape problems caused by missing indexes, and predicates so heterogeneous they belong in dynamic SQL all still need the toolkit above. Treat PSP as the feature that removes the most common reason to reach for the hints, not as a reason to forget the hints exist.
Why is clearing the plan cache an outage generator?
Because DBCC FREEPROCCACHE evicts every plan on the instance, so the next few minutes are a recompile storm across your busiest queries, and nothing about the underlying skew changed: the next compile can just as easily be the bad parameter again. I watched someone run it at noon on a five-thousand-batch-per-second OLTP box. CPU pinned at a hundred percent for four minutes while every plan in the cache rebuilt, and the incident it caused lasted longer than the sniffing incident it was meant to fix.
It also destroys the evidence you needed for a real fix, which is the part people regret later. If you genuinely must invalidate a plan, scope it: FREEPROCCACHE with a specific plan_handle, or sp_recompile against the underlying table to invalidate dependent plans, or just deploy the hint and let the new text compile fresh. Clearing the whole cache is the turn-it-off-and-on-again of the query processor, and it scales exactly as poorly as that sounds.
Watching plan variance with MonPG when SQL Server support lands
The signal worth trending here is variance itself: per-query spread between min and max duration over time, the number of active plans per query, and compile CPU as a fraction of total CPU, because a compile storm is visible long before users phone it in. MonPG does this class of plan-cache analysis for PostgreSQL today, and the Query Store side of this story pairs naturally with my notes on hunting regressions with Query Store. SQL Server support is on the roadmap and in active development, and plan variance is exactly the kind of counter it should graph. Status, as ever, is on the SQL Server monitoring (coming soon) page.