liveBook · Manning

DOP feedback

As explained in 100 SQL Server Mistakes and How to Avoid Them

DOP feedback is an adaptive SQL Server query-processing feature that evaluates execution history and adjusts a query's degree of parallelism.

Mistake: fixing a slow reporting query with a permanent MAXDOP hint. A high degree of parallelism can hurt that query and competing workloads, while SQL Server 2022 can evaluate execution history and adjust DOP automatically. section 10.6

The MarketingArchive reporting query

A reporting query runs with MAXDOP 16 and harms its own execution time and other queries. Adding a MAXDOP 8 hint seems reasonable, but it fixes one observed condition instead of adapting to later executions. Enable Query Store, turn on DOP feedback, and review the feedback settings. section 10.6

Apply these rules:

  • Prefer DOP feedback over a fixed query hint when running SQL Server 2022 and the query's best parallelism may vary. section 10.6
  • Remember that DOP feedback is disabled by default, unlike the other stated query feedback mechanisms. section 10.14 section 10.6
  • Use execution history and statistics to let SQL Server continuously evaluate the DOP used in the query plan. section 10.6

How 100 SQL Server Mistakes and How to Avoid Them covers it

How deep it goes
Explains historical execution-based adjustment and activation, but does not detail the adjustment algorithm.
Examples
Uses a reporting query, MAXDOP 16, a proposed MAXDOP 8 hint, and configuration queries.
What it assumes
Assumes familiarity with MAXDOP and query hints while teaching DOP feedback's purpose and disabled-by-default status.
What you can do after
Operate/configure DOP feedback and review its enabled state instead of hard-coding a query-level DOP.
How it connects
Connects DOP feedback with MAXDOP, Query Store, query hints, and intelligent query processing.
Cover of 100 SQL Server Mistakes and How to Avoid Them
liveBookmanning.com
Read 100 SQL Server Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of 100 SQL Server Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.