SQL Server 2022 Degree of Parallelism (DOP) Feedback

The definitive solution to the MAXDOP configuration!

Photo by Caspar Camille Rubin on Unsplash

Degree of parallelism (DOP) feedback is one of the new features of SQL Server 2022 and it is part of the Intelligent Query Processing (IQP) family features. These intelligent and adaptive features can improve the performance of existing workloads without changes to the application code. In this article I will talk about the MAXDOP option and how the DOP feedback allows you to optimize the use of resources and improve the scalability of workloads, when excessive parallelism can cause performance issues especially for the OLTP-centric queries.

Degree of parallelism feedback

DOP feedback in SQL Server 2022 further enhances Intelligent Query Processing by addressing the scenario that occurs when an OLTP query is repeatedly executed in parallel mode and performance issues are encountered. Parallelism in a query execution plan is often useful for analytic queries or for queries that handle large amounts of data. Anyway, OLTP-centric queries that are executed in parallel mode may encounter performance issues when the time spent coordinating all threads outweighs the advantages of using a parallel plan.

Before SQL Server 2019, the default value for the max degree of parallelism (MAXDOP) server configuration option and for MAXDOP database scoped configuration was set to zero (0). The value zero (0) tells the SQL Server Engine to use all available schedulers when processing a query with a parallel execution plan. SQL Server 2019 changes the MAXDOP default settings. The default for server configuration option in the on-premises instances is set with the result of a calculation based on the processors detected at the time of installation. The MAXDOP database scoped configuration changed to a default of 8 for Azure SQL Database. For more details on configuring the MAXDOP option, see the article Configure the max degree of parallelism Server Configuration Option.

The goal of the new DOP feedback function is to self-adjust the MAXDOP option to avoid the problems described above by eliminating the difficulties due to the determination of an all-encompassing default setting of MAXDOP and manual modification on each query.

How does DOP feedback work?

DOP feedback will identify for you the inefficiencies due to parallelism for repeated queries, based on query elapsed time and encountered wait types (the total elapsed time for each query is adjusted by ignoring Buffer Latch, Buffer IO, and Network IO waits which are external to the parallel query execution). If the use of parallelism is deemed inefficient, DOP Feedback will reduce the degree of parallelism in the next execution of the query. The mechanism will be triggered whatever the configured degree of parallelism. The elapsed time and the types of waiting detected with the new configuration will be compared with the previous ones, determining if there has been an improvement.

To evaluate the suitability of a query to be treated with DOP feedback, the query elapsed time is measured on some executions of the query itself. The goal is to increase overall concurrency and to reduce waits, even if it slightly increases query elapsed time. Only verified feedbacks will be kept.

If the correction on the degree of parallelism will produce a performance regression, the degree of parallelism will be reverted to the last known valid value for the query. In this context, even a query canceled by the user will be perceived as a regression. DOP feedback will not recompile execution plans.

A stable correction of the degree of parallelism is checked at each recompilation of the execution plan and can may readjust back up or be revised downwards again, however never above the MAXDOP setting (including MAXDOP hints). For more information, watch the video Azure SQL & SQL Server 2022: Intelligent Database Futures:

To enable DOP feedback at the database level, use the ALTER DATABASE command and set the DOP_FEEDBACK option to ON as shown in the following T-SQL code.

-- Enable DOP feedback at the database level
ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = ON;
GO

To disable DOP feedback at the database level, set the DOP_FEEDBACK option to OFF as shown in the following T-SQL code.

-- Disable DOP feedback at the database level
ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = OFF;
GO

To disable DOP feedback at the query level, use the DISABLE_DOP_FEEDBACK query hint.

If you have forced the execution plan for a query through the Query Store, DOP feedback can still be used to optimize the degree of parallelism of the query.

If you have used the MAXDOP query hint directly in the query code or through Query Store suggestions and the MAXDOP hint is greater than 2, DOP feedback can further lower the degree of parallelism using the new limit suggested in the manner described above. For additional details you can consult the articles Hints (Transact-SQL) — Query and Query Store hints (preview).

It is possible to monitor the functioning of DOP feedback both through 4 new Extended Events and through the sys.query_store_plan_feedback system view. Below are the 4 new Extended Events:

• dop_feedback_eligible_query: Occurs when the query plan becomes eligible for DOP feedback
• dop_feedback_provided: Occurs when the query plan becomes eligible for DOP feedback
• dop_feedback_validation: Occurs when validation occurs for the query runtime stats against a baseline or previous feedback stats
• dop_feedback_reverted: Occurs when a DOP feedback is reverted

Summary

Degree of parallelism (DOP) feedback is one of the new features of SQL Server 2022 and it is part of the Intelligent Query Processing (IQP) family features, it is able to self-adjust the MAXDOP option to avoid performance problems due to the use of parallelism in OLTP queries performed repeatedly.

Enjoy SQL Server 2022 DOP feedback!