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. 

 

Jose_Manuel_Jurado_0-1677179434997.png

 

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

 

 

Jose_Manuel_Jurado_1-1677179649620.png

 

  • 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.

 

Jose_Manuel_Jurado_2-1677181291444.png

 

 

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:

 

  • 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!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.