Memory Grant Feedback, Cardinality Feedback, Adaptive/Intelligent Processing Features

Learning outcomes

Modern SQL Server increasingly learns from execution evidence instead of requiring a DBA to hard-code every correction. But “Intelligent Query Processing is on” is too vague to be operationally useful: each feature has an engine/version boundary, compatibility prerequisite, database-scoped switch, eligibility rules, and sometimes a Query Store persistence dependency. This lesson turns those features into observable state rather than marketing labels.

Lab bootstrap

USE master;GOIF DB_ID(N'ServiceHubQSLab') IS NULLBEGIN  CREATE DATABASE ServiceHubQSLab;END;GOALTER DATABASE ServiceHubQSLab SET COMPATIBILITY_LEVEL = 170;ALTER DATABASE ServiceHubQSLab SET QUERY_STORE = ON(  OPERATION_MODE = READ_WRITE,  QUERY_CAPTURE_MODE = AUTO,  WAIT_STATS_CAPTURE_MODE = ON,  INTERVAL_LENGTH_MINUTES = 5,  MAX_STORAGE_SIZE_MB = 100,  SIZE_BASED_CLEANUP_MODE = AUTO,  CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 7));GOUSE ServiceHubQSLab;GOIF SCHEMA_ID(N'lab11') IS NULL EXEC(N'CREATE SCHEMA lab11 AUTHORIZATION dbo;');GOIF OBJECT_ID(N'lab11.WorkOrderFact',N'U') IS NULLBEGIN  CREATE TABLE lab11.WorkOrderFact  (    work_order_id bigint IDENTITY(1,1) NOT NULL,    customer_id int NOT NULL,    region_code char(3) NOT NULL,    status varchar(12) NOT NULL,    priority tinyint NOT NULL,    opened_at datetime2(0) NOT NULL,    amount decimal(12,2) NOT NULL,    notes varchar(200) NULL,    CONSTRAINT PK_lab11_WorkOrderFact PRIMARY KEY CLUSTERED(work_order_id)  );  ;WITH n AS  (    SELECT TOP (40000)      ROW_NUMBER() OVER (ORDER BY a.object_id,b.object_id) AS n    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b  )  INSERT lab11.WorkOrderFact(customer_id,region_code,status,priority,opened_at,amount,notes)  SELECT CASE WHEN n <= 24000 THEN 1 ELSE 2 + n % 3999 END,         CASE WHEN n <= 27000 THEN 'N01' WHEN n <= 35000 THEN 'W02' ELSE 'E03' END,         CASE WHEN n % 23 = 0 THEN 'ESCALATED' WHEN n % 5 = 0 THEN 'CLOSED' ELSE 'OPEN' END,         CASE WHEN n <= 27000 THEN 1 ELSE 5 END,         DATEADD(minute,n,'2026-01-01T00:00:00'),         CAST(20 + (n % 8000) / 10.0 AS decimal(12,2)),         CASE WHEN n % 41=0 THEN REPLICATE('x',180) END  FROM n;  CREATE INDEX IX_lab11_Customer    ON lab11.WorkOrderFact(customer_id)    INCLUDE(region_code,status,opened_at,amount);  CREATE INDEX IX_lab11_RegionStatus    ON lab11.WorkOrderFact(region_code,status,opened_at)    INCLUDE(customer_id,priority,amount);END;GOCREATE OR ALTER PROCEDURE lab11.GetOrdersByCustomer  @customer_id intASBEGIN  SET NOCOUNT ON;  SELECT work_order_id,region_code,status,opened_at,amount  FROM lab11.WorkOrderFact  WHERE customer_id=@customer_id  ORDER BY opened_at,work_order_id;END;GO

1. Build a feature matrix before troubleshooting

Database compatibility level gates many Intelligent Query Processing (IQP) behaviors independently from the SQL Server engine build. The course baseline—SQL Server 2025 CU7 with compatibility 170—makes the database eligible for the broadest current on-premises IQP set, but eligibility is not proof that any particular query used a feature.

USE ServiceHubQSLab;GOSELECT compatibility_level,is_query_store_onFROM sys.databases WHERE database_id=DB_ID();SELECT name,value,value_for_secondaryFROM sys.database_scoped_configurationsWHERE name IN( 'BATCH_MODE_MEMORY_GRANT_FEEDBACK', 'ROW_MODE_MEMORY_GRANT_FEEDBACK', 'MEMORY_GRANT_FEEDBACK_PERSISTENCE', 'MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT', 'TSQL_SCALAR_UDF_INLINING', 'DEFERRED_COMPILATION_TV', 'PARAMETER_SENSITIVE_PLAN_OPTIMIZATION', 'CE_FEEDBACK', 'DOP_FEEDBACK', 'OPTIONAL_PARAMETER_OPTIMIZATION')ORDER BY name;GOSELECT actual_state_desc,desired_state_descFROM sys.database_query_store_options;GO

2. Memory grant feedback learns from repeated execution

Sorts, hashes and other operators may request workspace memory before execution. If the estimate is badly wrong, the query can receive too little memory and spill, or too much and reduce concurrency. Memory grant feedback adjusts later executions based on prior behavior. Row-mode support begins at compatibility 150. SQL Server 2022 added percentile and persistence modes; persistence requires Query Store enabled and READ_WRITE.

A statement using OPTION(RECOMPILE) is not cached, so it does not provide the normal repeated cached-plan loop that memory grant feedback needs. This is an example of why one intervention can disable another learning mechanism.

USE ServiceHubQSLab;GOSELECT customer_id,COUNT_BIG(*) AS order_count,SUM(amount) AS amount_totalFROM lab11.WorkOrderFactGROUP BY customer_idORDER BY amount_total DESC;GO 4SELECT plan_id,feature_desc,state_desc,feedback_dataFROM sys.query_store_plan_feedbackORDER BY last_updated_time DESC;GO

Your result might contain no feedback row. That is valid: SQL Server only records feedback when a query is eligible and the mechanism decides adjustment is useful. The course never invents a spill or claims an improvement without runtime evidence.

3. CE feedback and DOP feedback are persisted learning, with different interactions

Cardinality Estimation (CE) feedback can learn alternative estimation-model choices for recurring misestimates and persist them through Query Store at compatibility 160+. Current documentation states that CE feedback is not used for a query that has a Query Store-forced plan or user hard-coded/Query Store hints that conflict with its learning path. Feedback can be inspected through sys.query_store_plan_feedback, Query Store hint metadata, and feature-specific Extended Events.

Degree of parallelism (DOP) feedback also requires compatibility 160+ and Query Store READ_WRITE for persistence. It is not a reason to delete a carefully chosen instance/database MAXDOP policy; it adjusts eligible query-level parallelism from evidence. Verify whether DOP_FEEDBACK is enabled before claiming it is active.

USE ServiceHubQSLab;GOSELECT plan_id,feature_desc,state_desc,feedback_data,last_updated_timeFROM sys.query_store_plan_feedbackORDER BY last_updated_time DESC;GOSELECT query_id,query_hint_text,source_desc,       last_query_hint_failure_reason_desc,query_hint_failure_countFROM sys.query_store_query_hintsORDER BY query_id;GO

4. SQL Server 2025: OPPO and broader adaptive plan behavior

Optional Parameter Plan Optimization (OPPO) addresses a specific dynamic-search pattern such as WHERE customer_id=@p OR @p IS NULL, where one plan often cannot be ideal for the “filtered” and “return all” cases. On SQL Server 2025 it requires compatibility 170 and OPTIONAL_PARAMETER_OPTIMIZATION = ON; the setting is on by default at compatibility 170.

OPPO uses the same Multiplan/dispatcher infrastructure family as PSP, but it solves optional-parameter shape, not arbitrary parameter skew. Do not rewrite a clean query into the optional-predicate pattern just to “get OPPO.” Use it when that predicate expresses the application’s real semantics.

USE ServiceHubQSLab;GODECLARE @customer_id int=NULL;SELECT COUNT_BIG(*) AS orders_foundFROM lab11.WorkOrderFactWHERE customer_id=@customer_id OR @customer_id IS NULL;GODECLARE @customer_id int=812;SELECT COUNT_BIG(*) AS orders_foundFROM lab11.WorkOrderFactWHERE customer_id=@customer_id OR @customer_id IS NULL;GOSELECT TOP (30) p.plan_id,p.query_id,p.plan_type_desc,p.last_execution_time,       qt.query_sql_textFROM sys.query_store_plan AS pJOIN sys.query_store_query AS q ON q.query_id=p.query_idJOIN sys.query_store_query_text AS qt ON qt.query_text_id=q.query_text_idWHERE qt.query_sql_text LIKE N'%customer_id=@customer_id OR @customer_id IS NULL%'ORDER BY p.last_execution_time DESC,p.plan_id;GO

5. Wrong approach: disable every adaptive feature after one surprising plan

IQP changes are scoped mechanisms, not a single switch. If one query regresses, first identify the exact feature in Showplan/Query Store/XEvents and reproduce the regression. Then prefer a query-level disable hint or the narrowest supported database-scoped change while you test a durable fix. Disabling a database-wide feature can make many unrelated queries worse and discard useful feedback.

Likewise, manual forcing can change the adaptive landscape. A forced plan can block CE feedback for that query; RECOMPILE prevents normal memory grant feedback; manual Query Store hints can supersede optimizer learning. Record these interactions in the incident timeline so “feature did not fire” is not misdiagnosed as an engine bug.

Authoritative references

• Intelligent Query Processing — feature/compatibility overview
• Memory grant feedback — row/batch feedback and persistence prerequisites
• Cardinality Estimation feedback — CE feedback, Query Store and forcing interactions
• DOP feedback — parallelism feedback prerequisites and persistence
• Optional Parameter Plan Optimization — SQL Server 2025 compatibility-170 adaptive optional predicates
• ALTER DATABASE SCOPED CONFIGURATION — effective IQP settings including OPPO
• SQL Server 2025 build versions — servicing baseline