sys.dm_exec_query_resource_semaphores (Transact-SQL)

 

Updated: August 9, 2016

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Returns the information about the current query-resource semaphore status in SQL Server. sys.dm_exec_query_resource_semaphores provides general query-execution memory status and allows you to determine whether the system can access enough memory. This view complements memory information obtained from sys.dm_os_memory_clerks to provide a complete picture of server memory status. sys.dm_exec_query_resource_semaphores returns one row for the regular resource semaphore and another row for the small-query resource semaphore. There are two requirements for a small-query semaphore:

  • The memory grant requested should be less than 5 MB

  • The query cost should be less than 3 cost units

System_CAPS_ICON_note.jpg Note


To call this from Azure SQL Data Warehouse or Parallel Data Warehouse, use the name sys.dm_pdw_nodes_exec_query_resource_semaphores.

Column nameData typeDescription
resource_semaphore_idsmallintNonunique ID of the resource semaphore. 0 for the regular resource semaphore and 1 for the small-query resource semaphore.
target_memory_kbbigintGrant usage target in kilobytes.
max_target_memory_kbbigintMaximum potential target in kilobytes. NULL for the small-query resource semaphore.
total_memory_kbbigintMemory held by the resource semaphore in kilobytes. If the system is under memory pressure or if forced minimum memory is granted frequently, this value can be larger than the target_memory_kb or max_target_memory_kb values. Total memory is a sum of available and granted memory.
available_memory_kbbigintMemory available for a new grant in kilobytes.
granted_memory_kbbigintTotal granted memory in kilobytes.
used_memory_kbbigintPhysically used part of granted memory in kilobytes.
grantee_countintNumber of active queries that have their grants satisfied.
waiter_countintNumber of queries waiting for grants to be satisfied.
timeout_error_countbigintTotal number of time-out errors since server startup. NULL for the small-query resource semaphore.
forced_grant_countbigintTotal number of forced minimum-memory grants since server startup. NULL for the small-query resource semaphore.
pool_idintID of the resource pool to which this resource semaphore belongs.
pdw_node_idintApplies to: Azure SQL Data Warehouse, Parallel Data Warehouse

The identifier for the node that this distribution is on.

On SQL Server requires VIEW SERVER STATE permission on the server.

On SQL Database Premium Tiers requires the VIEW DATABASE STATE permission in the database. On SQL Database Standard and Basic Tiers requires the SQL Database admin account.

Queries that use dynamic management views that include ORDER BY or aggregates might increase memory consumption and thus contribute to the problem they are troubleshooting.

Use sys.dm_exec_query_resource_semaphores for troubleshooting but do not include it in applications that will use future versions of SQL Server.

The Resource Governor feature enables a database administrator to distribute server resources among resource pools, up to a maximum of 64 pools. In SQL Server 2012 and higher, each pool behaves like a small independent server instance and requires 2 semaphores.

Execution Related Dynamic Management Views and Functions (Transact-SQL)
sys.dm_exec_query_memory_grants (Transact-SQL)

Community Additions

ADD
Show: