You might configure number of worker processes to 50 to allow multiple parallel queries to operate at the same time. If the table with the largest number of partitions has 10 partitions, you might set max parallel degree to 10, limiting all select queries to a maximum of 10 worker processes. Since hash-based scans operate best with 2–3 worker processes, max scan parallel degree could be set to 3.
For a single-table query, or a join involving serial access on other tables, some of the parallel possibilities allowed by these values are:
Parallel partition scans on any tables with 2–10 partitions
Hash-based table scans with up to 3 worker processes
Hash-based nonclustered index scans on tables with nonclustered indexes, with up to 3 worker processes
For nested-loop joins where parallel methods are used on more than one table, some possible parallel choices are:
Joins using a hash-based scan on one table and partitioned-based scans on tables with 2 or 3 partitions
Joins using partition- based scans on both tables. For example:
A parallel degree of 3 for a partitioned table multiplied by max scan parallel degree of 3 for a hash-based scan requires 9 worker processes.
A table with 2 partitions and a table with 5 partitions requires 10 worker processes for partition-based scans on both tables.
Tables with 4–10 partitions can be involved in a join, with one or more tables accessed in serial.
For merge joins:
For a full-merge join, 10 worker processes scan the base tables (unless these are fewer than 10 distinct values on the join keys); the number of partitions on the tables is not considered.
For a merge join that scans a table and selects rows into a worktable:
The scan that precedes the merge join may be performed in serial or in parallel. The degree of parallelism is determined in the usual way for such a query.
For the merge, 10 worker processes are used unless there are fewer distinct values in the join key.
For the sort, up to 10 worker processes can be used.
For fast performance, while creating a clustered index on a table with 10 partitions, the setting of 50 for number of worker processes allows you to set max parallel degree to 20 for the create index command.
For more information on configuring worker processes for sorting, see “Worker process requirements for parallel sorts”.
