AzureFeeds

🚀 Introduction

With SQL Server 2025, we have made Degree of parallelism (DOP) feedback and on by default feature. Originally introduced in SQL Server 2022, DOP feedback is now a core part of the platform’s self-tuning capabilities, helping workloads scale more efficiently without manual tuning.

 

⚙️ What Is DOP feedback?

DOP feedback is part of the Intelligent Query Processing (IQP) family of features. It dynamically adjusts the number of threads (DOP) used by a query based on runtime performance metrics like CPU time and elapsed time. If a query that has generated a parallel plan consistently underperforms due to excessive parallelism, the DOP feedback feature will reduce the DOP for future executions without requiring recompilation. Currently, DOP feedback will only recommend reductions to the degree of parallelism setting on a per query basis.

This feedback loop is:

• Persistent: Stored in Query Store. 
  
  
  
  • Persistence is not currently available for Query Store on readable secondaries. At the time in which this blog post will be published, DOP feedback on readable secondaries as a feature is not available. That may change in the near future, and we’ll provide an update to it’s status after that occurs.
  
  
  
  


• Adaptive: Adjusts a query’s DOP, monitors those adjustments, and reverts any changes to a previous DOP if performance regresses. This part of the system relies on Query Store being enabled as it relies on the runtime statistics captured within the Query Store.


• Scoped: Controlled via the DOP_FEEDBACK database-scoped configuration or at the individual query level with the use of the DISABLE_DOP_FEEDBACK query hint.

 

🧪 How It Works

1. Initial Execution: SQL Server compiles and executes a query with a default or manually set DOP.


2. Monitoring: Runtime stats are collected and compared across executions.


3. Adjustment: If inefficiencies are detected, DOP is lowered (minimum of 2).


4. Validation: If performance improves and is stable, the new DOP is persisted. If not, the DOP recommendation will be reverted to the previously known good DOP setting, which is typically the original setting that the feature used as a baseline.

At the end of the validation period any feedback that has been persisted, regardless of its state (i.e. stabilized, reverted, no recommendation, etc.) can be viewed by querying the sys.query_store_plan_feedback system catalog view:

SELECT
qspf.feature_desc,
qsq.query_id,
qsp.plan_id,
qspf.plan_feedback_id,
qsqt.query_sql_text,
qsp.query_plan,
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;

 

🆕 What’s New in SQL Server 2025?

• Enabled by Default: No need to toggle the database scoped configuration on, DOP feedback is active out of the box.


• Improved Stability: Enhanced validation logic ensures fewer regressions.


• Better Integration: Works seamlessly with other IQP features like Memory Grant Feedback and Parameter Sensitive Plan (PSP) optimization.


• Persistence: Feedback survives restarts and failovers thanks to Query Store enhancements.

 

📊 Visualizing the Feedback Loop

 

 

 

🧩 How can I see if DOP Feedback is something that would be beneficial for me?

Without setting up an Extended Event session for deeper analysis, looking over some of the data in the Query Store can be useful in determining if DOP feedback would find interesting enough queries for it to engage. At a minimum, if your SQL Server instance is operating with parallelism enabled and has:

o   a MAXDOP value of 0 (not generally recommended) or a MAXDOP value greater than 2

o   you observe multiple queries have execution runtimes of 10 seconds or more along with a degree of parallelism of 4 or greater

o   and have an execution count 15 or more according to the output from the query below  

SELECT TOP 20
plan_id, AVG(avg_dop) as dop, SUM(count_executions) as execution_count,
AVG(avg_duration)/1000000.0 as duration_in_seconds, MIN(min_duration)/1000000.0 as min_duration_in_seconds
FROM sys.query_store_runtime_stats
GROUP BY plan_id
ORDER BY dop desc, execution_count desc;

 

🧭 Best Practices

• Enable Query Store: This is required for DOP Feedback to function.


• Monitor feedback Extended events: Use Extended events like:




• dop_feedback_eligible_query


• dop_feedback_provided


• dop_feedback_analysis_stopped


• dop_feedback_stabilized


• dop_feedback_reassessment_failed


• dop_feedback_reverted

 

🧠 Behind the Scenes: How Feedback Is Evaluated

DOP feedback uses a rolling window of recent executions (typically 15) to evaluate:

• Average CPU time


• Standard deviation of CPU time


• Adjusted elapsed time


• Stability of performance across executions

If the adjusted DOP consistently improves efficiency without regressing performance, it is persisted. Otherwise, the system reverts to the last known good configuration (also knows as the default dop to the system). As an example, if the dop for a query started out with a value of 8, and DOP feedback determined that a DOP of 4 was an optimal number; if over the period of the rolling window and while the query is in the validation phase, if the query performance varied more than expected, DOP feedback will undo it’s change of 4 and set the query back to having a DOP of 8.

 

📚 Learn More

• https://learn.microsoft.com/sql/relational-databases/performance/intelligent-query-processing-degree-parallelism-feedback


• https://learn.microsoft.com/sql/relational-databases/performance/intelligent-query-processing


• https://www.microsoft.com/sql-server