SQL Server 2022 introduces a powerful new feature called Degree of Parallelism (DOP) feedback as part of the Intelligent Query Processing (IQP) family. This adaptive feature aims to enhance query performance without modifying application code. In this blog post, we will explore the MAXDOP option and how DOP feedback optimizes resource usage, improving workload scalability, especially for OLTP-centric queries that may face performance issues caused by excessive parallelism. I have prepared this note for one of my clients who regularly work with me on Comprehensive Database Performance Health Check and now wants to upgrade to SQL Server 2022.
Understanding Degree of Parallelism (DOP)
Parallelism is incredibly beneficial for reporting and analytical queries, as well as any query dealing with large volumes of data. However, executing OLTP-centric queries in parallel can sometimes lead to performance problems when the time spent coordinating multiple threads outweighs the advantages gained from using a parallel plan.
Historically, before SQL Server 2019, the default setting for the max degree of parallelism (MAXDOP) server configuration option and the MAXDOP database scoped configuration was set to zero (0). This meant that the SQL Server Engine would utilize all available schedulers when processing a query with a parallel execution plan. However, with SQL Server 2019, the MAXDOP default settings were modified based on the number of processors detected during installation. Nevertheless, determining an optimal MAXDOP setting and manually adjusting it for each query can still present challenges. This is where DOP feedback in SQL Server 2022 comes into play.
Solving Performance Issues with DOP Feedback
The Degree of Parallelism (DOP) feedback feature in SQL Server 2022 addresses a common problem in query performance optimization: determining the appropriate degree of parallelism for a given query. Excessive parallelism can lead to performance issues, particularly for OLTP-centric queries executed repeatedly. In scenarios where coordinating multiple threads takes longer than the benefits gained from parallelism, the overall query performance may suffer.
DOP feedback solves this challenge by automatically adjusting the MAXDOP option to an optimal level, eliminating the need for manual intervention to fine-tune the degree of parallelism for each query. This adaptive feature significantly improves query performance, prevents resource contention, and enables more efficient utilization of resources, resulting in better scalability for workloads.
How DOP Feedback Works
DOP feedback identifies inefficiencies in parallelism for repeated queries by analyzing the query’s elapsed time and encountered wait types (excluding Buffer Latch, Buffer IO, and Network IO waits). If the parallelism is deemed inefficient, DOP feedback reduces the degree of parallelism in the subsequent query execution. This mechanism is triggered regardless of the configured degree of parallelism. The new configuration’s elapsed time and wait types are then compared with the previous values to determine if any improvements have been achieved.
The goal of DOP feedback is to increase overall concurrency and reduce waits, even if it results in a slight increase in query elapsed time. Only verified feedback is retained.
If the adjusted DOP leads to a performance regression, DOP feedback reverts to the last known good DOP. Additionally, a user-canceled query is also considered a regression in this context. It is important to note that DOP feedback does not recompile plans.
Enabling and Disabling DOP Feedback
DOP feedback is, by default, enabled in SQL Server 2022 along with Query Store features. If your query store is disabled, DOP Feedback will give no results and remain ineffective.
First thing first you need to have your database compatibility to 2022 to use this feature.
To change compatibility at the database level, use the following T-SQL code:
USE [master] GO ALTER DATABASE [YourDB] SET COMPATIBILITY_LEVEL = 160 GO
To enable DOP feedback at the database level, use 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, use 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.
SELECT * FROM YourTableName OPTION (DISABLE_DOP_FEEDBACK);
To enable query store at the database level you can use the following script:
-- Enable Query Store at the database level USE [master] GO ALTER DATABASE [DbName] SET QUERY_STORE = ON GO ALTER DATABASE [DbName] SET QUERY_STORE (OPERATION_MODE = READ_WRITE) GO
Here are two important points to remember:
- Even if a query has a forced execution plan through Query Store, DOP feedback can still be used to optimize the degree of parallelism of the query.
- If a query uses the MAXDOP hint with a value greater than 2, DOP feedback can further lower the degree of parallelism using the new limit.
Monitoring DOP Feedback
DOP feedback can be monitored using extended events, or you can also monitor it with catalog view: sys.query_store_plan_feedback. Here is the script based on the catalog view I built for my client.
SELECT
qsq.query_id,
qsqt.query_sql_text,
qsp.query_plan,
qspf.feature_desc,
qspf.state_desc,
qspf.feedback_data,
qspf.create_time,
qspf.last_updated_time
FROM sys.query_store_query AS qsq
INNER JOIN sys.query_store_plan AS qsp
ON qsp.query_id = qsq.query_id
INNER JOIN sys.query_store_query_text AS qsqt
ON qsqt.query_text_id = qsq.query_text_id
INNER JOIN sys.query_store_plan_feedback AS qspf
ON qspf.plan_id = qsp.plan_id
WHERE qspf.feature_id = 3;
The query retrieves information about queries with Degree of Parallelism (DOP) feedback from the Query Store in SQL Server. It joins multiple system catalog views to obtain the query ID, query SQL text, query plan, feature description, state description, feedback data, create time, and last updated time. The WHERE clause filters for queries with a feature_id of 3, indicating DOP feedback.
Monitoring DOP Feedback: Key Considerations
When monitoring DOP feedback, keep the following considerations in mind:
Thresholds: Be aware of the thresholds that determine whether a query is eligible for DOP feedback. A query must meet certain criteria before it becomes eligible for DOP feedback.
False Positives: Be cautious of false positives when monitoring DOP feedback. If a query’s performance improves after adjusting the degree of parallelism, it may not always be due to the DOP feedback. External factors, such as underlying data or hardware changes, can also affect query performance.
Performance Regressions: Monitor for performance regressions after DOP feedback is applied. If regression occurs, DOP feedback will revert to the previous degree of parallelism. Investigate the cause of the regression and determine if it is related to the DOP feedback or other factors.
Query Store and MAXDOP Hints: Consider the interplay between DOP feedback, Query Store, and MAXDOP hints. If a query has a forced execution plan through Query Store or uses the MAXDOP hint directly in the query code, DOP feedback can still be used to optimize the degree of parallelism. Keep track of these settings when analyzing query performance.
Extended Events: Use the provided Extended Events to monitor DOP feedback-related activity. These events can help you understand when a query becomes eligible for DOP feedback, when feedback is provided, and when feedback is validated or reverted. This is another method besides using catalog views.
By considering these factors, you can effectively monitor DOP feedback and ensure that your SQL Server 2022 workloads are optimized for the best possible performance.
Summary
Degree of Parallelism (DOP) feedback in SQL Server 2022 is part of the Intelligent Query Processing (IQP) family features and is capable of self-adjusting the MAXDOP option to avoid performance problems due to excessive parallelism in repeated OLTP queries. I have recently started to work extensively with this feature, and I am very much looking forward to your feedback. Please leave a comment or subscribe to my YouTube Channel.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
