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.
|
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.
|
|
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.
.gif) Note: |
|
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:
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
|
Maximum degree of parallelism for the workload group. The default value, 0, uses global settings. Is not nullable.
|
Remarks
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).
Permissions
Requires VIEW SERVER STATE permission.