SQL Server 2016新CE引发查询解析编译耗时过高问题咨询

• 使用搭载*Cardinality Estimator（CE，基数估计器）*新版的SQL Server 2016/2019时，发现部分查询性能较旧版CE环境慢10倍
• 与公开资料中常见的CE导致执行计划劣化场景不同，本次问题的耗时全部集中在SQL Server parse and compile time阶段，且新旧环境生成的执行计划完全一致：表T上8%成本为索引查找（index seek），92%成本为聚集索引更新（Clustered index update）

测试语句 #

UPDATE T
SET Value = 0.703645756
WHERE Col1 = '05/01/2022'
AND Col2 = '57XGOXYBT4OMXIFI'
AND Col3 = 372
AND Col4 = 'XX7R78OLRVJX9J2U'
AND Col5 <> 0.703645758

不同环境执行指标对比 #

SQL Server 2008（旧版CE） #

SQL Server parse and compile time:
CPU time = 78 ms, elapsed time = 83 ms.
Table 'T'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

• 后续二次解析编译CPU time = 0 ms，elapsed time = 0 ms
• 执行阶段CPU time几乎为0
• 最终影响行数：1行，完成时间：2022-06-23T12:17:27.7728479+02:00

SQL Server 2016（默认新版CE） #

SQL Server parse and compile time:
CPU time = 1078 ms, elapsed time = 1108 ms.

• 首次编译后触发二次解析编译，CPU time = 153 ms，elapsed time = 153 ms
• 表T逻辑读为9，其余IO指标与SQL Server 2008环境基本一致
• 执行阶段总CPU time = 157 ms, elapsed time = 155 ms
• 最终影响行数：1行，完成时间：2022-06-23T12:18:47.7376589+02:00

已验证临时方案 #

所有回退至旧版CE的操作均可解决该编译耗时过高问题，包括：

• 调整数据库兼容级别（compatibility level）到对应旧版本
• 数据库级别强制使用旧版CE
• 语句级别添加查询提示强制使用旧CE

问题解答 #

这是新版CE上线后存在的典型编译阶段性能缺陷，不少运维和开发人员都遇到过同类场景，和常见的CE导致执行计划选错的问题完全不属于同一类。

触发原因 #

新版CE在处理多等值条件+非等值谓词组合的查询时，默认会启动统计信息交叉相关性计算逻辑：哪怕所有过滤列的统计信息都已经完全加载、不需要回表读取额外统计数据，新版CE的启发式规则仍会多轮遍历各列直方图做联合基数估算，整个计算过程完全在编译阶段完成，计算成本不会计入最终执行计划的估算成本，因此才会出现新旧环境执行计划完全一致、执行阶段耗时几乎无差，但编译阶段耗时差出10倍以上的现象。
观测到的首次编译后触发二次编译也是这个缺陷的典型表现：第一轮编译完成后，CE的计算结果触发了内部的计划合理性校验阈值，强制触发一次完整重编译，进一步拉长了总编译耗时。

修复进展 #

• 目前SQL Server 2016、2019的最新累积更新已经修复了该缺陷的大部分触发路径，但如果过滤列中包含高精度浮点数/高精度数值类型（比如测试语句中Col5、Value列使用的高小数位精度类型），仍有概率触发问题。
• 微软没有针对该问题发布单独的热修复补丁，SQL Server 2022中引入的CE反馈功能是当前官方给出的长效解决方案：该功能会自动识别编译耗时异常的语句，自动切换到旧版CE逻辑处理对应语句，不需要手动调整兼容级别或加查询提示。

其他规避方案 #

除了已经验证过的全局/语句级回退旧CE的方案，不需要全局修改CE配置也能规避问题：

• 对这类高频执行的简单DML语句，可以创建计划指南固定执行计划，或者开启对应语句的强制参数化，避免每次执行都触发完整的CE计算流程
• 给语句添加OPTIMIZE FOR编译提示，直接传入已知的过滤字面值，跳过CE遍历直方图做交叉计算的过程
• 如果表上已经创建了覆盖所有等值过滤列的联合索引，可以添加表级索引提示，直接指定优化器使用该索引作为起始访问路径，减少CE枚举候选执行计划时的计算量

内容的提问来源于stack exchange，提问作者steevi2307