SQL server slow with wait_type RESOURCE_SEMAPHORE

SQL Server slow without any reason ? lot timeout complain? if wait_type = RESOURCE_SEMAPHORE , can try this

We have issue when users keep complain their query timeout, query can’t complete

We run some query in SSMS, result return very fast; The server with low CPU, no blocking, no long run query and active connection keep accumulate.

We can try this method and check

--watch for waiter_count

-- if waiter_count> 0, mean query need wait for memory. Normally we should expect this value =0

--resource_semaphore_id = 0 , large query

--resource_semaphore_id=1, small query

 

SELECT waiter_count,grantee_count,* FROM sys.dm_exec_query_resource_semaphores

--if waiter_count>0 all the time, check this

--what query have granted_memory_kb
select granted_memory_kb ,grant_TIME,SESSION_ID,resource_semaphore_id from
sys.dm_exec_query_memory_grants
order by 1 desc

Look for session_id with largest granted_memory,

Check what query is running with this session,

Dbcc inputbuffeR(session_id)

Remember capture hostname, loginame and how long the query already runs.

Sp_who2(session_id)

Good Article :
SQL wait_type RESOURCE_SEMAPHORE troubleshooting