Ration memory across queries
Many queries run at once on one machine, each with operators that want memory: hash tables, sort buffers. Memory, unlike CPU, cannot be taken back instantly: reclaiming it means spilling to disk or killing a query. How much should each query get, decided when, and what happens when the guess is wrong?
You will end with a grant policy, a reserve, and an isolation plan for the queries sharing a machine.
Work through these in order: the first answer changes the meaning of all the others.
Work through it
List the operators that cannot spill
Window frames, holistic aggregates (exact medians, distinct counts), state inside user-defined functions, broadcast join sides. These are your kill candidates: for them an underestimate is fatal, not slow.
Find out where the grant comes from, and compare it with reality
If grants come from the optimizer's estimates and you have never compared them with actual peak use: you cannot tell whether you are short of memory or short of good estimates, and the two have opposite fixes. Estimates are wrong by multiples, with a heavy upper tail.
Grant in steps, not on a slope
A spilling operator's cost jumps at pass boundaries: a hash join that needs one more pass costs the same whether it is 1 MB or 100 MB short. Find those boundaries and round grants to them; bytes between steps are wasted.
Measure how correlated the peaks are
Scheduled batch windows, shared upstream data and retry storms all make queries peak together. If peaks are correlated: over-committing fails all at once rather than gradually, so reserve memory instead.
Find the memory the manager cannot see
User-defined functions, native libraries and runtime overhead use memory outside the grants. That amount is the minimum size of your reserve.
Give the pressure loop hysteresis and an early start
A single threshold makes the system spill, free memory, refill and spill again. Use a gap: start spilling at, say, 90% used and stop at 75%. Start early enough that spilling finishes before memory runs out. Without both, the loop oscillates or arrives too late, and both look like "we need more memory".
Separate latency-sensitive work from scans
If short, latency-sensitive queries share a machine with large scans: byte limits do not isolate them, because scans also consume memory bandwidth. No amount of capacity tuning fixes that; separate them.