Query Memory Spill

A warehouse cluster has a fixed amount of memory and divides it among the queries running on it. Double the number of concurrent queries and each one gets roughly half the working memory it had. Most queries do not care. The ones that build a hash table for a join, sort a large result or compute a window function over many rows do, because those operators need their intermediate data resident, and when it no longer fits they write it to storage and read it back.

That is spill, and its cost is not a few percent. Local disk is one to two orders of magnitude slower than memory for this access pattern, and remote object storage is worse again. A query that ran in 40 seconds takes minutes.

The damaging part is the feedback loop. A spilled query holds its memory slot for longer, so the next arrival finds even less memory free and is more likely to spill too. Load that rose by a factor of two produces latency that rose by a factor of eight, while CPU utilisation - the number on the dashboard - stays unremarkable, because spilled queries are waiting on I/O rather than computing.

Why it matters

This is the single commonest reason a warehouse that was sized comfortably becomes unpredictable under concurrency, and it is routinely misdiagnosed. The team sees slow queries and flat CPU, and concludes the platform needs more compute. Adding nodes helps only if the memory per query rises, and on many platforms adding concurrency scaling adds slots rather than memory per slot, which makes the problem worse at a higher bill.

It also explains why the same query is fast in the morning and slow at 09:30 with no change to the data: its memory grant is a function of what else is running.

Implementation patterns

• Cap concurrency so each query keeps a workable grant, rather than admitting every arrival and letting all of them spill. A queue of five queries running well beats fifteen running badly; total throughput is higher.
• Separate queues with separate reservations: a few slots with large memory for pipelines and heavy analysis, many slots with modest memory for dashboard queries. One pool tuned for the average query fits neither class.
• Reduce the intermediate, not the input. Filter before the join, aggregate before the join where the grain allows, and avoid SELECT * into a sort - the operators spill on what they carry, not on what the table holds.
• Pre-sort or cluster on the join key so the engine can use a merge strategy instead of building a hash table over the whole side.
• Alert on spill directly, not on duration. Managed warehouses expose it: Snowflake's query profile reports bytes spilled to local and to remote storage, Spark reports shuffle spill, and Postgres can log temporary files when a sort exceeds its working-memory setting.

Industry example

The clearest documented case is the one every team can reproduce in production on its own platform: a nightly pipeline whose large MERGE runs alone at 02:00 in 15 minutes, and takes over an hour when someone schedules a second heavy job in the same window. The query plan is identical and the data volume is the same. The only difference is the memory grant, and the query profile shows gigabytes spilled to remote storage in the slow run and none in the fast one.

It is also why dashboards degrade first during a backfill: small queries competing for slots with an operator that has taken most of the memory and is paging it through storage.

Failure scenarios

• Superlinear degradation under load, mistaken for a storage or network problem because CPU is flat and I/O is high.
• Remote spill on a cluster with little local disk, where the intermediate goes to object storage and the query becomes 10 to 100 times slower rather than 2 times.
• A single pathological query - a cross join from a missing predicate - spilling continuously and starving every other query for an hour.
• Concurrency scaling masking the cause by adding clusters, so cost rises while the per-query memory problem remains.
• Raising the memory limit and inverting the problem: fewer queries admitted, so run time improves and queue time becomes most of the response.

Trade-offs

The honest summary: spill is a sizing problem between memory and concurrency, and buying compute without changing that ratio buys a faster version of the same behaviour.

When not to use it

Spill is a mechanism rather than a choice, so the question is when to stop engineering around it. If a workload is scan-bound - filters and aggregates over large tables with small intermediates - spill is not the constraint, and queue and memory tuning will produce nothing. The diagnostic is cheap: if the query profile reports zero bytes spilled, concurrency is not your problem and the time belongs in layout and pruning instead.

Equally, an occasional overnight spill on a job with a six-hour window is not worth a reservation scheme. Tune the two or three queries that spill at the time people are watching.

Interview question

Q: Concurrency on your warehouse doubled after a team onboarded and p95 went up by a factor of four, with CPU flat at 45%. Walk me through the mechanism, the metric you would check first, and why adding a second cluster might make the bill worse without fixing it.

What a strong answer covers: memory is divided among running queries, so grants shrink as concurrency rises and join and sort operators spill once their intermediates no longer fit; the first metric is bytes spilled per query rather than duration or CPU; the feedback loop, since spilled queries hold slots longer; that concurrency scaling adds slots rather than memory per slot, so the heavy queries still spill; and the resolution as separate queues with explicit reservations, plus reducing intermediates by filtering and aggregating earlier.

Quick check

Quiz: Concurrency doubles, p95 quadruples, CPU stays flat. What is the mechanism, and which metric confirms it? - Each query's memory grant halved and the joins and sorts began spilling to storage; confirm with bytes spilled per query, not CPU or duration.

Flashcard: Why does warehouse latency degrade superlinearly with concurrency? - Memory is divided among running queries, so grants shrink, large operators spill to storage at one to two orders of magnitude slower, and spilled queries hold their slots longer - which squeezes the next arrival further.