alter thread pool

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]
		[for instance inst_name | global ]

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.
for instance [inst_name | global] – is name of the instance, or global for all instances.

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 the SAP ASE 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

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

Setting	Description
Enabled	With granular permissions enabled, you must have the `manage any thread pool` privilege.
Disabled	With granular permissions disabled, you must have sa_role. alter thread pool permission is not included in the grant all command.

Auditing

Information	Values
Event	143
Audit option
Command or access audited
Information in `extrainfo`	The old name, new name, and thread count