简单SQL查询执行计划生成耗时过长问题求助

核心现象总结 #

• 多个简单/复杂SQL查询均出现执行计划生成耗时超9分钟，生成后执行速度恢复正常
• 单表SELECT TOP 1000、仅关联2个小表的查询均受影响，排除查询本身复杂度问题
• 已重启服务器、安装补丁，问题未解决
• 执行计划缓存命中后查询速度正常，新查询或计划失效后问题复发

可能原因与排查步骤 #

1. 统计信息过期或损坏 #

查询优化器依赖准确的统计信息生成执行计划，若统计信息过期、缺失或损坏，会导致优化器花费大量时间估算数据分布：

• 检查目标表及关联表的统计信息状态：

  SELECT 
      OBJECT_NAME(s.object_id) AS TableName,
      s.name AS StatisticName,
      s.last_updated,
      s.rows,
      s.rows_sampled,
      s.unfiltered_rows
  FROM sys.stats s
  JOIN sys.objects o ON s.object_id = o.object_id
  WHERE o.type = 'U'; -- 仅针对用户表
  

• 若发现last_updated时间较早、采样率极低，手动更新统计信息（全量扫描保证准确性）：

  UPDATE STATISTICS dbo.YourTableName WITH FULLSCAN;
  -- 或更新所有表的统计信息
  EXEC sp_updatestats;
  

2. 查询计划缓存异常 #

计划缓存碎片化、缓存条目过多或损坏，可能导致优化器生成新计划时耗时增加：

• 检查计划缓存的使用情况：

  SELECT 
      objtype,
      COUNT(*) AS PlanCount,
      SUM(size_in_bytes)/1024/1024 AS TotalSizeMB
  FROM sys.dm_exec_cached_plans
  GROUP BY objtype;
  

• 清理碎片化的计划缓存（注意：会导致所有现有计划失效，需在业务低峰期操作）：

  DBCC FREEPROCCACHE; -- 清理所有计划缓存
  -- 或仅清理特定数据库的缓存
  DBCC FLUSHPROCINDB(YourDatabaseID);
  

3. 查询优化器资源不足或配置异常 #

查询优化器生成计划需要消耗CPU、内存资源，若服务器资源紧张或优化器配置被修改，会导致计划生成缓慢：

• 检查服务器实时资源使用情况（CPU、内存）：

  SELECT 
      cpu_count,
      physical_memory_kb/1024/1024 AS PhysicalMemoryGB,
      sqlserver_start_time
  FROM sys.dm_os_sys_info;
  
  -- 查看近期CPU使用率
  SELECT 
      DATEADD(ms, (record_value - cpu_ticks)/cpu_ticks_in_ms, GETDATE()) AS EventTime,
      CONVERT(DECIMAL(5,2), (100 - record_value / cpu_ticks * 100)) AS CPUUsagePercent
  FROM (
      SELECT 
          cpu_ticks,
          cpu_ticks_in_ms,
          CAST(target_data AS XML).value('(/SystemHealth/Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'bigint') AS record_value,
          record_id
      FROM sys.dm_os_ring_buffers
      WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
        AND record_id > (SELECT MAX(record_id) - 100 FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR')
  ) AS t;
  

• 检查查询优化器相关配置是否被变更：

  SELECT name, value, value_in_use, description
  FROM sys.configurations
  WHERE name IN ('max degree of parallelism', 'query optimizer hotfixes', 'cost threshold for parallelism');
  

4. 系统表或元数据访问缓慢 #

优化器生成计划时需要读取系统表获取表结构、索引等元数据，若系统表存在碎片、统计信息过期或磁盘IO瓶颈，会导致元数据读取缓慢：

• 检查系统数据库（master、msdb）的索引碎片情况：

  SELECT 
      OBJECT_NAME(ips.object_id) AS TableName,
      i.name AS IndexName,
      ips.avg_fragmentation_in_percent
  FROM sys.dm_db_index_physical_stats(DB_ID('master'), NULL, NULL, NULL, 'DETAILED') ips
  JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
  WHERE ips.avg_fragmentation_in_percent > 30; -- 碎片率超过30%需重建
  

• 重建系统表的索引（需在业务低峰期操作）：

  ALTER INDEX ALL ON sys.syscolumns REBUILD;
  ALTER INDEX ALL ON sys.sysindexes REBUILD;
  

5. 用扩展事件跟踪计划生成过程 #

若以上步骤无法定位问题，可通过扩展事件监控优化器生成计划的具体耗时环节：

• 创建扩展事件会话：

  CREATE EVENT SESSION [QueryPlanGeneration] ON SERVER 
  ADD EVENT sqlserver.query_optimizer_phase_end(
      ACTION(sqlserver.sql_text, sqlserver.session_id)
      WHERE sqlserver.session_id <> @@SPID),
  ADD EVENT sqlserver.query_optimizer_phase_start(
      ACTION(sqlserver.sql_text, sqlserver.session_id)
      WHERE sqlserver.session_id <> @@SPID)
  ADD TARGET package0.event_file(SET filename=N'QueryPlanGeneration.xel')
  WITH (STARTUP_STATE=OFF);
  

• 启动会话并运行慢查询后，分析事件文件中的阶段耗时：

  SELECT 
      DATEADD(ms, (x.event_data.value('(@timestamp)[1]', 'bigint') - s.ms_ticks)/1000, GETDATE()) AS EventTime,
      x.event_data.value('(@name)[1]', 'varchar(100)') AS EventName,
      x.event_data.value('(data[@name="phase"]/value)[1]', 'varchar(100)') AS PhaseName,
      x.event_data.value('(data[@name="duration"]/value)[1]', 'bigint')/1000 AS DurationMS,
      x.event_data.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS SQLText
  FROM (
      SELECT CAST(event_data AS XML) AS event_data
      FROM sys.fn_xe_file_target_read_file('QueryPlanGeneration*.xel', NULL, NULL, NULL)
  ) x
  CROSS JOIN sys.dm_os_sys_info s
  ORDER BY EventTime;
  

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