liveBook · Manning

Query processing feedback

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

A query feedback feature is an adaptive SQL Server optimization capability that uses execution history to adjust query-processing decisions.

Query processing feedback lets SQL Server adapt query plans using execution history. Query Store compares plans with their performance, helping identify and respond to previous inefficiencies instead of relying immediately on query hints. section 10.6

When MAXDOP 16 makes things worse

A large reporting query runs slowly while CX_PACKET and SOS_SCHEDULER_YIELD waits rise. Its plan uses MAXDOP 16, and the degree of parallelism appears to harm both the query and concurrent workloads. DOP feedback provides an adaptive alternative when the instance-wide setting benefits other queries. section 10.6

Listing 10.8 Enabling Query Store for the MarketingArchive database
ALTER DATABASE MarketingArchive
   SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE) ;

Enable Query Store for MarketingArchive in read-write mode before following the feedback examples. section 10.6

Listing 10.9 Turning on DOP feedback
ALTER DATABASE SCOPED CONFIGURATION 
   SET DOP_FEEDBACK = ON ;

Turn on database-scoped DOP feedback with ALTER DATABASE SCOPED CONFIGURATION. section 10.6

Listing 10.10 Reviewing enabled query feedback options
SELECT 
     name
   , CASE
         WHEN value = 1 THEN 'Enabled'
         ELSE 'Disabled'
     END as Enabled
FROM sys.database_scoped_configurations
WHERE name LIKE '%feedback%' ;

Review which query feedback options are enabled by querying sys.database_scoped_configurations. section 10.6

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

How deep it goes
Explains the Query Store dependency, a DOP scenario, and enabling or reviewing feedback, but does not detail the adaptation algorithm.
Examples
Uses MarketingArchive, a large reporting query, CX_PACKET, SOS_SCHEDULER_YIELD, and MAXDOP 16 as the operating scenario.
What it assumes
Requires Query Store to be enabled and assumes enough context to inspect waits, plans, and database-scoped configurations.
What you can do after
Operate/configure Query Store and DOP feedback, then inspect enabled feedback options with T-SQL.
How it connects
Links Query Store, query hints, memory grant feedback, MAXDOP, wait statistics, and parallelism.
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

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.