A parameterized query normally returns in milliseconds but intermittently takes minutes for days at a time, with no schema change and no data-volume change, and it recovers as soon as the statement is recompiled. Explain the plan-reuse behaviour that causes this and how you would confirm it.

## What sniffing is and why engines do it When a statement uses bind parameters, the optimizer has a choice at compile time: estimate selectivity generically, ignoring the values, or look at the actual values supplied for this compilation and estimate precisely for them. Most engines do the latter, at least for the first compile, because using the real value with a histogram gives a far better estimate than a blanket average. That peeking at values is **parameter sniffing**. The cached plan is then reused for later executions **with different values**. That reuse is the whole point of the cache - and also the whole problem. ## Why skew turns it into an incident Consider `WHERE tenant_id = ?` on a table where one tenant owns 80% of ten million rows and ten thousand other tenants own a few hundred each. - **Compiled with a small tenant.** Estimate: ~300 rows. Best plan: index seek on `tenant_id`, then a nested loop into other tables. Excellent - for small tenants. Run it for the big tenant and you perform eight million index lookups plus row fetches. Minutes instead of milliseconds. - **Compiled with the big tenant.** Estimate: eight million rows. Best plan: full scans with a hash join, maybe a parallel plan with a big memory grant. Reasonable for that tenant, but every small-tenant call now scans the whole table and takes a large memory grant to return 300 rows - which under concurrency also starves other queries. Neither plan is wrong; both are optimal for the value that was sniffed. The pathology is that a *single* plan is being asked to serve two very different workloads. ## Why it appears out of nowhere The compile-time value is essentially arbitrary from the application's point of view. It is whichever execution happened to arrive after the previous plan left the cache. Plans leave the cache when the server restarts, when memory pressure evicts them, when statistics are refreshed, when DDL touches a dependency, or when a maintenance job runs. So the incident correlates with an eviction event and with *whoever called first afterwards* - which is why it looks random, why it "fixes itself" after a restart, and why it comes back weeks later in the opposite direction. ## Confirming the diagnosis A disciplined confirmation looks like this: 1. **Establish plan reuse.** Same statement text, one cache entry, and executions with wildly different durations. Bimodal latency for one statement is the primary signal; an evenly slow statement is a different problem. 2. **Capture the cached plan and its compile-time values.** Most engines expose the plan for a cached statement together with the parameter values used to build it. If the compile values are unrepresentative of typical traffic, you have your answer. 3. **Compare estimated to actual rows.** On a slow execution, look for an operator where the estimate is orders of magnitude below or above actual. Estimated 1 row versus actual 8,000,000 on the driving side of a nested loop is the canonical fingerprint. 4. **Test with a literal.** Run the same query with the slow case's value written as a literal so the optimizer plans for it fresh. If that produces a different, fast plan, reuse is the cause - not a missing index or bad statistics. 5. **Check the data distribution.** Confirm the predicate column is genuinely skewed, and that a histogram exists on it. If statistics are missing or the histogram is too coarse, the optimizer never had value-specific knowledge in the first place, which is a related but distinct fix. What *rules it out*: if every execution is slow, or if slowness tracks table growth, or if the plan changes on every run, look elsewhere. ## The response options and their tradeoffs - **Force re-planning per execution.** Each execution optimizes for its own values. Correct plan every time, but you pay optimization CPU on every call - acceptable for a low-frequency, expensive query, wasteful for a high-frequency lookup. - **Force a value-agnostic plan.** Optimize for average selectivity so nobody is catastrophically wrong and nobody is optimal. Good when the cost gap between the two cases is moderate. - **Split the statement.** Route the known-heavy case to a separate statement, giving each shape its own cache entry and its own well-chosen plan. This is often the cleanest fix precisely because the two cases genuinely deserve different plans. - **Improve the estimates.** Ensure a histogram exists on the skewed column; sometimes the optimizer picks badly only because it cannot see the skew. - **Change the physical design.** Indexes that make both cases acceptable, or partitioning that makes the heavy case cheap by construction, remove the dilemma rather than managing it. - **Pin or hint a plan.** Fastest to apply, most brittle to live with: it freezes a decision made against today's data and hides future regressions. ## The framing that impresses Say explicitly that sniffing is not a bug. The alternative - never looking at values - produces uniformly mediocre estimates and its own class of bad plans. Sniffing is a bet that one plan generalizes across values; it only fails where the data is skewed enough that no single plan generalizes. Once you frame it that way, the fix follows naturally: either make one plan good enough, or stop trying to share one plan.