sys.dm_exec_query_resource_semaphores (Transact-SQL)
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 |
Nonunique ID of the resource semaphore. 0 for the regular resource semaphore and 1 for the small-query resource semaphore. .gif "Note") Note
This ID is unique in versions of SQL Server that are earlier than SQL Server 2008. This change can affect troubleshooting query execution. For more information, see the "Remarks" section later in this topic.
|
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. |
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.
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 20 pools. In SQL Server 2008, each pool behaves like a small independent server instance and requires 2 semaphores. The number of rows that are returned from sys.dm_exec_query_resource_semaphores can be up to 20 times more than the rows that are returned in SQL Server 2005.