SQL Server 2019查询各组件可正常运行但整体无限挂起如何排查

核心问题原因 #

你遇到的是典型的SQL Server优化器执行计划选择错误问题：多层嵌套IN子查询场景下，优化器大概率错误估算了关联行数，选择了嵌套循环连接，也就是外层3.5万条DATA_TABLE记录逐行去匹配内层子查询逻辑，相当于把内层300条结果的查询重复执行了3.5万次，自然会无限挂起。加NOLOCK只解决锁问题，对执行计划错误导致的性能问题无效。

排查步骤 #

1. 查看实际执行计划：运行查询前勾选SSMS的【包括实际的执行计划】选项，确认执行计划是否出现DATA_TABLE作为外层表的嵌套循环连接，且内层为子查询的组合逻辑，同时核对各步骤的估算行数和实际行数偏差是否超过10倍，偏差过大就是统计信息过时或者优化器估算错误。
2. 检查数据类型一致性：确认DATA_TABLE.LOOKUP_FIELD和REFERENCE_TABLE.LOOKUP_FIELD、REFERENCE_TABLE.CODE和QUEUE_1.CODE/QUEUE_2.CODE的数据类型完全一致，隐形类型转换会导致索引失效，大幅拉低关联效率。
3. 核对统计信息状态：执行以下语句更新涉及表的统计信息，过时的统计信息是优化器选错执行计划的常见诱因：

UPDATE STATISTICS dbo.DATA_TABLE
UPDATE STATISTICS dbo.REFERENCE_TABLE
UPDATE STATISTICS dbo.QUEUE_1
UPDATE STATISTICS dbo.QUEUE_2

最优解决方案 #

推荐用临时表固化内层子查询结果，强制优化器按照你验证过的高效顺序执行逻辑，完全规避执行计划选错的问题，代码如下：

-- 提前计算符合条件的LOOKUP_FIELD，和你单独执行第二层子查询逻辑一致
SELECT DISTINCT LOOKUP_FIELD
INTO #TempValidLookup
FROM dbo.REFERENCE_TABLE
WHERE
    FIELD_TYPE = 0
    AND CODE IN (
        SELECT CODE FROM dbo.QUEUE_1 WHERE DATETIME_ACKNOWLEDGED IS NULL
        -- 如果QUEUE_1和QUEUE_2的CODE没有重复，把UNION改成UNION ALL可以再提速
        UNION
        SELECT CODE FROM dbo.QUEUE_2 WHERE DATETIME_ACKNOWLEDGED IS NULL
    )

-- 给临时表加聚集索引，关联时直接走索引匹配
CREATE CLUSTERED INDEX IX_TempValidLookup_LookupField ON #TempValidLookup(LOOKUP_FIELD)

-- 主查询直接关联临时表，3.5万条匹配300条基本毫秒级完成
SELECT DISTINCT
    d.LOOKUP_FIELD,
    d.VALUE_FIELD
FROM dbo.DATA_TABLE d
INNER JOIN #TempValidLookup t ON d.LOOKUP_FIELD = t.LOOKUP_FIELD
WHERE d.VALUE_FIELD != 'XXXXXXXXXXX'

-- 清理临时表
DROP TABLE IF EXISTS #TempValidLookup

替代方案（不使用临时表） #

如果场景不允许用临时表，可以把嵌套IN改成显示JOIN，同时加查询提示强制优化器用更适合大表关联的哈希连接：

SELECT DISTINCT
    d.LOOKUP_FIELD,
    d.VALUE_FIELD
FROM dbo.DATA_TABLE d
INNER JOIN (
    SELECT DISTINCT r.LOOKUP_FIELD
    FROM dbo.REFERENCE_TABLE r
    INNER JOIN (
        SELECT CODE FROM dbo.QUEUE_1 WHERE DATETIME_ACKNOWLEDGED IS NULL
        UNION
        SELECT CODE FROM dbo.QUEUE_2 WHERE DATETIME_ACKNOWLEDGED IS NULL
    ) q ON r.CODE = q.CODE
    WHERE r.FIELD_TYPE = 0
) t ON d.LOOKUP_FIELD = t.LOOKUP_FIELD
WHERE d.VALUE_FIELD != 'XXXXXXXXXXX'
OPTION (HASH JOIN)

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