Adaptive Server must dynamically repartition intermediate data to match the partitioning scheme of another operand or to perform an efficient partition elimination. max repartition degree controls the amount of dynamic repartitioning Adaptive Server can do. If the value of max repartition degree is too high, the number of intermediate partitions becomes too large and the system becomes flooded with worker processes that compete for resources, which eventually degrades performance. The value for max repartition degree enforces the maximum number of partitions created for any intermediate data. Repartitioning is a CPU-intensive operation. The value of max repartition degree should not exceed the total number of Adaptive Server engines.
If all tables and indexes are unpartitioned, Adaptive Server uses the value for max repartition degree to provide the number of partitions to create as a result of repartitioning the data. When the value is set to 1, which is the default case, the value of max repartition degree is set to the number of online engines.
Use max repartition degree when using the force option to perform a parallel scan on a table or index.
select * from customers (parallel)
For example, if the customers table is unpartitioned and the force option is used, Adaptive Server tries to find the inherent partitioning degree of that table or index, which in this case is 1. It uses the number of engines configured for the server, or whatever degree is best based on the number of pages in the table or index that does not exceed the value of max repartition degree.
To set max repartition degree to 5:
sp_configure "max repartition degree", 5
