通过

ALTER WORKLOAD GROUP （Transact-SQL）

  • 项目

选择产品

在下一行中，选择感兴趣的产品名称，仅显示该产品的信息。

* SQL Server *  

SQL 托管实例

Azure Synapse
Analytics

 

SQL Server 和 SQL 托管实例

更改现有的资源调控器工作负荷组配置，并选择性地将其分配给其他资源调控器资源池。

注意

对于 Azure SQL 托管实例，必须在 master 数据库的上下文中修改资源调控器配置。

Transact-SQL 语法约定。

语法

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] } ]
[ ; ]

参数

group_name |[default]

现有用户定义的工作负荷组或资源调控器内置 default 工作负荷组的名称。

当与 ALTER WORKLOAD GROUP 一起使用时，default 必须位于括号（[]）或引号（""），以避免与系统保留字 DEFAULT冲突。 有关详细信息，请参阅 数据库标识符。

内置资源池和工作负荷组使用所有小写名称，例如 default。 在使用区分大小写的排序规则的服务器上使用小写 default。 不区分大小写的排序规则的服务器将 default、Default和 DEFAULT 视为相同的值。

IMPORTANCE = { LOW |MEDIUM |HIGH }

指定工作负荷组中请求的相对重要性。 默认值为 MEDIUM。

IMPORTANCE 是包含工作负荷组的资源池的本地。 同一资源池内具有不同重要性的工作负荷组相互影响，但不会影响其他资源池中的工作负荷组。

REQUEST_MAX_MEMORY_GRANT_PERCENT = 值

指定单个请求可从池获取的最大查询工作区内存量。 值 是 MAX_MEMORY_PERCENT定义的资源池大小的百分比。 默认值为 25。

在 SQL Server 2017（14.x）及更早版本中，值 为整数，允许的范围从 1 到 100。

从 SQL Server 2019 （15.x）开始，该值可以使用 float 数据类型的小数部分。 允许的范围从 0 到 100。

重要

指定的金额仅指通过查询内存授予获得的查询工作区内存。

不建议设置 值 过大（例如大于 70），因为服务器可能无法为其他并发查询留出足够的可用内存。 这可能会导致内存授予超时 错误 8645。

将 值 设置为 0 或小值可能会阻止使用需要工作区内存（如 sort 和 hash）的运算符的查询在用户定义的工作负荷组中运行。 如果查询内存要求超出此参数定义的限制，则会发生以下行为：

  • 对于用户定义的工作负荷组，服务器会尝试减少请求（DOP）的并行度（查询），直到内存要求低于限制，或直到 DOP 等于 1。 如果查询内存要求仍大于限制，则会发生错误 8657，并且查询失败。
  • 对于 internal 和 default 工作负荷组，服务器允许查询获取所需的内存。

在任一情况下，如果服务器的物理内存不足，则可能会出现 错误 8645。

REQUEST_MAX_CPU_TIME_SEC = 值

指定请求可以使用的最大 CPU 时间（以秒为单位）。 值 必须为 0 或正整数。 值 的默认设置为 0，这意味着无限制。

默认情况下，如果超过最大时间，资源调控器不会阻止请求继续。 但是，将生成事件。 有关详细信息，请参阅 CPU 阈值超出事件类。

从 SQL Server 2016 （13.x） SP2 和 SQL Server 2017 （14.x） CU3 开始，并使用 跟踪标志 2422，资源调控器在超过最大 CPU 时间时中止请求。

REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 值

指定查询可以等待查询工作区内存中的内存授予可用的最大时间（以秒为单位）。 值 必须为 0 或正整数。 值0 的默认设置使用基于查询成本的内部计算来确定最长时间。

达到内存授予超时时，查询并不总是失败。 仅当运行过多并发查询时，查询才会失败。 否则，查询可能只获得最小内存授予，从而导致查询性能降低。

MAX_DOP = 值

指定并行查询执行的最大并行度（MAXDOP）。 值的允许范围为 0 到 64。 值0 的默认设置使用全局设置。

有关详细信息，请参阅 MAXDOP。

GROUP_MAX_REQUESTS = 值

指定允许在工作负荷组中执行的最大同时请求数。 值 必须为 0 或正整数。 值 的默认设置为 0，并允许无限制的请求。 达到最大并发请求时，可以创建该组中的会话，但会处于等待状态，直到并发请求数低于指定的值。

USING { pool_name |[default] }

将工作负荷组与由 pool_name标识的用户定义资源池或 default 资源池相关联。 如果未提供 pool_name，或者未指定 USING 参数，则工作负荷组与内置 default 池相关联。

default 是保留字，在 USING中指定时，必须用括号（[]）或引号（""）括起来。

内置资源池和工作负荷组使用所有小写名称，例如 default。 在使用区分大小写的排序规则的服务器上使用小写 default。 不区分大小写的排序规则的服务器将 default、Default和 DEFAULT 视为相同的值。

言论

ALTER WORKLOAD GROUP 允许在 default 工作负荷组中，但不允许在 internal 组中使用。

在执行 ALTER RESOURCE GOVERNOR RECONFIGURE 之后，对工作负荷组配置的更改才会生效。

有关详细信息，请参阅 资源调控器 和 资源调控器工作负荷组。

MAXDOP

对于给定的查询，将按如下所示确定有效的 MAXDOP：

  • 只要查询提示不超过工作负荷组 MAX_DOP 设置，就 MAXDOP 作为查询提示。
  • MAXDOP 作为查询提示始终替代 max degree of parallelism 服务器配置。 有关详细信息，请参阅 服务器配置：最大并行度。
  • 工作负荷组 MAX_DOP 替代 max degree of parallelism 服务器配置和 MAXDOP数据库范围的配置。

每个 任务设置 MAXDOP 限制。 它不是每个 请求 或每个查询限制。 在执行并行查询期间，单个请求可以生成分配给 计划程序的多个任务。 有关详细信息，请参阅 线程和任务体系结构指南。

当查询在编译时（MAXDOP = 1）标记为串行时，无论工作负荷组或服务器配置设置如何，它都不能在运行时以并行方式执行。 为查询确定 MAXDOP 后，由于内存压力，只能降低该查询。 工作负荷组重新配置不会影响在内存授予队列中等待的查询。

缓存的计划

更改影响 MAX_DOP等设置的计划时，新设置仅在执行 DBCC FREEPROCCACHE (<pool_name>)后才会在以前缓存的计划中生效，其中 <pool_name> 是当前工作负荷组使用的资源调控器资源池的名称。

  • 如果将 MAX_DOP 更改为 1，则不需要执行 DBCC FREEPROCCACHE，因为并行计划可以在串行模式下运行。 但是，此类计划的效率可能低于编译为串行计划的计划。
  • 如果 MAX_DOP 从 1 更改为 0 或值大于 1，则不需要执行 DBCC FREEPROCCACHE。 但是，串行计划无法并行运行，因此清除相应的缓存允许使用并行度编译新计划。

警告

从与多个工作负荷组关联的资源池中清除缓存的计划会影响使用由 <pool_name>标识的用户定义资源池的所有工作负荷组。

索引创建

出于性能原因，允许索引创建使用比最初授予的更多内存工作区。 资源调控器支持这种特殊处理。 但是，初始授予和任何其他内存授予受工作负荷组和资源池设置的限制。

在分区表上创建非对齐索引所消耗的内存与所涉及的分区数成正比。 如果所需内存总量超过 REQUEST_MAX_MEMORY_GRANT_PERCENT 工作负荷组设置强制实施的每个查询限制，则创建索引可能会失败。 由于 default 工作负荷组允许查询超出每个查询的限制，且所需的最小内存才能开始向后兼容，因此，如果 default 资源池有足够的总内存，则可以使用 default 工作负荷组创建相同的索引。

权限

需要 CONTROL SERVER 权限。

例子

以下示例演示如何将默认组中请求的重要性从 MEDIUM 更改为 LOW。

ALTER WORKLOAD GROUP [default]
WITH (IMPORTANCE = LOW);

ALTER RESOURCE GOVERNOR RECONFIGURE;

以下示例演示如何将工作负荷组从当前已进入 default 池的池中移动。

ALTER WORKLOAD GROUP adHoc
USING [default];

ALTER RESOURCE GOVERNOR RECONFIGURE;

相关内容

SQL Server

* SQL 托管实例 *  

Azure Synapse
Analytics

 

SQL Server 和 SQL 托管实例

更改现有的资源调控器工作负荷组配置，并选择性地将其分配给其他资源调控器资源池。

注意

对于 Azure SQL 托管实例，必须在 master 数据库的上下文中修改资源调控器配置。

Transact-SQL 语法约定。

语法

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] } ]
[ ; ]

参数

group_name |[default]

现有用户定义的工作负荷组或资源调控器内置 default 工作负荷组的名称。

当与 ALTER WORKLOAD GROUP 一起使用时，default 必须位于括号（[]）或引号（""），以避免与系统保留字 DEFAULT冲突。 有关详细信息，请参阅 数据库标识符。

内置资源池和工作负荷组使用所有小写名称，例如 default。 在使用区分大小写的排序规则的服务器上使用小写 default。 不区分大小写的排序规则的服务器将 default、Default和 DEFAULT 视为相同的值。

IMPORTANCE = { LOW |MEDIUM |HIGH }

指定工作负荷组中请求的相对重要性。 默认值为 MEDIUM。

IMPORTANCE 是包含工作负荷组的资源池的本地。 同一资源池内具有不同重要性的工作负荷组相互影响，但不会影响其他资源池中的工作负荷组。

REQUEST_MAX_MEMORY_GRANT_PERCENT = 值

指定单个请求可从池获取的最大查询工作区内存量。 值 是 MAX_MEMORY_PERCENT定义的资源池大小的百分比。 默认值为 25。

在 SQL Server 2017（14.x）及更早版本中，值 为整数，允许的范围从 1 到 100。

从 SQL Server 2019 （15.x）开始，该值可以使用 float 数据类型的小数部分。 允许的范围从 0 到 100。

重要

指定的金额仅指通过查询内存授予获得的查询工作区内存。

不建议设置 值 过大（例如大于 70），因为服务器可能无法为其他并发查询留出足够的可用内存。 这可能会导致内存授予超时 错误 8645。

将 值 设置为 0 或小值可能会阻止使用需要工作区内存（如 sort 和 hash）的运算符的查询在用户定义的工作负荷组中运行。 如果查询内存要求超出此参数定义的限制，则会发生以下行为：

  • 对于用户定义的工作负荷组，服务器会尝试减少请求（DOP）的并行度（查询），直到内存要求低于限制，或直到 DOP 等于 1。 如果查询内存要求仍大于限制，则会发生错误 8657，并且查询失败。
  • 对于 internal 和 default 工作负荷组，服务器允许查询获取所需的内存。

在任一情况下，如果服务器的物理内存不足，则可能会出现 错误 8645。

REQUEST_MAX_CPU_TIME_SEC = 值

指定请求可以使用的最大 CPU 时间（以秒为单位）。 值 必须为 0 或正整数。 值 的默认设置为 0，这意味着无限制。

默认情况下，如果超过最大时间，资源调控器不会阻止请求继续。 但是，将生成事件。 有关详细信息，请参阅 CPU 阈值超出事件类。

从 SQL Server 2016 （13.x） SP2 和 SQL Server 2017 （14.x） CU3 开始，并使用 跟踪标志 2422，资源调控器在超过最大 CPU 时间时中止请求。

REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 值

指定查询可以等待查询工作区内存中的内存授予可用的最大时间（以秒为单位）。 值 必须为 0 或正整数。 值0 的默认设置使用基于查询成本的内部计算来确定最长时间。

达到内存授予超时时，查询并不总是失败。 仅当运行过多并发查询时，查询才会失败。 否则，查询可能只获得最小内存授予，从而导致查询性能降低。

MAX_DOP = 值

指定并行查询执行的最大并行度（MAXDOP）。 值的允许范围为 0 到 64。 值0 的默认设置使用全局设置。

有关详细信息，请参阅 MAXDOP。

GROUP_MAX_REQUESTS = 值

指定允许在工作负荷组中执行的最大同时请求数。 值 必须为 0 或正整数。 值 的默认设置为 0，并允许无限制的请求。 达到最大并发请求时，可以创建该组中的会话，但会处于等待状态，直到并发请求数低于指定的值。

USING { pool_name |[default] }

将工作负荷组与由 pool_name标识的用户定义资源池或 default 资源池相关联。 如果未提供 pool_name，或者未指定 USING 参数，则工作负荷组与内置 default 池相关联。

default 是保留字，在 USING中指定时，必须用括号（[]）或引号（""）括起来。

内置资源池和工作负荷组使用所有小写名称，例如 default。 在使用区分大小写的排序规则的服务器上使用小写 default。 不区分大小写的排序规则的服务器将 default、Default和 DEFAULT 视为相同的值。

言论

ALTER WORKLOAD GROUP 允许在 default 工作负荷组中，但不允许在 internal 组中使用。

在执行 ALTER RESOURCE GOVERNOR RECONFIGURE 之后，对工作负荷组配置的更改才会生效。

有关详细信息，请参阅 资源调控器 和 资源调控器工作负荷组。

MAXDOP

对于给定的查询，将按如下所示确定有效的 MAXDOP：

  • 只要查询提示不超过工作负荷组 MAX_DOP 设置，就 MAXDOP 作为查询提示。
  • MAXDOP 作为查询提示始终替代 max degree of parallelism 服务器配置。 有关详细信息，请参阅 服务器配置：最大并行度。
  • 工作负荷组 MAX_DOP 替代 max degree of parallelism 服务器配置和 MAXDOP数据库范围的配置。

每个 任务设置 MAXDOP 限制。 它不是每个 请求 或每个查询限制。 在执行并行查询期间，单个请求可以生成分配给 计划程序的多个任务。 有关详细信息，请参阅 线程和任务体系结构指南。

当查询在编译时（MAXDOP = 1）标记为串行时，无论工作负荷组或服务器配置设置如何，它都不能在运行时以并行方式执行。 为查询确定 MAXDOP 后，由于内存压力，只能降低该查询。 工作负荷组重新配置不会影响在内存授予队列中等待的查询。

缓存的计划

更改影响 MAX_DOP等设置的计划时，新设置仅在执行 DBCC FREEPROCCACHE (<pool_name>)后才会在以前缓存的计划中生效，其中 <pool_name> 是当前工作负荷组使用的资源调控器资源池的名称。

  • 如果将 MAX_DOP 更改为 1，则不需要执行 DBCC FREEPROCCACHE，因为并行计划可以在串行模式下运行。 但是，此类计划的效率可能低于编译为串行计划的计划。
  • 如果 MAX_DOP 从 1 更改为 0 或值大于 1，则不需要执行 DBCC FREEPROCCACHE。 但是，串行计划无法并行运行，因此清除相应的缓存允许使用并行度编译新计划。

警告

从与多个工作负荷组关联的资源池中清除缓存的计划会影响使用由 <pool_name>标识的用户定义资源池的所有工作负荷组。

索引创建

出于性能原因，允许索引创建使用比最初授予的更多内存工作区。 资源调控器支持这种特殊处理。 但是，初始授予和任何其他内存授予受工作负荷组和资源池设置的限制。

在分区表上创建非对齐索引所消耗的内存与所涉及的分区数成正比。 如果所需内存总量超过 REQUEST_MAX_MEMORY_GRANT_PERCENT 工作负荷组设置强制实施的每个查询限制，则创建索引可能会失败。 由于 default 工作负荷组允许查询超出每个查询的限制，且所需的最小内存才能开始向后兼容，因此，如果 default 资源池有足够的总内存，则可以使用 default 工作负荷组创建相同的索引。

权限

需要 CONTROL SERVER 权限。

例子

以下示例演示如何将默认组中请求的重要性从 MEDIUM 更改为 LOW。

ALTER WORKLOAD GROUP [default]
WITH (IMPORTANCE = LOW);

ALTER RESOURCE GOVERNOR RECONFIGURE;

以下示例演示如何将工作负荷组从当前已进入 default 池的池中移动。

ALTER WORKLOAD GROUP adHoc
USING [default];

ALTER RESOURCE GOVERNOR RECONFIGURE;

相关内容

SQL Server

SQL 托管实例

* Azure Synapse
Analytics *  

 

Azure Synapse Analytics

更改现有工作负荷组。

请参阅下面的 ALTER WORKLOAD GROUP 行为部分，详细了解 ALTER WORKLOAD GROUP 在运行和排队请求的系统上的行为方式。

CREATE WORKLOAD GROUP 的限制也适用于 ALTER WORKLOAD GROUP。 在修改参数之前，查询 sys.workload_management_workload_groups 以确保这些值在可接受的范围内。

语法

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 ] )
  [ ; ]

参数

group_name

正在更改的现有用户定义的工作负荷组的名称。 group_name 不可更改。

MIN_PERCENTAGE_RESOURCE = 值

值 是介于 0 到 100 的整数范围。 更改MIN_PERCENTAGE_RESOURCE时，所有工作负荷组MIN_PERCENTAGE_RESOURCE的总和不能超过 100。 更改MIN_PERCENTAGE_RESOURCE要求在命令完成之前在工作负荷组中完成所有正在运行的查询。 有关详细信息，请参阅本文中的 ALTER WORKLOAD GROUP 行为 部分。

CAP_PERCENTAGE_RESOURCE = 值

值 是一个介于 1 到 100 的整数范围。 CAP_PERCENTAGE_RESOURCE的值必须大于MIN_PERCENTAGE_RESOURCE。 更改CAP_PERCENTAGE_RESOURCE要求在命令完成之前在工作负荷组中完成所有正在运行的查询。 有关详细信息，请参阅本文中的 ALTER WORKLOAD GROUP 行为 部分。

REQUEST_MIN_RESOURCE_GRANT_PERCENT = 值

值 是介于 0.75 到 100.00 之间的小数。 REQUEST_MIN_RESOURCE_GRANT_PERCENT的值必须是MIN_PERCENTAGE_RESOURCE的一个因素，并且小于CAP_PERCENTAGE_RESOURCE。

REQUEST_MAX_RESOURCE_GRANT_PERCENT = 值

值 是十进制值，必须大于REQUEST_MIN_RESOURCE_GRANT_PERCENT。

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

更改工作负荷组请求的默认重要性。

QUERY_EXECUTION_TIMEOUT_SEC = 值

更改查询在取消之前可以执行的最大时间（以秒为单位）。 值必须为 0 或正整数。 值的默认设置为 0，这意味着无限制。

权限

需要 CONTROL DATABASE 权限。

例

以下示例检查目录视图中名为 wgDataLoads的工作负荷组的值，并更改这些值。

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 行为

在任何时间点，系统中都有三种类型的请求：

  • 尚未分类的请求。
  • 对对象锁或系统资源进行分类和等待的请求。
  • 已分类并正在运行的请求。

根据要更改的工作负荷组的属性，设置生效的时间将有所不同。

重要性或query_execution_timeout

对于重要性和query_execution_timeout属性，非分类请求会选取新的配置值。 使用旧配置等待和运行请求执行。 无论工作负荷组中是否有正在运行的查询，ALTER WORKLOAD GROUP 请求都会立即执行。

REQUEST_MIN_RESOURCE_GRANT_PERCENT或REQUEST_MAX_RESOURCE_GRANT_PERCENT

对于REQUEST_MIN_RESOURCE_GRANT_PERCENT和REQUEST_MAX_RESOURCE_GRANT_PERCENT，使用旧配置执行运行请求。 等待请求和非分类请求会选取新的配置值。 无论工作负荷组中是否有正在运行的查询，ALTER WORKLOAD GROUP 请求都会立即执行。

MIN_PERCENTAGE_RESOURCE或CAP_PERCENTAGE_RESOURCE

对于MIN_PERCENTAGE_RESOURCE和CAP_PERCENTAGE_RESOURCE，使用旧配置执行运行请求。 等待请求和非分类请求会选取新的配置值。

更改MIN_PERCENTAGE_RESOURCE和CAP_PERCENTAGE_RESOURCE需要清空正在更改的工作负荷组中正在运行的请求。 当减少MIN_PERCENTAGE_RESOURCE时，释放的资源将返回到共享池，从而允许来自其他工作负荷组的请求能够利用。 相反，增加MIN_PERCENTAGE_RESOURCE将等到仅利用共享池中所需资源的请求才能完成。 ALTER WORKLOAD GROUP 操作将对共享资源的访问权限优先于等待在共享池上执行的其他请求。 如果MIN_PERCENTAGE_RESOURCE的总和超过 100%，则 ALTER WORKLOAD GROUP 请求会立即失败。

锁定行为

更改工作负荷组需要跨所有工作负荷组进行全局锁定。 更改工作负荷组的请求会在已提交创建或删除工作负荷组请求的后面排队。 如果同时提交一批 alter 语句，则会按照提交的顺序处理这些语句。

另请参阅