sys.dm_resource_governor_workload_groups (Transact-SQL)
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.