alter thread pool

Description

Alters a thread pool.

Considerations for process mode

alter thread pool is not supported in process mode.

Syntax

alter thread pool pool_name with { pool name = “new_name”
	thread count = thread_count,
	[pool description = “description”]}
	[idle timeout = time_period]

Parameters

pool_name

name of the thread pool you are altering.

pool name = “new_name

new name for the pool you are altering.

thread count = thread_count

new number of threads in the thread pool. Must be greater than or equal to 1.

pool description = “description

describes the pool’s purpose. Must be fewer than 256 characters.

idle timeout = time_period

time, in microseconds, that threads look for work before going to sleep. A value of -1 means the threads never go to sleep, and continue to consume CPU if no work is available. A value of 0 indicates that threads immediately go to sleep if they find no work.

Examples

Example 1

Renames the order_pool thread pool to sales_pool:

alter thread pool order_pool with pool name = 'sales_pool'

Example 2

Modifies the sales_pool thread pool to contain 7 threads:

alter thread pool sales_pool with thread count = 7

Example 3

Modifies the name and description for sales_pool:

alter thread pool sales_pool with pool name = "larger_sales_pool", pool
    description = 'thread pool exclusive to sales group'

Example 4

Modify sales_pool so threads sleep if they find no work after 500 microseconds:

alter thread pool order_pool with idle timeout = 500

Example 5

Modify sales_pool so threads never go to sleep if they find no work:

alter thread pool order_pool with idle timeout = -1

Usage

Standards

ANSI SQL – Compliance level: Transact-SQL extension

Permissions

The permission checks for alter thread pool differ based on your granular permissions settings.

Granular permissions enabled

With granular permissions enabled, you must have the manage any thread pool privilege.

Granular permissions disabled

With granular permissions disabled, you must have sa_role.

alter thread pool permission is not included in the grant all command.

Auditing

Event

Audit option

Command or access audited

Information in extrainfo

143

The old name, new name, and thread count