For a business-critical transactional service, how would you decide between letting the optimizer freely re-choose access paths as data changes and pinning plans so they stay fixed?

## What the choice really trades A cost-based optimizer re-deciding access paths gives you **adaptability**: as tables grow, distributions shift, and indexes are added, plans follow. The price is **variance**: the decision is made from estimates, and estimates are sometimes wrong, so a statement's latency can change abruptly without a deployment. Pinning inverts both. You buy **predictability** and pay with **staleness**: the plan cannot improve, and it cannot escape a layout it has outgrown. A principal-level answer treats this as a risk allocation across a portfolio of statements, not a single global switch. ## Segment the workload first Different statements deserve different regimes. - **High-frequency transactional statements on a strict latency budget.** Few tables, well-understood predicates, and a plan that is obviously right, typically a unique or highly selective index lookup. Variance here is expensive and improvement potential is near zero, since the ideal plan is already known. These are the legitimate pinning candidates. - **Ad-hoc, reporting, and analytic statements.** Predicate shapes vary and data volumes swing. Freezing plans here is both impractical and harmful; adaptability is the point. - **Statements over data whose distribution genuinely evolves**, such as a status column that shifts from rare to common as a backlog drains. Pinning guarantees the plan will be wrong later. ## Prefer fixing inputs over freezing outputs Before considering a pin, exhaust the cheaper interventions, because they raise the quality of every plan rather than one: 1. **Statistics quality.** Sampling depth and refresh cadence tuned per table; higher targets on skewed, heavily filtered columns. Explicit refreshes after bulk loads instead of waiting for automatic thresholds. 2. **Estimability of predicates.** Extended or multi-column statistics where columns are dependent, so the independence assumption stops producing wild underestimates. Indexes on expressions the optimizer would otherwise treat as opaque. Predicates written so constants are visible at planning time. 3. **Making the good path robustly cheap.** An index carrying every column a hot query needs removes the row fetch, which flattens the cost curve and shrinks the gap between the best and worst plausible plan. Partitioning bounds the damage of a scan. Physical ordering aligned with the dominant range scan keeps the index path viable at higher match counts. 4. **Reducing plan sensitivity.** Splitting a statement that serves wildly different parameter shapes into distinct statements gives each its own plan and removes the shared-plan gamble. After these, the residual set of genuinely risky statements is usually small, and that is the correct scope for pinning. ## When pinning is the right call Use it when **the cost of a wrong plan is qualitatively different from a slow plan**: a checkout path that saturates connections and takes the service down, a statement inside a lock-holding transaction where a slow plan escalates into contention, a system where the same statement runs millions of times an hour so any regression is instantly systemic. Also use it as **time-boxed containment** during an incident. Stabilise now, diagnose properly afterwards, remove the pin when the root cause is fixed. That is a legitimate and common use, provided the removal actually happens. ## Governance, the part most answers omit A pin is a piece of production configuration that silently overrides an adaptive component. Treat it accordingly: - Record **who pinned it, when, why, and what evidence** justified the chosen plan. - Set a **review date**, and re-validate against current data at that date. - **Monitor** pinned statements specifically: their latency trend, and where the engine can report it, the cost of the pinned plan against alternatives the optimizer would now prefer. - Keep a **bounded inventory**. If the list grows past a couple of dozen, it is a signal that estimation quality or schema design is the real problem, and the pins are masking it. - Make pins **visible in code review and deployment**, not applied by hand in production and forgotten. ## Measure the right thing The argument is about worst cases, so evaluate on the tail: p99 and p999 latency, and the frequency and blast radius of plan-change incidents. An adaptive regime may show a better mean while producing occasional catastrophic outliers; a pinned regime may show a slightly worse mean and a much tighter tail. Which is preferable depends on whether the service degrades gracefully under a slow statement or collapses. ## The position to state Adaptive by default because data moves. Invest first in the inputs, since better estimates improve every statement rather than one. Pin a small, governed set where the worst case is unacceptable, and hold each pin to the same lifecycle discipline as any other production override. Never adopt pinning as a general policy to avoid dealing with statistics, because it converts an occasional visible incident into a slow, invisible drift that surfaces years later with nobody left who knows why the plan is frozen.