create thread pool

Considerations for process mode

create thread pool is not supported in process mode.

Syntax

create thread pool pool_name with thread count = count
	[, pool description = description ]
	[idle timeout = time_period]
		[for instance inst_name | global ]

Parameters

pool_name – name of the pool you are creating.
thread count = count – number of threads in the pool. Must be greater than or equal to 1.
pool description = description – (Optional) 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. The default is 100 microseconds. 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 – Creates a thread pool named sales_pool with 10 threads:
```
create thread pool sales_pool with thread count = 10
```

Example 2 – Creates a thread pool named order_pool, which includes a description:

create thread pool order_pool with thread count = 10,
pool description = 'used for handling order entry users'

Example 3 – Creates a thread pool named order_pool with 2 threads and an idle timeout of 500 microseconds:
```
create thread pool order_pool with thread count = 2,
idle timeout = 500
```

Usage

Use sp_addexeclass to associate workload with a user-created thread pool.
The SAP ASE server must have a sufficient number of free engines to bring online all threads (specified by count) in an engine pool. The value for max online engines determines the total number of engines. The total number of active threads in all engine pools cannot exceed the value of max online engines.
pool_name cannot start with syb_, which is reserved for SAP-created thread pools.
You cannot use Transact-SQL variables as parameters to create thread pool.
A value of 0 for idle timeout 0 indicates that threads immediately go to sleep if they find no work.
A value of -1 for idle timeout 0 indicates that threads never go to sleep, and continue to consume CPU if no work is available.
You can issue create thread pool with execute immediate.

When using the for instance clause:

If you do not specify the for instance clause, create thread pool creates thread pools on all instances.
You can only create instance-specific pools from the same instance.
The instance-specific attribute has precedence over global attributes.
To create thread pools with the same name but with different attributes, create a thread pool on first instance, then use alter thread pool on the other instances.
Rename and description are global operations; for example, both pool_name and description are same for all instances.

Standards

ANSI SQL – Compliance level: Transact-SQL extension

Permissions

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

Setting	Description
Enabled	With granular permissions enabled, you must be a user with the `manage any thread pool` privilege.
Disabled	With granular permissions disabled, you must be a user with sa_role.

Auditing

Information	Values
Event	42
Audit option
Command or access audited
Information in `extrainfo`	Pool name and thread count