Intelligent Query Processing: degree of parallelism feedback

October 20, 2022

Product
SQL

Part of the SQL Server 2022 blog series.

DOP inefficiencies are a constant challenge; Current DOP methods are inefficient

The degree of parallelism (DOP) with which a query is executed can greatly impact its performance. Any time a query is using parallelism, there is always the question of if it’s using the right amount of parallelism. Sometimes, if the degree of parallelism is too high, it can introduce inefficiencies into the query execution. If the degree of parallelism is too low, we may be missing out on some of the speed-up that parallelism can provide. Users can manually set a maximum degree of parallelism for a query or for a server using the MAXDOP setting or hint. However, it has been a constant challenge for users to manually determine and tweak the correct degree of parallelism for each query. At most, they would set the MAXDOP specifically when they notice a problem—they don’t typically try to determine the optimal degree of parallelism for each query in their workload.

Degree of parallelism feedback: feature overview

In SQL Server 2022, we introduced a new feature called DOP feedback. This feature will look at any parallel query and determine if it might perform better with a lower degree of parallelism than currently being used. For example, perhaps 16 threads will perform better than 20 if there are a lot of waits on other threads. It will test out the new degree of parallelism and, either decide that this was a good change and keep the 16 threads, or it will revert to previous levels of parallelism and go back to 20 threads. If the new degree of parallelism is good, then this optimization is persisted inside the query store and will be applied appropriately to a query for future executions.

**Figure 1:** DOP feedback reduces the degree of parallelism in a stepwise fashion, incrementally decreasing the degree of parallelism and verifying at each step.

DOP feedback never increases the degree of parallelism, at best, it will revert to a stable previous DOP, and it works incrementally, meaning instead of trying to drastically lower the degree of parallelism all at once, it will try a slightly lower degree of parallelism. Then if that’s good, it might try another slightly lower degree of parallelism. If the new, even lower degree of parallelism is good, it might try to reduce again down to the degree of parallelism of two, although it will not make a parallel plan become serial. If the new, lower DOP is not as good, we go back to the previous known good DOP and keep the query at that level.

Example

A query is compiled with a degree of parallelism of 32. This means that the query will split off 32 different threads to execute the query. If DOP feedback detects a fair amount of wait times between threads and CPU overhead, it will suggest a lower DOP—say, 20. On the next execution, the query will execute with a DOP of 20. If the performance is better over the next several executions, the DOP of 20 will be considered stabilized. However, DOP feedback may then determine that there are still too many waits and further attempt a DOP of 16. Again, several executions are used to verify the feedback. Then, perhaps, a DOP of 8 is tried. If after several executions the DOP 8 performance is not better, then the system will return to suggesting a DOP of 16 as the most recent, stable, and verified DOP.

Simple setup and easy optimization

With this feature enabled, all of this is done without triggering query recompiles, and without user action.

DOP feedback for SQL 22 addresses a long-held challenge for our customers: finding the right degree of parallelism for each query without having to manually test and tweak each query for optimal performance. This is one of a handful of features in Intelligent Query processing that will provide customers with the best performance and a low-touch to no-touch experience. You can read more about other similar features in the SQL 2022 blog series.