The following examples show the output of the set sort_resources command.
This example shows how Adaptive Server performs parallel sorting for a create index command on an unpartitioned table. Pertinent details for the example are:
The default segment spans 4 database devices.
max parallel degree is set to 20 worker processes.
number of sort buffers is set to the default, 500 buffers.
The following commands set sort_resources on and issue a create index command on the orders table:
set sort_resources on create index order_ix on orders (order_id)
Adaptive Server prints the following output:
The Create Index is done using Parallel Sort
Sort buffer size: 500
Parallel degree: 20
Number of output devices: 4
Number of producer threads: 1
Number of consumer threads: 4
The distribution map contains 3 element(s) for 4 partitions.
Partition Element: 1
458052
Partition Element: 2
909063
Partition Element: 3
1355747
Number of sampled records: 2418
In this example, the 4 devices on the default segment determine the number of consumer processes for the sort. Because the input table is not partitioned, the sort manager allocates 1 producer process, for a total degree of parallelism of 5.
The distribution map uses 3 dividing values for the 4 ranges. The lowest input values up to and including the value 458052 belong to the first range. Values greater than 458052 and less than or equal to 909063 belong to the second range. Values greater than 909063 and less than or equal to 1355747 belong to the third range. Values greater than 1355747 belong to the fourth range.
This example uses the same tables and devices as the first example. However, in this example, the input table is partitioned before creating the nonclustered index. The commands are:
set sort_resources on alter table orders partition 9 create index order_ix on orders (order_id)
In this case, the create index command under the sort_resources option prints the output:
The Create Index is done using Parallel Sort
Sort buffer size: 500
Parallel degree: 20
Number of output devices: 4
Number of producer threads: 9
Number of consumer threads: 4
The distribution map contains 3 element(s) for 4 partitions.
Partition Element: 1
458464
Partition Element: 2
892035
Partition Element: 3
1349187
Number of sampled records: 2448
Because the input table is now partitioned, the sort manager allocates 9 producer threads, for a total of 13 worker processes. The number of elements in the distribution map is the same, although the values differ slightly from those in the previous sort examples.
This example creates a clustered index on orders, specifying the segment name, order_seg.
set sort_resources on
alter table orders partition 9
create clustered index order_ix
on orders (order_id) on order_seg
Since the number of available worker processes is 20, this command can use 9 producers and 9 consumers, as shown in the output:
The Create Index is done using Parallel Sort Sort buffer size: 500 Parallel degree: 20 Number of output devices: 9 Number of producer threads: 9 Number of consumer threads: 9 The distribution map contains 8 element(s) for 9 partitions. Partition Element: 1 199141 Partition Element: 2 397543 Partition Element: 3 598758 Partition Element: 4 800484 Partition Element: 5 1010982 Partition Element: 6 1202471 Partition Element: 7 1397664 Partition Element: 8 1594563 Number of sampled records: 8055
This distribution map contains 8 elements for the 9 partitions on the table being sorted. The number of worker processes used is 18.
Create a clustered index first. Do not create nonclustered
indexes and then a clustered index. When you create a clustered
index all previous nonclustered index are rebuilt
For example, if only 10 worker processes had been available for this command, it could have succeeded using a single producer process to read the entire table. If fewer than 10 worker processes had been available, a warning message would be printed instead of the sort_resources output:
Msg 1538, Level 17, State 1: Server ’snipe’, Line 1: Parallel degree 8 is less than required parallel degree 10 to create clustered index on partition table. Change the parallel degree to required parallel degree and retry.
