Edit

Share via

ALTER WORKLOAD GROUP (Transact-SQL)

  • Article

Select a product

In the following row, select the product name you're interested in, and only that product's information is displayed.

* SQL Server *  

SQL Managed Instance

Azure Synapse
Analytics

 

SQL Server and SQL Managed Instance

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

Note

For Azure SQL Managed Instance, you must be in the context of the master database to modify resource governor configuration.

Transact-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]

The name of an existing user-defined workload group or the resource governor built-in default workload group.

default must be in brackets ([]) or quotation marks ("") when used with ALTER WORKLOAD GROUP to avoid a conflict with DEFAULT, which is a system reserved word. For more information, see Database identifiers.

Built-in resource pools and workload groups use all lowercase names, such as default. Use the lower case default on servers that use a case-sensitive collation. Servers with case-insensitive collation treat default, Default, and DEFAULT as the same value.

IMPORTANCE = { LOW | MEDIUM | HIGH }

Specifies the relative importance of a request in the workload group. The default value is MEDIUM.

IMPORTANCE is local to the resource pool that contains the workload group. Workload groups of different importance inside the same resource pool affect each other, but don't affect workload groups in other resource pools.

REQUEST_MAX_MEMORY_GRANT_PERCENT = value

Specifies the maximum amount of query workspace memory that a single request can take from the pool. value is a percentage of the resource pool size defined by MAX_MEMORY_PERCENT. Default value is 25.

In SQL Server 2017 (14.x) and older, value is an integer and the allowed range is from 1 through 100.

Starting with SQL Server 2019 (15.x), the value can be fractional using the float data type. The allowed range is from 0 through 100.

Important

The amount specified only refers to query workspace memory obtained via query memory grants.

It is not recommended to set value too large (for example, greater than 70) because the server may be unable to set aside enough free memory for other concurrent queries. This can lead to a memory grant timeout error 8645.

Setting value to 0 or a small value might prevent queries with operators that require workspace memory, such as sort and hash, from running in user-defined workload groups. If the query memory requirements exceed the limit defined by this parameter, the following behavior occurs:

  • For user-defined workload groups, the server tries to reduce the degree of parallelism (DOP) of the request (query) until the memory requirement falls under the limit, or until DOP equals 1. If the query memory requirement is still greater than the limit, error 8657 occurs and the query fails.
  • For the internal and default workload groups, the server permits the query to obtain the required memory.

In either case, error 8645 might occur if the server has insufficient physical memory.

REQUEST_MAX_CPU_TIME_SEC = value

Specifies the maximum amount of CPU time, in seconds, that a request can use. value must be 0 or a positive integer. The default setting for value is 0, which means unlimited.

By default, resource governor doesn't prevent a request from continuing if the maximum time is exceeded. However, an event is generated. For more information, see CPU Threshold Exceeded Event Class.

Starting with SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x) CU3, and using trace flag 2422, resource governor aborts a request when the maximum CPU time is exceeded.

REQUEST_MEMORY_GRANT_TIMEOUT_SEC = value

Specifies the maximum time, in seconds, that a query can wait for a memory grant from the query workspace memory to become available. value must be 0 or a positive integer. The default setting for value, 0, uses an internal calculation based on query cost to determine the maximum time.

A query doesn't always fail when a memory grant timeout is reached. A query only fails if there are too many concurrent queries running. Otherwise, the query might only get the minimum memory grant, resulting in reduced query performance.

MAX_DOP = value

Specifies the maximum degree of parallelism (MAXDOP) for parallel query execution. The allowed range for value is from 0 through 64. The default setting for value, 0, uses the global setting.

For more information, see MAXDOP.

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 is 0, and allows unlimited requests. When the maximum concurrent requests are reached, a session in that group can be created, but is placed in a wait state until the number of concurrent requests drops below the value specified.

USING { pool_name | [default] }

Associates the workload group with the user-defined resource pool identified by pool_name, or with the default resource pool. If pool_name isn't provided, or if the USING argument isn't specified, the workload group is associated with the built-in default pool.

default is a reserved word and when specified in USING, must be enclosed in brackets ([]) or quotation marks ("").

Built-in resource pools and workload groups use all lowercase names, such as default. Use the lower case default on servers that use a case-sensitive collation. Servers with case-insensitive collation treat default, Default, and DEFAULT as the same value.

Remarks

ALTER WORKLOAD GROUP is allowed on the default workload group, but not on the internal group.

Changes to the workload group configuration don't take effect until after ALTER RESOURCE GOVERNOR RECONFIGURE is executed.

For more information, see Resource governor and Resource governor workload group.

MAXDOP

For a given query, effective MAXDOP is determined as follows:

  • MAXDOP as a query hint is honored as long as it doesn't exceed the workload group MAX_DOP setting.
  • MAXDOP as a query hint always overrides the max degree of parallelism server configuration. For more information, see Server configuration: max degree of parallelism.
  • Workload group MAX_DOP overrides the max degree of parallelism server configuration and the MAXDOP database scoped configuration.

The MAXDOP limit is set per task. It isn't a per request or per query limit. During an execution of a parallel query, a single request can spawn multiple tasks that are assigned to a scheduler. For more information, see the Thread and task architecture guide.

When a query is marked as serial at compile time (MAXDOP = 1), it can't execute with parallelism at run time regardless of the workload group or server configuration setting. After MAXDOP is determined for a query, it can only be lowered due to memory pressure. Workload group reconfiguration does not affect queries waiting in the memory grant queue.

Cached plans

When you change a plan affecting setting such as MAX_DOP, the new setting takes effect in previously cached plans only after executing DBCC FREEPROCCACHE (<pool_name>), where <pool_name> is the name of a resource governor resource pool used by the current workload group.

  • If changing MAX_DOP to 1, executing DBCC FREEPROCCACHE isn't required because parallel plans can run in serial mode. However, such a plan might be less efficient than a plan compiled as a serial plan.
  • If changing MAX_DOP from 1 to 0 or a value greater than 1,executing DBCC FREEPROCCACHE isn't required. However, serial plans can't run in parallel, so clearing the respective cache allows new plans to potentially be compiled using parallelism.

Warning

Clearing cached plans from a resource pool that is associated with more than one workload group affects all workload groups using the user-defined resource pool identified by <pool_name>.

Index creation

For performance reasons, index creation is allowed to use more memory workspace than initially granted. Resource governor supports this special handling. However, the initial grant and any additional memory grants are limited by the workload group and resource pool settings.

The memory consumed to create a nonaligned index on a partitioned table is proportional to the number of partitions involved. If the total required memory exceeds the per-query limit enforced by the REQUEST_MAX_MEMORY_GRANT_PERCENT workload group setting, index creation might fail. Because the default workload group allows a query to exceed the per-query limit with the minimum required memory to start for backward compatibility, you might be able to create the same index using the default workload group if the default resource pool has enough total memory.

Permissions

Requires the 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);

ALTER RESOURCE GOVERNOR RECONFIGURE;

The following example shows how to move a workload group from the pool that it's currently into the default pool.

ALTER WORKLOAD GROUP adHoc
USING [default];

ALTER RESOURCE GOVERNOR RECONFIGURE;

Related content

SQL Server

* SQL Managed Instance *  

Azure Synapse
Analytics

 

SQL Server and SQL Managed Instance

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

Note

For Azure SQL Managed Instance, you must be in the context of the master database to modify resource governor configuration.

Transact-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]

The name of an existing user-defined workload group or the resource governor built-in default workload group.

default must be in brackets ([]) or quotation marks ("") when used with ALTER WORKLOAD GROUP to avoid a conflict with DEFAULT, which is a system reserved word. For more information, see Database identifiers.

Built-in resource pools and workload groups use all lowercase names, such as default. Use the lower case default on servers that use a case-sensitive collation. Servers with case-insensitive collation treat default, Default, and DEFAULT as the same value.

IMPORTANCE = { LOW | MEDIUM | HIGH }

Specifies the relative importance of a request in the workload group. The default value is MEDIUM.

IMPORTANCE is local to the resource pool that contains the workload group. Workload groups of different importance inside the same resource pool affect each other, but don't affect workload groups in other resource pools.

REQUEST_MAX_MEMORY_GRANT_PERCENT = value

Specifies the maximum amount of query workspace memory that a single request can take from the pool. value is a percentage of the resource pool size defined by MAX_MEMORY_PERCENT. Default value is 25.

In SQL Server 2017 (14.x) and older, value is an integer and the allowed range is from 1 through 100.

Starting with SQL Server 2019 (15.x), the value can be fractional using the float data type. The allowed range is from 0 through 100.

Important

The amount specified only refers to query workspace memory obtained via query memory grants.

It is not recommended to set value too large (for example, greater than 70) because the server may be unable to set aside enough free memory for other concurrent queries. This can lead to a memory grant timeout error 8645.

Setting value to 0 or a small value might prevent queries with operators that require workspace memory, such as sort and hash, from running in user-defined workload groups. If the query memory requirements exceed the limit defined by this parameter, the following behavior occurs:

  • For user-defined workload groups, the server tries to reduce the degree of parallelism (DOP) of the request (query) until the memory requirement falls under the limit, or until DOP equals 1. If the query memory requirement is still greater than the limit, error 8657 occurs and the query fails.
  • For the internal and default workload groups, the server permits the query to obtain the required memory.

In either case, error 8645 might occur if the server has insufficient physical memory.

REQUEST_MAX_CPU_TIME_SEC = value

Specifies the maximum amount of CPU time, in seconds, that a request can use. value must be 0 or a positive integer. The default setting for value is 0, which means unlimited.

By default, resource governor doesn't prevent a request from continuing if the maximum time is exceeded. However, an event is generated. For more information, see CPU Threshold Exceeded Event Class.

Starting with SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x) CU3, and using trace flag 2422, resource governor aborts a request when the maximum CPU time is exceeded.

REQUEST_MEMORY_GRANT_TIMEOUT_SEC = value

Specifies the maximum time, in seconds, that a query can wait for a memory grant from the query workspace memory to become available. value must be 0 or a positive integer. The default setting for value, 0, uses an internal calculation based on query cost to determine the maximum time.

A query doesn't always fail when a memory grant timeout is reached. A query only fails if there are too many concurrent queries running. Otherwise, the query might only get the minimum memory grant, resulting in reduced query performance.

MAX_DOP = value

Specifies the maximum degree of parallelism (MAXDOP) for parallel query execution. The allowed range for value is from 0 through 64. The default setting for value, 0, uses the global setting.

For more information, see MAXDOP.

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 is 0, and allows unlimited requests. When the maximum concurrent requests are reached, a session in that group can be created, but is placed in a wait state until the number of concurrent requests drops below the value specified.

USING { pool_name | [default] }

Associates the workload group with the user-defined resource pool identified by pool_name, or with the default resource pool. If pool_name isn't provided, or if the USING argument isn't specified, the workload group is associated with the built-in default pool.

default is a reserved word and when specified in USING, must be enclosed in brackets ([]) or quotation marks ("").

Built-in resource pools and workload groups use all lowercase names, such as default. Use the lower case default on servers that use a case-sensitive collation. Servers with case-insensitive collation treat default, Default, and DEFAULT as the same value.

Remarks

ALTER WORKLOAD GROUP is allowed on the default workload group, but not on the internal group.

Changes to the workload group configuration don't take effect until after ALTER RESOURCE GOVERNOR RECONFIGURE is executed.

For more information, see Resource governor and Resource governor workload group.

MAXDOP

For a given query, effective MAXDOP is determined as follows:

  • MAXDOP as a query hint is honored as long as it doesn't exceed the workload group MAX_DOP setting.
  • MAXDOP as a query hint always overrides the max degree of parallelism server configuration. For more information, see Server configuration: max degree of parallelism.
  • Workload group MAX_DOP overrides the max degree of parallelism server configuration and the MAXDOP database scoped configuration.

The MAXDOP limit is set per task. It isn't a per request or per query limit. During an execution of a parallel query, a single request can spawn multiple tasks that are assigned to a scheduler. For more information, see the Thread and task architecture guide.

When a query is marked as serial at compile time (MAXDOP = 1), it can't execute with parallelism at run time regardless of the workload group or server configuration setting. After MAXDOP is determined for a query, it can only be lowered due to memory pressure. Workload group reconfiguration does not affect queries waiting in the memory grant queue.

Cached plans

When you change a plan affecting setting such as MAX_DOP, the new setting takes effect in previously cached plans only after executing DBCC FREEPROCCACHE (<pool_name>), where <pool_name> is the name of a resource governor resource pool used by the current workload group.

  • If changing MAX_DOP to 1, executing DBCC FREEPROCCACHE isn't required because parallel plans can run in serial mode. However, such a plan might be less efficient than a plan compiled as a serial plan.
  • If changing MAX_DOP from 1 to 0 or a value greater than 1,executing DBCC FREEPROCCACHE isn't required. However, serial plans can't run in parallel, so clearing the respective cache allows new plans to potentially be compiled using parallelism.

Warning

Clearing cached plans from a resource pool that is associated with more than one workload group affects all workload groups using the user-defined resource pool identified by <pool_name>.

Index creation

For performance reasons, index creation is allowed to use more memory workspace than initially granted. Resource governor supports this special handling. However, the initial grant and any additional memory grants are limited by the workload group and resource pool settings.

The memory consumed to create a nonaligned index on a partitioned table is proportional to the number of partitions involved. If the total required memory exceeds the per-query limit enforced by the REQUEST_MAX_MEMORY_GRANT_PERCENT workload group setting, index creation might fail. Because the default workload group allows a query to exceed the per-query limit with the minimum required memory to start for backward compatibility, you might be able to create the same index using the default workload group if the default resource pool has enough total memory.

Permissions

Requires the 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);

ALTER RESOURCE GOVERNOR RECONFIGURE;

The following example shows how to move a workload group from the pool that it's currently into the default pool.

ALTER WORKLOAD GROUP adHoc
USING [default];

ALTER RESOURCE GOVERNOR RECONFIGURE;

Related content

SQL Server

SQL Managed Instance

* Azure Synapse
Analytics *  

 

Azure Synapse Analytics

Alters an existing workload group.

See the ALTER WORKLOAD GROUP behavior section below for further details on how ALTER WORKLOAD GROUP behaves on a system with running and queued requests.

Restrictions in place for CREATE WORKLOAD GROUP also apply to ALTER WORKLOAD GROUP. Prior to modifying parameters, query sys.workload_management_workload_groups to ensure the values are within acceptable ranges.

Syntax

ALTER WORKLOAD GROUP group_name
WITH
([ MIN_PERCENTAGE_RESOURCE = value ]
  [ [ , ] CAP_PERCENTAGE_RESOURCE = value ]
  [ [ , ] REQUEST_MIN_RESOURCE_GRANT_PERCENT = value ]
  [ [ , ] REQUEST_MAX_RESOURCE_GRANT_PERCENT = value ]
  [ [ , ] IMPORTANCE = { LOW | BELOW_NORMAL | NORMAL | ABOVE_NORMAL | HIGH }]
  [ [ , ] QUERY_EXECUTION_TIMEOUT_SEC = value ] )
  [ ; ]

Arguments

group_name

Is the name of the existing user-defined workload group being altered. group_name isn't alterable.

MIN_PERCENTAGE_RESOURCE = value

value is an integer range from 0 to 100. When altering MIN_PERCENTAGE_RESOURCE, the sum of MIN_PERCENTAGE_RESOURCE across all workload groups can't exceed 100. Altering MIN_PERCENTAGE_RESOURCE requires all running queries to complete in the workload group before the command will complete. For more information, see the ALTER WORKLOAD GROUP behavior section in this article.

CAP_PERCENTAGE_RESOURCE = value

value is an integer range from 1 through 100. The value for CAP_PERCENTAGE_RESOURCE must be greater than MIN_PERCENTAGE_RESOURCE. Altering CAP_PERCENTAGE_RESOURCE requires all running queries to complete in the workload group before the command will complete. For more information, see the ALTER WORKLOAD GROUP behavior section in this article.

REQUEST_MIN_RESOURCE_GRANT_PERCENT = value

value is a decimal with a range between 0.75 to 100.00. The value for REQUEST_MIN_RESOURCE_GRANT_PERCENT needs to be a factor of MIN_PERCENTAGE_RESOURCE and be less than CAP_PERCENTAGE_RESOURCE.

REQUEST_MAX_RESOURCE_GRANT_PERCENT = value

value is a decimal and must be greater than REQUEST_MIN_RESOURCE_GRANT_PERCENT.

IMPORTANCE = { LOW | BELOW_NORMAL | NORMAL | ABOVE_NORMAL | HIGH }

Alters the default importance of a request for the workload group.

QUERY_EXECUTION_TIMEOUT_SEC = value

Alters the maximum time, in seconds, that a query can execute before it's canceled. Value must be 0 or a positive integer. The default setting for value is 0, which means unlimited.

Permissions

Requires CONTROL DATABASE permission.

Example

The below example checks the values in the catalog view for a workload group named wgDataLoads, and changes the values.

SELECT *
FROM sys.workload_management_workload_groups
WHERE [name] = 'wgDataLoads'

ALTER WORKLOAD GROUP wgDataLoads WITH
( MIN_PERCENTAGE_RESOURCE            = 40
, CAP_PERCENTAGE_RESOURCE            = 80
, REQUEST_MIN_RESOURCE_GRANT_PERCENT = 10 )

ALTER WORKLOAD GROUP behavior

At any point in time there are three types of requests in the system:

  • Requests that haven't been classified yet.
  • Requests that are classified, and waiting, for object locks or system resources.
  • Requests that are classified, and running.

Based on the properties of a workload group being altered, the timing of when the settings take effect will differ.

Importance or query_execution_timeout

For the importance and query_execution_timeout properties, non-classified requests pick up the new config values. Waiting and running requests execute with the old configuration. The ALTER WORKLOAD GROUP request executes immediately regardless if there are running queries in the workload group.

REQUEST_MIN_RESOURCE_GRANT_PERCENT or REQUEST_MAX_RESOURCE_GRANT_PERCENT

For REQUEST_MIN_RESOURCE_GRANT_PERCENT and REQUEST_MAX_RESOURCE_GRANT_PERCENT, running requests execute with the old configuration. Waiting requests and non-classified requests pick up the new config values. The ALTER WORKLOAD GROUP request executes immediately regardless if there are running queries in the workload group.

MIN_PERCENTAGE_RESOURCE or CAP_PERCENTAGE_RESOURCE

For MIN_PERCENTAGE_RESOURCE and CAP_PERCENTAGE_RESOURCE, running requests execute with the old configuration. Waiting requests and non-classified requests pick up the new config values.

Changing MIN_PERCENTAGE_RESOURCE and CAP_PERCENTAGE_RESOURCE requires draining of running requests in the workload group that is being altered. When decreasing MIN_PERCENTAGE_RESOURCE, the freed resources are returned to the share pool allowing requests from other workload groups the ability to utilize. Conversely, increasing the MIN_PERCENTAGE_RESOURCE will wait until requests utilizing only the needed resources from the shared pool to complete. The ALTER WORKLOAD GROUP operation will have prioritized access to shared resources over other requests waiting to be executed on shared pool. If the sum of MIN_PERCENTAGE_RESOURCE exceeds 100%, the ALTER WORKLOAD GROUP request fails immediately.

Locking behavior

Altering a workload group requires a global lock across all workload groups. A request to alter a workload group would queue behind already submitted create or drop workload group requests. If a batch of alter statements is submitted at once, they're processed in the order in which they're submitted.

See also