A critical query intermittently abandons its index and the team wants to pin the access path with an optimizer directive. How do you decide between forcing the plan and fixing the underlying cause, and what does each choice cost you over the next two years?
answer
- who owns the access path: optimizer or us
- instability generator decides the fix
- pin = frozen assumptions, silent staleness
- least-binding mechanism, managed centrally
- every pin: reason, owner, review date
basics
~20 sForcing freezes today's assumptions about data volume and distribution; it stops the bleeding but goes stale silently as data grows. Prefer fixing the input - statistics, predicate shape, index design. Force only for a known-bad estimate you cannot repair, with an owner, a recorded reason, and a review date.
solid answer
~60 sI start by asking why the plan is unstable. Intermittent flips almost always mean the optimizer is near a cost tie or is estimating badly - skewed values with bound parameters, correlated predicates, a growing column drifting past its histogram. Those are fixable inputs: refresh and deepen statistics, add joint statistics, make predicates seekable, add a partial or covering index so one path is decisively cheaper rather than marginally. Forcing is legitimate in narrow cases: an estimate the engine structurally cannot get right, a tail-latency commitment where a rare bad plan is unacceptable, or an incident where I need relief now. Then I use the least-binding mechanism available - a captured or baselined plan rather than a hand-written directive that hard-codes an index name. The cost of forcing is that it is a silent contract with today's data. I record why, who owns it, what the trigger for removal is, and review it on a schedule; otherwise it becomes an unexplained brake three years later when the data has changed shape.
go deeper
Recognize that hints exist, that they override the optimizer, and that the usual first step is to check statistics and the query shape instead.
Name the fixable causes - stale statistics, skew with bound parameters, non-seekable predicates - and explain that a hint freezes assumptions that will age.
Match the remedy to the instability generator, prefer decisive index or statistics changes, and insist any pin is temporary with monitoring attached.
Set the policy: preference order, least-binding mechanism, mandatory metadata and review dates on pins, drift monitoring, and production-scale test data as the gate for plan regressions.
## Reframe the question "Force or fix" is really "who owns the decision about access paths - the optimizer at runtime, or us at authoring time." The optimizer adapts to changing data but depends on the quality of its inputs. A forced plan is immune to bad estimates but also immune to good ones: it will keep choosing an index long after that index stopped being the right answer. ## Diagnose the instability first Intermittent flips are diagnostic in themselves. Common generators: - **Skew plus bound parameters.** The plan is compiled for one parameter value and reused for very different ones, so which value warms the cache decides the day's performance. - **Cost ties.** Two plans within a few percent of each other flip on tiny statistical changes. The good fix here is to break the tie decisively with a better index, not to freeze a coin toss. - **Estimate errors** from correlation, expressions, or out-of-range values on a growing column. - **Environmental variance** - cache warmth, concurrency, changed cost constants after an upgrade. The fix follows the generator. Skew wants per-value plans, a partial index, or splitting the query shape. Ties want a decisive index. Estimate errors want statistics work. ## When forcing is the right call - **Structural estimation failure.** Some predicates cannot be estimated well - opaque function results, heavy inter-column dependencies the engine cannot model, plan-time-unknown values. If you have proved the estimate cannot be fixed, forcing encodes knowledge the optimizer does not have. - **Tail-latency contracts.** For a query on a checkout path, a plan that is fifteen percent slower on average but never catastrophic can beat one that is usually optimal and occasionally times out. Predictability is a legitimate objective. - **Incident response.** Relief now, root cause after. This is fine as long as the ticket to remove it is created at the same time. - **Vendor upgrade windows**, where you want to hold plans steady while validating a new optimizer version, then release them deliberately. ## What forcing costs 1. **It goes stale silently.** No alert fires when the directive becomes wrong. The table grows tenfold, the distribution shifts, and the frozen plan degrades gradually. 2. **It couples code to schema names.** A directive naming an index blocks renaming, replacing, or dropping that index - and can make the query fail outright or fall back unpredictably if it disappears. 3. **It hides the real defect.** Teams stop maintaining statistics because directives paper over the symptom, so every *other* query keeps suffering. 4. **It spreads.** One directive normalizes the practice, and within a year a proportion of the query surface is hand-planned by people who have left. 5. **It complicates upgrades.** Optimizer improvements cannot help queries that are pinned. ## What fixing costs It is not free either. Deeper statistics sampling costs maintenance windows and I/O. Extra indexes cost write throughput, storage, and buffer-pool space. Rewriting predicates for seekability touches application code and needs verification that result sets are unchanged. And some fixes take weeks while the incident is now. ## The governance answer The senior-versus-principal difference is process, not opinion. What I want in place: - A **preference order** written down: fix the estimate, then reshape the predicate, then change the index, then pin the plan. - **Least-binding mechanism.** Prefer engine features that capture and reuse a verified plan over directives embedded in application SQL, because the former are managed as data and can be listed, audited, and dropped centrally. - **Every pin carries metadata**: why, the measurement that justified it, the owner, and the condition or date for revisiting. - **Drift monitoring** on estimated-versus-actual rows and on latency for the pinned queries, so a stale pin surfaces as a signal instead of an outage. - **Representative test data.** Most bad plan decisions are invisible in a development database of ten thousand rows. Plan regressions need a data set with production scale and skew. ## The one-line stance Forcing a plan is borrowing against the future: it is sometimes the right trade, but it must be booked as debt with an owner and a maturity date, not slipped into a query and forgotten.
- Why is a captured or baselined plan usually preferable to a directive written into the application's SQL?A captured plan lives in the database as data, so it can be listed, audited, disabled, and expired centrally without a code deploy, and it typically fails soft by falling back to normal optimization if the objects it references change. A directive embedded in application SQL is invisible to database operators, couples the code to index names, and needs an application release to remove. The tradeoff is that captured plans are engine-specific machinery someone must understand and monitor.
- You inherit a codebase with dozens of undocumented optimizer directives. How do you unwind them safely?Inventory them first and attach current measurements: for each, compare the forced plan against what the optimizer would choose now on production-scale data. Many will turn out to be redundant because statistics or indexes improved, and those can be removed in small batches behind monitoring of latency and plan choice. The rest get an owner and a written justification, so what remains is deliberate rather than archaeological.
saying these in an interview costs you the question
- Treating optimizer directives as a normal tuning tool rather than debt with an owner
- Pinning a plan before establishing whether the row estimates are accurate
- Assuming a forced plan stays correct as data volume and distribution change
- Hard-coding index names in application SQL without considering schema evolution
- Validating plan choices only against a small development data set