Export (0) Print
Expand All

sys.dm_resource_governor_workload_groups (Transact-SQL)

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.

NoteNote
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

  • 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

Maximum degree of parallelism for the workload group. The default value, 0, uses global settings. Is not nullable.

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.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft