Skip to content

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

Latest commit

 

History

History
123 lines (97 loc) · 6.84 KB

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

File metadata and controls

123 lines (97 loc) · 6.84 KB
title CREATE EXTERNAL RESOURCE POOL (Transact-SQL) | Microsoft Docs
ms.custom
ms.date 08/07/2019
ms.prod sql
ms.reviewer
ms.technology machine-learning
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
author dphansen
ms.author davidph
manager cgronlund
monikerRange >=sql-server-2016||=sqlallproducts-allversions

CREATE EXTERNAL RESOURCE POOL (Transact-SQL)

[!INCLUDEtsql-appliesto-ss-xxxx-xxxx-xxx-md]

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

::: moniker range="=sql-server-2016||=sqlallproducts-allversions" For [!INCLUDErsql-productname-md] in [!INCLUDEsssql15-md], the external pool governs rterm.exe, BxlServer.exe, and other processes spawned by them. ::: moniker-end

::: moniker range=">=sql-server-2017||=sqlallproducts-allversions" For [!INCLUDErsql-productnamenew-md], the external pool governs rterm.exe, python.exe, BxlServer.exe, and other processes spawned by them. ::: moniker-end

Topic link icon 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 and can be up to 128 characters. This argument must be unique within an instance of [!INCLUDEssNoVersion] and must fulfill the rules for identifiers.

MAX_CPU_PERCENT =value
Specifies the maximum average CPU bandwidth that all requests in 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 [!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. 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 is thereafter bound only by computer resources.

Remarks

The [!INCLUDEssDE] 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 following statement defines an external pool that restricts CPU usage to 75 percent. The statement also defines 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