A query that normally runs in under a second occasionally takes minutes, with no schema or code change. How would you determine whether the optimizer's estimates or its cost model are to blame, and what are the common sources of such estimate failures?
answer
- Bimodal runtime = plan flip or estimate failure, not the cost formula
- Executed plan, bottom-up, first big est-vs-actual divergence
- Nested loop with huge loop count = under-estimated outer side
- Stale stats / correlation / parameter sniffing / opaque predicate / out-of-range
- Pin a plan only as containment, with a review date
basics
~20 sCapture the executed plan with actual row counts and compare them to the estimates operator by operator, bottom-up. The lowest node where estimated and actual diverge sharply is the cause. Common sources: stale statistics, correlated predicates, parameter-sensitive plans, opaque predicates, and values beyond the histogram's range.
solid answer
~60 s**Diagnose by comparison, not by intuition.** Get the executed plan with actual counts for both the fast and the slow execution. Then: 1. Walk bottom-up and find the **lowest** operator where estimated and actual rows diverge by orders of magnitude. Divergences above it are consequences. 2. If estimates match actuals and the plan is still slow, the problem is not estimation — look at the cost constants, the access paths available, or a genuine resource issue (spills, cache misses, contention). 3. If the two executions used **different plans**, this is plan instability: the same SQL compiled differently. Compare compiled parameter values and statistics timestamps. **Usual sources of estimate failure:** - **Stale statistics** after bulk loads or distribution shifts. - **Correlated columns**, where independence multiplies selectivities that overlap. - **Parameter sensitivity** — a plan compiled for a rare value reused for a common one, or the reverse. - **Opaque predicates** (function calls, leading-wildcard matching, expressions) that fall back to fixed default constants. - **Out-of-range values** — a timestamp filter for "today" beyond the histogram's last bucket, estimated as almost empty. Fix the root estimate; pin a plan only as containment.
code
text · 7 linesNESTED LOOP est=52 actual=418,000 time=214s
-> HASH JOIN est=52 actual=418,000
-> SEQ SCAN a est=40 actual=402,000 <-- ROOT: 10,000x low
-> SEQ SCAN b est=1,200 actual=1,190
-> INDEX SCAN c est=1 actual=1, loops=418,000
-- everything above SEQ SCAN a inherited its errorgo deeper
Know to capture the plan with actual row counts and compare them against the estimates rather than guessing.
Explain the bottom-up search for the first divergence and name the main sources: stale statistics, correlation, and unestimable predicates.
Distinguish plan instability from a single bad plan, recognise nested-loop loop-count signatures and parameter sensitivity, and rank fixes with plan pinning as time-boxed containment.
Treat it as reliability engineering: instrument plan changes and duration percentiles so flips are detected as events, define statistics maintenance as an operational job, and set policy for when frozen plans are acceptable and who reviews them.
## Read the symptom first "Usually fast, occasionally minutes, no code change" is a very specific shape. Steady degradation points at growth; intermittent bimodal behaviour points at **the same SQL being executed by different plans**, or by the same plan against wildly different data volumes. Both trace back to the optimizer's beliefs about the data rather than to the cost formula, which is static. ## The diagnostic sequence **1. Capture executed plans for both behaviours.** An estimated plan is insufficient — you need actual row counts, loop counts, and timing per operator. Where the engine keeps a plan history or query store, pull the plan for the fast executions and for the slow ones and diff them. Two different plan shapes and one problem class; one plan shape and a different one. **2. Locate the first divergence, bottom-up.** For each operator compare estimated to actual rows. Errors propagate upward, so the interesting node is the **lowest** one where the ratio explodes. A node showing estimated 40 / actual 900,000 with three healthy children is your root cause; the wildly wrong join above it is collateral. **3. Check loop counts.** A nested loop reporting a small per-iteration cost but hundreds of thousands of loops is the signature of an under-estimated outer side. This is the single most common shape behind "sometimes minutes". **4. Rule the cost model in or out.** If estimates track actuals closely and the plan is still poor, estimation is exonerated. Then look at: cost constants inappropriate for the storage (index paths ignored on flash because random-page cost still encodes a spinning disk), a missing access path so the best plan was never in the search space, or an execution-level problem such as a spill or buffer-cache pressure. This branch is much rarer than the estimation branch, which is why it comes second. ## Sources of estimate failure, and how each looks **Stale statistics.** After bulk loads, backfills, partition swaps, or a distribution shift, the stored profile describes a table that no longer exists. Tell: statistics timestamp long before the data change; refreshing fixes it. Note that some engines only auto-refresh after a percentage of rows change, so a huge table can drift a long way before the threshold trips. **Correlated predicates.** Multiple predicates on one table whose columns imply one another; independence multiplies overlapping restrictions and under-estimates. Tell: the estimate is roughly the product of the individual selectivities, and refreshing statistics changes nothing. Fix with multi-column/extended statistics. **Parameter sensitivity.** The engine compiles a plan using the first parameter value it sees and caches it. If that value was rare, the plan (a nested loop with index probes) is disastrous for the common value later supplied — and vice versa. Tell: identical SQL, two different runtimes, plan compiled once; the compiled parameter value is often recorded in the plan. Fixes include forcing a recompile for that statement, using optimize-for hints, restructuring so the two workloads use different statements, or engine features that keep multiple plan variants per parameter range. **Opaque predicates.** Function calls over columns, expressions, leading-wildcard pattern matching, and predicates on values the optimizer cannot inspect all fall back to hard-coded default selectivities. Tell: an estimate that looks like a round default fraction of the table with no relationship to the data. Fixes: index the expression, materialize it as a generated column with statistics, or rewrite the predicate into a sargable form. **Out-of-range values.** Histograms end at the last analyzed maximum. Monotonic columns — an auto-incrementing id, a created_at timestamp — are constantly queried beyond that boundary, so "rows created today" estimates as nearly empty and the optimizer picks a plan for a handful of rows that actually returns millions. Tell: a range predicate on a growing column with a tiny estimate. Fixes: more frequent statistics on that column, and engine features that extrapolate beyond the histogram. **Estimation over join trees.** Even with perfect base-table estimates, join cardinality assumptions (uniformity and containment) accumulate error over several joins. Tell: base scans estimate well but the estimate degrades steadily upward. This one has no clean fix beyond keeping plans robust and, where available, using execution feedback. ## Remediation, and how far to go Work on the root estimate first: refresh or increase statistics resolution; add multi-column statistics for correlated sets; make opaque predicates estimable; address parameter sensitivity explicitly. These fix the cause and let the optimizer keep adapting. When the workload cannot tolerate the risk while you do that — a payments path that must never take minutes — pin the plan (hint, baseline, forced plan) as **containment with an owner and a review date**. Pinning is not a fix: it freezes a plan that was right for today's data, and it will eventually be wrong. Finally, prevention: track plan changes and per-query duration percentiles so a plan flip is detected as an event rather than reported as an outage, and treat statistics maintenance on large, fast-moving tables as an explicit operational job rather than something the defaults happen to cover.
- The estimates match the actual row counts closely, yet the plan is still slow. Where do you look next?Estimation is exonerated, so look at the cost model's inputs and at execution reality. Check whether cost constants suit the storage — historic random-page costs can suppress index paths on flash — and whether the best plan was even available, since a missing index keeps it out of the search space entirely. Then look at execution-level effects the model does not capture well: sort or hash spills, buffer-cache pressure, lock waits, and concurrency.
- How does a parameter-sensitive plan produce this exact intermittent symptom?The engine compiles once for the first parameter value it sees and caches the plan for reuse. If it compiled for a rare value, it may choose a nested loop with index probes, which is optimal for a few rows and ruinous when a later execution supplies a common value matching millions. The same SQL then runs in milliseconds or minutes depending on which compilation is cached, which is why the symptom often appears after a restart, a statistics refresh, or a plan-cache eviction.
saying these in an interview costs you the question
- Diagnosing from an estimated plan that carries no actual row counts
- Blaming the cost constants before checking estimated versus actual cardinalities
- Fixing the topmost operator showing a divergence instead of the lowest one
- Assuming automatic statistics maintenance always keeps up on very large tables
- Pinning a plan as the primary fix and never revisiting it as the data changes