Skip to content

Latest commit

 

History

History
189 lines (132 loc) · 8.61 KB

alter-workload-group-transact-sql.md

File metadata and controls

189 lines (132 loc) · 8.61 KB
Raw
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
ALTER WORKLOAD GROUP (Transact-SQL)
Changes an existing Resource Governor workload group configuration, and optionally assigns it to a Resource Governor resource pool.
markingmyname
maghan
randolphwest
08/10/2022
sql
t-sql
reference
ALTER_WORKLOAD_GROUP_TSQL
ALTER WORKLOAD GROUP
ALTER WORKLOAD GROUP statement
TSQL
>=sql-server-2016||>=sql-server-linux-2017||=azure-sqldw-latest||=azuresqldb-mi-current

ALTER WORKLOAD GROUP (Transact-SQL)

[!INCLUDE select-product]

::: moniker range=">=sql-server-2016||>=sql-server-linux-2017"

:::row::: :::column::: * SQL Server *   :::column-end::: :::column::: SQL Managed Instance :::column-end::: :::column::: Azure Synapse
Analytics :::column-end::: :::row-end:::

 

SQL Server and SQL Managed Instance

[!INCLUDE alter-workload-group]

::: moniker-end ::: moniker range="=azuresqldb-mi-current"

:::row::: :::column::: SQL Server :::column-end::: :::column::: * SQL Managed Instance *   :::column-end::: :::column::: Azure Synapse
Analytics :::column-end::: :::row-end:::

 

SQL Server and SQL Managed Instance

[!INCLUDE alter-workload-group]

::: moniker-end ::: moniker range="=azure-sqldw-latest"

:::row::: :::column::: SQL Server :::column-end::: :::column::: SQL Managed Instance :::column-end::: :::column::: * Azure Synapse
Analytics *   :::column-end::: :::row-end:::

 

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

::: moniker-end