Adaptive Server requires a minimum number of worker processes to perform a parallel sort. If additional worker processes are available, the sort can be performed more quickly. The minimum number required and the maximum number that can be used are determined by the number of:
Partitions on the table, for creating clustered indexes
Devices, for creating nonclustered indexes
Threads used to create the worktable and the number of devices in tempdb, for merge joins
Devices in tempdb, for other queries that require sorts
If the minimum number of worker processes is not available:
Sorts for clustered indexes on partitioned tables must be performed in parallel; the sort fails if not enough worker processes are available.
Sorts for nonclustered indexes and sorts for clustered indexes on unpartitioned tables can be performed in serial.
All sorts for queries can be performed in serial.
The availability of worker processes is determined by server-wide and session-wide limits. At the server level, the configuration parameters number of worker processes and max parallel degree limit the total size of the pool of worker processes and the maximum number that can be used by any create index or select command.
The available processes at runtime may be smaller than the configured value of max parallel degree or the session limit, due to other queries running in parallel. The decision on the number of worker processes to use for a sort is made by the sort manager, not by the optimizer. Since the sort manager makes this decision at runtime, parallel sort decisions are based on the actual number of worker processes available when the sort begins.
See “Controlling the degree of parallelism” for more information about controlling the server-wide and session-wide limits.
