SQL Server 2022 – Cardinality Estimation (CE) Feedback for Performance

June 27, 2023

SQL Server 2022 - Cardinality Estimation (CE) Feedback for Performance ce-feedback-800x577 SQL Server 2022 (16.x) introduces an enhanced Cardinality Estimation (CE) feedback feature as part of the intelligent query processing family. This feature addresses suboptimal query execution plans caused by incorrect assumptions made by the CE model. In this blog post, we will explore the key aspects of CE feedback and how it improves query plan quality. I have prepared this note for one of my clients who regularly work with me on Comprehensive Database Performance Health Check and now wants to upgrade to SQL Server 2022.

Understanding Cardinality Estimation (CE)

Cardinality Estimation (CE) is the process by which the Query Optimizer estimates the total number of rows processed at each level of a query plan. SQL Server derives cardinality estimation primarily from histograms created during index or statistics creation. CE also considers constraint information and logical query rewrites to determine cardinality.

Different versions of the SQL Server Database Engine use different CE model assumptions based on data distribution and query patterns.

Solving Performance Issues with CE Feedback

Cardinality Estimation (CE) feedback, introduced in SQL Server 2022 (16.x), is an essential component of the intelligent query processing features that aims to address performance issues stemming from suboptimal query execution plans caused by incorrect CE model assumptions. This functionality is particularly valuable when upgrading from older versions of the Database Engine, as it helps reduce regression risks. Given the vast array of customer workloads and data distributions, no single set of CE models and assumptions can cater to all scenarios. CE feedback offers an adaptable solution by leveraging query runtime characteristics to identify and utilize a model assumption that better fits a given query and data distribution. Its primary focus lies in identifying plan operators where the estimated number of rows significantly deviates from the actual number of rows, applying feedback when significant model estimation errors occur and there are alternative viable models to try.

How CE Feedback Works

CE feedback operates by continuously learning and adapting to determine the most suitable CE model assumptions. The process begins by identifying model-related assumptions and evaluating their accuracy for recurring queries. If an assumption appears to be incorrect, the subsequent execution of the same query involves testing a modified query plan that adjusts the influential CE model assumption. The effectiveness of this adjustment is assessed by comparing the actual versus estimated rows from plan operators. It’s important to note that not all errors can be corrected using the available model variants in CE feedback.

If the adjusted assumption improves the quality of the query plan, the old plan is replaced with a new plan that incorporates the appropriate USE HINT query hint. This hint allows for adjustment of the estimation model through the Query Store hint mechanism. The only feedback that has been verified and demonstrates improved performance is persisted. However, if the adjusted model assumption leads to a performance regression or if the query is canceled by the user, CE feedback is not applied to that query. By utilizing this intelligent feedback mechanism, SQL Server can enhance the accuracy of its cardinality estimation, resulting in improved query performance and optimized execution plans.

CE Feedback Scenarios

CE feedback specifically targets scenarios where the default CE (CE120 or higher) produces suboptimal plans due to incorrect model assumptions. Let’s explore some of these scenarios:

Correlation

Correlation assumptions influence the selectivity estimation of predicates on tables or views. CE feedback can adjust between fully independent, partially correlated, and fully correlated assumptions based on the database compatibility level and the actual versus estimated cardinality.

Join Containment

Join containment assumptions affect the selectivity estimation of join and filter predicates. CE feedback can adjust between simple containment and base containment assumptions based on the database compatibility level.

Optimizer Row Goal

When a row goal is specified in a query, the Query Optimizer aims to find a plan that returns a smaller number of rows to reduce I/O. CE feedback can disable row goal scans and enable seeks when inefficiencies occur due to non-uniform data distribution.

Monitoring CE Feedback with T-SQL

To monitor the activity and results of CE feedback, you can use the following T-SQL query:

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

This query retrieves information from the Query Store tables to display details about queries and plans with CE feedback. It includes the query ID, SQL text, query plan, feature description, state description, feedback data, creation time, and last updated time.

Enabling and Disabling CE Feedback

CE feedback is, by default, enabled in SQL Server 2022 along with Query Store features. To enable CE feedback, set the database compatibility level to 160 or higher. The Query Store must be enabled and in READ_WRITE mode for the relevant databases.

To change compatibility at the database level, use the following T-SQL code:

USE [master]
GO
ALTER DATABASE [YourDB] SET COMPATIBILITY_LEVEL = 160
GO

To enable query store at the database level you can use the following script:

-- Enable Query Store at the database level
USE [master]
GO
ALTER DATABASE [DbName] SET QUERY_STORE = ON
GO
ALTER DATABASE [DbName] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)
GO

To enable CE feedback at the database level, use the following T-SQL code:

-- Enable CE feedback at the database level
ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = ON;
GO

To disable CE feedback at the database level, use the following T-SQL code:

-- Enable CE feedback at the database level
ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFF;
GO

To disable CE feedback at the query level, use the DISABLE_CE_FEEDBACK query hint:

SELECT *
FROM YourTableName
OPTION (DISABLE_CE_FEEDBACK);

There are two crucial considerations regarding the usage of Cardinality Estimation (CE) feedback:

Query Plan Forced Through Query Store: If a query has a query plan forced through Query Store, CE feedback will not be applied to that specific query. The query plan enforced through Query Store takes precedence over CE feedback adjustments.
Hard-Coded Query Hints or User-Set Query Store Hints: If a query includes hard-coded query hints or utilizes Query Store hints set by the user, CE feedback will not be utilized for that particular query. The presence of explicit query hints overrides the CE feedback mechanism.

Conclusion

Improved Cardinality Estimation (CE) feedback in SQL Server 2022 and beyond enhances the query optimization process by dynamically adjusting model assumptions. This adaptive feature significantly improves query plan quality by learning from query runtime characteristics.

CE feedback addresses correlation, join containment, and optimizer row goal scenarios, leading to more accurate cardinality estimation and better performance. By monitoring CE feedback activity and using T-SQL queries, you can gain insights into the impact and effectiveness of CE feedback in your environment. You can always reach out to me on YouTube.

Reference: Pinal Dave (http://blog.SQLAuthority.com)