Database engines divide RAM between memory shared by the whole instance and memory granted per operation for things like sorts and hash tables. Explain the difference between the two, and why raising the per-operation budget is riskier than raising the shared one.
answer
- Shared = buffer pool, once, everyone
- Work memory = per operator, per query, per session
- Multiply by operators x concurrency x parallel workers
- Overshoot = swap or OOM kill, not just slowness
- Global default conservative, raise per session/statement
basics
~20 sShared memory - mainly the buffer cache - is allocated once for the whole instance and used by everyone. Per-operation working memory is granted separately to each sort, hash join or hash aggregate in each concurrent query, so the same setting can be multiplied by operators per query times concurrent queries, and a value that looks safe alone can exhaust RAM under load.
solid answer
~60 s**Shared memory** is allocated once at startup and used by all sessions: the buffer pool caching data and index pages, the write-ahead log buffer, lock tables, shared caches. Its footprint is bounded and predictable - you size it once. **Per-operation (work) memory** is the budget an individual blocking operator may use before it must spill to temporary disk files: a sort's in-memory run, a hash join's hash table, a hash aggregate's group table, a materialized intermediate. It is granted **per operator, per query, per session** - not per instance. That multiplication is the risk. A single query plan can contain several sorts and hashes; multiply by concurrent queries and, in some engines, by parallel workers within a query. A setting that seems modest alone becomes a large multiple under peak concurrency, and the failure mode is nasty: swapping, or the OS killing the database process, rather than a slow query. So the discipline is: size shared memory generously and once; keep the global per-operation default conservative and raise it narrowly for the specific heavy queries or sessions that need it.
go deeper
Know that the cache of data pages is shared and sized once, while sorts and hashes each get their own memory allowance and spill to disk when they exceed it.
State the multiplication explicitly - operators per plan times concurrency - and why the global default should stay conservative.
Talk about the failure mode (swap or OOM kill), bounding concurrency via the pool, per-session or per-statement overrides, and distinguishing a real memory shortfall from a bad row estimate.
Frame it as a host memory budget under peak concurrency, with admission control or pool limits as the enforcement mechanism, and an explicit policy for who is allowed a large grant.
## Two kinds of memory with different lifetimes **Shared, instance-wide memory** is carved out once and lives for the life of the server. The dominant piece is the buffer pool - the cache of data and index pages that spares the engine a disk read. Alongside it sit the log buffer, lock and transaction structures, and shared metadata caches. Everyone uses the same pool; its size is a single number you plan for. Getting it wrong degrades hit rate gradually, which is a performance problem, not a stability one. **Per-operation working memory** is transient. Certain operators are **blocking** or state-holding: they must accumulate data before they can emit correct results. - A **sort** must see all input before emitting the first ordered row. - A **hash join** must build a hash table over one input before probing with the other. - A **hash aggregate** must keep one entry per group. - **Materialization** of an intermediate result, and set operations implemented by hashing or sorting. Each of these gets a working-memory budget. Stay under it and everything happens in RAM. Exceed it and the operator spills: the sort switches to an external merge sort writing sorted runs to temporary files, the hash join partitions both inputs to disk and processes them in batches. Spilling is correct but often an order of magnitude slower. ## Why the per-operation setting multiplies The budget is not a per-query or per-instance cap. Roughly: `worst case ~ budget x (memory-hungry operators per plan) x (concurrent queries) x (parallel workers per query)` A seven-way join with two sorts and three hash operations, run by 40 concurrent sessions, can request 200 grants of that budget. Set it to 256 MB "because reports were spilling" and you have described 50 GB of demand on a machine that may have 32 GB - most of which is already committed to the buffer pool and to per-connection overhead. The failure mode is what makes this dangerous. Undersized shared cache means more reads and slower queries. Oversubscribed working memory means the host starts swapping - at which point every query, including trivial ones, becomes pathologically slow - or the operating system's out-of-memory killer terminates the database process, taking down every session at once. Engines differ in how they police this. Some grant purely per operator with no global ceiling, making the arithmetic entirely the operator's responsibility. Others use an admission-control model: the optimizer estimates a memory grant for the whole query, and queries queue until their grant can be satisfied - bounding total usage but introducing waits and making bad row estimates directly visible as either spills or queueing. ## Practical sizing discipline 1. Budget the host explicitly: OS and filesystem cache, shared buffer pool, per-connection overhead, then what remains for working memory across peak concurrency. 2. Bound concurrency first. A connection pool with a sane maximum is what makes the multiplication tractable at all; unbounded connections make any per-operation setting unsafe. 3. Keep the global default conservative - enough that ordinary OLTP sorts and small hashes stay in memory. 4. Raise it narrowly where it pays: per session for a nightly report, per role for the analytics user, or per statement where the engine allows it. One query that spills 2 GB is a much better problem than an instance-wide setting that occasionally kills the server. 5. Remember that spilling is not automatically a bug. For genuinely large intermediates, an external merge sort is the right algorithm; the aim is to avoid spilling *small* operations, not to force everything into RAM. 6. Measure rather than guess: look at how much an operator actually used versus its estimate, since a spill caused by a bad row estimate is a statistics problem, not a memory problem.
- How does a connection pool with a bounded maximum size interact with per-operation memory sizing?The pool cap sets the concurrency multiplier in the worst-case arithmetic, turning an unbounded risk into a computable one. With at most N concurrent statements you can reason about N times the operators per plan times the budget. Without a cap, a traffic spike can open hundreds of sessions, each entitled to the same per-operator grant, and the host runs out of memory even though no single query looks unreasonable.
- Is a query spilling to temporary files always a problem to fix?No. Sorting or hashing an intermediate far larger than any sane memory budget is exactly what external algorithms exist for, and forcing it into RAM would be worse for everyone else. It is worth investigating when the spill is small - suggesting the budget is just below what the operator needed - or when the estimate is far off the actual row count, which points at stale statistics or a bad plan rather than at memory sizing.
Shared memory is the building's central library everyone borrows from. Per-operation memory is a desk allocated to each person for each task they start - promise every worker a huge desk and you run out of floor long before you run out of librarians.
saying these in an interview costs you the question
- Treating the per-operation setting as a per-query or per-instance limit
- Raising the global working-memory default to fix one report query
- Assuming the engine will gracefully degrade instead of swapping or being OOM-killed
- Confusing the buffer pool with working memory, or thinking a sort borrows from the buffer cache
- Sizing memory without bounding the number of concurrent sessions