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