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

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

• thread_count must be greater than or equal to 1.

• When you reduce a thread count, the thread pool you specify must wait for currently running tasks to yield before it reduces the number of threads, which may cause a slight delay in Adaptive Server shrinking the pool.

• You cannot rename system-created thread pools (which begin with syb_). However, you can use alter thread pool to change the number of threads or idle timeout in system-created thread pools.

• You cannot use Transact-SQL variables as parameters with alter thread pool.

• You can set idle timeout only for engine thread pools.

• You may issue alter thread pool with execute immediate.

Standards

ANSI SQL – Compliance level: Transact-SQL extension

Permissions

alter thread pool permission defaults to the system administrator. alter thread pool permission is not included in the grant all command.

Auditing