聚簇索引扫描与非聚簇索引查找哪个更优？核心需求为降低IO

你当前观察到的现象是SQL Server索引成本权衡的典型场景，具体原因和优化方案如下：

• 逻辑读下降的原因：你创建的NCIX覆盖非聚簇索引仅包含DateCreated和CampaignID两列，单数据页可存储的条目数远大于存储整行数据的聚簇索引，因此扫描相同量级的符合条件的条目时，需要访问的总页数更少，逻辑读下降约66%符合预期。
• CPU与执行时长暴涨的原因：
  1. 该索引以DateCreated为排序键，所有符合datecreated < 90天前条件的条目中，CampaignID是完全无序的，数据库必须逐行遍历所有符合条件的条目才能计算出最大、最小CampaignID，计算开销远高于聚簇索引扫描。另外你这里的非聚簇索引用的是范围扫描，不是非聚簇索引查找，所以不存在点查的低开销优势。
  2. 从执行统计可以看出非聚簇索引扫描走了单线程执行：163953ms的CPU时间和164163ms的执行时长几乎一致，所有计算压力都落在单核心上；而聚簇索引扫描的逻辑更简单，单核心仅需14秒就能完成全量计算，因此耗时更短。
• 优化器默认选择聚簇索引扫描的逻辑：SQL Server的成本模型会综合IO和CPU成本计算总执行代价，该场景下非聚簇索引节省的IO成本远低于额外增加的CPU成本，因此优化器默认选择聚簇索引扫描。

• 方案1（成本最低，兼容现有结构）：如果你的系统CPU资源有冗余，在强制走非聚簇索引的同时增加并行度提示即可，示例如下：

select 
    min(CampaignID), 
    max(CampaignID) 
from Campaign with (index = NCIX)
where datecreated < dateadd(day, -90, getutcdate())
option(maxdop 12) -- 可根据服务器可用CPU核心数调整，通常设为物理核心数的一半即可

调整后执行耗时会降到和原聚簇索引扫描相当的水平，同时保留IO下降66%的收益，完全匹配你降低IO的核心需求。

• 方案2（追求极致性能）：如果该查询是高频执行的核心查询，可以创建索引视图预聚合每日数据的最大最小CampaignID，或者按DateCreated对表做分区，分区裁剪后仅扫描符合条件的分区，可同时降低IO和CPU开销，但需要额外的存储和维护成本。

内容的提问来源于stack exchange，提问作者S.D.