Like parallel query processing, parallel sorting requires more resources than performing the same command in parallel. Response time for creating the index or sorting query results improves, but the server performs more work due to overhead.
Adaptive Server’s sort manager determines whether the resources required to perform a sort operation in parallel are available, and also whether a serial or parallel sort should be performed, given the size of the table and other factors. For a parallel sort to be performed, certain criteria must be met:
The select into/bulk copy/pllsort database option must be set to true with sp_dboption in the target database:
For indexes, the option must be enabled in the database where the table resides. For creating a clustered index on a partitioned table, this option must be enabled, or the sort fails. For creating other indexes, serial sorts can be performed if parallel sorts cannot be performed.
For sorting worktables, this option must be on in tempdb. Serial sorts can be performed if parallel sorts cannot be performed.
Parallel sorts must have a minimum number of worker processes available. The number depends on the number of partitions on the table and/or the number of devices on the target segment. The degree of parallelism at the server and session level must be high enough for the sort to use at least the minimum number of worker processes required for a parallel sort. Clustered indexes on partitioned tables must be created in parallel; other sorts can be performed in serial if there are not enough worker processes available. “Worker process requirements for parallel sorts” and “Worker process requirements for select query sorts”.
For select commands that require sorting, and for creating nonclustered indexes, the table to be sorted must be at least eight times the size of the available sort buffers (the value of the number of sort buffers configuration parameter), or the sort will be performed in serial mode. This ensures that Adaptive Server does not perform parallel sorting on smaller tables that would not show significant improvements in performance. This rule does not apply to creating clustered indexes on partitioned tables, since this operation always requires a parallel sort.
For create index commands, the value of the number of sort buffers configuration parameter must be at least as large as the number of worker processes available for the parallel sort.
See “Sort buffer configuration guidelines”.
You cannot use the dump transaction command
after indexes are created using a parallel sort. You must dump the
database. Serial create index commands can be
recovered, but only by completely re-doing the indexing command,
which can greatly lengthen recovery time. Performing database dumps
after serial create indexes is recommended to speed recovery, although
it is not required in order to use dump transaction.
