Skip to main content
ALTER EXTERNAL RESOURCE POOL (Transact-SQL)
 

Updated: March 21, 2016

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016) noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Changes Resource Governor external pool that was defined resources for external processes. For R Services the external pool governs rterm.exe, BxlServer.exe, and other processes spawned by them.

Topic link icon  Transact-SQL Syntax Conventions.

ALTER EXTERNAL RESOURCE POOL { pool_name | "default" }  
[ 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 ]  

{ pool_name | "default" }
Is the name of an existing user-defined external resource pool or the default external resource pool that is created when SQL Server is installed.
"default" must be enclosed by quotation marks ("") or brackets ([]) when used with ALTER EXTERNAL RESOURCE POOL to avoid conflict with DEFAULT, which is a system reserved word.

MAX_CPU_PERCENT =value
Specifies the maximum average CPU bandwidth that all requests in the external 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.

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

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 with a default setting of 100. The allowed range for value is from 1 through 100.

MAX_PROCESSES =value
Specifies the maximum number of processes allowed for the external resource pool. Specify 0 to set an unlimited threshold for the pool which will be bound only be computer resources. The default is 0.

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

For more information on 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).

Requires CONTROL SERVER permission.

The following statement changes an external pool restricting the CPU usage to 50 percent and the maximum memory to 25 percent of the available memory on the computer.

ALTER EXTERNAL RESOURCE POOL ep_1  
WITH (  
    MAX_CPU_PERCENT = 50  
    , AFFINITY CPU = AUTO  
    , MAX_MEMORY_PERCENT = 25  
);  
GO  
ALTER RESOURCE GOVERNOR RECONFIGURE;  
GO  

external scripts enabled Server Configuration Option
SQL Server R Services
Known Issues for SQL Server R Services
CREATE EXTERNAL RESOURCE POOL (Transact-SQL)
DROP EXTERNAL RESOURCE POOL (Transact-SQL)
ALTER RESOURCE POOL (Transact-SQL)
CREATE WORKLOAD GROUP (Transact-SQL)
Resource Governor Resource Pool
ALTER RESOURCE GOVERNOR (Transact-SQL)