A database can discard or rebuild a cached execution plan rather than reuse it. What events cause that, and why can a plan that was never invalidated still be a bad plan today?

## Two different reasons to throw a plan away Engines distinguish invalidation for **correctness** from recompilation for **quality**, and confusing them is a common interview stumble. **Correctness invalidation** is mandatory. The cached plan references catalog objects by identity - this table, this column ordinal, this index, this function overload. If any of them changes, the plan may be unrunnable or plainly wrong, so it must go. Triggers include: - any DDL on a referenced table or view - adding, dropping or retyping a column, renaming, replacing a view definition; - creating, dropping or rebuilding an index, since a new index opens an access path the optimizer never considered and a dropped one removes a path it chose; - constraint changes, because optimizers use constraints for reasoning - a `NOT NULL` or a check constraint can eliminate branches or enable transformations; - changes to the executing principal's privileges, or to row-level security policies; - changes to session settings that participate in the cache key or in plan choice. Engines implement this with dependency tracking: each cache entry records the objects and catalog versions it depends on, and a catalog change bumps a version that invalidates every dependent entry. **Quality recompilation** is discretionary. The plan would still run, but the engine believes it is no longer the best choice - almost always because the statistics it was costed against have been replaced. Concretely: - an explicit statistics refresh on a referenced table; - automatic statistics maintenance firing after a modified-row threshold is crossed; - engine-specific rules such as re-planning when a temporary object's cardinality changes materially, or an explicit per-statement recompile directive from the application. **Involuntary loss** is neither: a restart empties the cache, memory pressure evicts entries under an eviction policy, and administrators can flush the cache outright. The plan was fine; it is simply gone, and the next execution recompiles - which, importantly, re-runs parameter sniffing with whatever values arrive first. ## Why a valid plan can still be a bad plan This is the heart of the question. Invalidation is driven by **catalog and statistics events**, not by reality. Between those events, the data can drift arbitrarily: - a table grows from 10,000 to 10 million rows without crossing the auto-statistics threshold in a way that produces a timely refresh; - the distribution shifts - a status column that used to be 5% `PENDING` becomes 60% `PENDING` after an upstream slowdown; - values move outside the histogram's recorded range, the classic "ascending key" problem: rows inserted with today's timestamp fall beyond the highest value statistics know about, so a predicate on recent data is estimated at nearly zero rows and the optimizer picks a plan sized for nothing; - correlation between columns changes, so independence assumptions that were roughly true become badly wrong. In every case the cached plan remains perfectly *valid*: it references existing objects and existing indexes, so nothing invalidates it. It is simply costed against a world that no longer exists. The engine has no cheap way to notice, because noticing would mean measuring reality on every execution. ## What that implies operationally - **Statistics maintenance is a reliability control, not housekeeping.** The freshness of statistics determines both the quality of new plans and whether stale ones get replaced at all. - **Recompilation is not free and not neutral.** Every recompile re-runs planning, costing CPU and taking latches, and for a parameterized statement it re-sniffs values - so a routine maintenance job can flip a well-behaved statement into a bad plan by pure timing. Statistics jobs and restarts are therefore a common trigger for "it broke overnight, nothing changed". - **Ascending keys need explicit handling.** Predicates on recent timestamps or increasing identifiers are the most common victims of the drift problem; more frequent statistics on those columns, or physical design that avoids the estimate mattering, is the usual answer. - **Correlate incidents with events.** When a plan regression appears, the first questions are: was there DDL, an index change, a statistics refresh, a restart, or a cache flush at that moment? If yes, you have a recompile-plus-sniffing story. If no, you likely have drift against a plan that was never invalidated. - **Watch both directions.** Too little recompilation leaves stale plans in place; too much burns CPU and destabilizes plan choice. Neither extreme is safe, which is why engines expose thresholds rather than a switch. ## The sentence that answers the question A cached plan is invalidated when the *catalog* or *statistics* change, but it becomes wrong when the *data* changes - and those are not the same event. Plan quality decays silently in the gap between them.