create-external-resource-pool-transact-sql.md

title	CREATE EXTERNAL RESOURCE POOL (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	08/10/2017
ms.prod	sql-server-2016
ms.reviewer
ms.suite
ms.technology	database-engine
ms.tgt_pltfrm
ms.topic	language-reference
f1_keywords	CREATE EXTERNAL RESOURCE POOL CREATE EXTERNAL_RESOURCE_POOL_TSQL EXTERNAL RESOURCE POOL EXTERNAL_RESOURCE_POOL_TSQL EXTERNAL RESOURCE EXTERNAL_RESOURCE_TSQL
dev_langs	TSQL
helpviewer_keywords	CREATE EXTERNAL RESOURCE POOL statement
ms.assetid	8cc798ad-c395-461c-b7ff-8c561c098808
caps.latest.revision	12
author	BYHAM
ms.author	rickbyh
manager	jhubbard
ms.workload	Inactive

CREATE EXTERNAL RESOURCE POOL (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2016-xxxx-xxxx-xxx_md]

Creates an external pool used to define resources for external processes. For R Services the external pool governs rterm.exe, BxlServer.exe, and other processes spawned by them. A resource pool represents a subset of the physical resources (memory and CPUs) of an instance of the Database Engine. Resource Governor enables a database administrator to distribute server resources among resource pools, up to a maximum of 64 pools.

Transact-SQL Syntax Conventions.

Syntax

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, can be up to 128 characters, must be unique within an instance of [!INCLUDEssNoVersion], and must comply with the rules for identifiers.

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 [!INCLUDEssNoVersion] CPUs identified by the given CPU_IDs.

When you use AFFINITY NUMANODE = ( <NUMA_node_range_spec> ), the external resource pool is affinitized to the [!INCLUDEssNoVersion] 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.

Remarks

The [!INCLUDEssDE] 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).

Permissions

Requires CONTROL SERVER permission.

Examples

The following statement defines an external pool that restricts CPU usage to 75 percent and the maximum memory to 30 percent of the available memory on the computer.

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

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