What happens when an operator that must buffer its input — such as a sort or a hash-join build side — is granted less memory than its input needs, and how do you recognize and fix that in production?

## Why a spill happens at all Blocking operators must hold state proportional to their input: a sort holds the rows, a hash join's build side holds a hash table, a hash aggregate holds one entry per group. The engine gives the operator a **memory grant** — a per-operation working-memory budget derived from configuration and from the optimizer's estimated row count and row width. If the real data exceeds that budget, the engine must either fail the query or move part of the state to temporary storage. Every serious engine chooses the latter: correctness is preserved, performance degrades. ## What spilling looks like mechanically **External merge sort.** Read input until memory is full, sort that chunk in memory, write it out as a sorted *run*, and repeat. When input ends, merge the runs. With enough memory for a single merge pass, the total extra I/O is one write plus one read of the whole data set. With too little memory to merge all runs at once, you need multiple merge passes and the I/O multiplies. Cost grows roughly with the number of passes, i.e. logarithmically in the ratio of data size to memory — but each pass is a full sequential scan of a large temp file. **Partitioned (grace) hash join.** Hash the build input into P partitions by a partitioning hash of the join key. Partitions that fit stay in memory; the rest are written to temp files. The probe input is partitioned the same way. Then, for each spilled partition pair, load the build partition into memory and probe it. If one partition is still too large, recurse with a different hash function. This is why **skew is the pathological case**: if a single key value accounts for a huge share of rows, no partitioning function separates it, recursion does not shrink it, and the engine falls back to a much worse strategy or thrashes. **Hash aggregation** spills similarly by partitioning groups, and is likewise vulnerable to a few enormous groups. ## Why the query gets so much slower - Extra full read+write passes over data that was previously CPU-resident. - Temp I/O is often on shared storage, so several spilling queries contend for the same device. - Spill I/O is not free of CPU either: serialization, partitioning hashes, and buffer copying. - The transition is abrupt. Just under the grant, everything is in memory; just over, you pay whole extra passes. Runtimes therefore appear to jump discontinuously when a table grows slightly or a parameter changes selectivity. ## Recognizing it 1. **Runtime plan statistics.** Look for spill or temp-file indicators on the breaker: bytes written to temporary storage, number of sort runs or merge passes, number of hash batches or recursion level. 2. **Estimate versus actual rows** at the blocking operator. Spills almost always accompany a large underestimate: stale statistics, correlated predicates treated as independent, a filter on an expression the estimator cannot reason about, or a parameterized plan reused for an atypical parameter value. 3. **System-level signals.** Temp tablespace or temp directory growth, I/O on the temp volume, waits attributed to temp reads/writes. 4. **Workload shape.** Nonlinear slowdowns and high variance for the same query text across parameters. ## Fixing it, in order of leverage 1. **Fix the estimate.** Refresh statistics; add multi-column or expression statistics where the estimator's independence assumption is wrong; rewrite a predicate into a form the estimator can use. A correct estimate often produces both a bigger grant and a better plan. 2. **Reduce the volume that must be buffered.** Project only the needed columns (row width is a direct multiplier on sort memory), push filters below the breaker, aggregate earlier, and use a top-N sort when only a few rows are needed so memory becomes O(N). 3. **Reshape the plan.** Make the smaller input the build side. Provide an index that supplies the required ordering so the sort disappears entirely. Consider a merge join over already-ordered inputs, which streams instead of buffering. Pre-aggregate to shrink a grouping input. 4. **Address skew explicitly.** Separate the heavy key (handle it with its own path), or salt/split it, when a single value dominates a join or grouping key. 5. **Raise the memory grant — last, and deliberately.** Working memory is per operation, and a plan may contain several blocking operators, each under multiple parallel workers, across many concurrent sessions. The real budget is grant × operators × workers × concurrency. Raising it globally to fix one report is how machines run out of memory. Prefer raising it for the specific session or workload that needs it. ## What good judgement sounds like Spilling is not inherently a bug: for a genuinely large sort it is the correct, designed behaviour, and giving every query enough memory to avoid it is unaffordable. The judgement call is whether this operator *should* have been that big. If actual rows match the estimate and the data really is huge, tune the spill (fewer, larger runs; faster temp storage) and accept it. If the actual dwarfs the estimate, the estimate is the bug and the spill is only the symptom.