相同查询与数据在另一服务器执行慢、读操作多的排查咨询

问题背景 #

两台数据完全相同的服务器上，同一内连接查询的执行计划与性能差异极大：

• Server 1（快速）：

  Table 'A'. Scan count 1, logical reads 1001
  Table 'B'. Scan count 1, logical reads 183
  

• Server 2（缓慢，耗时20+秒）：

  Table 'B'. Scan count 1, logical reads 432112
  Table 'A'. Scan count 1, logical reads 2643
  

查询语句：

SELECT *
FROM TableA AS A
INNER JOIN TableB AS B ON A.employee_id = B.employee_id
                       AND A.year = B.year
                       AND A.month = B.month
WHERE A.year = 2024
  AND A.month = 10

注释掉AND A.month = 10后，Server 2的执行顺序与Server 1一致，耗时缩短，但逻辑读仍偏高：

Table 'A'. Scan count 1, logical reads 2807
Table 'B'. Scan count 1, logical reads 191

调试步骤 #

1. 校验并更新统计信息 #

查询优化器依赖准确的统计信息生成最优计划，过时的统计信息会导致错误的执行决策：

• 在Server 2上执行全量更新统计信息：

  UPDATE STATISTICS TableA WITH FULLSCAN;
  UPDATE STATISTICS TableB WITH FULLSCAN;
  

• 对比两台服务器的统计信息状态：

  EXEC sp_autostats 'TableA';
  EXEC sp_autostats 'TableB';
  

  确认统计信息的更新时间和采样率一致。

2. 对比执行计划细节 #

在两台服务器上生成实际执行计划（SSMS按Ctrl+M后执行查询），重点对比：

• 表的连接顺序与连接类型（嵌套循环、哈希连接、合并连接）
• 索引的使用情况：是否用到了过滤或联合索引（比如TableA的(year, month, employee_id)联合索引、TableB的(employee_id, year, month)联合索引）
• 估算行数与实际行数的偏差：偏差过大说明统计信息失效。

3. 检查索引一致性 #

确认两台服务器的表索引完全一致：

• 查询索引详情：

  EXEC sp_helpindex 'TableA';
  EXEC sp_helpindex 'TableB';
  

• 若Server 2缺少Server 1上的高效索引，立即创建对应索引，例如：

  -- 为TableA创建过滤+联合索引，适配WHERE和JOIN条件
  CREATE NONCLUSTERED INDEX IX_TableA_Year_Month_EmployeeID
  ON TableA (year, month, employee_id)
  INCLUDE (...); -- 包含查询需要的其他列，避免键查找
  
  -- 为TableB创建联合索引，适配JOIN条件
  CREATE NONCLUSTERED INDEX IX_TableB_EmployeeID_Year_Month
  ON TableB (employee_id, year, month);
  

4. 核对服务器配置参数 #

部分配置会影响优化器行为，对比两台服务器的关键参数：

SELECT 
  @@MAXDOP AS [并行度],
  @@COMPATIBILITY_LEVEL AS [兼容级别],
  (SELECT value FROM sys.configurations WHERE name = 'cost threshold for parallelism') AS [并行成本阈值];

• 若兼容级别不同，可尝试将Server 2调整为与Server 1一致（需测试业务影响）
• 并行度设置差异也可能导致执行计划偏差，按需调整。

5. 排查表碎片问题 #

即使数据内容相同，表碎片率差异会增加逻辑读：

• 检查碎片率：

  DBCC SHOWCONTIG('TableA');
  DBCC SHOWCONTIG('TableB');
  

• 碎片率过高时，重建或重组索引：

  -- 重建索引（适合碎片率>30%）
  ALTER INDEX ALL ON TableA REBUILD;
  ALTER INDEX ALL ON TableB REBUILD;
  
  -- 重组索引（适合碎片率5%-30%）
  ALTER INDEX ALL ON TableA REORGANIZE;
  ALTER INDEX ALL ON TableB REORGANIZE;
  

6. 临时强制最优执行计划（可选） #

若上述步骤未解决，可临时强制使用Server 1的最优计划逻辑：

• 强制连接顺序：

  SELECT *
  FROM TableA AS A
  INNER JOIN TableB AS B ON A.employee_id = B.employee_id
                         AND A.year = B.year
                         AND A.month = B.month
  WHERE A.year = 2024
    AND A.month = 10
  OPTION (FORCE ORDER);
  

• 强制指定索引：

  SELECT *
  FROM TableA AS A WITH (INDEX(IX_TableA_Year_Month_EmployeeID))
  INNER JOIN TableB AS B ON ...
  WHERE ...;
  

注意：提示仅为临时方案，优先修复统计信息、索引等根本问题。

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