Smarter Parallelism: Degree of parallelism feedback in SQL Server 2025

🚀 Introduction

With SQL Server 2025, we have made Degree of parallelism (DOP) feedback an 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. The feature works with database compatibility 160 or higher.

⚙️ 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 plan basis. The Query Store must be enabled for every database where DOP feedback is used, and be in a "read write" state.

This feedback loop is:

• Persistent: Stored in Query Store. 
  • Persistence is not currently available for Query Store on readable secondaries. This is subject to 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 , Cardinality Estimation feedback, and Parameter Sensitive Plan (PSP) optimization.

📊 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
    qsq.query_id, qsrs.plan_id,
    [replica_type] = 
        CASE
            WHEN replica_group_id = '1' THEN 'PRIMARY'
            WHEN replica_group_id = '2' THEN 'SECONDARY'
            WHEN replica_group_id = '3' THEN 'GEO SECONDARY'
            WHEN replica_group_id = '4' THEN 'GEO HA SECONDARY'
            ELSE TRY_CONVERT(NVARCHAR (200), qsrs.replica_group_id)
        END,
    AVG(qsrs.avg_dop) as dop, 
    SUM(qsrs.count_executions) as execution_count,
    AVG(qsrs.avg_duration)/1000000.0 as duration_in_seconds, 
    MIN(qsrs.min_duration)/1000000.0 as min_duration_in_seconds
FROM sys.query_store_runtime_stats qsrs
    INNER JOIN sys.query_store_plan qsp ON qsp.plan_id = qsrs.plan_id
    INNER JOIN sys.query_store_query qsq ON qsq.query_id = qsp.query_id
GROUP BY qsrs.plan_id, qsq.query_id, qsrs.replica_group_id
ORDER BY dop desc, execution_count desc;

🧠 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.

🧠 Note: The adjusted elapsed time intentionally excludes wait statistics that are not relevant to parallelism efficiency. This includes ignoring buffer latch, buffer I/O, and network I/O waits, which are external to parallel query execution. This ensures that feedback decisions are based solely on CPU and execution efficiency, not external factors like I/O or network latency.

🧭 Best Practices

• Enable Query Store: This is required for DOP feedback to function.
• Monitor DOP feedback extended events
  • SQL Server provides a set of extended events to help you monitor and troubleshoot the DOP feedback lifecycle. Below is a sample script to create a session that captures key events, followed by a breakdown of what each event means.

IF EXISTS (SELECT *
FROM sys.server_event_sessions
WHERE name = 'dop_xevents')
    DROP EVENT SESSION [dop_xevents] ON SERVER;
GO
CREATE EVENT SESSION [dop_xevents] ON SERVER 
    ADD EVENT sqlserver.dop_feedback_analysis_stopped,
    ADD EVENT sqlserver.dop_feedback_eligible_query,
    ADD EVENT sqlserver.dop_feedback_provided,
    ADD EVENT sqlserver.dop_feedback_reassessment_failed,
    ADD EVENT sqlserver.dop_feedback_reverted,
    ADD EVENT sqlserver.dop_feedback_stabilized
--  ADD EVENT sqlserver.dop_feedback_validation
WITH (
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0 KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = OFF
);

⚠️ Note: The extended event that has been commented out (dop_feedback_validation) is part of the debug channel. Enabling it may introduce additional overhead and should be used with caution in production environments.

📋 DOP Feedback Extended Events Reference

🔍 Understanding the feedback_data JSON in DOP feedback

In the "How it works" section of this article, we had provided a sample script that showed some of the data that can be persisted within the sys.query_store_plan_feedback catalog view. When DOP feedback stabilizes, SQL Server stores a JSON payload in the feedback_data column of that view, figuring out how to interpret that data can sometimes be challenging. From a structural perspective, the feedback_data field contains a JSON object with two main sections; LastGoodFeedback and BaselineStats. As an example

{
  "LastGoodFeedback": {
    "dop": "2",
    "avg_cpu_time_ms": "12401",
    "avg_adj_elapsed_time_ms": "12056",
    "std_cpu_time_ms": "380",
    "std_adj_elapsed_time_ms": "342"
  },
  "BaselineStats": {
    "dop": "4",
    "avg_cpu_time_ms": "17843",
    "avg_adj_elapsed_time_ms": "13468",
    "std_cpu_time_ms": "333",
    "std_adj_elapsed_time_ms": "328"
  }
}

One method that can be used to extract this data could be to utilize the JSON_VALUE function:

SELECT
    qspf.plan_id,
    qs.query_id,
    qt.query_sql_text,
    qsp.query_plan_hash,
    qspf.feature_desc,
    -- LastGoodFeedback metrics
    JSON_VALUE(qspf.feedback_data, '$.LastGoodFeedback.dop') AS last_good_dop,
    JSON_VALUE(qspf.feedback_data, '$.LastGoodFeedback.avg_cpu_time_ms') AS last_good_avg_cpu_time_ms,
    JSON_VALUE(qspf.feedback_data, '$.LastGoodFeedback.avg_adj_elapsed_time_ms') AS last_good_avg_adj_elapsed_time_ms,
    JSON_VALUE(qspf.feedback_data, '$.LastGoodFeedback.std_cpu_time_ms') AS last_good_std_cpu_time_ms,
    JSON_VALUE(qspf.feedback_data, '$.LastGoodFeedback.std_adj_elapsed_time_ms') AS last_good_std_adj_elapsed_time_ms,
    -- BaselineStats metrics
    JSON_VALUE(qspf.feedback_data, '$.BaselineStats.dop') AS baseline_dop,
    JSON_VALUE(qspf.feedback_data, '$.BaselineStats.avg_cpu_time_ms') AS baseline_avg_cpu_time_ms,
    JSON_VALUE(qspf.feedback_data, '$.BaselineStats.avg_adj_elapsed_time_ms') AS baseline_avg_adj_elapsed_time_ms,
    JSON_VALUE(qspf.feedback_data, '$.BaselineStats.std_cpu_time_ms') AS baseline_std_cpu_time_ms,
    JSON_VALUE(qspf.feedback_data, '$.BaselineStats.std_adj_elapsed_time_ms') AS baseline_std_adj_elapsed_time_ms
FROM sys.query_store_plan_feedback AS qspf
    JOIN sys.query_store_plan AS qsp
    ON qspf.plan_id = qsp.plan_id
    JOIN sys.query_store_query AS qs
    ON qsp.query_id = qs.query_id
    JOIN sys.query_store_query_text AS qt
    ON qs.query_text_id = qt.query_text_id
WHERE qspf.feature_desc = 'DOP Feedback'
    AND ISJSON(qspf.feedback_data) = 1;

🧪 Why This Matters

This JSON structure is critical for:

• Debugging regressions: You can compare baseline and feedback statistics to understand if a change in DOP helped or hurt a set of queries.
• Telemetry and tuning: Tools can be used to parse this JSON payload to surface insights in performance dashboards.
• Transparency: It provides folks that care about the database visibility into how SQL Server is adapting to their workload.

📚 Learn More

• Intelligent Query Processing: degree of parallelism feedback
• Degree of parallelism (DOP) feedback
• Intelligent query processing in SQL databases
• Microsoft SQL Server