Assume that max parallel degree is set to 10 worker processes and max scan parallel degree is set to 3 worker processes.
For a single-table query on a heap table with 6 partitions and no useful nonclustered index, the optimizer costs the following access methods:
A parallel partition scan using 6 worker processes
A serial table scan using a single process
If max parallel degree is set to 5 worker processes, then the optimizer does not consider the partition scan for a table with 6 partitions.
The situation changes if the query involves a join. If max parallel degree is set to 10 worker processes, the query involves a join, and a table with 6 partitions is the outer table in the query, then the optimizer considers the following access methods:
A partition scan using 6 worker processes
A hash-based table scan using 3 worker processes
A merge join using 10 worker processes
A serial scan using a single process
If max scan parallel degree is set to 5 and max scan parallel degree is set to 3, then the optimizer considers the following access methods:
A hash-based table scan using 3 worker processes
A merge join using 5 worker processes
A serial scan using a single process
Finally, if max parallel degree is set to 5 and max scan parallel degree is set to 1, then the optimizer considers only a merge join as a parallel access method.
