An expression keeps producing a poor row estimate even when the same shape runs repeatedly. CE feedback for expressions gives SQL Server 2025 another way to learn from that pattern. Verify its prerequisites before expecting a change.
Start With the Estimate Error
I compare estimated and actual rows before attributing a slow plan to cardinality feedback. A slow query doesn't automatically have a cardinality problem. Blocking and expensive work can occur with accurate estimates.
An expression can combine columns or predicates in ways a simple histogram doesn't describe fully. The optimizer uses model assumptions to estimate that result. Repeated execution provides evidence about those assumptions.
SQL Server 2025 extends feedback to expression fingerprints. The fingerprint represents a recognizable expression shape. Learning can apply to repeating expressions across queries rather than only one literal statement.
The feature looks for significant estimate differences and a viable alternative model. It doesn't guarantee a correction for every expression. A repeated bad estimate still needs investigation when no useful alternative is learned.
Keep the original actual plan as a baseline. Save the expression, parameters and data distribution used by the test. A changed workload makes later plan comparisons harder to interpret.
Confirm the Prerequisites for CE Feedback for Expressions
The SQL Server feature requires SQL Server 2025. Its documented database compatibility requirement is 160 or later. Don't assume every 2025 feature requires level 170.
The database scoped configuration is CE_FEEDBACK_FOR_EXPRESSIONS. Inspect it in the database containing the workload. A matching server version doesn't configure every database identically.
The next query records version, compatibility and the relevant configuration. It doesn't change any setting. On my new SQL Server 2025 test database, both settings already showed a value of 1. Run it before collecting the baseline plan.
A database compatibility change affects more than this feature. Test any such change across the representative workload. Don't raise compatibility solely to force one example without reviewing those wider effects.
CE feedback for expressions is a specific learning mechanism. It isn't interchangeable with every setting containing CE_FEEDBACK in its name. Keep the feature name explicit in the investigation notes.
SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion;
SELECT name, compatibility_level FROM sys.databases WHERE database_id = DB_ID();
SELECT name, value, value_for_secondary
FROM sys.database_scoped_configurations
WHERE name IN (N'CE_FEEDBACK_FOR_EXPRESSIONS', N'CE_FEEDBACK');Keep Query Store Ready for Persistence
Query Store supports persisted feedback and the broader feedback investigation. Keep it enabled and in READ_WRITE mode for a complete test. Inspect actual state rather than the requested state alone.
Expression feedback uses an in-memory fingerprint cache. Persisted expression information is held internally through Query Store. That distinction matters when reading public feedback views.
A restart can lose recently learned information before its persistence cycle completes. Don't assume every observation has already become durable. Record restarts during the test interval.
Readable-secondary expression feedback has persistence limitations. A failover therefore changes what learned information remains available. Test that topology separately when it matters to the workload.
The commands below are configuration examples for an isolated test database. Record prior settings before executing them. Don't change production compatibility or Query Store policy without a workload review.
SELECT desired_state_desc, actual_state_desc, query_capture_mode_desc, readonly_reason
FROM sys.database_query_store_options;
ALTER DATABASE CURRENT SET QUERY_STORE = ON
( OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO );
ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK_FOR_EXPRESSIONS = ON;
Give CE Feedback for Expressions Repeated Evidence
One execution supplies only one observation. Feedback analysis and validation need repeating evidence. Test the same representative expression with realistic parameters over repeated calls.
The following setup creates a disposable sample. Its values illustrate a computed predicate. They do not promise that this small data set will trigger feedback.
Use a representative problematic query for the meaningful experiment. Save each actual plan and its corresponding execution context. Don't label a later plan improved without comparing the estimates and observed work.
The loop submits the same parameterized expression repeatedly. It is a test workload, not a measured performance result. No reduction in elapsed time or reads is asserted here.
I avoid adding RECOMPILE solely to make the demonstration dramatic. Recompilation and feedback interact with plan history and observation. Keep the test close to the application's normal behavior.
CREATE TABLE dbo.ExpressionFeedbackDemo
(ItemId int NOT NULL PRIMARY KEY, Amount decimal(12,2) NOT NULL, Tax decimal(12,2) NOT NULL);
INSERT dbo.ExpressionFeedbackDemo(ItemId, Amount, Tax)
SELECT n, n % 1000, n % 1000 * 0.10
FROM (SELECT TOP (5000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS s;
DECLARE @Execution int = 0;
WHILE @Execution < 20
BEGIN
EXEC sys.sp_executesql
N'SELECT COUNT_BIG(*) AS MatchingItems FROM dbo.ExpressionFeedbackDemo WHERE Amount + Tax > @Boundary;',
N'@Boundary decimal(12,2)', @Boundary = 900.00;
SET @Execution += 1;
END;Inspect Plan Feedback With Its Scope Intact
sys.query_store_plan_feedback reports plan-level feedback state. Join it to plans, queries and query text. The selected text helps connect a row to your investigation.
Read feature_desc and state_desc before interpreting feedback_data. Pending validation isn't a confirmed improvement. A rejected or regressed recommendation needs its own explanation.
This view is useful for the broader CE feedback review. It isn't a complete inventory of every expression fingerprint. Don't require one row there as proof that expression feedback exists.
The SQL below uses documented columns from the feedback view. It shows recorded plan feedback for the sample text when present. An empty result doesn't certify feature inactivity across the database. My small sample returned no rows here.
Query Store capture also influences visible query history. Confirm the relevant query was captured before drawing conclusions from missing rows. Restricted permissions can limit your inspection too.
SELECT q.query_id, p.plan_id, f.feature_desc, f.state_desc,
f.feedback_data, f.create_time, f.last_updated_time, qt.query_sql_text
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
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
WHERE qt.query_sql_text LIKE N'%ExpressionFeedbackDemo%'
ORDER BY f.last_updated_time DESC;Find Evidence of CE Feedback for Expressions
The expression fingerprint cache is exposed through sys.dm_exec_ce_feedback_cache. Review that supported 2025 view under the required inspection permissions. Its evidence complements plan-level feedback.
Showplan includes a CardinalityFeedback attribute when an expression feedback hint is applied. Inspect the saved XML for that attribute. Connect it to the operator whose estimate you are comparing.
Expression-specific Extended Events provide another observation path. Use documented events for expression hint application and telemetry. Keep that collection bounded to the test workload.
What changed in the actual row estimate after the accepted feedback? Answer that with saved plans. A configuration value of one doesn't answer it.
Keep the Outcome Measured on Your Server
The example supplies a way to collect evidence. It doesn't claim a measured improvement. Data distribution, expression shape and workload repetition determine the observed outcome.
I compare execution work as well as estimate quality. A better estimate matters because it supports better choices. The resulting plan still needs an end-to-end workload test.
CE feedback for expressions adds adaptive evidence to the optimizer's decisions. Keep its prerequisites, persistence and observation layers separate. Then report the change your server demonstrated.
The optimizer hasn't attended the meeting where everyone agreed the estimate should be better. It needs execution evidence. Repeating that evidence is more useful than repeating the complaint.
Related reading on this blog: SQL Server 2022: Cardinality Estimation (CE) Feedback for Performance and SQL SERVER 2022: Persistence and Percentile Memory Grant Feedback.
Feedback is not an immediate plan repair, it is learning that needs repeated evidence and validation.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
