ALTER WORKLOAD GROUP (Transact-SQL)

  • 项目

选择一个产品

在下面的行中，选择你感兴趣的产品名称，系统将只显示该产品的信息。

* SQL Server *  

SQL 托管实例

Azure Synapse
分析

 

SQL Server 和 SQL 托管实例

更改现有 Resource Governor 工作负载组配置，并且可以选择将其分配给 Resource Governor 资源池。

注意

对于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"

现有用户定义工作负荷组或 Resource Governor 默认工作负荷组的名称。 在安装 SQL Server 时资源调控器创建“默认”和内部组。

与 ALTER WORKLOAD GROUP 一起使用时，选项 "default" 必须用引号 ("") 引起来或用方括号 ([]) 括起来，以免与系统保留字 DEFAULT 冲突。 有关详细信息，请参阅 Database Identifiers。

预定义工作负荷组和资源池都使用小写名称，例如“default”。 对于使用区分大小写排序规则的服务器，应当注意这一点。 使用不区分大小写排序规则的服务器（例如 SQL_Latin1_General_CP1_CI_AS）会将“"default"和“"Default"”视为相同。

IMPORTANCE = { LOW | MEDIUM | HIGH }

指定工作负荷组中某个请求的相对重要性。 重要性为以下值之一：

  • LOW
  • MEDIUM（默认值）
  • HIGH

在内部，每个重要性设置都存储为用于计算的一个数字。

IMPORTANCE 对资源池而言是局部性的；同一资源池内重要性不同的工作负荷组会相互影响，但不会影响其他资源池中的工作负荷组。

REQUEST_MAX_MEMORY_GRANT_PERCENT = value

指定单个请求可以从池中获取的最大内存量。 value 是相对于 MAX_MEMORY_PERCENT 指定的资源池大小的百分比 。 默认值为 25。 指定的量指的只是查询执行授予内存。

在 SQL Server 2017 (14.x) 之前，value 是 int，允许的范围是 1 到 100。 从 SQL Server 2019 (15.x) 开始，值是 float 数据类型，允许的范围是 0 到 100。

重要

将 value 设置为 0 可阻止在用户定义的工作负荷组中运行具有 SORT 和 HASH JOIN 操作的查询 。

建议不要将 value 设置为大于 70，这是因为如果正在运行其他并发查询，则服务器可能无法保留足够的空闲内存 。 可能最终会导致查询超时错误 8645。

如果查询内存要求超过了此参数指定的限制，服务器会执行以下操作：

  • 对于用户定义的工作负荷组，服务器会尝试降低查询的并行度，直到内存要求降到限制范围以内，或直到并行度等于 1。 如果查询内存要求仍然大于限制值，则会发生错误 8657。
  • 对于内部和默认工作负荷组，服务器会允许查询获取必需的内存。

如果服务器没有足够的物理内存，则这两种情况都会出现超时错误 8645。

REQUEST_MAX_CPU_TIME_SEC = value

指定请求可以使用的最长 CPU 时间，以秒为单位。 value 必须为 0 或一个正整数 。 value 的默认设置为 0，也就是说无限制 。 默认情况下，如果超过最长时间，Resource Governor 并不会阻止继续发出请求。 但会生成一个事件。 有关详细信息，请参阅 CPU Threshold Exceeded 事件类。

从 SQL Server 2016 (13.x) SP2 和 SQL Server 2017 (14.x) CU3 开始以及使用跟踪标志 2422 时，Resource Governor 将在超出最大时间时中止请求。

REQUEST_MEMORY_GRANT_TIMEOUT_SEC = value

指定查询等待内存授予（工作缓冲区内存）变为可用的最长时间（以秒为单位）。

查询并不总是在达到内存授予超时的时候失败。 仅当有太多并发查询运行时，查询才失败。 否则，查询只能获取最小内存授予，从而导致查询性能下降。

value 必须是正整数 。 value 的默认设置为 0，表示使用基于查询开销的内部计算来确定最长时间 。

MAX_DOP = value

指定并行请求的最大并行度 (DOP)。 value 必须为 0 或正整数（1 到 255） 。 value 为 0 时，服务器选择最大并行度 。 这是默认设置，也是推荐设置。

数据库引擎为 MAX_DOP 设置的实际值可能小于指定值。 最终值由公式 min(255, CPU 的数目) 确定 。

注意

更改 MAX_DOP 可能会对服务器的性能产生不利影响。 如果必须更改 MAX_DOP，我们建议将其设置为小于或等于在单个 NUMA 节点中存在的硬件计划程序的最大数目。 我们建议您不要将 MAX_DOP 设置为大于 8 的值。

按如下方式处理 MAX_DOP：

  • 只要作为查询提示的 MAX_DOP 不超过工作负荷组 MAX_DOP，便遵守作为查询提示的 MAX_DOP。

  • 作为查询提示的 MAX_DOP 始终会覆盖 sp_configure 'max degree of parallelism'。

  • 工作负荷组 MAX_DOP 覆盖 sp_configure 'max degree of parallelism'。

  • 如果在编译时将查询标记为串行 (MAX_DOP = 1)，则在运行时无法更改回并行，不论工作负荷组或 sp_configure 如何设置。

配置 DOP 后，只能在授予内存不足时降低它。 工作负荷组重新配置在授予内存队列中等待时不可见。

GROUP_MAX_REQUESTS = value

指定在工作负荷组中允许执行的同时请求最大数。 value 必须为 0 或一个正整数 。 value 的默认设置为 0，表示允许的请求数不限 。 当达到最大并发请求数时，该组中的用户可以登录但置于等待状态，直至并发请求数降到指定值之下。

USING { pool_name | "default" }

将工作负荷组与由 pool_name 标识的用户定义资源池关联起来，这实际上是将此工作负荷组放入资源池中 。 如果没有提供 pool_name，或如果没有使用 USING 参数，则将工作负荷组放入预定义的 Resource Governor 默认池。

与 ALTER WORKLOAD GROUP 一起使用时，选项 "default" 区分大小写且必须用引号 ("") 引起来或用方括号 ([]) 括起来，以免与系统保留字 DEFAULT 冲突。 有关详细信息，请参阅 Database Identifiers。

备注

允许对默认组使用 ALTER WORKLOAD GROUP。

对工作负荷组配置的更改直到执行 ALTER RESOURCE GOVERNOR RECONFIGURE 后才会生效。 在更改计划影响到设置时，只有在执行 DBCC FREEPROCCACHE (*pool_name*) 后，新设置才会在之前已缓存的计划中生效，其中 pool_name 是与工作负荷组相关联的 Resource Governor 资源池的名称。

  • 如果将 MAX_DOP 更改为 1，则由于并行计划可以在串行模式中运行，因此不需要执行 DBCC FREEPROCCACHE。 但是，它可能不如编译为串行计划的计划那么有效。

  • 如果将 MAX_DOP 从 1 更改为 0 或大于 1 的值，则不需要执行 DBCC FREEPROCCACHE。 但串行计划不能并行运行，因此清除相应的缓存将允许使用并行编译新计划。

注意

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

执行 DDL 语句时，应熟悉 Resource Governor 状态。 有关详细信息，请参阅 Resource Governor。

REQUEST_MEMORY_GRANT_PERCENT：在 SQL Server 2005 (9.x) 中，允许索引创建操作使用比最初授予的工作区内存多的工作区内存，以便提高性能。 这个特别处理在更高版本中由资源调控器支持，然而，最初授予及任何其他内存授予都受资源池和工作负荷组设置的限制。

对已分区表创建索引

对非对齐的已分区表创建索引所占用的内存与涉及的分区数成正比。 如果所需的内存总量超过 Resource Governor 工作负荷组设置规定的每个查询的限制 (REQUEST_MAX_MEMORY_GRANT_PERCENT)，则可能无法执行此索引创建。 由于“默认”工作负荷组允许查询超过每个查询的限制，并在开始时使用所需的最低内存以便与 SQL Server 2005 (9.x) 保持兼容，因此，如果“默认”资源池配置了足够多的内存总量以运行此类查询，则用户或许能够在“默认”工作负载组中运行相同的索引创建。

权限

需要 CONTROL SERVER 权限。

示例

下面的示例说明了如何将默认组中请求的重要性从 MEDIUM 更改为 LOW。

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

下面的示例说明了如何将一个工作负荷组从它所在的池中移到默认池中。

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

另请参阅

SQL Server

* SQL 托管实例 *  

Azure Synapse
分析

 

SQL Server 和 SQL 托管实例

更改现有 Resource Governor 工作负载组配置，并且可以选择将其分配给 Resource Governor 资源池。

注意

对于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"

现有用户定义工作负荷组或 Resource Governor 默认工作负荷组的名称。 在安装 SQL Server 时资源调控器创建“默认”和内部组。

与 ALTER WORKLOAD GROUP 一起使用时，选项 "default" 必须用引号 ("") 引起来或用方括号 ([]) 括起来，以免与系统保留字 DEFAULT 冲突。 有关详细信息，请参阅 Database Identifiers。

预定义工作负荷组和资源池都使用小写名称，例如“default”。 对于使用区分大小写排序规则的服务器，应当注意这一点。 使用不区分大小写排序规则的服务器（例如 SQL_Latin1_General_CP1_CI_AS）会将“"default"和“"Default"”视为相同。

IMPORTANCE = { LOW | MEDIUM | HIGH }

指定工作负荷组中某个请求的相对重要性。 重要性为以下值之一：

  • LOW
  • MEDIUM（默认值）
  • HIGH

在内部，每个重要性设置都存储为用于计算的一个数字。

IMPORTANCE 对资源池而言是局部性的；同一资源池内重要性不同的工作负荷组会相互影响，但不会影响其他资源池中的工作负荷组。

REQUEST_MAX_MEMORY_GRANT_PERCENT = value

指定单个请求可以从池中获取的最大内存量。 value 是相对于 MAX_MEMORY_PERCENT 指定的资源池大小的百分比 。 默认值为 25。 指定的量指的只是查询执行授予内存。

在 SQL Server 2017 (14.x) 之前，value 是 int，允许的范围是 1 到 100。 从 SQL Server 2019 (15.x) 开始，值是 float 数据类型，允许的范围是 0 到 100。

重要

将 value 设置为 0 可阻止在用户定义的工作负荷组中运行具有 SORT 和 HASH JOIN 操作的查询 。

建议不要将 value 设置为大于 70，这是因为如果正在运行其他并发查询，则服务器可能无法保留足够的空闲内存 。 可能最终会导致查询超时错误 8645。

如果查询内存要求超过了此参数指定的限制，服务器会执行以下操作：

  • 对于用户定义的工作负荷组，服务器会尝试降低查询的并行度，直到内存要求降到限制范围以内，或直到并行度等于 1。 如果查询内存要求仍然大于限制值，则会发生错误 8657。
  • 对于内部和默认工作负荷组，服务器会允许查询获取必需的内存。

如果服务器没有足够的物理内存，则这两种情况都会出现超时错误 8645。

REQUEST_MAX_CPU_TIME_SEC = value

指定请求可以使用的最长 CPU 时间，以秒为单位。 value 必须为 0 或一个正整数 。 value 的默认设置为 0，也就是说无限制 。 默认情况下，如果超过最长时间，Resource Governor 并不会阻止继续发出请求。 但会生成一个事件。 有关详细信息，请参阅 CPU Threshold Exceeded 事件类。

从 SQL Server 2016 (13.x) SP2 和 SQL Server 2017 (14.x) CU3 开始以及使用跟踪标志 2422 时，Resource Governor 将在超出最大时间时中止请求。

REQUEST_MEMORY_GRANT_TIMEOUT_SEC = value

指定查询等待内存授予（工作缓冲区内存）变为可用的最长时间（以秒为单位）。

查询并不总是在达到内存授予超时的时候失败。 仅当有太多并发查询运行时，查询才失败。 否则，查询只能获取最小内存授予，从而导致查询性能下降。

value 必须是正整数 。 value 的默认设置为 0，表示使用基于查询开销的内部计算来确定最长时间 。

MAX_DOP = value

指定并行请求的最大并行度 (DOP)。 value 必须为 0 或正整数（1 到 255） 。 value 为 0 时，服务器选择最大并行度 。 这是默认设置，也是推荐设置。

数据库引擎为 MAX_DOP 设置的实际值可能小于指定值。 最终值由公式 min(255, CPU 的数目) 确定 。

注意

更改 MAX_DOP 可能会对服务器的性能产生不利影响。 如果必须更改 MAX_DOP，我们建议将其设置为小于或等于在单个 NUMA 节点中存在的硬件计划程序的最大数目。 我们建议您不要将 MAX_DOP 设置为大于 8 的值。

按如下方式处理 MAX_DOP：

  • 只要作为查询提示的 MAX_DOP 不超过工作负荷组 MAX_DOP，便遵守作为查询提示的 MAX_DOP。

  • 作为查询提示的 MAX_DOP 始终会覆盖 sp_configure 'max degree of parallelism'。

  • 工作负荷组 MAX_DOP 覆盖 sp_configure 'max degree of parallelism'。

  • 如果在编译时将查询标记为串行 (MAX_DOP = 1)，则在运行时无法更改回并行，不论工作负荷组或 sp_configure 如何设置。

配置 DOP 后，只能在授予内存不足时降低它。 工作负荷组重新配置在授予内存队列中等待时不可见。

GROUP_MAX_REQUESTS = value

指定在工作负荷组中允许执行的同时请求最大数。 value 必须为 0 或一个正整数 。 value 的默认设置为 0，表示允许的请求数不限 。 当达到最大并发请求数时，该组中的用户可以登录但置于等待状态，直至并发请求数降到指定值之下。

USING { pool_name | "default" }

将工作负荷组与由 pool_name 标识的用户定义资源池关联起来，这实际上是将此工作负荷组放入资源池中 。 如果没有提供 pool_name，或如果没有使用 USING 参数，则将工作负荷组放入预定义的 Resource Governor 默认池。

与 ALTER WORKLOAD GROUP 一起使用时，选项 "default" 区分大小写且必须用引号 ("") 引起来或用方括号 ([]) 括起来，以免与系统保留字 DEFAULT 冲突。 有关详细信息，请参阅 Database Identifiers。

备注

允许对默认组使用 ALTER WORKLOAD GROUP。

对工作负荷组配置的更改直到执行 ALTER RESOURCE GOVERNOR RECONFIGURE 后才会生效。 在更改计划影响到设置时，只有在执行 DBCC FREEPROCCACHE (*pool_name*) 后，新设置才会在之前已缓存的计划中生效，其中 pool_name 是与工作负荷组相关联的 Resource Governor 资源池的名称。

  • 如果将 MAX_DOP 更改为 1，则由于并行计划可以在串行模式中运行，因此不需要执行 DBCC FREEPROCCACHE。 但是，它可能不如编译为串行计划的计划那么有效。

  • 如果将 MAX_DOP 从 1 更改为 0 或大于 1 的值，则不需要执行 DBCC FREEPROCCACHE。 但串行计划不能并行运行，因此清除相应的缓存将允许使用并行编译新计划。

注意

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

执行 DDL 语句时，应熟悉 Resource Governor 状态。 有关详细信息，请参阅 Resource Governor。

REQUEST_MEMORY_GRANT_PERCENT：在 SQL Server 2005 (9.x) 中，允许索引创建操作使用比最初授予的工作区内存多的工作区内存，以便提高性能。 这个特别处理在更高版本中由资源调控器支持，然而，最初授予及任何其他内存授予都受资源池和工作负荷组设置的限制。

对已分区表创建索引

对非对齐的已分区表创建索引所占用的内存与涉及的分区数成正比。 如果所需的内存总量超过 Resource Governor 工作负荷组设置规定的每个查询的限制 (REQUEST_MAX_MEMORY_GRANT_PERCENT)，则可能无法执行此索引创建。 由于“默认”工作负荷组允许查询超过每个查询的限制，并在开始时使用所需的最低内存以便与 SQL Server 2005 (9.x) 保持兼容，因此，如果“默认”资源池配置了足够多的内存总量以运行此类查询，则用户或许能够在“默认”工作负载组中运行相同的索引创建。

权限

需要 CONTROL SERVER 权限。

示例

下面的示例说明了如何将默认组中请求的重要性从 MEDIUM 更改为 LOW。

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

下面的示例说明了如何将一个工作负荷组从它所在的池中移到默认池中。

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

另请参阅

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 = value

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

CAP_PERCENTAGE_RESOURCE = value

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

REQUEST_MIN_RESOURCE_GRANT_PERCENT = value

value 为十进制数，范围介于 0.75 和 100.00 之间。 REQUEST_MIN_RESOURCE_GRANT_PERCENT 的值必须是 MIN_PERCENTAGE_RESOURCE 的因数，并且必须小于 CAP_PERCENTAGE_RESOURCE。

REQUEST_MAX_RESOURCE_GRANT_PERCENT = value

value 为十进制数，必须大于 REQUEST_MIN_RESOURCE_GRANT_PERCENT。

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

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

QUERY_EXECUTION_TIMEOUT_SEC = value

更改查询在取消之前可以执行的最长时间（以秒为单位）。 value 必须为 0 或一个正整数。 value 的默认设置为 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 请求会立即失败。

锁定行为

更改工作负荷组要求对所有工作负荷组进行全局锁定。 更改工作负荷组的请求排在已提交的创建或删除工作负荷组请求后面。 如果一次提交一批更改语句，它们将按照提交的顺序进行处理。

另请参阅