create thread pool

Creates a user-defined thread pool.

create thread pool is not supported in process mode.

create thread pool pool_name with thread count = count
[, pool description = description ]
[idle timeout = time_period]

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.

Creates a thread pool named sales_pool with 10 threads:

create thread pool sales_pool with thread count = 10

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'

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

Use sp_addexeclass to associate workload with a user-created thread pool.
Adaptive 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 Sybase-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.

ANSI SQL – Compliance level: Transact-SQL extension

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

Granular permissions enabled	With granular permissions enabled, you must be a user with the manage any thread pool privilege.
Granular permissions disabled	With granular permissions disabled, you must be a user with sa_role.

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