SQL Server查询优化器弃用筛选索引选择非筛选索引问题咨询

你的筛选索引使用并无错误 #

你创建的筛选索引IX_cod_ponto_hora_leit设计逻辑是合理的：

• 索引键列cod_ponto和hora_leit完全匹配查询的过滤条件（COD_PONTO = 610、HORA_LEIT <= '2022-09-29 12:25:00.000'）以及排序需求（ORDER BY HORA_LEIT DESC）
• 筛选条件WHERE VAZAO IS NOT NULL与查询中的过滤逻辑一致，能够缩小索引的存储范围，降低索引维护和查询时的IO成本
• INCLUDE (vazao)确保索引覆盖了查询所需的所有字段，避免了回表操作

查询优化器选择非筛选索引的常见原因 #

SQL Server查询优化器选择IX_cod_ponto_hora_leit_St_bomba而非你的筛选索引，通常有以下几种可能性：

• 统计信息过时：如果表的统计信息未及时更新，优化器无法准确评估筛选索引对应的实际数据行数，可能误判非筛选索引的查询成本更低
• 索引维护状态差异：非筛选索引的碎片更少、数据页密度更高，导致优化器认为它的IO开销更小
• 数据分布特性：如果表中VAZAO IS NOT NULL的行占比极高（比如超过90%），筛选索引的范围优势会被弱化，优化器可能认为两者性能差异可忽略，甚至非筛选索引因无需额外判断筛选条件而更快
• 索引使用历史：若该非筛选索引此前被频繁调用，优化器可能会倾向于选择它（此因素影响相对较小）

是否需要替换为无筛选索引？ #

不一定，建议先完成以下排查再做决定：

1. 更新统计信息：执行UPDATE STATISTICS DADOS;后重新运行查询，观察优化器是否会切换到筛选索引
2. 对比执行计划：查看实际执行计划的成本估算，对比两个索引的逻辑读、物理读等指标，判断哪个索引的实际性能更优
3. 评估数据分布：执行以下查询统计数据分布：

SELECT 
    COUNT(*) AS 总行数,
    COUNT(CASE WHEN VAZAO IS NOT NULL THEN 1 END) AS VAZAO非空行数
FROM DADOS;

如果VAZAO非空行占比很低（比如低于30%），筛选索引的优势显著，应保留并尝试引导优化器选择它；如果占比极高，筛选索引的意义不大，可以考虑换成无筛选索引，或直接保留现有的非筛选索引

若排查后优化器仍不选择筛选索引，可临时使用查询提示强制指定索引（仅用于验证，不推荐长期依赖）：

SELECT TOP (100)
    HORA_LEIT
FROM
    DADOS WITH (INDEX(IX_cod_ponto_hora_leit))
WHERE
    COD_PONTO = 610
    AND HORA_LEIT <= '2022-09-29 12:25:00.000'
    AND VAZAO IS NOT NULL
ORDER BY
    HORA_LEIT DESC

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