同一查询在不同Azure SQL DB中逻辑读相近但性能差异大的排查方法

X Azure SQL DB与Y Azure SQL DB运行在不同的SQL弹性池中。同一查询返回行数不同，但逻辑读次数相近，该查询在X Azure SQL DB中耗时904ms，在Y Azure SQL DB中却耗时4207ms。以下为该查询的时间与IO统计信息：

-- X AZURE SQL DB QUERY TIME/IO STATS
(300 rows affected)
Table 'A'. Scan count 1, logical reads 15709, physical reads 0
Table 'B'. Scan count 1, logical reads 1488, physical reads 0
Table 'C'. Scan count 1, logical reads 2, physical reads 0
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0
Table 'D'. Scan count 304, logical reads 645, physical reads 0
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0
Table 'E'. Scan count 1, logical reads 603, physical reads 0
Table 'F'. Scan count 0, logical reads 1204, physical reads 0
Table 'G'. Scan count 301, logical reads 1825, physical reads 0
Table 'H'. Scan count 0, logical reads 606, physical reads 0
Table 'I'. Scan count 303, logical reads 610, physical reads 0
Table 'J'. Scan count 1, logical reads 2, physical reads 0
Table 'K'. Scan count 0, logical reads 0, physical reads 0

SQL Server Execution Times:
CPU time = 906 ms,  elapsed time = 904 ms.


-- Y AZURE SQL DB QUERY TIME/IO STATS
(2 rows affected)
Table 'A'. Scan count 1, logical reads 23625, physical reads 0
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0
Table 'E'. Scan count 1, logical reads 5, physical reads 0
Table 'F'. Scan count 0, logical reads 8, physical reads 0
Table 'G'. Scan count 2, logical reads 12, physical reads 0
Table 'H'. Scan count 0, logical reads 14, physical reads 0
Table 'D'. Scan count 7, logical reads 14, physical reads 0
Table 'I'. Scan count 7, logical reads 14, physical reads 0
Table 'B'. Scan count 5, logical reads 1464, physical reads 0
Table 'C'. Scan count 1, logical reads 2, physical reads 0
Table 'J'. Scan count 1, logical reads 2, physical reads 0
Table 'K'. Scan count 0, logical reads 0, physical reads 0

SQL Server Execution Times:
CPU time = 4203 ms,  elapsed time = 4207 ms.

排查根本原因的步骤 #

• 对比实际执行计划：分别在两个数据库中生成该查询的实际执行计划，重点关注：
  • 表A的访问路径差异：X库中表A逻辑读更少，Y库中逻辑读更高，需检查索引结构、数据碎片或数据分布是否不同
  • 连接算子的选择：查看是嵌套循环、哈希匹配还是合并连接，以及各算子的执行时间占比，Y库CPU耗时接近总耗时，可能存在低效的算子执行逻辑
  • 统计信息有效性：Y库中表E、F、G等逻辑读骤降，但CPU耗时更高，大概率是统计信息过时导致查询优化器选择了错误的执行计划
• 检查弹性池资源状态：确认两个弹性池的DTU/vCore配额、实时资源使用率（CPU、内存、IOPS），Y库所在弹性池可能存在资源竞争，导致查询出现等待事件。可通过sys.dm_db_resource_stats查看数据库级别的资源使用情况，排查是否有资源瓶颈
• 验证数据与索引差异：
  • 对比表A的行数、索引定义（包括非聚集索引的键列、包含列），Y库表A逻辑读更高，可能缺少覆盖索引或索引存在碎片
  • 检查表B的扫描次数：X库是1次扫描，Y库是5次，说明Y库中表B的访问方式存在重复扫描，需确认是否缺少必要索引或查询计划中存在低效的循环逻辑
• 捕获等待事件：在Y库执行查询时，用sys.dm_exec_session_wait_stats查看当前会话的等待类型和等待时长，排查是否存在PAGEIOLATCH_*、RESOURCE_SEMAPHORE等等待，确认是否是资源等待导致的耗时增加
• 核对数据库配置：检查两个数据库的兼容性级别、MAXDOP设置、基数估算器版本（如是否开启LEGACY_CARDINALITY_ESTIMATION），不同配置会直接影响查询计划的生成
• 更新统计信息并验证：手动更新Y库中相关表的统计信息（执行UPDATE STATISTICS [表名] WITH FULLSCAN），重新执行查询，若性能改善则说明是统计信息过时导致的问题

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