ALTER RESOURCE POOL (Transact-SQL)

{ pool_name | "default" }

Is the name of an existing user-defined resource pool or the default resource pool that is created when SQL Server 2012 is installed.

"default" must be enclosed by quotation marks ("") or brackets ([]) when used with ALTER RESOURCE POOL to avoid conflict with DEFAULT, which is a system reserved word. For more information, see Database Identifiers.

Note
Predefined workload groups and resource pools all use lowercase names, such as "default". This should be taken into account for servers that use case-sensitive collation. Servers with case-insensitive collation, such as SQL_Latin1_General_CP1_CI_AS, will treat "default" and "Default" as the same.

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 the 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.

Copy

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 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 through 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.

MAX_CPU_PERCENT and MAX_MEMORY_PERCENT must be greater than or equal to 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%.

When you are executing DDL statements, we recommend that you be familiar with Resource Governor states. For more information, see Resource Governor.

Requires CONTROL SERVER permission.

The following example keeps all the default resource pool settings on the default pool except for MAX_CPU_PERCENT, which is changed to 25.

ALTER RESOURCE POOL "default"
WITH
     ( MAX_CPU_PERCENT = 25)
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

In the following example, the CAP_CPU_PERCENT sets the hard cap to 80% and AFFINITY SCHEDULER is set to an individual value of 8 and a range of 12 to 16.

ALTER RESOURCE POOL Pool25
WITH( 
     MIN_CPU_PERCENT = 5,
     MAX_CPU_PERCENT = 10,     
     CAP_CPU_PERCENT = 80,
     AFFINITY SCHEDULER = (8, 12 TO 16), 
     MIN_MEMORY_PERCENT = 5,
     MAX_MEMORY_PERCENT = 15
);

GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

Reference

Concepts