Edit

Share via

CREATE RESOURCE POOL (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Managed Instance

Creates a resource governor resource pool. A resource pool represents a subset of the physical resources (CPU, memory, and IO) of a Database Engine instance. Resource governor enables you to reserve or limit server resources among resource pools, up to a maximum of 64 pools.

Resource governor isn't available in every edition of SQL Server. For a list of features that are supported by the editions of SQL Server, see Editions and supported features of SQL Server 2022.

Note

For Azure SQL Managed Instance, you must be in the context of the master database to modify resource governor configuration.

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

Is the user-defined name for the resource pool. pool_name is alphanumeric, can be up to 128 characters, must be unique within a Database Engine instance, and must comply with the rules for Database 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 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

Applies to: SQL Server 2012 (11.x) and later.

Specifies a hard cap on the CPU bandwidth that all requests in the resource pool 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.

Note

Due to the statistical nature of CPU governance, you might notice occasional short spikes exceeding the value specified in CAP_CPU_PERCENT.

AFFINITY {SCHEDULER = AUTO | ( <scheduler_range_spec> ) | NUMANODE = (<NUMA_node_range_spec>)}

Applies to: SQL Server 2012 (11.x) and later.

Attach the resource pool to specific schedulers. The default value is AUTO.

Specifying <scheduler_range_spec> for AFFINITY SCHEDULER affinitizes the resource pool to the Database Engine schedulers identified by the given IDs. These IDs map to the values in the scheduler_id column in sys.dm_os_schedulers.

Specifying <NUMA_node_range_spec> for AFFINITY NUMANODE affinitizes the resource pool to the Database Engine schedulers that map to the logical CPUs that correspond to the given NUMA node or a range of nodes. You can use the following Transact-SQL query to discover the mapping between the physical NUMA configuration and the Database Engine 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

Specifies the minimum amount of query workspace memory reserved for the resource pool that can't 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 maximum amount of query workspace memory that requests in this resource pool can use. value is an integer with a default setting of 100. The allowed range for value is from 1 through 100.

MIN_IOPS_PER_VOLUME = value

Applies to: SQL Server 2014 (12.x) and later.

Specifies the minimum I/O operations per second (IOPS) per disk volume to reserve for the resource pool. The allowed range for value is from 0 through 2^31-1 (2,147,483,647). Specify 0 to indicate no minimum for the pool. The default is 0.

MAX_IOPS_PER_VOLUME = value

Applies to: SQL Server 2014 (12.x) and later.

Specifies the maximum I/O operations per second (IOPS) per disk volume to allow for the resource pool. The allowed range for value is from 0 through 2^31-1 (2,147,483,647). Specify 0 to remove an IOPS limit for the pool. The default is 0.

If the MAX_IOPS_PER_VOLUME for a pool is set to 0, the pool isn't IO-governed at all and can take all the IOPS in the system even if other pools have MIN_IOPS_PER_VOLUME set. 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) to make this pool IO-governed and to honor the IOPS reservations that might exist for other pools.

Remarks

The sum of MIN_CPU_PERCENT or MIN_MEMORY_PERCENT for all resource pools can't exceed 100 percent.

MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME specify the minimum and maximum IOs per second. The IOs can be either reads or writes, and can be of any size. Therefore, with the same IOPS limits, the minimum and maximum IO throughput can vary depending on the mix of IO sizes in the workload.

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. Although there might be short spikes higher than CAP_CPU_PERCENT, workloads can't exceed CAP_CPU_PERCENT for extended periods of time, even when additional CPU capacity is available.

The total CPU percentage for each affinitized component (scheduler(s) or NUMA node(s)) can't exceed 100 percent.

For more information, see Resource governor and Resource governor resource pool.

Permissions

Requires the CONTROL SERVER permission.

Examples

For additional resource governor configuration examples, see Resource governor configuration examples and best practices.

Create a resource pool

This example created a resource pool named bigPool. This pool uses the default resource governor settings.

CREATE RESOURCE POOL bigPool;
ALTER RESOURCE GOVERNOR RECONFIGURE;

Set CPU and memory reservations and limits

This example configures the adhocPool resource pool as follows:

  • Reserves 10 percent of CPU and 5 percent of query workspace memory using MIN_CPU_PERCENT and MIN_MEMORY_PERCENT respectively.
  • Sets a 15 percent query workspace memory limit using MAX_MEMORY_PERCENT.
  • Sets a 20 percent soft CPU cap a 30 percent hard CPU cap using MAX_CPU_PERCENT and CAP_CPU_PERCENT respectively.
  • Affinitizes the pool to two ranges of logical CPUs (0 to 63 and 128 to 191) using AFFINITY SCHEDULER.

Applies to: SQL Server 2012 (11.x) and later.

CREATE 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)
     );

Set IOPS reservation and limit

This example reserves 200 IOPS per volume for the pool using MIN_IOPS_PER_VOLUME, and limits the IOPS per volume to 1000 using MAX_IOPS_PER_VOLUME. These values govern the total physical I/O read and write operations that are available for requests using the resource pool.

Applies to: SQL Server 2014 (12.x) and later.

CREATE RESOURCE POOL PoolAdmin
WITH (
     MIN_IOPS_PER_VOLUME = 200,
     MAX_IOPS_PER_VOLUME = 1000
     );

Related content