CREATE RESOURCE POOL (Transact-SQL)
Creates a Resource Governor resource pool. Resource Governor is not available in every edition of Microsoft SQL Server. For a list of features that are supported by the editions of SQL Server, see Features Supported by the Editions of SQL Server 2012.
.gif "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 ])
]
[;]
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
Is the user-defined name for the resource pool. pool_name is alphanumeric, can be up to 128 characters, must be unique within an instance of SQL Server, and must comply with the rules for identifiers.MIN_CPU_PERCENT =value
Specifies the guaranteed average CPU bandwidth for all requests in the resource pool when there is CPU contention. value is an integer with a default setting of 0. The allowed range for value is from 0 through 100.MAX_CPU_PERCENT =value
Specifies the maximum average CPU bandwidth that all requests in resource pool will receive when there is CPU contention. value is an integer with a default setting of 100. The allowed range for value is from 1 through 100.CAP_CPU_PERCENT =value
Specifies a hard cap on the CPU bandwidth that all requests in the resource pool will receive. Limits the maximum CPU bandwidth level to be the same as the specified value. value is an integer with a default setting of 100. The allowed range for value is from 1 through 100.AFFINITY {SCHEDULER = AUTO | (Scheduler_range_spec) | NUMANODE = (<NUMA_node_range_spec>)}
Attach the resource pool to specific schedulers. The default value is AUTO.AFFINITY SCHEDULER = (Scheduler_range_spec) maps the resource pool to the SQL Server schedules identified by the given IDs. These IDs map to the values in the scheduler_id column in sys.dm_os_schedulers (Transact-SQL).
When you use AFFINITY NAMANODE = (NUMA_node_range_spec), the resource pool is affinitized to the SQL Server schedulers that map to the physical CPUs that correspond to the given NUMA node or range of nodes. You can use the following Transact-SQL query to discover the mapping between the physical NUMA configuration and the SQL 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 < 1048576MIN_MEMORY_PERCENT =value
Specifies the minimum amount of memory reserved for this resource pool that can not be shared with other resource pools. value is an integer with a default setting of 0 The allowed range for value is from 0 to 100.MAX_MEMORY_PERCENT =value
Specifies the total server memory that can be used by requests in this resource pool. value is an integer with a default setting of 100. The allowed range for value is from 1 through 100.
Remarks
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 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.
The total CPU percentage for each affinitized component (scheduler(s) or NUMA node(s)) should not exceed 100%.
Permissions
Requires CONTROL SERVER permission.
Examples
The following example shows how to create a resource pool named bigPool. This pool uses the default Resource Governor settings.
CREATE RESOURCE POOL bigPool;
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
In the following example, the CAP_CPU_PERCENT sets the hard cap to 30% and AFFINITY SCHEDULER is set to a range of 0 to 63, 128 to 191.
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);
Remarks
The Resource Governor feature enables a database administrator to distribute server resources among resource pools, up to a maximum of 64 pools.
See Also
Reference
ALTER RESOURCE POOL (Transact-SQL)
DROP RESOURCE POOL (Transact-SQL)
CREATE WORKLOAD GROUP (Transact-SQL)
ALTER WORKLOAD GROUP (Transact-SQL)