When Microsoft introduced Degree of Parallelism (DOP) Feedback for SQL Server, I immediately wanted to see it in action. The idea is compelling: if a repeating query uses more parallel workers than it really needs, SQL Server learns from previous executions and quietly chooses a better DOP the next time.
At least, that's how I understood it.
I decided to test it out in the lab.
The Lab
I started with SQL Server 2025 RTM and enabled the documented prerequisites for DOP Feedback.
- Query Store in READ_WRITE mode
- DOP Feedback enabled
- Compatibility Level 160
From there, I configured the environment to encourage parallel plans.
- MAXDOP set to 14
- Cost Threshold for Parallelism reduced to 50
- Batch Mode on Rowstore disabled during part of the testing to force row-mode execution
I built a repeatable workload, verified an actual DOP of 14, confirmed row-mode execution, and watched Query Store capture every execution.
Everything appeared to satisfy the documented requirements, so I expected SQL Server to at least consider the workload.
Then I Started Measuring
The execution plan was fully parallel.
The workload executed repeatedly.
Query Store captured every execution.
The query also generated measurable parallelism waits:
| Wait Type | Increase During Test |
|---|---|
| CXPACKET | +27,678 ms |
| CXCONSUMER | +23,424 ms |
| CXSYNC_PORT | +1,130 ms |
| CXSYNC_CONSUMER | +62 ms |
This wasn't a tiny query. It was clearly exercising the parallel execution engine.
So I expected the workload to become a candidate for DOP Feedback.
It Never Happened.
sys.query_store_plan_feedback remained empty again and again.
I decided to create an Extended Events session to capture the DOP Feedback lifecycle, but the event I expected to see most, dop_feedback_eligible_query, never fired.
Then I thought compatibility level might be the missing piece, so I repeated the entire test under Compatibility Level 170.
No change.
No persisted feedback. No eligibility event. No evidence that SQL Server ever considered my workload a candidate for DOP Feedback. Ever.
At this point, the absence of evidence had become evidence itself.
What changed?
My understanding of the feature. I began this test assuming that a sufficiently expensive parallel query with measurable CX waits would become eligible for DOP Feedback. But nothing in my testing supported that assumption.
Instead, the evidence suggests something more interesting.
It tells me that DOP Feedback isn't just looking for every expensive parallel query. There are clearly additional eligibility criteria that determine whether SQL Server even considers a query for review. Those criteria aren't fully documented, and my workload never satisfied them.
Microsoft documents the feature and its prerequisites. Those internal decision points, however, remain intentionally undocumented.
My workload simply never crossed that line.
At this point I stopped trying to invent new workloads. If SQL Server wasn't even raising the eligibility event, making the query bigger or more expensive wasn't going to teach me anything. The more interesting question now is why the engine ignored the workload in the first place.
Bottom Line
I set out to demonstrate SQL Server automatically tuning a query. Instead, I found something I wasn't expecting.
After verifying Query Store, DOP Feedback, Compatibility Levels 160 and 170, row-mode execution, repeated executions, measurable CX waits, and a fully parallel execution plan -- SQL Server never considered the workload eligible for DOP Feedback.
That doesn't mean DOP Feedback doesn't work. It means SQL Server is far more selective than I expected.
One of the easiest mistakes to make with adaptive features is assuming they'll engage simply because all the prerequisites are enabled. My tests have reminded me that's only the beginning. The harder part is understanding what makes a workload eligible in the first place.
My next step is to find a workload that does become eligible and compare it against this one. When I find it, I'll write the sequel.
More to Read
Microsoft Learn: Degree of parallelism (DOP) feedback
MSFT Tech Community: Smarter Parallelism -- DOP feedback in SQL Server 2025
Microsoft Learn: sys.query_store_plan_feedback
Erik Darling: What’s The Point Of DOP Feedback In SQL Server 2022?
No comments:
Post a Comment