CREATE RESOURCE POOL (Transact-SQL)CREATE RESOURCE POOL (Transact-SQL)

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

SQL ServerSQL Server 中创建资源调控器资源池。Creates a Resource Governor resource pool in SQL ServerSQL Server. 资源池表示数据库引擎实例的部分物理资源(内存、CPU 和 IO)。A resource pool represents a subset of the physical resources (memory, CPUs and IO) of an instance of the Database Engine. 数据库管理员可以使用资源调控器在多个资源池之间分发服务器资源,最多可为 64 个池。Resource Governor enables a database administrator to distribute server resources among resource pools, up to a maximum of 64 pools. 资源调控器并非在每个 SQL ServerSQL Server 版本中都提供。Resource Governor is not available in every edition of SQL ServerSQL Server. 有关 SQL ServerSQL Server各版本支持的功能列表,请参阅 SQL Server 2016 各个版本支持的功能For a list of features that are supported by the editions of SQL ServerSQL Server, see Features Supported by the Editions of SQL Server 2016.

“主题链接”图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions.

语法Syntax

CREATE RESOURCE POOL pool_name  
[ 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]  

参数Arguments

pool_name pool_name
资源池的用户定义名称。Is the user-defined name for the resource pool. pool_name 由字母数字组成,最多可包含 128 个字符,在 SQL ServerSQL Server 实例中必须是唯一的,并且必须符合标识符规则 。pool_name is alphanumeric, can be up to 128 characters, must be unique within an instance of SQL ServerSQL Server, and must comply with the rules for identifiers.

MIN_CPU_PERCENT = value MIN_CPU_PERCENT =value
指定在出现 CPU 争用时资源池中的所有请求保证能接收的平均 CPU 带宽。Specifies the guaranteed average CPU bandwidth for all requests in the resource pool when there is CPU contention. value 为整数且默认设置为 0 。value is an integer with a default setting of 0. value 的允许范围是 0 到 100 。The allowed range for value is from 0 through 100.

MAX_CPU_PERCENT = value MAX_CPU_PERCENT =value
指定存在 CPU 争用时,资源池中的所有请求将接收的最大平均 CPU 带宽。Specifies the maximum average CPU bandwidth that all requests in resource pool will receive when there is CPU contention. value 为整数且默认设置为 100 。value is an integer with a default setting of 100. value 的允许范围是 1 到 100 。The allowed range for value is from 1 through 100.

CAP_CPU_PERCENT =value CAP_CPU_PERCENT =value
适用于SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

指定资源池中的所有请求都将收到的 CPU 带宽硬性上限。Specifies a hard cap on the CPU bandwidth that all requests in the resource pool will receive. 将 CPU 最大带宽级别限制为与指定值相同。Limits the maximum CPU bandwidth level to be the same as the specified value. value 为整数且默认设置为 100 。value is an integer with a default setting of 100. value 的允许范围是 1 到 100 。The allowed range for value is from 1 through 100.

AFFINITY {SCHEDULER = AUTO | ( <scheduler_range_spec> ) | NUMANODE = (<NUMA_node_range_spec>)}AFFINITY {SCHEDULER = AUTO | ( <scheduler_range_spec> ) | NUMANODE = (<NUMA_node_range_spec>)}
适用于SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

将资源池附加到特定的计划程序。Attach the resource pool to specific schedulers. 默认值为 AUTO。The default value is AUTO.

AFFINITY SCHEDULER = ( < Scheduler_range_spec ) 将资源池映射到由给定 ID 标识的 SQL ServerSQL Server 计划。AFFINITY SCHEDULER = ( <scheduler_range_spec> ) maps the resource pool to the SQL ServerSQL Server schedules identified by the given IDs. 这些 ID 映射到 sys.dm_os_schedulers (Transact-SQL) 的 scheduler_id 列中的值。These IDs map to the values in the scheduler_id column in sys.dm_os_schedulers (Transact-SQL).

当使用 AFFINITY NUMANODE = (<NUMA_node_range_spec> ) 时,资源池关联到映射至物理 CPU 的 SQL ServerSQL Server 计划程序,而这些 CPU 与给定的一个 NUMA 节点或一系列节点相对应 。When you use AFFINITY NUMANODE = ( <NUMA_node_range_spec> ), the resource pool is affinitized to the SQL ServerSQL Server schedulers that map to the physical CPUs that correspond to the given NUMA node or range of nodes. 您可以使用以下 Transact-SQLTransact-SQL 查询发现物理 NUMA 配置与 SQL ServerSQL Server 计划程序 ID 之间的映射。You can use the following Transact-SQLTransact-SQL query to discover the mapping between the physical NUMA configuration and the SQL ServerSQL Server scheduler IDs.

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 MIN_MEMORY_PERCENT =value
指定为此资源池保留的、不能与其他资源池共享的最小内存量。Specifies the minimum amount of memory reserved for this resource pool that can not be shared with other resource pools. value 为整数,默认设置为 0,value 的允许范围为 0 到 100 。value is an integer with a default setting of 0 The allowed range for value is from 0 to 100.

MAX_MEMORY_PERCENT =value MAX_MEMORY_PERCENT =value
指定此资源池中的请求可使用的总服务器内存量。Specifies the total server memory that can be used by requests in this resource pool. value 为整数且默认设置为 100 。value is an integer with a default setting of 100. value 的允许范围是 1 到 100 。The allowed range for value is from 1 through 100.

MIN_IOPS_PER_VOLUME =value MIN_IOPS_PER_VOLUME =value
适用于SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更高版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.

指定为资源池保留的每个磁盘卷每秒的最小 I/O 操作数 (IOPS)。Specifies the minimum I/O operations per second (IOPS) per disk volume to reserve for the resource pool. value 的允许范围是 0 到 2^31-1 (2,147,483,647) 。The allowed range for value is from 0 through 2^31-1 (2,147,483,647). 指定 0 表示池没有最小值阈值。Specify 0 to indicate no minimum threshold for the pool. 默认值为 0。The default is 0.

MAX_IOPS_PER_VOLUME =value MAX_IOPS_PER_VOLUME =value
适用于SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更高版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.

指定可用于该资源池的每个磁盘卷每秒的最大 I/O 操作数 (IOPS)。Specifies the maximum I/O operations per second (IOPS) per disk volume to allow for the resource pool. value 的允许范围是 0 到 2^31-1 (2,147,483,647) 。The allowed range for value is from 0 through 2^31-1 (2,147,483,647). 指定 0 表示为池设置无限制的阈值。Specify 0 to set an unlimited threshold for the pool. 默认值为 0。The default is 0.

如果池的 MAX_IOPS_PER_VOLUME 设置为 0,则该池根本不受管控,可以采用系统中的所有 IOPS,即使其他池设置了 MIN_IOPS_PER_VOLUME 也是如此。If the MAX_IOPS_PER_VOLUME for a pool is set to 0, the pool is not governed at all and can take all the IOPS in the system even if other pools have MIN_IOPS_PER_VOLUME set. 对于这种情况,建议在希望管控此池的 IO 时将该池的 MAX_IOPS_PER_VOLUME 值设置为较高的数字(例如,最大值 2^31-1)。For this case, we recommend that you set the MAX_IOPS_PER_VOLUME value for this pool to a high number (for example, the maximum value 2^31-1) if you want this pool to be governed for IO.

RemarksRemarks

MIN_IOPS_PER_VOLUMEMAX_IOPS_PER_VOLUME 指定每秒最小和最大读取或写入数。MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME specify the minimum and maximum reads or writes per second. 这些读取或写入可以是任何大小,并且不指示最小或最大流量。These reads or writes can be of any size and do not indicate minimum or maximum throughput.

MAX_CPU_PERCENTMAX_MEMORY_PERCENT 的值必须分别大于或等于 MIN_CPU_PERCENTMIN_MEMORY_PERCENT 的值。The values for MAX_CPU_PERCENT and MAX_MEMORY_PERCENT must be greater than or equal to the values for MIN_CPU_PERCENT and MIN_MEMORY_PERCENT, respectively.

CAP_CPU_PERCENT 不同于 MAX_CPU_PERCENT,因为资源池关联的工作负载可以使用高于 MAX_CPU_PERCENT 值的 CPU 容量(如果可用),但不能超过 CAP_CPU_PERCENT 的值。CAP_CPU_PERCENT differs from MAX_CPU_PERCENT in that workloads associated with the pool can use CPU capacity above the value of MAX_CPU_PERCENT if it is available, but not above the value of CAP_CPU_PERCENT.

每个关联组件(计划程序或 NUMA 节点)的总 CPU 百分比不能超过 100%。The total CPU percentage for each affinitized component (scheduler(s) or NUMA node(s)) should not exceed 100 percent.

权限Permissions

需要 CONTROL SERVER 权限。Requires CONTROL SERVER permission.

示例Examples

1.演示如何创建资源池1. Shows how to create a resource pool

该示例创建了名为“bigPool”的资源池。This example created a resource pool named "bigPool". 此池使用默认的资源调控器设置。This pool uses the default Resource Governor settings.

CREATE RESOURCE POOL bigPool;  
GO  
ALTER RESOURCE GOVERNOR RECONFIGURE;  
GO  

2.将 CAP_CPU_PERCENT 设置为硬上限并设置关联计划程序2. Set the CAP_CPU_PERCENT to a hard cap and set AFFINITY SCHEDULER

将 CAP_CPU_PERCENT 设置为 30% 的硬上限,并将关联计划程序的范围设置为 0 到 63、128 到 191。Set the CAP_CPU_PERCENT to a hard cap of 30 percent and sets AFFINITY SCHEDULER to a range of 0 to 63, 128 to 191.

适用于SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

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

3.设置 MIN_IOPS_PER_VOLUME 和 MAX_IOPS_PER_VOLUME3. Set MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME

将 MIN_IOPS_PER_VOLUME 设置为 20,将 MAX_IOPS_PER_VOLUME 设置为 100。Set MIN_IOPS_PER_VOLUME to 20 and MAX_IOPS_PER_VOLUME to 100. 这些值控制可用于资源池的物理 I/O 读取和写入操作。These values govern the physical I/O read and write operations that are available for the resource pool.

适用于SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更高版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later.

CREATE RESOURCE POOL PoolAdmin  
WITH (  
    MIN_IOPS_PER_VOLUME = 20,  
    MAX_IOPS_PER_VOLUME = 100  
      );  

另请参阅See Also

ALTER RESOURCE POOL (Transact-SQL) ALTER RESOURCE POOL (Transact-SQL)
DROP RESOURCE POOL (Transact-SQL) DROP RESOURCE POOL (Transact-SQL)
CREATE WORKLOAD GROUP (Transact-SQL) CREATE WORKLOAD GROUP (Transact-SQL)
ALTER WORKLOAD GROUP (Transact-SQL) ALTER WORKLOAD GROUP (Transact-SQL)
DROP WORKLOAD GROUP (Transact-SQL) DROP WORKLOAD GROUP (Transact-SQL)
ALTER RESOURCE GOVERNOR (Transact-SQL) ALTER RESOURCE GOVERNOR (Transact-SQL)
资源调控器资源池 Resource Governor Resource Pool
创建资源池Create a Resource Pool