Articles

SQL Server Execution Plan Interview Questions: Estimates, Lookups, and Parameter Sensitivity

Practice SQL Server execution plan questions by tracing row estimates, Key Lookups, parameter skew, and measured trade-offs before changing indexes.

An execution plan is a hypothesis about how SQL Server will get the rows, not a ranking of operators to delete. In an interview, the useful move is to connect an estimated row count to the actual workload: a Key Lookup can be sensible for ten customers and disastrous for ten million matching orders. The same query text may also need different plans for different parameter values.

This guide uses one original order-search scenario to show how to inspect that chain of evidence. For broader query-tuning preparation, see SQL Query Optimization Interview Questions. You can apply the method even if you have never memorized every icon in SQL Server Management Studio.

Evidence boundary: Official facts about plans, statistics, indexes, and parameter-sensitive optimization come from Microsoft documentation linked below. Candidate reports: no verified same-cycle report establishes that a named employer asks this exact exercise. Inference: the Orders workload, numbers, cost comparison, and interview scoring rubric are original teaching examples; measure them on a real system before making a production change.

The interview exercise: one query, two parameter shapes

Imagine a reporting endpoint that lists recent orders for a customer. The table has 20 million rows. A nonclustered index starts with customer_id, order_date; the query selects order_id, order_date, and a wide shipping_note not contained in that index. The exact schema and distribution below are invented.

SELECT order_id, order_date, shipping_note
FROM dbo.Orders
WHERE customer_id = @customer_id
  AND order_date >= @from_date;

For a typical customer, ten rows match. For an enterprise customer, two million rows match. A cached plan compiled for ten rows seeks the nonclustered index and performs a Key Lookup to fetch shipping_note for each match. The plan estimated 12 rows, but a later execution for the enterprise customer returned 2,000,000. Your interview task is to explain what the plan proves, what it does not prove, and which change you would test first.

A strong opening answer: “I would compare an actual plan for representative small and large customers, locate the first large estimate-versus-actual divergence, and check its source. The two-million-row execution may make repeated lookups expensive, but I would not add an INCLUDE column until I know the write cost, note width, and plan alternatives. I would compare runtime and resource use for both parameter classes.” That answer distinguishes observation from diagnosis.

What does an estimated plan actually tell you?

Official fact: SQL Server's optimizer uses the query, schema, and statistics to choose a plan. Its execution plan overview describes the plan as the chosen access and processing strategy. An estimated plan is available without running the statement; an actual plan comes from an execution and adds runtime observations and warnings. Microsoft's actual-plan guide documents that distinction.

An estimated 12 rows is the optimizer's estimate at compilation for that operator under that compilation context. It is not a guarantee that every later call returns 12 rows. An actual 2,000,000 rows on one execution proves what happened in that execution, not that the estimate will always be wrong. Capture the parameter values or their safe statistical class, query text, database compatibility level, plan identity, relevant index definitions, and execution time. Without those, two screenshots can be different queries in all but name.

Read operators in the data-flow direction and inspect properties, not merely the biggest graphical percentage. Start at the leaf access paths and track where row counts diverge. A join that estimated one row but received a million can make downstream join and memory choices look bizarre; the root cause may be much earlier. The visual cost percentage is an estimated fraction within that plan, not measured elapsed-time attribution.

The actual plan is itself one observation. Warm cache, concurrent load, parameter values, row goal, and other runtime conditions matter. Pair it with elapsed time, logical reads, CPU, returned-row count, and relevant wait or spill evidence. Avoid treating a single fast run as proof that the strategy scales.

Why can a Key Lookup be either good or bad?

A Key Lookup retrieves columns missing from a chosen nonclustered index by using the row locator to reach the base row. In our example, shipping_note triggers that extra access. If only ten rows qualify, ten lookups may cost much less than reading a broad table or a much wider covering index. If two million qualify, the same plan can repeat a random-access-style operation roughly two million times. The lookup operator is not a defect by its name alone.

The interviewer's follow-up is often “Why not simply cover the query?” An INCLUDE (shipping_note) candidate may remove the lookup, but it also expands an index used and maintained on writes. A long or frequently updated note can be a poor include choice. A filter, projection change, pagination strategy, or different access path might address the actual workload better. Microsoft's index design guide emphasizes the trade-off between read benefit and index maintenance.

Ask whether the endpoint truly needs the wide note for every returned row. If it displays twenty rows at a time, a narrow first-stage query plus a bounded detail fetch can alter the cost profile. If it exports all matching rows, pagination is not a magic substitute for an export requirement. Confirm ordering and page consistency rather than proposing OFFSET as a cure by reflex.

Work the lookup break-even as a thought experiment

Suppose the narrow seek costs one unit to locate an initial range and each lookup adds L units. Suppose scanning an alternative path costs S units for this environment. The simplified comparison is seek + N × L versus S, where N is the number of qualifying rows. At N=10, the lookup path can win; at N=2,000,000, it may lose. These are not SQL Server's real cost formulas. They show why the cardinality estimate can flip a plan choice.

The exact crossover depends on storage, cache, row size, parallelism, predicate selectivity, and the alternative index. A plan that appears “more expensive” in estimated units can still run faster for a particular measured execution. In an interview, state the directional mechanism, then ask for evidence. Do not quote an invented universal percentage such as “lookups are bad past 10% of the table.”

Where did the bad row estimate come from?

Statistics summarize data distributions for the optimizer. Check whether the relevant statistics exist, when they last updated, and whether the distribution has changed. The leading index key may describe customer selectivity better than a predicate combination involving order_date. Correlated predicates, skewed customers, expressions around indexed columns, implicit conversions, or a filtered population can all affect estimation. This list is a set of hypotheses, not a claim that every mismatch is “stale stats.”

For the invented Orders table, compare an actual plan for a typical customer and an enterprise customer. If each plan is reasonable when separately compiled but one reused plan performs badly for the other, parameter sensitivity is a strong hypothesis. If both are poorly estimated even when compiled for their own values, investigate statistics, data types, predicates, and schema before treating plan caching as the sole issue.

Test a targeted statistics refresh in a controlled environment when staleness is plausible. Show before-and-after estimates and measured reads or duration. A full statistics update can change many plans, so the blast radius matters. Likewise, changing compatibility level or cardinality estimator behavior to solve one query is a broader intervention than a targeted query fix.

What is parameter sensitivity, and what does SQL Server offer?

SQL Server can reuse a cached plan for calls with different parameter values. Reuse saves compilation cost, but a plan chosen for one distribution slice may be a poor fit for another. This is the core mechanism often described as parameter sniffing; “sniffing” is not inherently a bug. Our small and large customers are intentionally unlike each other.

Official fact: Microsoft's Parameter Sensitive Plan optimization documentation describes a feature introduced with SQL Server 2022 and compatibility level 160 that can keep a dispatcher plan and query variants for eligible parameterized queries. Eligibility and supported predicate forms matter. Do not promise that upgrading will make this query generate variants without inspecting the actual plan and Query Store evidence.

Other options may include changing the query shape, an appropriate index, a targeted recompilation choice, or a carefully scoped plan intervention. Each has a cost: recompilation burns CPU, a forced plan can suit one parameter class and harm another, and hints can age badly after data distribution changes. If the environment supports PSP optimization, inspect whether it engages before layering older workarounds on top of it.

Official fact: Query Store retains query plans and runtime statistics that help compare behavior over time. A single production plan screenshot lacks that time dimension. You need the workload's parameter distribution and baseline latency percentiles, not only its worst anecdote.

A two-plan diagnostic worksheet

For the interview, put the typical and enterprise executions side by side. This worksheet is an original exercise, not a SQL Server output format.

Question	Typical customer	Enterprise customer	Why it matters
Rows estimated at first selective access?	12	12 if plan reused	Reveals the compilation hypothesis.
Rows actually produced?	10	2,000,000	Quantifies mismatch for each execution.
Number of Key Lookup executions?	About 10	Potentially about 2,000,000	Connects plan shape to repeated work.
Logical reads, CPU, elapsed time?	Measure	Measure	Tests user-visible and resource impact.
Same plan or separate variants?	Record plan ID	Record plan ID	Distinguishes reuse from alternative selection.
Statistics and predicate types?	Inspect	Inspect	Checks a competing explanation.

Do not fill measured cells with hypothetical values. Record the actual count of lookup executions in the plan; an operator can run in a more complex context where one-row-per-lookup shorthand misleads. If the query has a TOP, join, or row goal, capture that too. If the enterprise customer returns two million rows by design, a slow response may partly be the cost of transferring two million wide rows; an index cannot remove the output volume.

Your recommendation should name the test and its failure condition: “I will test a narrower projection and a candidate covering index. I will reject the index if write latency or storage grows beyond the accepted budget, or if large-customer reads do not improve meaningfully.” That is more credible than “add an index because the plan recommends one.” Microsoft explicitly warns in its missing-index tuning guide to review existing indexes and workload context.

Common interview traps

“A scan means no index was used” is false; an index scan can read an index, and a scan may be the right choice for broad output. “The operator with the highest cost percentage is the bottleneck” confuses estimates with measured time. “Update statistics fixes parameter sniffing” might improve a plan but does not erase skew. “Force the fast plan” ignores the slow parameter class. “A covering index is free” omits storage and write amplification. “Actual rows differ, therefore SQL Server is broken” ignores compilation context and data change.

Another trap is comparing a cold-cache large-customer run with a warmed small-customer run and attributing all difference to the plan. Keep the parameter class and environment explicit. Test representative repetition, then inspect a distribution rather than one dramatic trace. In a live system, safe sampling and plan capture should respect customer data and performance budgets.

Practice questions tied to the diagnostic

Complete practice question	Apply it to this plan
Optimize a SQL query plan tree	Explain operator order and cardinality propagation.
Explain Database Index Benefits and Write Costs	Weigh lookup removal against index maintenance.
Explain Database Indexes and Why Every Column Should Not Be Indexed	Challenge the blanket covering-index answer.
Optimize SQL to minimize scans	Decide when a scan really is avoidable.
Defend a Project's Schema, Indexes, and Concurrency Model	Defend the production trade-off, not only the screenshot.

Finish your rehearsal with a ninety-second explanation: “The plan estimated a dozen rows because of its compilation context, then a skewed parameter returned two million. I would confirm this across Query Store and representative actual plans, test the smallest query or index change that benefits both classes, and watch write cost and tail latency.” If you can defend that sequence under follow-up questions, you are reading the plan rather than reciting operator names. Start with Optimize a SQL query plan tree and narrate each estimate you would verify.