添加WHERE子句条件后SQL Server查询性能骤降问题排查

在SQL Server 2022 Express（v16.0.4120.1）环境中，给查询的WHERE子句添加一行简单条件后，性能出现断崖式下降，具体情况如下：

慢查询（耗时15秒以上） #

SELECT 
    HSP.ServiceProviderID AS SP, *
FROM 
    ALPCensus
LEFT JOIN 
    ALPPriorApproval PA ON PA.SSNumber = ALPCensus.SSNumber  
LEFT JOIN 
    ALPHeader ON ALPHeader.PartnerID = PA.PartnerID
LEFT JOIN 
    HomeServiceProviders HSP ON HSP.ServiceProviderID = ALPHeader.ServiceProviderID
LEFT JOIN 
    HomeMastResident HMR ON HMR.SSNumber = ALPCensus.SSNumber  
WHERE 
    [Day] BETWEEN PA.StartDate AND PA.EndDate
    AND [Day] < '2024-05-21'
    AND PA.IsPending = 0
    AND HSP.IsCapitation = 0
    AND [Status] IN ('*', 'P')
    AND Billed = 0
    AND HSP.ServiceProviderID = 136   -- 该行条件为快查询所缺失

快查询（移除最后一行条件，耗时不足1秒） #

SELECT 
    HSP.ServiceProviderID AS SP, *
FROM 
    ALPCensus
LEFT JOIN 
    ALPPriorApproval PA ON PA.SSNumber = ALPCensus.SSNumber  
LEFT JOIN 
    ALPHeader ON ALPHeader.PartnerID = PA.PartnerID
LEFT JOIN 
    HomeServiceProviders HSP ON HSP.ServiceProviderID = ALPHeader.ServiceProviderID
LEFT JOIN 
    HomeMastResident HMR ON HMR.SSNumber = ALPCensus.SSNumber  
WHERE 
    [Day] BETWEEN PA.StartDate AND PA.EndDate
    AND [Day] < '2024-05-21'
    AND PA.IsPending = 0
    AND HSP.IsCapitation = 0
    AND [Status] IN ('*', 'P')
    AND Billed = 0

关键现象与排查信息 #

• 仅添加AND HSP.ServiceProviderID = 136这一行条件，就导致查询性能急剧下降；但添加AND HSP.ServiceProviderID IS NOT NULL时，查询仍保持快速执行
• 已确认HSP.ServiceProviderID为int类型，服务器资源充足，相关表索引配置齐全
• 数据库设置无异常
• 执行计划差异：慢查询采用了低效的执行路径，快查询则使用了高效的连接策略；当将数据库兼容级别改为2008或2012后，原慢查询的执行计划恢复高效逻辑，查询瞬时完成

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