同一条SQL在SQL Server同实例两克隆库运行速度差异大的原因是什么？

以下是最常见的几种诱因，按出现概率从高到低排序：

• 统计信息过时
  运行多年的库A没有及时更新表T的统计信息，SQL Server查询优化器基于过时的统计值生成了错误的执行计划，比如选错索引、选错关联算法，导致执行效率极低。而新恢复的库B在恢复完成后会自动触发表统计信息的更新，优化器可以拿到准确的行数、数据分布信息，生成最优执行计划，运行速度自然更快。
• 索引碎片过高
  库A的表T经过多年的增删改操作，聚集索引、非聚集索引都产生了大量碎片，数据页离散存储，查询相同数据需要的IO次数远高于连续存储的情况。刚恢复的库B所有数据页都是连续排列的，碎片率极低，IO开销小很多。
• 执行计划缓存异常
  库A中可能之前运行过类似的查询，缓存了不符合当前查询场景的执行计划，每次执行都复用了错误的缓存计划，导致效率极低。库B刚完成恢复，没有旧的执行计划缓存，首次执行就会生成匹配当前查询的最优计划。
• 锁竞争阻塞
  库A是正在对外提供服务的运行态库，会有大量业务读写操作同时访问表T，执行查询时很容易被其他写入事务阻塞，等待锁资源的时间占了总耗时的大头。库B没有其他业务流量，不存在锁竞争，查询可以直接跑满资源。
• 物理存储碎片化
  库A的表T经过多年的页拆分、空间回收，数据在磁盘上的物理存储非常离散，随机IO开销很高。库B恢复时会把数据连续写入磁盘，物理IO效率更高。

快速验证方案 #

• 分别在两个库打开实际执行计划，对比执行步骤、逻辑读/物理读次数、索引选择的差异
• 执行 DBCC SHOW_STATISTICS('T', 索引名) 查看两个库表T的统计信息更新时间、数据分布是否一致
• 执行 SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('T'), NULL, NULL, 'DETAILED') 查看两个库表T的索引碎片率
• 在库A执行查询时查看等待类型，确认是否存在 LCK_M_* 锁等待、PAGEIOLATCH_* IO等待类的异常等待

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