Monitor Cardinality Feedback in SQL Server 2022

It’s possible for you to see new technology at work if you use Extended Events to monitor cardinality feedback. To put it simply, cardinality, the number of rows being returned, is estimated by SQL Server. Sometimes, it gets these estimates right. Sometimes, it gets them wrong. New functionality within SQL Server 2022 uses Query Store to see how well those estimates are working. If they’re off, the optimizer can actually change plans to get you different behaviors based on this feedback.

There’s even more than one way to monitor cardinality feedback. Let’s talk about it.

Extended Events

First up, to really see the full set of behaviors in action, we can use Extended Events:

CREATE EVENT SESSION [CardinalityFeedback]
ON SERVER
    ADD EVENT sqlserver.query_ce_feedback_telemetry,
    ADD EVENT sqlserver.query_feedback_analysis,
    ADD EVENT sqlserver.query_feedback_validation,
    ADD EVENT sqlserver.sql_batch_completed
    ADD TARGET package0.event_file
    (SET FILENAME = N'CardinalityFeedback')
WITH
(
    TRACK_CAUSALITY = ON
);

If I were running this on a production instance, I’d probably want more filtering in place to reduce the amount of data captured. However, you see the basics. We can get the CE feedback telemetry, how it’s capturing data for this, the analysis and the validation of the analysis, all as different events. By adding in batch complete (and/or rpc complete) you can then add in causality tracking to see how it all comes together in real time.

I have an example in my new book (releasing soon) showing this in action.

Catalog Views

The way it works is of course through plan forcing to use hints to drive the choice of a different plan. So, you can query sys.query_store_query_hints to see those forced plans. You can also query sys.query_store_plan_feedback to see the CE feedback, as well as other plan feedback (parallel plans, others, oh, 2022 is cool) for other operations.

SELECT qsq.query_id,
       qsqt.query_sql_text,
       qsp.query_plan,
       qspf.feature_desc,
       qspf.state_desc,
       qspf.feedback_data
FROM sys.query_store_query AS qsq
    JOIN sys.query_store_plan AS qsp
        ON qsp.query_id = qsq.query_id
    JOIN sys.query_store_query_text AS qsqt
        ON qsqt.query_text_id = qsq.query_text_id
    JOIN sys.query_store_plan_feedback AS qspf
        ON qspf.plan_id = qsp.plan_id
WHERE qspf.feature_id = 1;

The results can look like this:

I have three states shown here for the queries I’ve been testing; VERIFICATION_REGRESSED, VERIFICATION_PASSED, NO_RECOMMENDATION. In short, the first query had some CE anomolies, but performance got worse after trying hints. The second query on the other hand, passed. The rest of the queries were investigated, but didn’t make the cut. We really can see what’s happening with our queries.

I added this query because it is possible to use these views to observe some of the behavior, but it’s just not as complete as what you get if you monitor cardinality feedback using Extended Events.

Conclusion

The key point here isn’t the cool cardinality estimation feedback, which is cool. No, instead, it’s the importance that you need to place on learning how to use Extended Events. As we move more and more to newer functionality within SQL Server, separated farther and farther from SQL Server 2008, the last release with any kind of updates to Trace Events (Profiler), Extended Events skills are more important. You simply can’t do this kind of work in Trace, and you will need to do this kind of work.