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 problemthey 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.
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.
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 DOPsay, 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.
- Download the preview of SQL Server 2022.
- Learn how to configure the max degree of parallelism Server Configuration Option.
- See what’s new in SQL Server 2022.
The post Intelligent Query Processing: degree of parallelism feedback appeared first on Microsoft SQL Server Blog.