sys.dm_exec_query_resource_semaphores

New: 14 April 2006

Returns the information about the current query-resource semaphore status. 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.

Column name Data type Description

resource_semaphore_id

smallint

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.

Permissions

Requires VIEW SERVER 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.

sys.dm_exec_query_resource_semaphores is new in SQL Server 2005 Service Pack 1. Information in this view is specific to the internal implementation of SQL Server 2005 and is expected to change in the next release of SQL Server. Use sys.dm_exec_query_resource_semaphores for troubleshooting but do not include it in applications that will use future versions of SQL Server.

See Also

Reference

Execution Related Dynamic Management Views and Functions
sys.dm_exec_query_memory_grants

Help and Information

Getting SQL Server 2005 Assistance