多表关联SQL查询耗时过长，求排查原因（关联列为主键且有索引）

针对你给出的SQL查询，虽然关联列都设为主键并建了索引，但跨库查询仍可能因为多种原因变慢，以下是具体排查方向：

• 优先查看执行计划
  这是最关键的一步，用SSMS开启「包括实际执行计划」（Ctrl+M）后运行查询，重点关注：

  • 是否存在表扫描/聚集索引扫描：即便关联列是主键，若优化器选择扫描而非索引Seek，大概率是统计信息过期或索引失效导致。
  • 有无键查找（Key Lookup）：如果查询需要回表获取非索引列，会额外增加IO开销，此时需要创建覆盖索引。
  • 连接顺序是否合理：跨库场景下优化器可能选错连接顺序，可尝试用OPTION (FORCE ORDER)强制按SQL中的表顺序连接，测试性能变化。

• 更新跨库统计信息
  不同数据库的统计信息独立，若统计信息过期，优化器会生成低效执行计划。手动更新三个表的统计信息：

  UPDATE STATISTICS sampledb1.dbo.xshooter;
  UPDATE STATISTICS sampledb2.dbo.dp_access;
  UPDATE STATISTICS sampledb3.dbo.raw;
  

• 验证索引有效性

  • 确认sampledb2.dbo.dp_access的file_id确实是主键：你提到关联列是主键，但此处t.dp_id = d.file_id，若file_id只是普通索引，需检查索引是否为唯一索引，且是否包含查询所需的所有列（避免回表）。可创建覆盖索引优化：

    CREATE NONCLUSTERED INDEX IX_dp_access_file_id 
    ON sampledb2.dbo.dp_access(file_id) 
    INCLUDE (hide_flag, metadata_release_date, run_code, instrument_code);
    

  • 同理给sampledb3.dbo.raw创建覆盖索引：

    CREATE NONCLUSTERED INDEX IX_raw_dp_id 
    ON sampledb3.dbo.raw(dp_id) 
    INCLUDE (s_region);
    

  • 检查主键索引碎片：碎片率过高会降低索引查找效率，用以下语句查看碎片：

    SELECT 
      OBJECT_NAME(ips.object_id) AS table_name,
      i.name AS index_name,
      ips.avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
    JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
    WHERE OBJECT_NAME(ips.object_id) IN ('xshooter', 'dp_access', 'raw');
    

    若碎片率超过30%，重建索引：ALTER INDEX PK_xxx ON 库名.dbo.表名 REBUILD;

• 排查跨库访问开销

  • 先测试单表查询速度：分别执行三个表的单条件查询，确认是否单表本身就慢：

    SELECT dp_id FROM sampledb1.dbo.xshooter WHERE dp_id = 'TEST';
    SELECT hide_flag, metadata_release_date, run_code, instrument_code FROM sampledb2.dbo.dp_access WHERE file_id = 'TEST';
    SELECT s_region FROM sampledb3.dbo.raw WHERE dp_id = 'TEST';
    

  • 若单表快但JOIN后慢，需检查数据库所在存储的IO性能，或是否跨不同实例导致网络延迟。

• 检查锁与阻塞
  查询慢可能是被其他会话阻塞，用以下语句查看阻塞情况：

  SELECT 
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    t.text AS sql_text
  FROM sys.dm_exec_requests r
  CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
  WHERE r.session_id <> @@SPID;
  

  若存在LCK_M_*类等待，说明有锁冲突；PAGEIOLATCH_*则指向IO瓶颈。

• 确认数据量规模
  检查每个表中dp_id='TEST'（或file_id='TEST'）的行数：

  SELECT COUNT(*) FROM sampledb1.dbo.xshooter WHERE dp_id = 'TEST';
  SELECT COUNT(*) FROM sampledb2.dbo.dp_access WHERE file_id = 'TEST';
  SELECT COUNT(*) FROM sampledb3.dbo.raw WHERE dp_id = 'TEST';
  

  若某张表该条件返回大量数据，JOIN后的结果集过大也会导致耗时增加。

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