A nightly reporting query that sorts and groups a large table ran in 40 seconds for months and now takes 25 minutes, with no code change and only steady data growth. Walk through how you would confirm that sort/aggregate memory is the cause and what you would change.

## Why the curve is a cliff The shape of the regression is the biggest clue. Steady data growth producing a 37× slowdown with no code change is the signature of a **threshold crossing**, not of linear growth. Two candidates dominate: a working set that no longer fits the buffer pool, and a sort or hash aggregate whose state no longer fits its memory budget so it began spilling to temporary files. Both are step functions; the second is the one this question is about, and it is the more common cause when the query contains ORDER BY, GROUP BY, or DISTINCT over a large set. ## Step 1 — get an executed plan, not an estimated one Every engine distinguishes the plan it *would* use from the plan it *did* run with measured row counts and resource usage. Only the executed form reports sort methods and spill sizes. Ask for it on the real data volume — reproducing on a scaled-down copy will hide the very threshold you are hunting. What you are reading for: - **Sort operators**: a method described as external merge / external sort, with a temp size in MB or GB. An in-memory sort reports a small memory figure instead. - **Hash aggregates / hash distinct**: a batch or partition count above one, plus disk usage. One batch means it stayed resident. - **Actual vs estimated rows** at each operator, especially the input to the sort/aggregate and the aggregate's own output (which is the group count). ## Step 2 — corroborate outside the plan Plan output can be sampled or truncated, so confirm with instance-level evidence over the job's window: temporary file creation count and bytes, temp tablespace / tempdb usage, and I/O on the temp device. A large spike aligned to the job is confirmation. If the temp device is shared with data files, the spill also degrades everything else running at the same time — which explains "the whole system feels slow at 2am" reports that accompany this. Rule out the alternatives while you are there: a plan *shape* change (different join or access method than before) points at statistics or parameter sensitivity rather than memory; a buffer-cache hit-ratio collapse with high physical reads points at working-set growth; lock waits point somewhere else entirely. If the plan shape is identical to the old one and only the sort method changed, you have your answer. ## Step 3 — classify the cause Two distinct stories produce the same spill: **(a) Honest growth.** Estimates match actuals; the data simply outgrew the budget. The fix is about the operator's input size or its budget. **(b) Estimation failure.** Actual rows or groups far exceed the estimate. The planner sized the plan for a small hash table and chose hash aggregation; reality needed far more. Correlated grouping columns and stale statistics are the usual culprits. Here, fixing the estimate can change the plan to something structurally better, not merely make the same plan fit. ## Step 4 — remediate in leverage order 1. **Shrink what reaches the operator.** Two independent levers: fewer rows (push predicates below the sort, aggregate earlier, filter partitions) and narrower rows (project only the columns actually needed — spilled tuples carry every column the plan propagates, so dropping a couple of wide text columns can halve temp bytes). This is usually the largest and safest win. 2. **Eliminate the operator.** An index on the grouping or ordering key gives ordered input, converting a hash aggregate that spills into a streaming grouped aggregate with negligible memory, or removing a sort entirely. For a reporting job that always groups the same way, this is often the permanent fix. Weigh the write cost of the extra index. 3. **Repair estimates.** Refresh statistics; where the grouping key spans correlated columns, create multi-column/extended statistics so the distinct-group estimate stops being the product of per-column guesses. 4. **Consider pre-aggregation.** A nightly report that repeatedly grinds the same base data is a candidate for an incrementally maintained summary table, turning an O(all history) job into O(yesterday). 5. **Raise the memory budget last, and scope it.** The budget is charged per operator per query, so a global increase multiplies across every concurrent session and is a classic route to OOM. Setting it for the batch role or that session only gives the report the memory it needs without exposing the OLTP workload. ## Step 5 — make it not recur Record the temp bytes for the job and alert on them. The operational lesson is that the metric to watch is not "query duration" — which stays flat right up to the cliff — but the headroom quantities: sort/hash working-set size versus budget, and estimated versus actual rows on stateful operators. Those degrade visibly for weeks before the duration does. ## What interviewers are listening for A disciplined evidence chain (executed plan → operator-level spill evidence → system counters), the discrimination between honest growth and estimation failure, and a remediation order that puts "raise the memory setting" *last* with an explicit note about per-operator-per-query multiplication under concurrency. Candidates who open with "increase work_mem" have skipped both the diagnosis and the blast-radius question.