create thread pool

Creates a user-defined 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

Examples

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.

SettingDescription
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

InformationValues
Event

42

Audit option
Command or access audited
Information in extrainfo

Pool name and thread count

Related reference
alter thread pool
drop thread pool