SQL Server同列多索引建议优化方案及创建优先级问询

问题1：是否需要创建全部4条建议索引？ #

不需要，仅需1条经过合并优化的索引即可覆盖所有查询的性能需求，无需冗余创建4条。
你可以优先创建如下非聚集索引：

CREATE NONCLUSTERED INDEX IX_TableName_CodeQualifier_LineCode_Status_LineNo
ON [TableName] ([Code Qualifier], [Line Code], [Status], [Line No_])

该索引的覆盖逻辑：

• 适配Query2：四个键列完全匹配Query2的等值过滤条件，索引命中后无需额外过滤
• 适配Query1：前两个键列[Code Qualifier]、[Line Code]为等值匹配，SQL Server可跳过[Status]列直接过滤后续的[Line No_]，符合最左前缀匹配规则，可正常命中索引；且索引默认包含聚集索引键[Message Batch Number]、[Message Line Number]，无需回表即可完成ORDER BY排序
• 适配Query3：最左前缀为[Code Qualifier]，完全匹配Query3的更新过滤条件，可快速定位需要更新的行，无需全表扫描

另外现有3个未被使用的索引可申请删除，避免无谓的存储占用和极少量的更新维护开销。

问题2：逐次创建的顺序和合并规则 #

合并规则 #

无需保留原4条建议索引，直接使用上述合并后的单条索引即可，所有查询都可命中，没有冗余。

创建顺序 #

如果需要分步骤验证，按查询优先级从高到低排序：

1. 第一步先创建上述合并后的联合索引，完成后直接验证3个查询的性能，90%以上的场景下这一步即可满足所有性能要求
2. 如果实测发现Query3的更新性能仍未达预期（大概率不会出现），再补充创建一条窄索引[TableName] ([Code Qualifier])即可

问题3：无修改权限时的验证方案 #

不需要实际修改生产索引即可验证方案有效性，可通过以下方法确认：

• 执行计划比对：拿业务常用的参数值生成三个查询的预估/实际执行计划，对比索引创建前后的预估开销、逻辑读次数、是否有全表扫描/排序操作，即可判断性能提升幅度
• 数据库引擎优化顾问(DTA)验证：将三个查询的语句作为负载输入DTA，它会基于表的真实统计信息输出全局最优的索引建议，可验证你合并的索引是否为最优方案
• 统计信息校验：查询sys.dm_db_index_usage_stats确认现有三个索引确实无任何用户查询命中，可安全删除；同时查询字段区分度（如SELECT COUNT(DISTINCT [Code Qualifier])*1.0/COUNT(*) FROM [TableName]），确认索引键的过滤效率符合预期
• 测试环境复现：在和生产数据结构、统计信息一致的测试环境创建目标索引，实际运行业务查询验证性能，测试结果可直接复现生产的提升效果

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