1

I was thinking about something like this today but I couldn't find this piece of information.

When the SQL Server requests memory and the OS can't provide it, we get a RESOURCE_SEMAPHORE wait type.

but I can't find info like:

  1. lets say the query asks for 3GB. But it's only possible to provide 2, does the query still run slowly, or it keeps there waiting?

  2. RESOURCE_SEMAPHORE means the query is in a "stopped" state just waiting for the memory?

  3. Memory grants pending should always be on 0 right? this means how much memory the queries are requesting but still waiting right?

I was reading Memory Grants: The mysterious SQL Server memory consumer with Many Names from Microsoft, but it doesn't explain this, it just explains what a Memory grant is.

1 Answer 1

6

Paul Randal describes the RESOURCE_SEMAPHORE:

This wait type is when a thread is waiting for a query execution memory grant so it can begin executing. [emphasis added]

I understand without the needed memory the query cannot begin. His article also mentions the query wont wait for ever:

High waits of this type are because queries cannot get the memory they need to execute, and you may also see error 8645 (“A timeout occurred while waiting for memory resources to execute the query in resource pool ‘’ (). Rerun the query.”

The doc How to troubleshoot SQL Server error 8645 might also help:

The error message 8645 is raised when memory-intensive queries, such as those involving sorting and hashing, are queued and are unable to obtain the requested memory before a time-out period. SQL Server waits for memory for a length of time that is based on the query wait configuration value. By default, the query wait value is set to -1, which means SQL Server waits for 25 times the estimated cost of the query. If you modify the query wait value to a non-negative number, SQL Server waits for the number of seconds that you specify in the value of the query wait option.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.