Export (0) Print
Expand All

sys.resource_governor_workload_groups (Transact-SQL)

Returns the stored workload group configuration in SQL Server. Each workload group can subscribe to one and only one resource pool.

Applies to: SQL Server (SQL Server 2008 through current version).

Column name

Data type

Description

group_id

int

Unique ID of the workload group. Is not nullable.

name

sysname

Name of the workload group. Is not nullable.

importance

sysname

Is 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.

Note Note

Importance only applies to workload groups in the same resource pool.

request_max_memory_grant_percent

int

Maximum memory grant, as a percentage, for a single request. The default value is 25. Is not nullable.

Note Note

If this setting is higher than 50 percent, large queries will run one at a time. Therefore, there is greater risk of getting an out-of-memory error while the query is running.

request_max_cpu_time_sec

int

Maximum CPU use limit, in seconds, for a single request. The default value, 0, specifies no limit. Is not nullable.

Note Note

For more information, see CPU Threshold Exceeded Event Class.

request_memory_grant_timeout_sec

int

Memory grant time-out, in seconds, for a single request. The default value, 0, uses an internal calculation based on query cost. Is not nullable.

max_dop

int

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

Note Note

This setting will override the query option maxdop.

group_max_requests

int

Maximum number of concurrent requests. The default value, 0, specifies no limit. Is not nullable.

pool_id

int

ID of the resource pool that this workload group uses.

group_min_memory_percent

int

Percentage of MIN_MEMORY_PERCENT, which is the minimum amount of memory reserved for the resource pool that cannot be shared with other resource pools. This value provides a guaranteed minimum for a group.

Applies to: SQL Server 2012 through SQL Server 2014.

The catalog view displays the stored metadata. To see the in-memory configuration, use the corresponding dynamic management view, sys.dm_resource_governor_workload_groups (Transact-SQL).

The stored and in-memory configuration can be different if the Resource Governor configuration has been changed but the ALTER RESOURCE GOVERNOR RECONFIGURE statement has not been applied.

Requires VIEW ANY DEFINITION permission to view contents, requires CONTROL SERVER permission to change contents.

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

Community Additions

ADD
Show:
© 2014 Microsoft