Export (0) Print
Expand All

sys.dm_resource_governor_workload_groups (Transact-SQL)


Updated: March 30, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

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.

System_CAPS_ICON_note.jpg Note

To call this from Azure SQL Data Warehouse or Parallel Data Warehouse, use the name sys.dm_pdw_nodes_resource_governor_workload_groups.

Column nameData typeDescription
group_idintID of the workload group. Is not nullable.
namesysnameName of the workload group. Is not nullable.
pool_idintID of the resource pool. Is not nullable.
external_pool_idintApplies to: SQL Server 2016 through SQL Server 2016.

ID of the external resource pool. Is not nullable.
statistics_start_timedatetimeTime that statistics collection was reset for the workload group. Is not nullable.
total_request_countbigintCumulative count of completed requests in the workload group. Is not nullable.
total_queued_request_countbigintCumulative count of requests queued after the GROUP_MAX_REQUESTS limit was reached. Is not nullable.
active_request_countintCurrent request count. Is not nullable.
queued_request_countintCurrent queued request count. Is not nullable.
total_cpu_limit_violation_countbigintCumulative count of requests exceeding the CPU limit. Is not nullable.
total_cpu_usage_msbigintCumulative CPU usage, in milliseconds, by this workload group. Is not nullable.
max_request_cpu_time_msbigintMaximum 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_countintCurrent count of blocked tasks. Is not nullable.
total_lock_wait_countbigintCumulative count of lock waits that occurred. Is not nullable.
total_lock_wait_time_msbigintCumulative sum of elapsed time, in milliseconds, a lock is held. Is not nullable.
total_query_optimization_countbigintCumulative count of query optimizations in this workload group. Is not nullable.
total_suboptimal_plan_generation_countbigintCumulative count of suboptimal plan generations that occurred in this workload group due to memory pressure. Is not nullable.
total_reduced_memgrant_countbigintCumulative count of memory grants that reached the maximum query size limit. Is not nullable.
max_request_grant_memory_kbbigintMaximum memory grant size, in kilobytes, of a single request since the statistics were reset. Is not nullable.
active_parallel_thread_countbigintCurrent count of parallel thread usage. Is not nullable.
importancesysnameCurrent 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_percentintCurrent setting for the maximum memory grant, as a percentage, for a single request. Is not nullable.
request_max_cpu_time_secintCurrent setting for maximum CPU use limit, in seconds, for a single request. Is not nullable.
request_memory_grant_timeout_secintCurrent setting for memory grant time-out, in seconds, for a single request. Is not nullable.
group_max_requestsintCurrent setting for the maximum number of concurrent requests. Is not nullable.
max_dopintMaximum degree of parallelism for the workload group. The default value, 0, uses global settings. Is not nullable.
pdw_node_idintApplies to: Azure SQL Data Warehouse, Parallel Data Warehouse

The identifier for the node that this distribution is on.

This dynamic management view shows the in-memory configuration. To see the stored configuration metadata, use the sys.resource_governor_workload_groups 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. statistics_start_time is set to the current system date and time, the other counters are set to zero (0).

Requires VIEW SERVER STATE permission.

Dynamic Management Views and Functions (Transact-SQL)
sys.dm_resource_governor_resource_pools (Transact-SQL)
sys.resource_governor_workload_groups (Transact-SQL)

Community Additions

© 2016 Microsoft