Memory Grants and Spills
Overview
A query that asks for four gigabytes of workspace memory and uses forty megabytes has taken four gigabytes away from the buffer pool and from every other query trying to start.
It is one of the most common causes of a server that is slow with no obvious bottleneck, and nothing else in this application looks at it. The Memory report follows RAM down to the database and stops; workspace memory is the other half of the memory story.
The ranking metric
Wasted grant: granted memory, minus the most the query was ever seen to use, multiplied by how often it runs.
It is a single derived number and it is the thing no other tool puts on a screen. Everybody alerts on RESOURCE_SEMAPHORE; alerting says it happened, and this says which query to fix.
Where to find it
In the tree, right-click an instance and choose Instance Level Reports → Memory Grants and Spills. It is a recommended report and appears in the Server Overview list.
Requirements
| Requirement | Why |
|---|---|
| SQL Server 2016 SP1 or newer | The grant columns on sys.dm_exec_query_stats arrived there. |
| SQL Server 2016 SP2 / 2017 for spills | total_spills arrived later than the grant columns. |
VIEW SERVER STATE |
For every DMV on the page. |
The live view works on every build back to SQL Server 2005, so an older instance is turned away from the league table rather than from the whole page. The capability is probed with COL_LENGTH rather than from a version number, because the columns arrived in service packs and a version number does not answer the question on an unpatched build.
Reading the chart
The bars are nested, not stacked, and that is the whole point of the drawing.
- The outer bar is what was granted, in the row’s state colour.
- The inner bar is what was actually used, drawn inside it from the same left edge.
- The empty remainder of the outer bar is memory reserved and never touched. That is the finding.
Stacking used against wasted as two adjacent segments would have hidden it in plain sight: two segments read as parts of a total, and the reader is being asked to see one bar sitting inside another.
Requested gets a tick rather than a bar. Requested against granted is a different story from granted against used – it says the resource semaphore trimmed the request, which is evidence about the instance rather than about the query. It belongs on the row and must not compete with the two lengths that carry the finding.
Spill is drawn past the right hand end of the granted bar, hatched, in its own colour, because it is the opposite failure of the same mechanism. A grant too large wastes memory; a grant too small spills the sort or the hash to tempdb and turns a memory operation into an IO operation. Both belong on one page and neither is a segment of the other.
Every quantity on the chart is kilobytes, which is the unit the grant DMVs use. Nothing is converted on the way in, so nothing can be converted twice.
The three views
Grant waste
The league table, from the plan cache, ranked by memory reserved and not used.
Right now
Every query holding or waiting for a grant, from sys.dm_exec_query_memory_grants, with the semaphore state beside it.
A grant row with a NULL grant_time is the point of this view. That query has not started: it is sitting in the semaphore queue with a plan and no memory. Those rows sort to the top, and wait_time_ms says how long they have been there. Nothing else in this application shows them.
This view refreshes itself every five seconds. The Pause button stops the countdown, and the pause is remembered per report and survives navigating away and coming back.
Spills
The opposite failure. Cached plans that ran out of granted memory and wrote to tempdb, ranked by how much went there.
The banner
| Line | When it appears |
|---|---|
| N queries are queued for workspace memory right now | waiter_count on either semaphore is above zero. Until they get a grant they have not started at all. |
| N queries have errored out waiting for a memory grant | timeout_error_count. That is error 8645, and it is a failed query rather than a slow one. |
| N grants have been forced through at the minimum size | forced_grant_count. Those queries ran, and they will have spilled. |
| This build has no spill counters | Older than SQL Server 2016 SP2 and 2017. |
The toolbar
| Control | What it does |
|---|---|
| Grant waste / Right now / Spills | The three views. |
| Pause / Resume | Only on the live view. Stops the five second refresh. |
| Refresh | Re-reads everything now. |
Reading the grid
| Column | What it is |
|---|---|
| State | Waste, Queued, Spill, Watch or Healthy, in the chart’s colour. |
| Query | The statement, cut out of its batch with the statement offsets. |
| Database | Which database the plan belongs to. |
| Exec / day or Session | Executions per day on the cache views, the session id on the live one. |
| Granted / Max used | The two numbers the finding is made of. |
| Waste / day | The ranking metric on the waste view. |
| Spilled | Kilobytes written to tempdb by this plan. |
| Plan age or Waiting | What the per day figure is extrapolated from, or how long this query has been queued. |
| Next step | What to do about it, and never “add memory”. |
Four things the page states rather than assumes
max_used_grant_kb is a high water mark across every execution of that plan, not a per execution figure. Waste computed against it is therefore the conservative number – the real waste is at least this and probably more – which is the right direction to be wrong in.
Grant figures are kilobytes and are per query rather than per node. Multiplying by degree of parallelism is a common and expensive mistake.
Executions per day is extrapolated from the age of the cached plan, and the plan cache is volatile. A plan cached twenty minutes ago carries a per day figure with twenty minutes of evidence behind it, so the plan age is in the grid next to it. The extrapolation is clamped at one hour of age so a plan cached two minutes ago cannot be multiplied out to a day.
Memory grant feedback changes the story and its availability varies. Batch mode from SQL Server 2017, row mode from 2019, percentile and persistence from 2022. A query with feedback active is already correcting itself and needs a few more executions rather than a hint. The footer says which of those this build has.
The fix is almost never “add memory”
Grants are the mechanism by which one bad cardinality estimate becomes an instance wide concurrency problem. Ten queries with 25 percent grants each and the eleventh waits, no matter how trivial it is.
The fix is the estimate that produced the request, which is usually stale statistics or an implicit conversion feeding a predicate. Both have a report in this application, and the Next step column refers to them by name rather than leaving the reader at a dead end.
Where the data comes from
| Source | What it gives |
|---|---|
sys.dm_exec_query_memory_grants |
The live view: requested, granted, used, ideal, queue position, wait time. |
sys.dm_exec_query_resource_semaphores |
Target and available memory, grantees, waiters, timeouts, forced grants. |
sys.dm_exec_query_stats |
The historical grant and spill columns, per cached plan. |
sys.dm_exec_sql_text |
The statement behind each plan. |
sys.dm_os_wait_stats |
The RESOURCE_SEMAPHORE share of wait time. |
Two semaphores exist on every instance – one for ordinary grants and one for small ones – so the rows are kept apart rather than summed. A page that added their target memory together would report a pool twice the size of the one queries actually queue in.
RESOURCE_SEMAPHORE_QUERY_COMPILE is reported next to RESOURCE_SEMAPHORE and never added to it. It is queries waiting for memory to compile in, which is a different queue with a different fix, and treating the two as one number is how somebody ends up adding memory to fix a plan cache problem.
Messages you may see
The Memory Grants report requires SQL Server 2016 SP1 or newer. Neither the grant columns nor the resource semaphores view exists here.
No query is holding or waiting for a memory grant at this moment. The live view on a quiet instance. It is a snapshot, not a history: a query that came and went between two reads was never here.
No cached plan is reserving noticeably more workspace memory than it uses. A real result. The floor is one megabyte, below which a grant is not worth anybody’s attention.
Related reports
| Report | Why you would go there |
|---|---|
| Memory | Where the rest of the memory on this instance has gone. |
| Plan Warnings | The implicit conversion behind a bad estimate behind a bad grant. |
| Waits by Query | Which queries the RESOURCE_SEMAPHORE waiting belongs to. |
| Parallelism Calibration | Grants are per query and scale with degree of parallelism at compile time. |
| What is Active | What else was running when the queue built up. |
Frequently asked questions
Why is the waste figure described as conservative? Because it is computed against the largest amount that plan was ever seen to use, across every execution. Most executions used less, so the real waste is larger.
Why does a query show a grant far larger than the table it reads? The grant comes from the estimated row count and row size at compile time. A bad estimate produces a bad grant regardless of how much data is really there.
Should I raise max server memory to fix this? Almost never. A larger pool means a larger semaphore target and therefore larger grants for the same bad estimates. The estimate is the fix.
What is a forced grant? The semaphore gave a query the minimum grant rather than making it wait any longer. It runs, and it will spill. A rising forced_grant_count is an instance under sustained workspace memory pressure.
Why does the live view show a session with no query text? The batch left the plan cache while the grant was still held. The session and its queue position are still worth showing, which is why the text lookup is an OUTER APPLY rather than a CROSS APPLY.
Can I kill a queued query from this page? No. Nothing on this page changes anything on the instance.