Cardinality Estimation Feedback in SQL Server 2022 and 2025

---

The SQL Server query optimizer estimates how many rows each operation in an execution plan will process before the query runs. These cardinality estimates drive every major plan choice: which join algorithm to use, whether to seek or scan, how much memory to grant, and whether to use parallelism. When estimates are significantly wrong, the optimizer makes bad decisions that produce slow plans.

Cardinality Estimation Feedback, introduced in SQL Server 2022 as part of the Intelligent Query Processing family, addresses this automatically. When a query’s actual row counts differ significantly from the optimizer’s estimates, CE Feedback stores corrected cardinality hints in Query Store and applies them on the next compilation. The query gets a better plan without any code changes.

1 Why Cardinality Estimates Go Wrong Beginner

The query optimizer produces execution plans based on statistics: histogram data about the distribution of values in table columns. When statistics are accurate and data is well-distributed, the optimizer’s row count estimates are close to reality and the resulting plans are efficient. When statistics are stale, data has skewed distribution, or queries involve multiple predicates where the optimizer assumes column independence, estimates diverge from reality.

The consequences of bad cardinality estimates depend on how wrong they are and which plan decisions they affect. An estimate that is ten times too low on the outer side of a nested loop join causes SQL Server to choose nested loops for a join that needed a hash join. An overestimate on a filter predicate causes an over-allocation of memory that wastes buffer pool. An underestimate on a parallelism decision may leave the query serial when it should be parallel.

Updating statistics fixes the immediate problem but does not prevent the same estimates from going wrong again as data continues to change. CE Feedback addresses the pattern at the plan level rather than the statistics level, correcting estimation errors that persist despite reasonable statistics maintenance.

2 How CE Feedback Works Beginner

CE Feedback operates silently in the background. There is no alert, no notification, and no manual step required. The process follows a cycle similar to DOP Feedback and Memory Grant Feedback.

1. A query executes and SQL Server compares the estimated row counts for each operator in the plan against the actual row counts from runtime statistics.
2. If the difference exceeds a significance threshold, SQL Server identifies the query as a CE Feedback candidate and begins tracking it.
3. On subsequent executions, SQL Server applies adjusted cardinality hints that correct the known estimation errors. These hints are stored in Query Store and do not modify the query text or the underlying statistics.
4. The adjusted plan is executed and its performance is measured. If the corrected estimates produce a better plan consistently, the feedback is accepted and persisted.
5. If the adjusted estimates cause regression, the feedback is reverted and the original cardinality model is restored for that query.

Property	Behavior
Trigger	Significant divergence between estimated and actual row counts
Storage	sys.query_store_plan_feedback with feature_id = 2
Scope	Per plan, per query. Other queries and global settings are unaffected.
Persistence	Survives restarts. Applied on next compilation after Query Store reload.
Reversibility	Automatically reverted if adjusted estimates cause performance regression
Visibility	Fully observable through Query Store DMVs and Extended Events

3 Requirements and Setup Beginner

-- Requirement 1: Database compatibility level 160 or higher
SELECT name, compatibility_level
FROM sys.databases
WHERE name = DB_NAME();

-- Set to 160 if needed (SQL Server 2022 minimum for CE Feedback)
ALTER DATABASE YourDatabase SET COMPATIBILITY_LEVEL = 160;

-- Requirement 2: Query Store must be enabled in READ_WRITE mode
SELECT desired_state_desc, actual_state_desc
FROM sys.database_query_store_options;

ALTER DATABASE YourDatabase SET QUERY_STORE = ON;
ALTER DATABASE YourDatabase
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

-- Verify CE Feedback is not disabled at the database level
-- NULL or ON means enabled (default)
SELECT name, value
FROM sys.database_scoped_configurations
WHERE name = 'CE_FEEDBACK';

-- CE Feedback is enabled by default in SQL Server 2022 and 2025
-- at compatibility level 160 with Query Store in READ_WRITE mode
-- No additional configuration required

4 Viewing CE Feedback Decisions Intermediate

All CE Feedback decisions are stored in sys.query_store_plan_feedback. The feature_id = 2 filter isolates CE Feedback from DOP Feedback (feature_id = 3) and Memory Grant Feedback (feature_id = 1) stored in the same table.

-- View all CE Feedback decisions in the current database
SELECT
    qspf.feature_desc,
    qsq.query_id,
    qsp.plan_id,
    qspf.state_desc,
    LEFT(qsqt.query_sql_text, 300)                  AS QueryText,
    qspf.create_time,
    qspf.last_updated_time,
    qspf.feedback_data
FROM sys.query_store_query                          qsq
JOIN sys.query_store_plan                           qsp
    ON qsp.query_id = qsq.query_id
JOIN sys.query_store_query_text                     qsqt
    ON qsqt.query_text_id = qsq.query_text_id
JOIN sys.query_store_plan_feedback                  qspf
    ON qspf.plan_id = qsp.plan_id
WHERE qspf.feature_id = 2                          -- 2 = CE Feedback
ORDER BY qspf.last_updated_time DESC;

-- feature_id values for reference:
-- 1 = Memory Grant Feedback
-- 2 = CE Feedback
-- 3 = DOP Feedback

-- state_desc lifecycle:
-- Pending:    estimation error detected, not yet validated
-- Verifying:  adjusted hints applied and being tested
-- Approved:   feedback accepted, hints applied to future compilations
-- Rejected:   feedback tested and reverted due to regression or instability

-- Extract CE Feedback details including the corrected estimates
SELECT
    qspf.plan_id,
    qsq.query_id,
    qspf.state_desc,
    LEFT(qt.query_sql_text, 200)                    AS QueryText,
    -- The feedback_data JSON contains the corrected cardinality hints
    qspf.feedback_data,
    qspf.create_time,
    qspf.last_updated_time
FROM sys.query_store_plan_feedback                  qspf
JOIN sys.query_store_plan                           qsp
    ON qspf.plan_id = qsp.plan_id
JOIN sys.query_store_query                          qsq
    ON qsp.query_id = qsq.query_id
JOIN sys.query_store_query_text                     qt
    ON qsq.query_text_id = qt.query_text_id
WHERE qspf.feature_desc = 'CE Feedback'
ORDER BY qspf.last_updated_time DESC;

5 Finding Queries with Estimation Errors Intermediate

CE Feedback identifies candidates automatically, but understanding which queries have significant estimation errors helps with manual investigation and prioritization. The execution plan XML contains both estimated and actual row counts for every operator. Query Store’s runtime stats also surface queries where plan shape changed unexpectedly.

-- Find queries where CE Feedback has been applied or is pending
-- Useful for tracking which plans have been adjusted
SELECT
    qsq.query_id,
    qsp.plan_id,
    qspf.state_desc,
    qspf.feature_desc,
    LEFT(qsqt.query_sql_text, 300)                  AS QueryText,
    qsrs.avg_duration / 1000000.0                   AS AvgDurationSec,
    qsrs.count_executions,
    qspf.last_updated_time
FROM sys.query_store_plan_feedback                  qspf
JOIN sys.query_store_plan                           qsp
    ON qsp.plan_id  = qspf.plan_id
JOIN sys.query_store_query                          qsq
    ON qsq.query_id = qsp.query_id
JOIN sys.query_store_query_text                     qsqt
    ON qsqt.query_text_id = qsq.query_text_id
JOIN sys.query_store_runtime_stats                  qsrs
    ON qsrs.plan_id = qsp.plan_id
WHERE qspf.feature_id = 2
ORDER BY qsrs.avg_duration DESC;

-- Find queries where plans changed recently (potential estimation issue)
-- A plan change without a schema or statistics change often indicates
-- the optimizer got different estimates and chose a different strategy
SELECT TOP 20
    qsq.query_id,
    COUNT(DISTINCT qsp.plan_id)                     AS PlanCount,
    MIN(qsrs.avg_duration) / 1000000.0              AS MinAvgDurationSec,
    MAX(qsrs.avg_duration) / 1000000.0              AS MaxAvgDurationSec,
    LEFT(qsqt.query_sql_text, 200)                  AS QueryText
FROM sys.query_store_query                          qsq
JOIN sys.query_store_plan                           qsp
    ON qsp.query_id = qsq.query_id
JOIN sys.query_store_runtime_stats                  qsrs
    ON qsrs.plan_id = qsp.plan_id
JOIN sys.query_store_query_text                     qsqt
    ON qsqt.query_text_id = qsq.query_text_id
WHERE qsrs.last_execution_time >= DATEADD(DAY, -7, GETDATE())
GROUP BY qsq.query_id, qsqt.query_sql_text
HAVING COUNT(DISTINCT qsp.plan_id) > 1              -- multiple plans = instability
   AND MAX(qsrs.avg_duration) / MIN(qsrs.avg_duration) > 5  -- 5x duration variance
ORDER BY MAX(qsrs.avg_duration) DESC;

6 Managing CE Feedback Intermediate

CE Feedback operates automatically and in most cases requires no management. There are two situations where manual intervention is appropriate.

Disabling CE Feedback for a specific database

-- Disable CE Feedback at the database level
-- Use this if CE Feedback is causing unexpected plan regressions
-- or if you need to validate performance without the feature active
ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFF;

-- Re-enable
ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = ON;

-- Check current state
SELECT name, value
FROM sys.database_scoped_configurations
WHERE name = 'CE_FEEDBACK';

Removing specific feedback entries

-- Remove CE Feedback for a specific plan if it is causing problems
-- SQL Server will re-evaluate the query from scratch on next execution
-- Replace @PlanFeedbackId with the plan_feedback_id from sys.query_store_plan_feedback
EXEC sys.sp_query_store_remove_plan_feedback @plan_feedback_id = @PlanFeedbackId;

-- Find the plan_feedback_id for a specific query
SELECT qspf.plan_feedback_id, qspf.state_desc, qspf.last_updated_time
FROM sys.query_store_plan_feedback  qspf
JOIN sys.query_store_plan           qsp  ON qsp.plan_id  = qspf.plan_id
JOIN sys.query_store_query          qsq  ON qsq.query_id = qsp.query_id
WHERE qsq.query_id  = @YourQueryId
  AND qspf.feature_id = 2;

7 Workshop: Observe CE Feedback Correcting a Bad Estimate Advanced

This workshop creates a scenario where the optimizer will produce a bad cardinality estimate, then observes CE Feedback correcting it. Run in a non-production SQL Server 2022 or 2025 environment with compatibility level 160.

-- Step 1: Verify prerequisites
SELECT name, compatibility_level FROM sys.databases WHERE name = DB_NAME();
SELECT desired_state_desc, actual_state_desc FROM sys.database_query_store_options;

-- Step 2: Create a table with skewed data distribution
-- Skewed data is one of the most common causes of bad cardinality estimates
CREATE TABLE dbo.CEFeedbackTest
(
    ID          INT IDENTITY PRIMARY KEY,
    Category    NVARCHAR(50) NOT NULL,
    Amount      DECIMAL(18,2),
    EventDate   DATE
);

-- Insert 100,000 rows with heavy skew: 95% in one category
INSERT INTO dbo.CEFeedbackTest (Category, Amount, EventDate)
SELECT
    CASE WHEN ABS(CHECKSUM(NEWID())) % 100 < 95
         THEN 'Common'       -- 95% of rows
         ELSE 'Rare'         -- 5% of rows
    END,
    ABS(CHECKSUM(NEWID())) % 10000 / 100.0,
    DATEADD(DAY, -(ABS(CHECKSUM(NEWID())) % 365), GETDATE())
FROM sys.all_objects s1
CROSS JOIN sys.all_objects s2
WHERE ROW_NUMBER() OVER (ORDER BY s1.object_id) <= 100000;

-- Create statistics but they will still underestimate combinations
CREATE STATISTICS ST_Category ON dbo.CEFeedbackTest (Category);
CREATE STATISTICS ST_Date ON dbo.CEFeedbackTest (EventDate);

-- Step 3: Run a query that will produce estimation errors
-- The optimizer may underestimate rows for combined filter predicates
DECLARE @Cat NVARCHAR(50) = 'Common';
DECLARE @Date DATE = DATEADD(DAY, -180, GETDATE());

SELECT COUNT(*), AVG(Amount)
FROM dbo.CEFeedbackTest
WHERE Category  = @Cat
  AND EventDate >= @Date;

-- Step 4: Run the same query 20+ times to give CE Feedback enough data
-- to detect the estimation error and begin applying corrections
DECLARE @i INT = 0;
WHILE @i < 25
BEGIN
    SELECT COUNT(*), AVG(Amount)
    FROM dbo.CEFeedbackTest
    WHERE Category  = 'Common'
      AND EventDate >= DATEADD(DAY, -180, GETDATE());
    SET @i += 1;
END;

-- Step 5: Check for CE Feedback activity
SELECT
    qspf.state_desc,
    qspf.feature_desc,
    qspf.create_time,
    qspf.last_updated_time,
    qspf.feedback_data
FROM sys.query_store_plan_feedback              qspf
JOIN sys.query_store_plan                       qsp
    ON qsp.plan_id = qspf.plan_id
JOIN sys.query_store_query                      qsq
    ON qsq.query_id = qsp.query_id
JOIN sys.query_store_query_text                 qsqt
    ON qsqt.query_text_id = qsq.query_text_id
WHERE qspf.feature_id = 2
  AND qsqt.query_sql_text LIKE '%CEFeedbackTest%'
ORDER BY qspf.last_updated_time DESC;

-- Step 6: Clean up
DROP TABLE dbo.CEFeedbackTest;

References

• Microsoft Docs: Cardinality Estimation Feedback
• Microsoft Docs: Intelligent Query Processing Feedback
• Microsoft Docs: sys.query_store_plan_feedback
• SQLYARD: Smarter Parallelism in SQL Server 2025: DOP Feedback
• SQLYARD: A Beginner-to-Advanced Guide to Query Store
• SQLYARD: SQL Server Performance Tuning Complete Guide
• SQLYARD: SQL Server Index Tuning Guide