sys-dm-exec-query-resource-semaphores-transact-sql.md

title	description	author	ms.author	ms.date	ms.service	ms.subservice	ms.topic	f1_keywords	helpviewer_keywords	dev_langs	monikerRange
sys.dm_exec_query_resource_semaphores (Transact-SQL)	sys.dm_exec_query_resource_semaphores (Transact-SQL)	rwestMSFT	randolphwest	02/24/2023	sql	system-objects	reference	sys.dm_exec_query_resource_semaphores_TSQL dm_exec_query_resource_semaphores_TSQL sys.dm_exec_query_resource_semaphores dm_exec_query_resource_semaphores	sys.dm_exec_query_resource_semaphores dynamic management view	TSQL	>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

sys.dm_exec_query_resource_semaphores (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw]

Returns the information about the current query-resource semaphore status in [!INCLUDEssNoVersion]. 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

Note

To call this from [!INCLUDEssazuresynapse-md] or [!INCLUDEssPDW], use the name sys.dm_pdw_nodes_exec_query_resource_semaphores. [!INCLUDEsynapse-analytics-od-unsupported-syntax]

Column name	Data type	Description
resource_semaphore_id	smallint	Nonunique ID of the resource semaphore. 0 for the regular resource semaphore and 1 for the small-query resource semaphore.
target_memory_kb	bigint	Grant usage target in kilobytes.
max_target_memory_kb	bigint	Maximum potential target in kilobytes. NULL for the small-query resource semaphore.
total_memory_kb	bigint	Memory 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_kb	bigint	Memory available for a new grant in kilobytes.
granted_memory_kb	bigint	Total granted memory in kilobytes.
used_memory_kb	bigint	Physically used part of granted memory in kilobytes.
grantee_count	int	Number of active queries that have their grants satisfied.
waiter_count	int	Number of queries waiting for grants to be satisfied.
timeout_error_count	bigint	Total number of time-out errors since server startup. NULL for the small-query resource semaphore.
forced_grant_count	bigint	Total number of forced minimum-memory grants since server startup. NULL for the small-query resource semaphore.
pool_id	int	ID of the resource pool to which this resource semaphore belongs.
pdw_node_id	int	Applies to: [!INCLUDEssazuresynapse-md], [!INCLUDEssPDW] The identifier for the node that this distribution is on.

Permissions

On [!INCLUDEssNoVersion_md] and SQL Managed Instance, requires VIEW SERVER STATE permission.

On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account, the Microsoft Entra admin account, or membership in the ##MS_ServerStateReader## server role is required. On all other SQL Database service objectives, either the VIEW DATABASE STATE permission on the database, or membership in the ##MS_ServerStateReader## server role is required.

Permissions for SQL Server 2022 and later

Requires VIEW SERVER PERFORMANCE STATE permission on the server.

Remarks

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 [!INCLUDEssNoVersion].

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

See Also

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