A reporting query ran in 40 milliseconds for months using an index, and now takes 30 seconds because the engine switched to a full table scan. Neither the query text nor the schema changed. How do you diagnose why the chosen access path flipped?

## Framing An access path flip is not random. The optimizer compared two cost numbers and one of them crossed the other. Since neither the query nor the schema changed, one of the **inputs** to those numbers changed. Diagnosis is therefore a search for which input moved, and there are only a handful of candidates. ## Step 1: capture the plan with actual row counts Everything starts here. Run the statement with the engine's execute-and-instrument option and read the table access node. The critical comparison is **estimated rows versus actual rows**. - **Estimate high, actual low** (say estimate 900,000, actual 300): the optimizer was misled. It chose a scan because it expected a large result. This is an estimation defect and the fix is upstream. - **Estimate and actual both high**: the optimizer is right and the data changed. The query now genuinely matches a large fraction of the table, and 30 seconds may be the honest price. The fix belongs to the query or the schema, not the optimizer. - **Estimate low but a scan was still chosen**: rarer, and points at the index-side cost inputs, such as a degraded clustering statistic or a bloated index. Also keep the plan. Comparing today's plan against a stored earlier one narrows the search immediately. ## Step 2: interrogate the statistics Ask three questions of the catalog for the filtered column: 1. **When were statistics last refreshed?** A refresh is a change event, and so is the absence of one. Automatic refresh usually triggers after a proportion of rows change, so a table that grew steadily can go a long time between refreshes and then flip abruptly when one fires. 2. **Do the stored values match reality?** Compare the recorded row count against the real count, and the recorded distinct-value count and frequent-value list against a quick aggregate of the actual distribution. Sampled statistics on a skewed column are noisy; a resample can genuinely move an estimate by an order of magnitude in either direction. 3. **Are correlated predicates involved?** If the query filters two dependent columns, the independence assumption multiplies their selectivities and produces an unstable estimate whose error changes as the distribution shifts. Multi-column or extended statistics address this directly. ## Step 3: look for a real change in the data Separate estimate drift from genuine drift: - Did the table grow substantially? Both the scan cost and the estimated match count scale, and the crossover point moves. - Did the filtered value change frequency? A status value that was rare during a backlog can become the majority state after a migration. Then the scan is correct and the old plan was correct for the old data. - Was there a bulk load, backfill, or mass update? These are the classic events that both change the distribution and, on some engines, relocate rows and wreck clustering. ## Step 4: consider parameter sensitivity and plan caching Where a plan is compiled once and reused for all parameter values, the plan reflects the value present at compile time. A restart, a cache eviction, or a statistics refresh forces a re-plan, and if the first execution afterwards happened to use an atypical value, the whole workload inherits a plan built for it. Evidence includes a flip that coincides with a restart or failover rather than with a data change, and per-value timing that varies wildly for the same statement. Remedies are engine-specific in name but conceptual in kind: re-plan per value, split the statement so each shape gets its own plan, or provide the optimizer with the distribution information it needs. ## Step 5: examine the index side The index path can become genuinely more expensive without any change in selectivity: - **Bloat and fragmentation** inflate the number of leaf pages, so walking a range reads more. - **Degraded clustering.** After heavy updates, rows that used to sit together are scattered, and the correlation statistic drops. The optimizer then prices the row fetches nearer the random cost, and the scan wins legitimately. - **An index becoming unusable or invalid** after a maintenance operation removes the path from the menu entirely, which shows up as the index simply never appearing in candidate plans. ## Step 6: act, in the right order 1. **Refresh statistics** on the table, then re-plan and re-measure. This resolves a large share of flips outright. 2. If the estimate is still wrong, **fix estimability**: extended statistics for correlated columns, an index on an expression the optimizer cannot see through, or a rewritten predicate that exposes the constant. 3. If the estimate is right and the data changed, **fix the workload**: a more selective predicate, a covering index that removes the row fetch, partitioning so the scan reads only relevant data, or accepting a scan and running the report differently. 4. **Only then** consider forcing or pinning the plan, and treat it as containment with an owner and an expiry, because it freezes a decision against data that will keep moving. ## What makes the answer senior The order. A junior reaches for a hint; a senior first proves whether the optimizer was misled or merely right about worse data, using the estimate-versus-actual comparison, and only overrides a decision they have already explained.