Smarter Parallelism in SQL Server 2025: DOP Feedback Explained

---

Parallelism in SQL Server is powerful when queries genuinely benefit from splitting work across multiple CPU threads. It becomes a problem when queries are over-parallelized: CXPACKET and CXCONSUMER waits accumulate, CPU is consumed coordinating threads rather than doing work, and other queries on the server suffer from thread starvation.

The traditional fix is manual: adjust MAXDOP, tune cost threshold for parallelism, and use query hints on specific problem queries. SQL Server 2025 adds an automatic layer on top of that. Degree of Parallelism Feedback observes each query’s runtime behavior, detects over-parallelization, and progressively reduces the DOP for that specific query’s plan. The decision is stored in Query Store so it persists across restarts and applies to future executions automatically.

1 What DOP Feedback Does Beginner

DOP Feedback automatically adjusts the number of parallel threads used by a specific query based on real runtime performance data. When SQL Server detects that a query is consistently over-parallelized, it reduces the effective DOP for that plan without changing any global server settings and without requiring a query hint to be added to the code.

Three properties define how DOP Feedback behaves:

• Persistent. Feedback decisions are stored in Query Store. SQL Server remembers them across restarts and applies them to future executions of the same plan.
• Adaptive. If reducing DOP improves performance consistently, the new DOP becomes the baseline. If performance varies or regresses, SQL Server rolls back the adjustment to the original DOP.
• Scoped. The adjustment applies only to the specific plan for the specific query. Other queries and global MAXDOP settings are unaffected.

2 How the Feedback Loop Works Beginner

The feedback cycle runs automatically in the background while the workload executes. No configuration is required beyond ensuring Query Store is enabled and the database is at compatibility level 160 or higher.

1. SQL Server compiles and executes the query with the current DOP based on the global MAXDOP setting and cost threshold.
2. Runtime statistics are recorded in Query Store: CPU time, elapsed time, standard deviation, and adjusted elapsed time that excludes latch waits, buffer I/O, and network delays.
3. SQL Server evaluates whether the query is over-parallelized. The signal is high CPU coordination overhead relative to the work being done, combined with stable or worsening elapsed time compared to what a lower DOP would achieve.
4. SQL Server lowers the effective DOP for that plan, with a minimum DOP of 2. The adjusted DOP is applied to subsequent executions.
5. If performance stabilizes at the new DOP, the feedback is accepted and persisted. If performance varies or worsens, SQL Server reverts to the original DOP baseline.

Decision Metric	What SQL Server Measures
Average CPU time	Total CPU cost at current DOP
Standard deviation	Consistency of performance across executions
Adjusted elapsed time	Elapsed time excluding waits unrelated to parallelism
Stability window	Performance measured over a rolling window of executions before accepting feedback

3 What Is New in SQL Server 2025 Beginner

DOP Feedback was introduced in SQL Server 2022 as an opt-in feature requiring a database scoped configuration setting. SQL Server 2025 promotes it to default behavior and strengthens the underlying implementation in three areas.

• Enabled by default. No database scoped configuration or trace flag is required. DOP Feedback activates automatically for any database at compatibility level 160 or higher with Query Store enabled in read-write mode.
• Improved validation logic. The 2022 implementation occasionally produced oscillation, where DOP was reduced and then restored repeatedly without stabilizing. The 2025 version has stronger acceptance criteria that reduce false regressions and unnecessary oscillation.
• Better integration with other IQP features. DOP Feedback now coordinates more effectively with Memory Grant Feedback, Parameter Sensitive Plan optimization, and Cardinality Estimation Feedback. The features share runtime signals through Query Store without conflicting with each other.

4 Identifying Candidates for DOP Feedback Intermediate

DOP Feedback operates automatically, but understanding which queries are likely to benefit helps focus manual monitoring and validation. Candidates have high execution counts, long average duration, and significant parallelism.

-- Find queries that are likely candidates for DOP Feedback adjustment
-- Filters for parallel plans running 10+ seconds with execution count above 15
SELECT TOP 20
    qsq.query_id,
    qsrs.plan_id,
    CASE qsrs.replica_group_id
        WHEN '1' THEN 'PRIMARY'
        WHEN '2' THEN 'SECONDARY'
        WHEN '3' THEN 'GEO SECONDARY'
        WHEN '4' THEN 'GEO HA SECONDARY'
        ELSE TRY_CONVERT(NVARCHAR(200), qsrs.replica_group_id)
    END                                             AS ReplicaType,
    AVG(qsrs.avg_dop)                               AS AvgDOP,
    SUM(qsrs.count_executions)                      AS ExecutionCount,
    AVG(qsrs.avg_duration) / 1000000.0              AS AvgDurationSec,
    MIN(qsrs.min_duration) / 1000000.0              AS MinDurationSec
FROM sys.query_store_runtime_stats                  qsrs
JOIN sys.query_store_plan                           qsp
    ON qsp.plan_id = qsrs.plan_id
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
HAVING MIN(qsrs.min_duration) / 1000000.0 >= 10    -- 10 seconds or longer
   AND AVG(qsrs.avg_dop) >= 4                      -- DOP 4 or higher
   AND SUM(qsrs.count_executions) >= 15            -- at least 15 executions
ORDER BY AvgDOP DESC, ExecutionCount DESC;

5 Viewing Persisted Feedback Intermediate

All accepted DOP Feedback decisions are stored in sys.query_store_plan_feedback. This view shows the state of each feedback decision, when it was created, and when it was last updated. The feature_id = 3 filter isolates DOP Feedback from other IQP feedback types stored in the same table.

-- View all persisted DOP Feedback decisions in the current database
SELECT
    qspf.feature_desc,
    qsq.query_id,
    qsp.plan_id,
    qspf.plan_feedback_id,
    LEFT(qsqt.query_sql_text, 300)                  AS QueryText,
    qspf.state_desc,
    qspf.create_time,
    qspf.last_updated_time,
    qspf.feedback_data
FROM sys.query_store_query                          qsq
JOIN sys.query_store_plan                           qsp
    ON qsp.query_id = qsq.query_id
JOIN sys.query_store_query_text                     qsqt
    ON qsqt.query_text_id = qsq.query_text_id
JOIN sys.query_store_plan_feedback                  qspf
    ON qspf.plan_id = qsp.plan_id
WHERE qspf.feature_id = 3                          -- 3 = DOP Feedback
ORDER BY qspf.last_updated_time DESC;

-- state_desc values:
-- Pending:    feedback identified, not yet verified
-- Verifying:  testing the adjusted DOP across executions
-- Approved:   feedback accepted and applied to future executions
-- Rejected:   feedback tested and reverted due to regression

6 Reading the Feedback JSON Intermediate

Once DOP Feedback stabilizes, the feedback_data column stores a JSON document containing the baseline statistics and the last good feedback metrics. Comparing these two sets of numbers shows exactly what SQL Server measured and why it made the DOP adjustment.

-- Extract DOP Feedback JSON to compare baseline vs adjusted DOP
SELECT
    qspf.plan_id,
    qs.query_id,
    LEFT(qt.query_sql_text, 200)                    AS QueryText,
    qspf.feature_desc,
    qspf.state_desc,
    -- Current adjusted DOP from LastGoodFeedback
    JSON_VALUE(qspf.feedback_data, '$.LastGoodFeedback.dop')
                                                    AS AdjustedDOP,
    JSON_VALUE(qspf.feedback_data, '$.LastGoodFeedback.avg_cpu_time_ms')
                                                    AS AdjustedAvgCPUMs,
    JSON_VALUE(qspf.feedback_data, '$.LastGoodFeedback.avg_adj_elapsed_time_ms')
                                                    AS AdjustedAvgElapsedMs,
    -- Original DOP from BaselineStats
    JSON_VALUE(qspf.feedback_data, '$.BaselineStats.dop')
                                                    AS BaselineDOP,
    JSON_VALUE(qspf.feedback_data, '$.BaselineStats.avg_cpu_time_ms')
                                                    AS BaselineAvgCPUMs,
    JSON_VALUE(qspf.feedback_data, '$.BaselineStats.avg_adj_elapsed_time_ms')
                                                    AS BaselineAvgElapsedMs
FROM sys.query_store_plan_feedback                  qspf
JOIN sys.query_store_plan                           qsp
    ON qspf.plan_id = qsp.plan_id
JOIN sys.query_store_query                          qs
    ON qsp.query_id = qs.query_id
JOIN sys.query_store_query_text                     qt
    ON qs.query_text_id = qt.query_text_id
WHERE qspf.feature_desc = 'DOP Feedback'
  AND ISJSON(qspf.feedback_data) = 1
ORDER BY qspf.last_updated_time DESC;

Example of what the JSON contains after feedback stabilizes:

{
  "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"
  }
}

-- In this example:
-- SQL Server reduced DOP from 4 to 2
-- Average CPU time dropped from 17,843ms to 12,401ms (30% reduction)
-- The query is doing less coordination work and more actual work
-- Standard deviation is similar, confirming the improvement is stable

7 Extended Events for DOP Feedback Monitoring Advanced

Extended Events provide a real-time view of the DOP Feedback lifecycle, showing exactly when SQL Server identifies a candidate, applies a change, and either accepts or reverts it. This session is useful during the initial evaluation period after enabling SQL Server 2025 on a workload.

-- Create an Extended Events session to track DOP Feedback lifecycle
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
WITH (
    MAX_MEMORY              = 4096 KB,
    EVENT_RETENTION_MODE    = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY    = 30 SECONDS
);
GO

-- Start capturing
ALTER EVENT SESSION dop_xevents ON SERVER STATE = START;

-- Stop and clean up when done
-- ALTER EVENT SESSION dop_xevents ON SERVER STATE = STOP;
-- DROP EVENT SESSION dop_xevents ON SERVER;

Event Name	What It Means
dop_feedback_eligible_query	A query has been identified as a candidate for DOP Feedback evaluation
dop_feedback_provided	SQL Server has applied an adjusted DOP to a plan for evaluation
dop_feedback_stabilized	The adjusted DOP has been accepted and persisted as the new baseline
dop_feedback_reverted	The adjusted DOP was tested but caused regression and has been rolled back
dop_feedback_reassessment_failed	A reassessment of existing feedback failed, typically due to workload changes
dop_feedback_analysis_stopped	DOP Feedback analysis stopped for a query, usually due to insufficient data

8 Workshop: Observe DOP Feedback in Action Advanced

This workshop walks through the complete DOP Feedback observation cycle on a SQL Server 2025 instance. Run in a non-production environment with a workload that includes parallel queries running ten or more seconds.

Step 1: Verify prerequisites

-- Database must be compatibility level 160 or higher
SELECT name, compatibility_level
FROM sys.databases
WHERE name = DB_NAME();

-- Query Store must be enabled in READ_WRITE mode
SELECT desired_state_desc, actual_state_desc
FROM sys.database_query_store_options;

-- Enable if needed
ALTER DATABASE YourDatabase SET QUERY_STORE = ON;
ALTER DATABASE YourDatabase SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

-- Check that DOP Feedback is not disabled at the database level
SELECT name, value
FROM sys.database_scoped_configurations
WHERE name = 'DOP_FEEDBACK';
-- NULL or 1 = enabled (default in SQL Server 2025)

Step 2: Identify heavy parallel queries

-- Use the candidate identification query from Section 4
-- Pick two or three queries with DOP 4 or higher and 10+ second average duration
-- Record their query_id and plan_id values and their current avg_dop

Step 3: Capture baseline performance

-- For each candidate query, capture baseline metrics before feedback fires
SELECT
    qsq.query_id,
    qsp.plan_id,
    qsrs.avg_dop,
    qsrs.avg_duration / 1000000.0      AS AvgDurationSec,
    qsrs.avg_cpu_time / 1000000.0      AS AvgCPUSec,
    qsrs.count_executions
FROM sys.query_store_runtime_stats     qsrs
JOIN sys.query_store_plan              qsp ON qsp.plan_id  = qsrs.plan_id
JOIN sys.query_store_query             qsq ON qsq.query_id = qsp.query_id
WHERE qsq.query_id = @YourQueryId     -- replace with actual query_id
ORDER BY qsrs.last_execution_time DESC;

Step 4: Start Extended Events and run the workload

-- Start the XEvent session from Section 7
ALTER EVENT SESSION dop_xevents ON SERVER STATE = START;

-- Execute the heavy parallel queries repeatedly
-- SQL Server needs enough executions to evaluate and stabilize feedback
-- Typically 15 to 30 executions over several minutes
-- Monitor the XEvent session for dop_feedback_eligible_query events

Step 5: Check feedback state

-- Monitor feedback state as it progresses
SELECT
    qspf.plan_id,
    qspf.state_desc,           -- watch for: Pending → Verifying → Approved
    qspf.last_updated_time,
    JSON_VALUE(qspf.feedback_data, '$.LastGoodFeedback.dop') AS AdjustedDOP,
    JSON_VALUE(qspf.feedback_data, '$.BaselineStats.dop')    AS BaselineDOP
FROM sys.query_store_plan_feedback qspf
WHERE qspf.feature_id = 3
ORDER BY qspf.last_updated_time DESC;

Step 6: Compare baseline vs adjusted performance

-- After state_desc reaches Approved, compare performance
-- Re-run the candidate query and compare against the baseline captured in Step 3
-- The adjusted DOP should show lower avg_cpu_time with similar or better elapsed time

-- If state_desc shows Rejected, check the XEvent session for dop_feedback_reverted
-- to understand why the adjustment was rolled back

References

• Microsoft Docs: Intelligent Query Processing Feedback
• Microsoft Docs: Degree of Parallelism Feedback
• Microsoft Docs: sys.query_store_plan_feedback
• SQLYARD: SQL Server MAXDOP Guide
• SQLYARD: Should You Change the Cost Threshold for Parallelism?
• SQLYARD: Investigating Serial vs Parallel Executions with Query Store
• SQLYARD: A Beginner-to-Advanced Guide to Query Store