基数估计 (CE) 反馈

  • 项目

适用于：SQL Server 2022 (16.x) 及更高版本。

从 SQL Server 2022 (16.x) 开始，基数估算 (CE) 反馈是智能查询处理功能系列的一部分，当使用欠佳查询执行计划进行重复查询的问题由错误的 CE 模型假设引起时，它可以处理这些问题。 从旧版数据库引擎升级时，此方案有助于降低与默认 CE 相关的回归风险。

由于没有一组单一的 CE 模型和假设可以适应多种客户工作负载和数据分布，因此 CE 反馈提供了一个基于查询运行时特征的适应性解决方案。 CE 反馈将识别并使用更适合给定查询和数据分布的模型假设，以提高查询执行计划的质量。 目前，CE 反馈可以识别计划运算符，其中估计的行数和实际行数相差很大。 当出现重大模型估计错误时，将应用反馈，并且有可行的替代模型可供尝试。

有关其他查询反馈功能，请参阅内存授予反馈和并行度 (DOP) 反馈。

了解基数估算 (CE) 反馈

基数估算 (CE) 是查询优化器估算在查询计划的每个级别处理的总行数的方法。 SQL Server 中的基数估计主要派生自手动或自动创建索引或统计信息时所创建的直方图。 有时，SQL Server 还使用查询的约束信息和逻辑重写来确定基数。

不同版本的数据库引擎根据数据的分布和查询方式使用不同的 CE 模型假设。 有关详细信息，请参阅 CE 的版本。

基数估算 (CE) 反馈实现

基数估算 (CE) 反馈随着时间的推移了解哪些 CE 模型假设是最优的，然后应用一直以来最正确的假设：

  1. CE 反馈识别与模型相关的假设并评估它们对于重复查询是否准确。

  2. 如果假设看起来不正确，则使用查询计划测试同一查询的后续执行，该查询计划会调整有影响的 CE 模型假设并验证其是否有帮助。 我们通过查看计划运算符的实际行数与估计行数来识别错误。 并非所有错误都可以通过 CE 反馈中提供的模型变体来纠正。

  3. 如果它提高了计划质量，则将旧查询计划替换为使用相应 USE HINT 查询提示调整估计模型的查询计划，通过查询存储提示机制实现。

仅保留经过验证的反馈。 如果调整后的模型假设导致性能回归，则不将 CE 反馈用于该查询。 在这种情况下，用户取消的查询也被视为回归。

基数估算 (CE) 反馈应用场景

基数估算 (CE) 反馈解决了使用默认 CE（CE120 或更高版本）时错误 CE 模型假设导致的被视为回归的问题，并且可以选择性地使用不同模型假设。 应用场景包括关联、联接包含和优化器行目标。

基数估算 (CE) 反馈相关性

当查询优化器估计给定表或视图上谓词的选择性或满足所述谓词的行数时，它使用关联模型假设。 这些假设可以是以下类型的谓词：

  • 完全独立（CE70 的默认设置），其中基数通过乘以所有谓词的选择性来计算。

  • 部分关联（CE120 或更高版本的默认设置），其中基数是使用指数退避的变体计算的，按照从最高选择性谓词到最低选择性谓词的顺序对选择性排序。

  • 完全关联，其中基数是使用所有谓词的最低选择性计算的。

以下示例在数据库兼容性设置为 120 或更高时使用部分关联：

USE AdventureWorks2016_EXT;
GO
SELECT AddressID, AddressLine1, AddressLine2
FROM Person.Address
WHERE StateProvinceID = 79 AND City = N'Redmond';
GO

当数据库兼容性设置为 160 并使用默认相关性时，CE 反馈根据与实际行数相比，估算基数是被低估还是高估，一步步尝试将相关性移向正确的方向。 如果实际行数大于估计基数，则使用完全关联。 如果实际行数小于估计基数，则使用完全独立。

有关详细信息，请参阅 CE 的版本。

基数估算 (CE) 反馈联接包含

当查询优化器估计联接谓词和适用的筛选器谓词的选择性时，它将使用包含假设模型。 这些假设包括：

  • 简单包含（CE70 的默认设置），假设联接谓词完全关联，其中首先计算筛选器选择性，然后将联接选择性考虑在内。

  • “基本包含”（CE120 和更高版本的默认设置），假设联接谓词与下游筛选器之间没有相关性，其中首先计算联接选择性，然后将筛选器选择性考虑在内。

以下示例在数据库兼容性设置为 120 或更高时使用基本包含：

USE AdventureWorksDW2016_EXT;
GO
SELECT *
FROM dbo.FactCurrencyRate AS f
INNER JOIN dbo.DimDate AS d ON f.DateKey = d.DateKey
WHERE d.MonthNumberOfYear = 7 AND f.CurrencyKey = 3 AND f.AverageRate > 1;
GO

有关详细信息，请参阅 CE 的版本。

基数估算 (CE) 反馈和查询优化器行目标

当查询优化器估计执行计划的基数时，它通常假设必须处理所有表中所有符合条件的行。 但是，某些查询模式会导致查询优化器搜索返回较少行数的计划，以减少 I/O。 如果查询通过使用 TOP、IN 或 EXISTS 关键字、FAST 查询提示或 SET ROWCOUNT 语句，指定了在运行时可能要求的目标行数（行目标），则该行目标将用作查询优化过程的一部分，如以下示例所示：

USE AdventureWorks2016_EXT;
GO
SELECT TOP 1 soh.*
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID;
GO

应用行目标计划时，查询计划中的估计行数会减少，因为查询优化器假设必须处理较少的行数才能达到行目标。

虽然行目标对于某些查询模式是一种有益的优化策略，但如果数据分布不均匀，扫描的页数可能比估算的多，这意味着行目标变得低效。 CE 反馈可以禁用行目标扫描，并在检测到这种低效时启用查找。

在执行计划中，没有特定于 CE 反馈的属性，但会为查询存储提示列出一个属性。 查找 CE feedback 的 QueryStoreStatementHintSource。

有关基数估算 (CE) 反馈的注意事项

  • 要启用基数估算 (CE) 反馈，请在为执行查询时连接的数据库启用数据库兼容性级别 160。 对于使用 CE 反馈的每个数据库，必须启用查询存储并处于 READ_WRITE 模式。

  • 若要在数据库级别禁用 CE 反馈，请使用 CE_FEEDBACK数据库范围的配置。 例如，在用户数据库中执行：

    ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFF;

  • 若要在查询级别禁用 CE 反馈，请使用 DISABLE_CE_FEEDBACK 查询提示。

CE 反馈活动通过 query_feedback_analysis 和 query_feedback_validation XEvents 可见。

可以使用 sys.query_store_query_hints 目录视图跟踪 CE 反馈设置的提示。

可以使用 sys.query_store_plan_feedback 目录视图跟踪反馈信息。

如果查询具有通过查询存储强制执行的查询计划，则 CE 反馈不会用于该查询。

如果查询使用硬编码查询提示或在使用用户设置的查询存储提示，则 CE 反馈不会用于该查询。 有关详细信息，请参阅提示 (Transact-SQL) - 查询和查询存储提示。

从 SQL Server 2022 (16.x) 开始，当启用次要副本的查询存储时，CE 反馈对于可用性组中的次要副本不是副本感知的。 CE 反馈目前仅对主要副本有益。 故障转移时，应用于主要副本或次要副本的反馈将会丢失。 有关详细信息，请参阅次要副本的查询存储。

基数估算 (CE) 反馈的持久性

适用于：SQL Server（SQL Server 2022 (16.x) 及更高版本）

基数估算 (CE) 反馈可以检测应持久化行目标优化的应用场景，并通过以查询存储提示的形式将其保存在查询存储中来保留此更改。 新的优化将用于未来的查询执行。 CE 反馈将在行目标优化查询模式之外保留其他应用场景，如反馈应用场景中所述。 CE 反馈当前处理 CE 相关性模型使用的谓词选择性应用场景，以及 CE 包含模型处理的联接谓词应用场景。

此功能是在 SQL Server 2022 (16.x) 中引入的，但是此性能增强可用于在数据库兼容性级别 160 或更高版本或 160 及更高版本的 QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n 提示中运行的查询，并且当数据库已启用查询存储并处于“read write”状态时。

基数估算 (CE) 反馈的已知问题

问题 发现日期 状态 解决日期
在特定条件下为 SQL Server 2022 (16.x) 应用累积更新 8 后，SQL Server 性能降低。 启用 CE 反馈后，可能会遇到计划高速缓存利用率急剧上升以及 CPU 利用率意外增加。 2023 年 12 月 有解决方法

已知问题详细信息

在特定条件下为 SQL Server 2022 应用累积更新 8 后，SQL Server 性能降低

从 SQL Server 2022 (16.x) 累积更新 8 开始，SQL Server 可能会显示 CPU 和内存利用率意外增加。 此外，还可能会观察到 RESOURCE_SEMAPHORE_QUERY_COMPILE 等待的增加。 可能还会注意到，使用该计划缓存限制的计划缓存对象数量稳步增加，接近计划缓存限制，使用诸如 ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE、DBCC FREESYSTEMCACHE 或 DBCC FREEPROCCACHE 之类的技术手动清除计划缓存没有任何帮助。 只有少数客户观察到这种行为。

此问题不会影响所有工作负载，具体取决于已生成的不同计划数，以及符合 CE 反馈功能参与资格的计划数。 CE 反馈分析发生重大模型错误估算的计划运算符期间，存在这样一种应用场景：在此分析阶段，引用的计划可能会在内存中取消引用，而不允许随后通过正常的最近最少使用的 (LRU) 算法从内存中删除该计划。 LRU 机制是 SQL Server 强制实施计划逐出策略的一种方法。 如果系统面临内存压力，SQL Server 还将从内存中删除计划。 当 SQL Server 尝试删除未通过正确方式取消引用的计划时，它无法从计划缓存中删除这些计划，导致缓存继续增长。 不断增长的缓存可能会导致额外的编译，最终会使用更多 CPU 和内存。 有关详细信息，请参阅计划缓存内部。

症状：随着时间的推移，SQL 计划或对象计划中正在使用并被标记为“脏”的计划缓存条目数会增加到 50,000 或更多。 如果观察到计划缓存条目开始接近此级别，并且 CPU 利用率意外增加，则系统可能会遇到此问题。 SQL Server 2022 (16.x) 累积更新 9 中提供了相关修补程序。 请参阅 KB5030731。 该修补程序尝试解决当基数估计 (CE) 反馈尝试获取关联配置文件时，计划缓存条目被逐出导致内存损坏的问题。 针对此问题的其他修补程序将在即将发布的累积更新中提供。

要监视系统正在使用的计划缓存条目数，以下示例可用作现有计划缓存条目数的时间点视图。 例如，定期观察标记为“脏”的计划缓存条目的数量是监视这种现象的一种方法。

SELECT
  CASE
    WHEN mce.[name] LIKE 'SQL Plan%' THEN 'SQL Plans'
    WHEN mce.[name] LIKE 'Object Plan%' THEN 'Object Plans'
    ELSE '[All other cache stores]'
  END AS PlanType,
  COUNT(*) AS [Number of plans marked to be removed]
FROM sys.dm_os_memory_cache_entries AS mce
LEFT OUTER JOIN sys.dm_exec_cached_plans AS ecp 
  ON mce.memory_object_address = ecp.memory_object_address
WHERE mce.is_dirty = 1
AND ecp.bucketid is NULL
GROUP BY
  CASE
    WHEN mce.[name] LIKE 'SQL Plan%' THEN 'SQL Plans'
    WHEN mce.[name] LIKE 'Object Plan%' THEN 'Object Plans'
    ELSE '[All other cache stores]'
  END;

另一组查询还将提供与上一个示例相同的信息，同时允许观察其他性能指标。 计划缓存命中率将会降低，与每秒批处理请求数相关的编译数也会减少。以下查询可用于一段时间内监视系统。 关注“缓存命中率”（意外下降）、“使用中的缓存对象”（计数增加到接近 50,000 的水平而不减少）以及低于预期的“每秒批处理请求”比率（与“每秒编译次数”的增加相比）。

--SQL Plan (Adhoc and Prepared plans)
SELECT
    CASE 
        WHEN [counter_name] = 'Cache Hit Ratio' THEN 'Cache Hit Ratio'
        WHEN [counter_name] = 'Cache Object Counts' THEN 'Cache Object Counts'
        WHEN [counter_name] = 'Cache Objects in use' THEN 'Cache Objects in use'
        WHEN [counter_name] = 'Cache Pages' THEN 'Cache Pages'
    END AS [SQLServer:Plan Cache (SQL Plans)],
    CASE
        WHEN [counter_name] = 'Cache Hit Ratio' THEN NULL
        ELSE FORMAT(cntr_value, '#,###')
    END AS [Counter Value],
    CASE
        WHEN [counter_name] = 'Cache Hit Ratio' THEN
            FORMAT(TRY_CONVERT(DECIMAL(5, 2), (cntr_value * 1.0 / NULLIF((SELECT cntr_value 
        FROM sys.dm_os_performance_counters WHERE 
        [object_name] LIKE '%:Plan Cache%' AND [counter_name] = 'Cache Hit Ratio Base'
        AND instance_name LIKE 'SQL Plan%'), 0))), '0.00%')
    END AS [SQL Plan Cache Hit Ratio]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%:Plan Cache%'
    AND [counter_name] IN ('Cache Hit Ratio', 'Cache Object Counts', 'Cache Objects in use', 'Cache Pages')
    AND instance_name LIKE 'SQL Plan%'
ORDER BY [counter_name];

--Module/Stored procedure based plans
SELECT
    CASE 
        WHEN [counter_name] = 'Cache Hit Ratio' THEN 'Cache Hit Ratio'
        WHEN [counter_name] = 'Cache Object Counts' THEN 'Cache Object Counts'
        WHEN [counter_name] = 'Cache Objects in use' THEN 'Cache Objects in use'
        WHEN [counter_name] = 'Cache Pages' THEN 'Cache Pages'
    END AS [SQLServer:Plan Cache (Object Plans)],
    CASE
        WHEN [counter_name] = 'Cache Hit Ratio' THEN NULL
        ELSE FORMAT(cntr_value, '#,###')
    END AS [Counter Value],
    CASE
        WHEN [counter_name] = 'Cache Hit Ratio' THEN
            FORMAT(TRY_CONVERT(DECIMAL(5, 2), (cntr_value * 1.0 / NULLIF((SELECT cntr_value 
        FROM sys.dm_os_performance_counters WHERE 
        [object_name] LIKE '%:Plan Cache%' AND [counter_name] = 'Cache Hit Ratio Base'
        AND instance_name LIKE 'Object Plan%'), 0))), '0.00%')
    END AS [SQL Plan Cache Hit Ratio]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%:Plan Cache%'
    AND [counter_name] IN ('Cache Hit Ratio', 'Cache Object Counts', 'Cache Objects in use', 'Cache Pages')
    AND instance_name LIKE 'Object Plan%'
ORDER BY [counter_name];

SELECT
    CASE
        WHEN [counter_name] = 'Batch Requests/sec' THEN 'Batch Requests/sec'
        WHEN [counter_name] = 'SQL Compilations/sec' THEN 'SQL Compilations/sec'
    END AS [SQLServer:SQL Statistics],
    FORMAT(cntr_value, '#,###') AS [Counter Value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%:SQL Statistics%'
AND counter_name IN ('Batch Requests/sec', 'SQL Compilations/sec' 
);

解决方法：如果系统遇到上述症状，可以在数据库级别禁用 CE 反馈功能，直到有其他可用的修复程序。 要回收此问题占用的计划缓存内存，需要重启 SQL Server 实例。 禁用 CE 反馈功能后，可以执行此重启操作。 若要在数据库级别禁用 CE 反馈，请使用 CE_FEEDBACK数据库范围的配置。 例如，在用户数据库中执行：

ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFF;

反馈和报告问题

有关反馈或问题，请发送电子邮件至 CEFfeedback@microsoft.com

相关内容