Share via

  • Article

CREATE RESOURCE POOL (Transact-SQL)

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

Topic link iconTransact-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