RESOURCE_SEMAPHORE
Published 14 February 2023
This wait occurs when memory can’t be granted to allow query execution, or a process is waiting while query optimization takes place. The most likely cause is an excessive amount of memory requests or concurrent processes. This can also be common in Decision Support or Data Warehousing environments, where large queries involving memory-intensive operations such as sorts and hash joins typically occur.
Sorts and hash joins rely on grants to reserve temporary space in memory before they actually use it. It is resource semaphore’s job to manage memory grant requests and keep within the server memory limit. If there’s not enough free memory, the query is put into the waiting queue.
Investigating
Check the affected queries and the Top queries table. Tune problematic queries if necessary; check for missing indexes, or indexes containing an incorrect sort order. See: Query tuning (TechNet) and Showplan Operator of the Week – SORT.
To ensure accurate memory allocations, check that the statistics on tables and indexes used the affected queries are up to date. See: Statistics in SQL Server.
Query the DMV sys.dm_exec_query_memory_grants to see how many queries are waiting for grants, and which queries are using the most resource. See: sys.dm_exec_query_memory_grants (TechNet).
Query the DMV sys.dm_exec_query_resource_semaphores to show the amount of memory held. See: sys.dm_exec_query_resource_semaphores (TechNet).
If you’re experiencing ongoing memory issues, consider installing memory-related custom metrics, such as Cached pages in TempDB and Buffer pressure.