SQL自动索引推荐最佳实践及SQL Server定时自动增删索引合理性咨询

这种每半小时全量创建、删除所有推荐索引的操作完全不属于合理的生产实践，在1TB规模、高事务量的核心ERP库场景下，风险远大于收益：

• 索引创建属于高资源消耗操作，大表建索引会占用大量CPU、内存、IO资源，加锁期间还会阻塞正常业务写操作，核心ERP的订单、库存类高频写入表极易出现事务超时、业务报错
• 索引删除会直接导致原有依赖该索引的查询性能骤降，甚至触发全表扫描，高峰期会直接引发数据库雪崩
• 临时索引的维护成本极高：每一次写入操作都需要同步更新所有关联索引，频繁增删索引还会导致执行计划频繁重编译，进一步消耗数据库资源
• 原脚本适配的是40GB小库场景，数据量增长25倍后，索引操作的耗时、资源消耗会呈非线性增长，原有的半小时执行周期根本无法完成全量索引操作，会出现作业堆积，进一步挤占业务资源

• 索引推荐的固有局限性：SQL Server的缺失索引推荐基于运行时查询统计生成，仅考虑查询加速，完全不评估写性能开销、索引重复度、存储开销，大部分推荐的索引都存在重复、冗余或者性价比极低的问题
• 作业异常的连锁影响：如果索引创建/删除作业执行失败，可能出现残留大量无用索引、或者必要索引被误删的情况，没有回滚和校验机制的话，故障排查难度极高
• 执行计划稳定性问题：索引频繁变动会导致SQL执行计划持续变更，业务性能会出现无规律波动，很难做性能基线管理和故障排查
• 核心业务的容灾风险：ERP系统的订单、支付类事务对延迟、可用性要求极高，索引操作引发的阻塞、超时可能直接导致订单丢失、对账错误等业务级故障

• 禁止全量自动执行索引变更：所有索引的增删操作必须经过DBA人工核验，确认收益大于开销后，在业务低峰期灰度执行
• 索引推荐结果必须做二次校验，校验维度包括：
  • 索引的使用率预估，排除低性价比的窄范围查询专用索引
  • 现有索引的冗余度排查，避免创建字段前缀重复的冗余索引
  • 写性能影响评估：高频写入表的索引总数建议控制在5个以内，单索引字段数不超过5个
• 可采用可控灰度的自动索引优化策略：如果要使用自动化能力，建议采用以下方案：
  • 仅在非核心、查询密集的非事务库开启自动索引创建，且设置资源阈值，CPU/IO使用率超过70%时自动暂停
  • 索引删除前必须做至少7天的使用率校验，确认连续7天无任何查询引用后，先禁用7天，无性能异常再执行删除
  • 所有索引变更操作都要留痕，保留操作前的数据库备份、执行计划基线，出现异常可快速回滚
• SQL Server 2014适配优化：迁移完成后可以使用SQL Server原生的缺失索引DMV做性能分析，配合查询存储（Query Store）功能追踪索引的实际使用率，替代非官方的第三方自动脚本。更高版本的自动索引调优功能也必须开启审核模式，禁止默认自动执行变更。

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