Examples

The following examples show the output of the set sort_resources command.


Nonclustered index on a nonpartitioned table

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 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.


Nonclustered index on a partitioned table

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.


Clustered index on partitioned table executed in parallel

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.

NoteCreate 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


Sort failure

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.