More parallel workers do not always get a query home sooner. DOP feedback, introduced in SQL Server 2022, tests lower parallelism for repeating queries that spend too much effort coordinating workers.
Understand What the Feedback Adjusts
Degree of parallelism describes the parallel execution width used by a query. Coordinating parallel work has a cost. When added workers do not provide enough benefit, they consume resources other queries need. SQL Server evaluates eligible repeated executions and tests a lower degree for later executions.
I review whole-workload response before declaring a higher degree successful. One reporting query looks different in isolation than it does beside busy application traffic. A lower degree can improve resource sharing even when an individual run changes little. More workers are useful only when the work benefits from them.
The feature operates within your configured limits. It does not rewrite the query, replace missing indexes, or fix inaccurate estimates by itself. Keep ordinary plan analysis in the investigation. An expensive scan does not become an efficient search merely because fewer workers share it.
Check Version, Edition, and Compatibility for DOP Feedback
DOP feedback requires SQL Server 2022 or later and database compatibility level 160 or higher. On SQL Server, check edition support too. This feature is available in Enterprise and its corresponding developer edition, rather than Standard. The setting's presence alone does not certify that your edition supports the behavior.
SELECT SERVERPROPERTY('Edition') AS InstalledEdition,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
DB_NAME() AS CurrentDatabase;
SELECT name, compatibility_level
FROM sys.databases
WHERE database_id = DB_ID();
SELECT name, value
FROM sys.database_scoped_configurations
WHERE name IN (N'DOP_FEEDBACK', N'MAXDOP');Run these checks in the application database you intend to assess. SQL Server 2022 does not enable the feedback setting by default. A new database on my SQL Server 2025 instance already showed it as 1, with Query Store in READ_WRITE. Read its current value rather than assuming that an engine upgrade activated it. Compatibility changes deserve their own workload rehearsal before you combine them with this test.
Keep Query Store Writable
Query Store must be enabled and in READ_WRITE state. It provides the place to retain verified feedback. A read-only store cannot serve that persistence path. Inspect storage consumption and the actual state before deciding that an empty feedback view means the feature has failed.
SELECT actual_state_desc, desired_state_desc,
current_storage_size_mb, max_storage_size_mb,
query_capture_mode_desc
FROM sys.database_query_store_options;For an existing lab database named DopLab, the following commands enable the required store and feedback setting. Apply them after reviewing the test database's configuration. Keep the original values in your test record. Production changes belong in a planned rollout with representative monitoring.
ALTER DATABASE [DopLab] SET QUERY_STORE = ON;
ALTER DATABASE [DopLab]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO
USE [DopLab];
GO
ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = ON;The database still needs the required compatibility level and supported edition. Collect a baseline before enabling the feature. Otherwise, later query statistics show the changed state without giving you a fair comparison. Keep background load and important parameter variations visible in that baseline.
Give Repeated Executions Time to Be Evaluated
Feedback responds to eligible repeating queries. A single test execution does not establish whether a lower degree helps. Run your representative workload enough times to observe its behavior. Avoid generating artificial retries against production simply to make a demonstration more dramatic.
SQL Server validates the adjustment and can return to the prior good degree after regression. Verified feedback is persisted when a stable choice is established. The process does not require a plan recompile for each adjustment. Recompilation can trigger reassessment of a previously learned choice.
Serial queries sit outside this feedback mechanism. Its minimum adjusted degree is two. If every relevant query already runs serially, this setting has little work to perform. First inspect actual execution plans and parallelism before spending an afternoon asking an idle feature for a testimonial.
Inspect the DOP Feedback Records
The feedback view covers several query-processing features. Filter feature_id to three for the parallelism feature. Join its plan identifier to Query Store's plan and query rows. The timestamps and state description help distinguish retained feedback from an unverified or invalid entry.
SELECT f.plan_feedback_id, q.query_id, p.plan_id,
f.feature_desc, f.state_desc,
f.create_time, f.last_updated_time, f.feedback_data
FROM sys.query_store_plan_feedback AS f
JOIN sys.query_store_plan AS p ON p.plan_id = f.plan_id
JOIN sys.query_store_query AS q ON q.query_id = p.query_id
WHERE f.feature_id = 3
ORDER BY f.last_updated_time DESC;An absent row does not prove that a query never had an inefficient parallel execution. Check eligibility, capture settings, workload repetition, and current plans. Do not parse undocumented feedback_data fields into a permanent monitoring contract. Keep the raw value for inspection and use documented columns for durable queries.
Compare Degree and Runtime by Interval
Choose a plan identifier from the preceding query and substitute it below. The example value is simply a placeholder for that choice. The query summarizes successful executions per Query Store interval. It weights averages by execution count, since one interval can contain multiple runtime-statistics rows.
DECLARE @PlanID bigint = 1;
SELECT i.start_time, i.end_time,
SUM(s.count_executions) AS ExecutionCount,
SUM(s.avg_dop * s.count_executions) /
NULLIF(SUM(s.count_executions), 0) AS AverageDop,
MIN(s.min_dop) AS LowestDop,
MAX(s.max_dop) AS HighestDop,
SUM(s.avg_duration * s.count_executions) /
NULLIF(SUM(s.count_executions), 0) / 1000.0 AS AverageDurationMs,
SUM(s.avg_cpu_time * s.count_executions) /
NULLIF(SUM(s.count_executions), 0) / 1000.0 AS AverageCpuMs
FROM sys.query_store_runtime_stats AS s
JOIN sys.query_store_runtime_stats_interval AS i
ON i.runtime_stats_interval_id = s.runtime_stats_interval_id
WHERE s.plan_id = @PlanID AND s.execution_type = 0
GROUP BY i.start_time, i.end_time
ORDER BY i.start_time;Duration and CPU are recorded in microseconds before this conversion. AverageDop is a workload average, not a promise about the next execution. Compare intervals with similar parameters and concurrency. Use actual-plan runtime properties when investigating suspicious reported degrees. History summaries and a specific execution answer different questions.
How DOP Feedback Fits With MAXDOP and Cost Threshold
MAXDOP bounds parallel execution width. The cost threshold influences whether the optimizer considers a parallel plan based on estimated serial cost. That threshold is not a measured duration in seconds. Feedback adjusts an eligible query after execution evidence exists, within the applicable parallelism ceiling.
SELECT name, value_in_use
FROM sys.configurations
WHERE name IN
(N'max degree of parallelism',
N'cost threshold for parallelism');I read these settings and query hints before interpreting a degree change. Database settings and workload controls also affect the available ceiling. Review hint compatibility for your engine build when a query fails eligibility. Keep your test statement free of conflicting hints instead of assuming feedback can override every instruction.
Do not raise MAXDOP or lower cost threshold merely to give feedback more activity. Configure sensible instance limits for the hardware and workload first. Then let the feature evaluate individual eligible statements. A learning mechanism should refine a reasonable baseline, rather than compensate for arbitrary server-wide settings.
Evaluate Resource Sharing Before Keeping the Change
Which other queries benefit when this query uses fewer workers? Check application response, processor pressure, waits, and throughput alongside the selected query's history. Separate successful runs from canceled or failed executions during analysis. Averages without workload context can hide an important service regression.
If the test does not justify retaining the feature, restore the recorded scoped setting. The following command disables it in the current database. Keep Query Store history available for comparison. Revisit the decision after query, data, or workload changes instead of treating the first experiment as permanent truth.
ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = OFF;DOP feedback provides a measured way to reduce excessive parallelism. Accept it based on representative repeated work and verified evidence. Keep the surrounding limits, edition, and capture requirements explicit so the same investigation can be repeated later.
Related reading on this blog: SQL SERVER Performance: When MAXDOP = 1 Slowed Down the Entire Business and Do Queries Always Respect Cost Threshold of Parallelism? Interview Question of the Week #216.
Parallelism is not a race to use every worker, it is a way to share useful work efficiently.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
