SQL Server创建非聚集索引后生产环境性能未达预期求助

看来你遇到了挺头疼的问题——测试环境明明见效的索引，到生产环境就不管用了，甚至调了列顺序也没改善。我来分享几个我平时排查这类问题的思路，你可以一步步来验证：

• 先确认生产环境的索引是否真的按预期工作
  别想当然认为索引创建完就一定生效，先跑EXEC sp_helpindex '你的表名'核对一下索引的列顺序、类型（非聚集）是不是和测试环境完全一致。然后关键的一步：看查询的执行计划！不管是用SET SHOWPLAN_XML ON;跑查询看计划，还是在SSMS里开图形化执行计划，重点看优化器有没有选择你创建的索引——有时候因为统计信息过时，优化器会觉得走全表扫描更快，直接忽略你的索引。

• 对比测试环境和生产环境的数据差异
  这是最容易被忽略的点：测试环境的数据集大小、数据分布和生产环境是不是一样？比如测试环境只有几十万条数据，生产环境有几千万，那索引的性能表现肯定天差地别。另外，数据分布也很关键——比如你查询过滤的列，在测试环境里值分布均匀，但生产环境里某个值占了90%的数据，优化器会判断走全表扫描比走索引更高效，自然就不会用你的索引。还有，生产环境的索引有没有碎片？可以用DBCC SHOWCONTIG('你的表名')或者查询sys.dm_db_index_physical_stats看看碎片率，如果超过30%，赶紧重建索引（ALTER INDEX 索引名 ON 表名 REBUILD;），碎片太多的索引根本发挥不了作用。

• 检查统计信息是否过时
  生产环境的数据通常一直在增删改，如果统计信息很久没更新，优化器拿到的是旧的数据分布情况，就会做出错误的执行计划。你可以跑UPDATE STATISTICS '你的表名' WITH FULLSCAN;强制更新统计信息，更新完再跑查询试试，很多时候这就能解决问题。

• 确认测试和生产环境的查询语句完全一致
  有时候看起来一样的查询，可能藏着细微差别：比如参数值不同？生产环境的查询多了几个过滤条件？或者有没有隐式类型转换？比如索引列是INT类型，但查询里传的是VARCHAR的参数，这会导致索引失效，只能走全表扫描，这时候索引再好也没用。

• 看看生产环境的服务器资源是不是瓶颈
  如果生产环境此时CPU、内存、磁盘IO已经跑满了，就算索引设计得再好，查询也快不起来。你可以看看任务管理器的CPU、内存占用，或者用SQL Server的性能计数器看看Logical Disk % Disk Time是不是很高，Memory Pages/sec是不是异常，CPU Usage是不是接近100%。要是资源不够，得先解决资源问题，比如加内存、优化磁盘IO，光调索引没用。

• 复盘索引的设计是否真的适配生产环境的查询
  仔细看你的查询语句：过滤条件、排序字段、JOIN的列是不是和索引的列顺序匹配？比如查询是WHERE A = @param1 AND B = @param2 ORDER BY C，那最优的索引应该是(A, B) INCLUDE (C)或者(A, B, C)。如果测试环境的查询逻辑和生产环境不一样，那索引自然也得调整。另外，有没有做覆盖索引？如果查询需要返回很多非索引列，就算走了索引也得回表取数据，这会拖慢速度——可以把需要的列放到INCLUDE里，做成覆盖索引，这样查询直接从索引里拿数据，不用回表。

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