SQL Server Books online  defines the RESOURCE SEMAPHORE SQL wait stat type as “Occurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts”

RESOURCE SEMAPHORE usually correlates with poor response time to all the users ,  so a high percentage of RESOURCE SEMAPHORE wait time is a critical indicator.

 The Resource Semaphore manager reserves memory or throttles the queries if the memory requested by the queries exceeds the memory capacity.

Memory intensive queries  - using sorting and hashing are the main underlying reasons.  The GROUP BY clause and ORDER BY clause   use sorting and hashing.

A built in feature of SQL server as it doesn’t want to overcommit queries beyond the memory capacity. SQL Server checks to see if memory grant is necessary. If not , the query executes immediately. If there isn’t enough memory , the query queues.

Troubleshooting

1)  Capture and fine tune slow running queries  using SQL Server query tuning techniques

2)  If step 1 doesn’t help, look to distribute queries across a wider time frame.

3)  Use the DMV - sys.dm_exec_query_resource_semaphores

4)  Use the DMV sys.dm_exec_query_memory_grants

Displays queries with memory grant – or waiting for a memory grant. When RESOURCE_SEMAPHORE wait stat is reported – I usually check this DMV as an immediate check.

 5)  Cross check the wait type RESOURCE SEMAPHORE with the SQL Serverperfmon counters

5.1)          Memory Grants Pending  - If greater than 0 , there is a problem

5.2)          Memory Grants Outstanding – look for a higher than normal value

Use these queries to access the current Memory Grants Pending  values and Memory Grants Outstanding.