通过

ALTER RESOURCE POOL (Transact-SQL)

  • 项目

适用于：SQL ServerAzure SQL 托管实例

更改数据库引擎实例的现有 资源调控器 资源池配置。

备注

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

Transact-SQL 语法约定

语法

ALTER RESOURCE POOL { pool_name | [default] }
[WITH
    ( [ MIN_CPU_PERCENT = value ]
    [ [ , ] MAX_CPU_PERCENT = value ]
    [ [ , ] CAP_CPU_PERCENT = value ]
    [ [ , ] AFFINITY {
                        SCHEDULER = AUTO
                      | ( <scheduler_range_spec> )
                      | NUMANODE = ( <NUMA_node_range_spec> )
                      }]
    [ [ , ] MIN_MEMORY_PERCENT = value ]
    [ [ , ] MAX_MEMORY_PERCENT = value ]
    [ [ , ] MIN_IOPS_PER_VOLUME = value ]
    [ [ , ] MAX_IOPS_PER_VOLUME = value ]
)]
[;]

<scheduler_range_spec> ::=
{SCHED_ID | SCHED_ID TO SCHED_ID}[,...n]

<NUMA_node_range_spec> ::=
{NUMA_node_ID | NUMA_node_ID TO NUMA_node_ID}[,...n]

参数

{ pool_name |[default] }

现有用户定义的资源池或内置 default 资源池的名称。

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

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

MIN_CPU_PERCENT = 值

指定在出现 CPU 争用时资源池中的所有请求保证能接收的平均 CPU 带宽。 value 为整数且默认设置为 0。 value 的允许范围是 0 到 100。

MAX_CPU_PERCENT = 值

指定资源池中所有请求在出现 CPU 争用时接收的最大平均 CPU 带宽。 value 为整数且默认设置为 100。 value 的允许范围是 1 到 100。

CAP_CPU_PERCENT = 值

适用于：SQL Server 2012 (11.x) 及更高版本。

指定资源池中所有请求接收的 CPU 带宽的硬上限。 将最大 CPU 带宽级别限制为与指定值相同。 value 为整数且默认设置为 100。 value 的允许范围是 1 到 100。

备注

由于 CPU 治理的统计性质，你可能会注意到偶尔出现超出 CAP_CPU_PERCENT中指定的值的短峰值。

AFFINITY {SCHEDULER = AUTO |（<scheduler_range_spec>） |NUMANODE = （<NUMA_node_range_spec>）}

适用于：SQL Server 2012 (11.x) 及更高版本。

将资源池附加到特定的计划程序。 默认值为 AUTO。

指定 <scheduler_range_spec>AFFINITY SCHEDULER 将资源池关联到由给定 ID 标识的数据库引擎计划程序。 这些 ID 映射到 sys.dm_os_schedulersscheduler_id 列中的值。

指定 <NUMA_node_range_spec>AFFINITY NUMANODE 将资源池关联到映射到对应于给定 NUMA 节点或节点范围的逻辑 CPU 的数据库引擎计划程序。 可以使用以下 Transact-SQL 查询来发现物理 NUMA 配置与数据库引擎计划程序 ID 之间的映射。

SELECT osn.memory_node_id AS numa_node_id,
       sc.cpu_id,
       sc.scheduler_id
FROM sys.dm_os_nodes AS osn
INNER JOIN sys.dm_os_schedulers AS sc
ON osn.node_id = sc.parent_node_id
   AND
   sc.scheduler_id < 1048576;

MIN_MEMORY_PERCENT = 值

指定为无法与其他资源池共享的资源池保留的最小查询工作区内存量。 value 为整数且默认设置为 0。 值 允许的范围从 0 到 100。

MAX_MEMORY_PERCENT = 值

指定此资源池中请求可以使用的最大查询工作区内存量。 value 为整数且默认设置为 100。 value 的允许范围是 1 到 100。

MIN_IOPS_PER_VOLUME = 值

适用于：SQL Server 2014 (12.x) 及更高版本。

指定为资源池保留的每个磁盘卷每秒的最小 I/O 操作数 (IOPS)。 value 的允许范围是 0 到 2^31-1 (2,147,483,647)。 指定 0 以指示池的最小值。 默认值为 0。

MAX_IOPS_PER_VOLUME = 值

适用于：SQL Server 2014 (12.x) 及更高版本。

指定可用于该资源池的每个磁盘卷每秒的最大 I/O 操作数 (IOPS)。 value 的允许范围是 0 到 2^31-1 (2,147,483,647)。 指定 0 以删除池的 IOPS 限制。 默认值为 0。

如果池的 MAX_IOPS_PER_VOLUME 设置为 0，则池根本不受 IO 控制，并且即使其他池已设置 MIN_IOPS_PER_VOLUME，也可以获取系统中的所有 IOPS。 在这种情况下，建议将此池的 MAX_IOPS_PER_VOLUME 值设置为高数字（例如，最大值 2^31-1），以使此池 IO 受管理，并遵循其他池可能存在的 IOPS 预留。

注解

所有资源池的 MIN_CPU_PERCENT 或 MIN_MEMORY_PERCENT 的总和不能超过 100%。

MIN_IOPS_PER_VOLUME 和 MAX_IOPS_PER_VOLUME 指定每秒最小和最大 IO 数。 IO 可以是读取或写入，并且可以是任何大小。 因此，具有相同的 IOPS 限制，最小和最大 IO 吞吐量可能会因工作负荷中的 IO 大小组合而异。

MAX_CPU_PERCENT 和 MAX_MEMORY_PERCENT 的值必须分别大于或等于 MIN_CPU_PERCENT 和 MIN_MEMORY_PERCENT的值。

CAP_CPU_PERCENT 不同于 MAX_CPU_PERCENT，因此与池关联的工作负荷可以使用 CPU 容量高于 MAX_CPU_PERCENT 的值（如果可用），但不能超过 CAP_CPU_PERCENT的值。 尽管峰值可能比 CAP_CPU_PERCENT短，但工作负荷在较长时间内不能超过 CAP_CPU_PERCENT，即使有额外的 CPU 容量可用也是如此。

每个关联组件（计划程序（s）或 NUMA 节点（s）的总 CPU 百分比不能超过 100%。

有关详细信息，请参阅 资源调控器 和 资源调控器资源池。

缓存的计划

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

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

警告

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

权限

需要 CONTROL SERVER 权限。

示例

以下示例保留 default 池的所有当前资源池设置，MAX_CPU_PERCENT除外，更改为 25%。

ALTER RESOURCE POOL [default] WITH (MAX_CPU_PERCENT = 25);

ALTER RESOURCE GOVERNOR RECONFIGURE;

以下示例修改 adhocPool 资源池，如下所示：

  • 分别使用 MIN_CPU_PERCENT 和 MIN_MEMORY_PERCENT 保留 10% 的 CPU 和 5% 的查询工作区内存。
  • 使用 MAX_MEMORY_PERCENT设置 15% 的查询工作区内存限制。
  • 使用 MAX_CPU_PERCENT 和 CAP_CPU_PERCENT 分别设置 30% 的软 CPU 上限和 30% 的硬 CPU 上限。
  • 使用 AFFINITY SCHEDULER将池关联到两个逻辑 CPU 范围（0 到 63 和 128 到 191）。

适用于：SQL Server 2012 (11.x) 及更高版本。

ALTER RESOURCE POOL adhocPool
WITH (
     MIN_CPU_PERCENT = 10,
     MAX_CPU_PERCENT = 20,
     CAP_CPU_PERCENT = 30,
     MIN_MEMORY_PERCENT = 5,
     MAX_MEMORY_PERCENT = 15,
     AFFINITY SCHEDULER = (0 TO 63, 128 TO 191)
     );

ALTER RESOURCE GOVERNOR RECONFIGURE;

相关内容