Muokkaa

Jaa

CREATE EXTERNAL RESOURCE POOL (Transact-SQL)

  • Artikkeli

Applies to: SQL Server 2016 (13.x) and later versions

Creates an external pool to define resources for external processes. A resource pool represents a subset of the physical resources (memory and CPUs) of a Database Engine instance. A Resource Governor can distribute server resources among resource pools, up to a maximum of 64 pools.

For R Services (In-Database) in SQL Server 2016 (13.x), the external pool governs rterm.exe, BxlServer.exe, and other processes spawned by them.

For Machine Learning Services, the external pool governs rterm.exe, python.exe, BxlServer.exe, and other processes spawned by them.

Transact-SQL syntax conventions

Syntax

CREATE EXTERNAL RESOURCE POOL pool_name  
[ WITH (  
    [ MAX_CPU_PERCENT = value ]  
    [ [ , ] MAX_MEMORY_PERCENT = value ]  
    [ [ , ] MAX_PROCESSES = value ]   
    )   
]  
[ ; ]  

<CPU_range_spec> ::=    
{ CPU_ID | CPU_ID  TO CPU_ID } [ ,...n ]  

CREATE EXTERNAL RESOURCE POOL pool_name  
[ WITH (  
    [ MAX_CPU_PERCENT = value ]  
    [ [ , ] AFFINITY CPU =    
            {  
                AUTO   
              | ( <cpu_range_spec> )   
              | NUMANODE = ( <NUMA_node_id> )   
            } ]   
    [ [ , ] MAX_MEMORY_PERCENT = value ]  
    [ [ , ] MAX_PROCESSES = value ]   
    )   
]  
[ ; ]  

<CPU_range_spec> ::=    
{ CPU_ID | CPU_ID  TO CPU_ID } [ ,...n ]

Arguments

pool_name
Is the user-defined name for the external resource pool. pool_name is alphanumeric and can be up to 128 characters. This argument must be unique within an instance of SQL Server and must fulfill the rules for identifiers.

MAX_CPU_PERCENT =value
The maximum average CPU bandwidth for all requests that the external resource pool can receive when there's CPU contention. value is an integer. The allowed range for value is from 1 through 100.

MAX_MEMORY_PERCENT =value
Specifies the total server memory that can be used by requests in this external resource pool. value is an integer. The allowed range for value is from 1 through 100.

MAX_PROCESSES =value
The maximum number of processes allowed for the external resource pool. 0 = unlimited threshold for the pool, which is thereafter bound only by computer resources.

MAX_CPU_PERCENT =value
The maximum average CPU bandwidth for all requests that the external resource pool can receive when there's CPU contention. value is an integer. The allowed range for value is from 1 through 100.

AFFINITY {CPU = AUTO | ( <CPU_range_spec>) | NUMANODE = (<NUMA_node_range_spec>)} Attach the external resource pool to specific CPUs.

AFFINITY CPU = ( <CPU_range_spec> ) maps the external resource pool to the SQL Server CPUs identified by the given CPU_IDs.

When you use AFFINITY NUMANODE = **(<NUMA_node_range_spec> ), the external resource pool is affinitized to the SQL Server physical CPUs that correspond to the given NUMA node or range of nodes.

MAX_MEMORY_PERCENT =value
Specifies the total server memory that can be used by requests in this external resource pool. value is an integer. The allowed range for value is from 1 through 100.

MAX_PROCESSES =value
The maximum number of processes allowed for the external resource pool. 0 = unlimited threshold for the pool, which is thereafter bound only by computer resources.

Remarks

The Database Engine implements the resource pool when you execute the ALTER RESOURCE GOVERNOR RECONFIGURE statement.

For general information about resource pools, see Resource Governor Resource Pool, sys.resource_governor_external_resource_pools (Transact-SQL), and sys.dm_resource_governor_external_resource_pool_affinity (Transact-SQL).

For information specific to managing external resource pools that are used for machine learning, see Resource governance for machine learning in SQL Server.

Permissions

Requires CONTROL SERVER permission.

Examples

The external pool has restricted CPU usage to 75 percent. The maximum memory is 30 percent of the available memory on the computer.

CREATE EXTERNAL RESOURCE POOL ep_1
WITH (  
    MAX_CPU_PERCENT = 75
    , MAX_MEMORY_PERCENT = 30
);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

CREATE EXTERNAL RESOURCE POOL ep_1
WITH (  
    MAX_CPU_PERCENT = 75
    , AFFINITY CPU = AUTO
    , MAX_MEMORY_PERCENT = 30
);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

See also