Lesson Learned #333: RESOURCE_SEMAPHORE Wait Type

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. 

• As Azure SQL DB and MI is working use resource governor, I used the query resource semaphore with union of resource pools to determine if the pool of the user. 

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, it is SloSharedPool1 that represents the resources assigned to my database and resource_semaphore_id=0 represent memory for large queries. 

• So, all points to that our customer is running queries that are involved many resources, like sorts, and many of them are waiting for a high amount of memory, as you could see in the below picture in the memory grant option of the execution plan.

So, my lessons learned is RESOURCE_SEMAPHORE is a memory related wait type that shows when a query requests memory 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:

• Review the execution plan for the queries that are using large sorts and joins to reduce the number needed. 
• Add an index to the table where the sort is performing. 
• Review the value of MAXDOP. 
• Review the option to use an Indexed View.

Enjoy!