Share via

  • Article

ALTER WORKLOAD GROUP (Transact-SQL)

Changes an existing Resource Governor workload group configuration and optionally, assigns it to a Resource Governor resource pool.

Topic link iconTransact-SQL Syntax Conventions.

Syntax

ALTER WORKLOAD GROUP { group_name | "default" }
[ WITH
    ([ IMPORTANCE = { LOW | MEDIUM | HIGH } ]
      [ [ , ] REQUEST_MAX_MEMORY_GRANT_PERCENT =value ]
      [ [ , ] REQUEST_MAX_CPU_TIME_SEC =value ]
      [ [ , ] REQUEST_MEMORY_GRANT_TIMEOUT_SEC =value ]
      [ [ , ] MAX_DOP =value ]
      [ [ , ] GROUP_MAX_REQUESTS =value ] )
 ]
[ USING { pool_name | "default" } ]
[ ; ]

Arguments

  • group_name | "default"
    Is the name of an existing user-defined workload group or the Resource Governor default workload group that is created when SQL Server 2008 is installed.

    The option "default" must be enclosed by quotation marks ("") or brackets ([]) when used with ALTER WORKLOAD GROUP to avoid conflict with DEFAULT, which is a system reserved word. For more information, see Delimited Identifiers (Database Engine).

    Note

    Predefined workload groups and resource pools all use lower case names, such as "default". This should be taken into account for servers that use case-sensitive collation. Servers with case-insensitive collation, such as SQL_Latin1_General_CP1_CI_AS, will treat "default" and "Default" as the same.

  • IMPORTANCE = { LOW | MEDIUM | HIGH }
    Specifies the relative importance of a request in the workload group. Importance is one of the following:

    • LOW

    • MEDIUM (default)

    • HIGH

    Note

    Internally, each importance setting is stored as a number that is used for calculations.

    IMPORTANCE is local to the resource pool; workload groups of different importance inside the same resource pool affect each other, but do not affect workload groups in another resource pool.

  • REQUEST_MAX_MEMORY_GRANT_PERCENT =value
    Specifies the maximum amount of memory that a single request can take from the pool. This percentage is relative to the resource pool size specified by MAX_MEMORY_PERCENT.

    Note

    The amount specified only refers to query execution grant memory.

    value must be 0 or a positive integer. The allowed range for value is from 0 through 100. The default setting for value is 25.

    Note the following:

    • Setting value to 0 prevents queries with SORT and HASH JOIN operations in user-defined workload groups from running.

    • We do not recommend setting value greater than 70 because the server may be unable to set aside enough free memory if other concurrent queries are running. This may eventually lead to query time-out error 8645.

    Note

    If the query memory requirements exceed the limit that is specified by this parameter, the server does the following:

    For user-defined workload groups, the server tries to reduce the query degree of parallelism until the memory requirement falls under the limit, or until the degree of parallelism equals 1. If the query memory requirement is still greater than the limit, error 8657 occurs.

    For internal and default workload groups, the server permits the query to obtain the required memory.

    Be aware that both cases are subject to time-out error 8645 if the server has insufficient physical memory.

    For more information about Resource Governor error messages, see Troubleshooting Resource Governor.

  • REQUEST_MAX_CPU_TIME_SEC =value
    Specifies the maximum time, in seconds, that a query can wait for a resource to become available before the query fails. value must be zero or a positive integer. The default setting for value, 0, uses an internal calculation based on query cost to determine the maximum time.

    Note

    Resource Governor will not prevent a request from continuing if the maximum time is exceeded. However, an event will be generated. For more information, see CPU Threshold Exceeded Event Class.

  • REQUEST_MEMORY_GRANT_TIMEOUT_SEC =value
    Specifies the maximum time, in seconds, that a query can wait for memory grant (work buffer memory) to become available.

    Note

    A query does not always fail when the memory grant time-out is reached. A query will only fail if there are too many concurrent queries running. Otherwise, the query may only get the minimum memory grant, resulting in reduced query performance.

    value must be a positive integer. The default setting for value, 0, uses an internal calculation based on query cost to determine the maximum time.

  • MAX_DOP =value
    Specifies the maximum degree of parallelism (DOP) for parallel requests. value must be 0 or a positive integer. The allowed range for value is from 0 through 64. The default setting for value, 0, the server chooses the max degree of parallelism. This is the default and recommended setting. The maximum value used by the server is 64 when value equals 0. MAX_DOP is handled as follows:

    • MAXDOP as a query hint is honored as long as it does not exceed workload group MAX_DOP.

    • MAXDOP as a query hint always overrides sp_configure 'max degree of parallelism'.

    • Workload group MAX_DOP overrides sp_configure 'max degree of parallelism'.

    • If the query is marked as serial (MAX_DOP = 1 ) at compile time, it cannot be changed back to parallel at run time regardless of the workload group or sp_configure setting.

    After DOP is configured, it can only be lowered on grant memory pressure. Workload group reconfiguration is not visible while waiting in the grant memory queue.

  • GROUP_MAX_REQUESTS =value
    Specifies the maximum number of simultaneous requests that are allowed to execute in the workload group. value must be 0 or a positive integer. The default setting for value, 0, allows unlimited requests.

  • USING { pool_name | "default" }
    Associates the workload group with the user-defined resource pool identified by pool_name, which in effect puts the workload group in the resource pool. If pool_name is not provided or if the USING argument is not used, the workload group is put in the predefined Resource Governor default pool.

    The option "default" must be enclosed by quotation marks ("") or brackets ([]) when used with ALTER WORKLOAD GROUP to avoid conflict with DEFAULT, which is a system reserved word. For more information, see Delimited Identifiers (Database Engine).

    Note

    The option "default" is case-sensitive.

Remarks

ALTER WORKLOAD GROUP is allowed on the default group.

Changes to the workload group configuration do not take effect until after ALTER RESOURCE GOVERNOR RECONFIGURE is executed.

When you are executing DDL statements, we recommend that you be familiar with Resource Governor states. For more information, see Resource Governor States.

REQUEST_MEMORY_GRANT_PERCENT: In SQL Server 2005, index creation is allowed to use more workspace memory than initially granted for improved performance. This special handling is supported by Resource Governor in SQL Server 2008. However, the initial grant and any additional memory grant are limited by resource pool and workload group settings.

Index Creation on a Partitioned Table

The memory consumed by index creation on a non-aligned partitioned table is proportional to the number of partitions involved. If the total required memory exceeds the per-query limit (REQUEST_MAX_MEMORY_GRANT_PERCENT) imposed by the Resource Governor workload group setting, this index creation may fail to execute. Because the "default" workload group allows a query to exceed the per-query limit with the minimum required memory to start for SQL Server 2005 compatibility, the user may be able to run the same index creation in the "default" workload group, if the "default" resource pool has enough total memory configured to run such query.

Permissions

Requires CONTROL SERVER permission.

Examples

The following example shows how to change the importance of requests in the default group from MEDIUM to LOW.

ALTER WORKLOAD GROUP "default"
WITH (IMPORTANCE = LOW)
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

The following example shows how to move a workload group from the pool that it is in to the default pool.

ALTER WORKLOAD GROUP adHoc
USING [default];
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

Change History

Updated content

Added "Note the following" information to the description of the REQUEST_MAX_MEMORY_GRANT_PERCENT argument.