Edit

sys.dm_resource_governor_workload_groups (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Returns workload group statistics and the current in-memory configuration of the workload group. This view can be joined with sys.dm_resource_governor_resource_pools to get the resource pool name.

Note

To call this from Azure Synapse Analytics or Analytics Platform System (PDW), use the name sys.dm_pdw_nodes_resource_governor_workload_groups. This syntax is not supported by serverless SQL pool in Azure Synapse Analytics.

Column name Data type Description
group_id int ID of the workload group. Is not nullable.
name sysname Name of the workload group. Is not nullable.
pool_id int ID of the resource pool. Is not nullable.
external_pool_id int Applies to: Starting with SQL Server 2016 (13.x).

ID of the external resource pool. Is not nullable.
statistics_start_time datetime Time that statistics collection was reset for the workload group. Is not nullable.
total_request_count bigint Cumulative count of completed requests in the workload group. Is not nullable.
total_queued_request_count bigint Cumulative count of requests queued after the GROUP_MAX_REQUESTS limit was reached. Is not nullable.
active_request_count int Current request count. Is not nullable.
queued_request_count int Current queued request count. Is not nullable.
total_cpu_limit_violation_count bigint Cumulative count of requests exceeding the CPU limit. Is not nullable.
total_cpu_usage_ms bigint Cumulative CPU usage, in milliseconds, by this workload group. Is not nullable.
max_request_cpu_time_ms bigint Maximum CPU usage, in milliseconds, for a single request. Is not nullable.

Note: This is a measured value, unlike request_max_cpu_time_sec, which is a configurable setting. For more information, see CPU Threshold Exceeded Event Class.
blocked_task_count int Current count of blocked tasks. Is not nullable.
total_lock_wait_count bigint Cumulative count of lock waits that occurred. Is not nullable.
total_lock_wait_time_ms bigint Cumulative sum of elapsed time, in milliseconds, a lock is held. Is not nullable.
total_query_optimization_count bigint Cumulative count of query optimizations in this workload group. Is not nullable.
total_suboptimal_plan_generation_count bigint Cumulative count of suboptimal plan generations that occurred in this workload group due to memory pressure. Is not nullable.
total_reduced_memgrant_count bigint Cumulative count of memory grants that reached the maximum query size limit. Is not nullable.
max_request_grant_memory_kb bigint Maximum memory grant size, in kilobytes, of a single request since the statistics were reset. Is not nullable.
active_parallel_thread_count bigint Current count of parallel thread usage. Is not nullable.
importance sysname Current configuration value for the relative importance of a request in this workload group. Importance is one of the following, with Medium being the default: Low, Medium, or High.

Is not nullable.
request_max_memory_grant_percent int Current setting for the maximum memory grant, as a percentage, for a single request. Is not nullable.
request_max_cpu_time_sec int Current setting for maximum CPU use limit, in seconds, for a single request. Is not nullable.
request_memory_grant_timeout_sec int Current setting for memory grant time-out, in seconds, for a single request. Is not nullable.
group_max_requests int Current setting for the maximum number of concurrent requests. Is not nullable.
max_dop int Configured maximum degree of parallelism for the workload group. The default value, 0, uses global settings. Is not nullable.
effective_max_dop int Applies to: Starting with SQL Server 2012 (11.x).

Effective maximum degree of parallelism for the workload group. Is not nullable.
total_cpu_usage_preemptive_ms bigint Applies to: Starting with SQL Server 2016 (13.x).

Total CPU time used while in preemptive mode scheduling for the workload group, measured in ms. Is not nullable.

To execute code that is outside SQL Server (for example, extended stored procedures and distributed queries), a thread has to execute outside the control of the non-preemptive scheduler. To do this, a worker switches to preemptive mode.
request_max_memory_grant_percent_numeric float Applies to: Azure SQL Managed Instance and starting with SQL Server 2019 (15.x).

Current setting for the maximum memory grant, as a percentage, for a single request. Similar to request_max_memory_grant_percent, which returns an integer, request_max_memory_grant_percent_numeric returns a float. Starting with SQL Server 2019 (15.x), the parameter REQUEST_MAX_MEMORY_GRANT_PERCENT accepts values with a possible range of 0-100 and stores them as the float data type. Prior to SQL Server 2019 (15.x), REQUEST_MAX_MEMORY_GRANT_PERCENT is an integer with possible range of 1-100. For more information, see CREATE WORKLOAD GROUP.

Is not nullable.
pdw_node_id int Applies to: Azure Synapse Analytics, Analytics Platform System (PDW)

The identifier for the node that this distribution is on.

Remarks

This dynamic management view shows the in-memory configuration. To see the stored configuration metadata, use the sys.resource_governor_workload_groups (Transact-SQL) catalog view.

When ALTER RESOURCE GOVERNOR RESET STATISTICS is successfully executed, the following counters are reset: statistics_start_time, total_request_count, total_queued_request_count, total_cpu_limit_violation_count, total_cpu_usage_ms, max_request_cpu_time_ms, total_lock_wait_count, total_lock_wait_time_ms, total_query_optimization_count, total_suboptimal_plan_generation_count, total_reduced_memgrant_count, and max_request_grant_memory_kb. The counter statistics_start_time is set to the current system date and time, and the other counters are set to zero (0).

Permissions

Requires VIEW SERVER STATE permission.

Permissions for SQL Server 2022 and later

Requires VIEW SERVER PERFORMANCE STATE permission on the server.

See also