Skip to content

Latest commit

 

History

History
156 lines (128 loc) · 7.61 KB

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

File metadata and controls

156 lines (128 loc) · 7.61 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
ALTER EXTERNAL RESOURCE POOL (Transact-SQL)
ALTER EXTERNAL RESOURCE POOL (Transact-SQL)
VanMSFT
vanto
08/06/2020
sql
machine-learning-services
reference
ALTER_EXTERNAL_RESOURCE_POOL_TSQL
ALTER EXTERNAL RESOURCE POOL statement
TSQL
>=sql-server-2016||>=sql-server-linux-ver15

ALTER EXTERNAL RESOURCE POOL (Transact-SQL)

[!INCLUDE SQL Server 2016 and later]

Changes a Resource Governor external pool that specifies resources that can be used by external processes.

::: moniker range="=sql-server-2016" 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||>=sql-server-linux-ver15" For [!INCLUDErsql-productnamenew-md], the external pool governs rterm.exe, python.exe, BxlServer.exe, and other processes spawned by them. ::: moniker-end

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

::: moniker range=">=sql-server-ver15||>=sql-server-linux-ver15"

ALTER EXTERNAL RESOURCE POOL { pool_name | "default" }
[ WITH (
    [ MAX_CPU_PERCENT = value ]
    [ [ , ] MAX_MEMORY_PERCENT = value ]
    [ [ , ] MAX_PROCESSES = value ]
    )
]
[ ; ]
  
<CPU_range_spec> ::=
{ CPU_ID | CPU_ID  TO CPU_ID } [ ,...n ]

::: moniker-end ::: moniker range="=sql-server-2016||=sql-server-2017"


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 ]

::: moniker-end

[!INCLUDEsql-server-tsql-previous-offline-documentation]

Arguments

{ pool_name | "default" }
Is the name of an existing user-defined external resource pool or the default external resource pool that is created when [!INCLUDEssNoVersion] 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.

::: moniker range=">=sql-server-ver15||>=sql-server-linux-ver15" MAX_CPU_PERCENT =value
Specifies the maximum average CPU bandwidth that all requests in the external resource pool can receive when there is 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
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. ::: moniker-end

::: moniker range="=sql-server-2016||=sql-server-2017" MAX_CPU_PERCENT =value
Specifies the maximum average CPU bandwidth that all requests in the external resource pool can receive when there is 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. ::: moniker-end

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 the use of external resource pools to govern machine learning jobs, see Resource governance for machine learning in SQL Server...

Permissions

Requires CONTROL SERVER permission.

Examples

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. ::: moniker range=">=sql-server-ver15||>=sql-server-linux-ver15"

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

::: moniker-end

::: moniker range="=sql-server-2016||=sql-server-2017"

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

::: moniker-end

See also