max parallel degree

max parallel degree specifies the server-wide maximum number of worker processes allowed per query. This is called the “maximum degree of parallelism.”

Summary Information

Default value

1

Range of values

1–255

Status

Dynamic

Display level

Basic

Required role

System administrator

Configuration group

Query Tuning

If max parallel degree is too low, the performance gain for a given query may not be as significant as possible; if max parallel degree is too high, the server may compile plans that require more processes than are actually available at execution time, or the system may become saturated, resulting in decreased throughput. To enable parallel partition scans, set this parameter to be equal to or greater than the number of partitions in the table you are querying.

The value of this parameter must be less than or equal to the current value of number of worker processes.

If you set max parallel degree to 1:

Changing max parallel degree causes all query plans in the procedure cache to be invalidated, and new plans are compiled the next time you execute a stored procedure or trigger.

See Performance and Tuning Series: Query Processing and Abstract Plans > Parallel Sorting