1 out of 1 rated this helpful - Rate this topic

CREATE RESOURCE POOL (Transact-SQL)

SQL Server 2012

Creates a Resource Governor resource pool. Resource Governor is available only on the Enterprise, Developer, and Evaluation editions of SQL Server.

Topic link icon Transact-SQL Syntax Conventions.

Syntax


CREATE RESOURCE POOL pool_name
[ WITH
    ( [ MIN_CPU_PERCENT = value ]
    [ [ , ] MAX_CPU_PERCENT = value ]
    [ [ , ] MIN_MEMORY_PERCENT = value ]
    [ [ , ] MAX_MEMORY_PERCENT = value ] )
]
[;]

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

The sum of the values for MIN_CPU_PERCENT and MIN_MEMORY_PERCENT for all the resource pools must 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

Reference

ALTER 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