RESOURCE_SEMAPHORE wait type

RESOURCE_SEMAPHORE:

Long explanation: RESOURCE_SEMAPHORE 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.

Short explanation: memory grant pressure.

( Understanding SQL server memory grant: http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx )

In order to get more data, use:

select * from sys.dm_exec_query_resource_semaphores

Returns the information about the current query-resource semaphore status.

http://technet.microsoft.com/en-us/library/ms366321.aspx

• total_memory_kb - Memory held by the resource semaphore in kilobytes. check it against target_memory_kb or max_target_memory_kb values.
• waiter_count - the number of queries waiting for grants to be satisfied.

select * from sys.dm_exec_query_memory_grants

Returns information about the queries that have acquired a memory grant or that still require a memory grant to execute

http://technet.microsoft.com/en-us/library/ms365393.aspx

SELECT cntr_value AS [Memory Grants Outstanding]
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = 'MSSQL$MyInstance:Memory Manager'
AND counter_name = 'Memory Grants Outstanding'

SELECT OBJECT_NAME,cntr_value AS [Memory Grants Pending]
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = 'MSSQL$MyInstance:Memory Manager'
AND counter_name = 'Memory Grants Pending'

An useful link regarding RESOURCE_SEMAPHORE:

http://www.sqlserver-dba.com/2011/06/sql-wait-type-resource-semaphore-and-how-to-reduce-it.html