This topic has not yet been rated - Rate this topic

ALTER RESOURCE POOL (Transact-SQL)

SQL Server 2008 R2

Changes an existing Resource Governor resource pool configuration.

Topic link icon Transact-SQL Syntax Conventions.The introduction is required.


ALTER RESOURCE POOL { pool_name | "default" }
[WITH
     ( [ MIN_CPU_PERCENT = value ]
     [ [ , ] MAX_CPU_PERCENT = value ]
     [ [ , ] MIN_MEMORY_PERCENT = value ]
     [ [ , ] MAX_MEMORY_PERCENT = value ] )
]
[;]

Arguments

{ pool_name | "default" }

Is the name of an existing user-defined resource pool or the default resource pool that is created when SQL Server 2008 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 Delimited Identifiers (Database Engine).

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.

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.

Remarks

MAX_CPU_PERCENT and MAX_MEMORY_PERCENT must be greater than or equal to MIN_CPU_PERCENT and MIN_MEMORY_PERCENT, respectively.

The sums of MIN_CPU_PERCENT and MIN_MEMORY_PERCENT for all the resource pools must 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 States.

Permissions

Requires CONTROL SERVER permission.

Examples

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

Reference

CREATE RESOURCE POOL (Transact-SQL)

DROP RESOURCE POOL (Transact-SQL)

CREATE WORKLOAD GROUP (Transact-SQL)

ALTER WORKLOAD GROUP (Transact-SQL)

DROP WORKLOAD GROUP (Transact-SQL)

ALTER RESOURCE GOVERNOR (Transact-SQL)

Concepts

Resource Governor Concepts

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD