Site icon

Lesson Learned #333: RESOURCE_SEMAPHORE Wait Type

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

Today, we got a service request that our customer reported that they query are taking too much for their execution. The main wait stats found was RESOURCE_SEMAPHORE and I would like to share with you my lessons learned here. 


We executed this query to find out the queries and check the resource semaphore wait type. 



select tasks.task_address, parent_task_address, task_state, exec_context_id, tasks.scheduler_id, work.worker_address, tasks.session_id, req.status, req.command, blocking_session_id, wait_type, wait_time, req.last_wait_type, cpu_time, dop, sched.scheduler_address, sched.parent_node_id, sched.status, sched.is_online, sched.active_worker_address, substring (REPLACE (REPLACE (SUBSTRING (ST.text , (req.statement_start_offset/2) + 1 , ( (CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text) ELSE req.statement_end_offset END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 1, 512) AS statement_text, * from sys.dm_exec_requests req join sys.dm_os_workers work on req.task_address = work.task_address join sys.dm_os_tasks tasks on req.session_id = tasks.session_id join sys.dm_os_schedulers sched on sched.scheduler_id = tasks.scheduler_id CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as ST where req.status <> 'background' and req.session_id<> @@spid order by req.session_id,tasks.scheduler_id, work.worker_address, req.status



We found that 4 processes are waiting for memory available to perform the operation. 



During the execution of the queries to verify that the issue is regarding about memory resource, I executed the following query to check if the 4 request that are waiting for memory. 



SELECT * FROM SYS.dm_exec_query_resource_semaphores A inner join SYS.dm_resource_governor_resource_pools B on a.pool_id = b.pool_id








So, my lessons learned is RESOURCE_SEMAPHORE wait type is a memory related wait type that shows when a query memory request cannot be assigned/granted immediately. This occurs on databases that are under memory pressure either great number of queries that are running concurrent or few queries for large tables with expensive operations like sort or joins, for example. 


To lower this RESOURCE_SEMAPHORE obviusly increasing the number of vCores will allow us to have memory memory, but, other topics that we applied to reduce this was:




Exit mobile version