RESOURCE_SEMAPHORE

Description

Occurs while query memory requests cannot be granted due to other concurrent queries. High values of this wait type may indicate excessive number of concurrent queries, or excessive memory requests

Resolved by

DBAs and Developers

Suggested solutions

  1. Ensure that statistics are up to date
  2. Run a trace/Profiler/Extended Event session along with Windows Performance Monitor to determine which queries require an excessive amount of memory
  3. Indexing, look for high duration queries with large sorts and hash joins
  4. If possible rewrite queries to avoid large UNION, DISTINCT and ORDER BY operations
  5. Increase the maximum memory allocation

Additional research

Troubleshooting SQL Server RESOURCE_SEMAPHORE
Resource_semaphore wait type when using SORT

See more

Consider using ApexSQL Monitor, a Windows and SQL Server monitoring tool able to track SQL Server wait types, including the individual query wait statistics

⇐ Back to index