Summary information |
|
|---|---|
Default value |
1 |
Range of values |
1–255 |
Status |
Dynamic |
Display level |
Basic |
Required role |
System administrator |
Configuration group |
Query Tuning |
max parallel degree specifies the server-wide maximum number of worker processes allowed per query. This is called the “maximum degree of parallelism.”
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:
Adaptive Server scans all tables or indexes serially.
Adaptive Server forces serial query execution and the optimizer may select plans with a higher parallel degree than if it is disabled.
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 Chapter 9, “Parallel Sorting” in the Performance and Tuning Series: Query Processing and Abstract Plans.
