SQL Server查询极慢无阻塞、执行计划正常的故障排查

表变量基数估算偏差问题 #

• SQL Server默认对表变量的基数估算固定为1行，仅2019及以上版本在兼容级别150+时默认启用表变量延迟编译。部分Windows/SQL Server补丁可能意外修改实例级trace flag、数据库兼容级别配置，导致8个存储百万级数据的表变量在做Hash Join时，基数估算严重失真，最终分配的执行内存远小于实际需求，Hash计算、排序操作大量溢出到tempdb，直接推高INSERT语句开销。
• 验证方法：抓取实际执行计划，核对每个表变量插入完成后的估算行数、实际行数值差，查看Hash Match、Sort算子是否存在Hash Warning、Sort Warnings告警，对比算子的内存授予值与实际内存使用值，若授予内存不足实际需求的1/10即可确认问题。临时验证可对语句加OPTION (RECOMPILE, QUERYTRACEON 2453)提示，或直接将表变量替换为结构一致的#开头临时表，观察耗时是否回落。

补丁引发的底层IO/内存退化 #

• 不要仅核对执行计划结构变化，需逐节点对比算子属性，重点排查补丁对存储栈、内存分配逻辑的影响：
  • 通过性能监视器抓取Avg. Disk Sec/Read、Avg. Disk Sec/Write指标，重点看tempdb所在磁盘的IO延迟，正常该值应低于10ms，若延迟升至100ms以上，会直接拖慢表变量写入、Hash溢写的速度。
  • 核对SQL Servermax server memory配置是否在实例重启后被重置，同时检查Windows层是否有补丁附带的后台进程（如Defender全盘扫描、更新服务扫描）占用大量内存，导致SQL Server缓冲池可用内存不足，基表数据缓存命中率下降，查询反复从磁盘读数据。
  • 检查杀毒软件排除规则是否在补丁更新后被重置，未将SQL Server数据文件、日志文件、tempdb目录加入扫描排除列表，IO请求被安全软件过滤拦截产生额外延迟。

并发场景下的tempdb资源争抢 #

• 你提到负载水平与故障前一致，但需重点排查多会话同时调用函数时的tempdb资源争抢：所有会话的表变量、Hash Join溢出数据都存储在tempdb，若tempdb配置不合理，高并发下会出现PFS/GAM/SGAM系统页闩锁争抢，对应等待类型为PAGELATCH_UP/PAGELATCH_SH，等待资源集中在tempdb的1:1:1、1:1:2、1:1:3号页。
• 验证方法：查询sys.dm_os_wait_stats抓取实例Top等待，结合sys.dm_tran_locks确认是否存在tempdb页闩锁等待；同时核对tempdb数据文件数量、初始大小、自动增长配置是否符合规范，比如是否仅配置1个数据文件、多数据文件大小不一致导致分配倾斜、重启后文件自动增长触发大量IO等待。
• 补充核对实例MAXDOP（最大并行度）配置是否在重启后被重置为默认值0，导致单条INSERT语句占用所有CPU核心，多会话并发时CPU资源耗尽，出现大量CXPACKET、SOS_SCHEDULER_YIELD等待。

函数与语句本身的隐性问题 #

• 首先确认该函数为多语句表值函数（MSTVF）还是内联表值函数：多语句表值函数的返回结果基数固定估算为100行（2014及以下版本为1行），若补丁或配置变更后优化器无法对其做内联优化，不仅函数本身执行无法利用并行，上游调用该函数的查询也会生成错误执行计划，高并发下性能会出现非线性下跌。
• 逐列核对INSERT语句的表变量列定义、基表列、最终Join关联列的数据类型、排序规则是否完全一致，排查补丁更新后是否出现数据类型优先级、排序规则默认值变更，引发隐式转换导致基表索引无法被利用，从索引Seek退化为全表扫描。
• 单用户环境下单独执行函数内的单条INSERT语句，记录执行耗时、CPU、逻辑读指标，和故障前基线对比：如果单用户执行耗时已经远高于故障前7分钟的总耗时，说明是语句本身的执行计划问题；如果单用户执行速度正常，仅并发场景下变慢，可确认是共享资源争抢问题。

其他易遗漏核查点 #

• 通过sys.dm_db_index_physical_stats核查关联基表（尤其是堆表）的碎片率、前推记录（Forwarded Records）数量，若堆表存在大量前推记录，全表扫描的IO量会数倍增长，即使统计信息更新也无法解决该问题。
• 若实例开启了查询存储（Query Store），直接对比补丁安装前、实例重启前、当前三个时间点同一条语句的CPU耗时、逻辑读、物理读、等待类型指标，无需人工对比执行计划即可快速定位性能退化的具体环节。
• 检查表变量是否未设置主键、索引，导致最后8个表变量做Hash Join时需要反复全表扫描，此前因内存充足缓存命中率高未暴露问题，当内存不足缓存命中率下降后，扫描开销会被指数级放大。

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