sys.dm_exec_query_resource_semaphores – Day 45 – One DMV a Day

Hello Geeks and welcome to the Day 45 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

Yesterday I have covered sys.dm_exec_query_memory_grants. Today as mentioned in my last post, I will be covering sys.dm_exec_query_resource_semaphores. This DMV returns information for regular resource semaphore and small query resource semaphore for each pool id in the instance.

Sys.dm_exec_query_resource_semaphores will help you understand if the system can access enough memory. When used with sys.dm_os_memory_clerks you can get the complete memory picture. A query when requests for memory grant the resource pool is checked if the memory is available to satisfy.

By default there will be two resource pools in any SQL instance, Default and Internal. When I run select on sys.dm_exec_query_resource_semaphores I will see the below output. I haven’t configured my resource governor.

select * from sys.dm_exec_query_resource_semaphores

sys.dm_exec_query_resource_semaphores

The above output from sys.dm_exec_query_resource_semaphores is straight forward. The target server memory for each pool is the target grant usage. The available memory is memory available for grants. Granted_memory_kb is the memory which is granted.

Used_memory_kb is the actual used memory out of granted. If this value is very less than the granted_memory_kb we are getting bad memory grants. So there will be no query which will wait on RESOURCE_SEMAPHORE waittype. Also there are two other columns in sys.dm_exec_resource_semaphore which can indicate memory grant problems. They are waiter_count and timeout_error_count.

The well-known offenders for RESOURCE_SEMAPHORE waittypes are

Large datatypes – VARCHAR(MAX) is estimated for 50% of max – 4000
T-SQL XML.query method usage. The estimates can be very bad
Batch sorts can have incorrect estimates
Linked server queries – Views or lack of access to stats can result in bad estimates

There is another waittype which is associated with memory allocations during compilations. RESOURCE_SEMAPHORE_QUERY_COMPILE is visible when the query is waiting for memory to compile. The result is longer execution times for your queries. A compilation memory is not the memory needed for execution. This is used for parsing, algebraization and optimization. The classification is done using this memory need for compilation. Based on the classification they use gateways to get memory to compile. You can read more about gateway architecture here.

Tomorrow I will be covering another execution related DMV. Stay tuned. Till then

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

Leave a Reply

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