如何查询SQL Server 2019中SPID已回收的历史查询等待统计信息？

核心结论 #

sys.dm_exec_session_wait_stats仅存储当前活跃会话的等待统计，当SPID被回收复用后，旧会话的相关数据会被彻底清除，无法直接通过这个DMV获取几天前的历史查询等待信息。

可行的历史数据收集方案 #

要获取历史等待统计，必须提前部署数据收集机制，针对SQL Server 2019，推荐以下几种方式：

• 扩展事件（Extended Events）
  创建持续运行的扩展事件会话，捕获wait_info或wait_info_external事件，将数据存储到文件目标中，后续可查询文件追溯历史等待数据。示例创建语句：

  CREATE EVENT SESSION [Historical_Wait_Stats] ON SERVER 
  ADD EVENT sqlos.wait_info(
      ACTION(sqlserver.session_id, sqlserver.sql_text)
      WHERE ([duration]>0))
  ADD TARGET package0.event_file(SET filename=N'C:\XEvents\Historical_Waits.xel')
  WITH (STARTUP_STATE=ON);
  

  启动会话后，用以下语句查询历史数据：

  SELECT 
      CAST(event_data AS XML).value('(/event/@timestamp)[1]', 'DATETIME2') AS event_time,
      CAST(event_data AS XML).value('(/event/data[@name="wait_type"]/value)[1]', 'NVARCHAR(128)') AS wait_type,
      CAST(event_data AS XML).value('(/event/data[@name="duration"]/value)[1]', 'BIGINT')/1000 AS duration_ms,
      CAST(event_data AS XML).value('(/event/action[@name="session_id"]/value)[1]', 'INT') AS spid,
      CAST(event_data AS XML).value('(/event/action[@name="sql_text"]/value)[1]', 'NVARCHAR(MAX)') AS sql_text
  FROM sys.fn_xe_file_target_read_file('C:\XEvents\Historical_Waits*.xel', NULL, NULL, NULL);
  

• SQL Server管理数据仓库（MDW）
  配置MDW并启用等待统计数据收集，它会定期从DMV提取数据并持久化到仓库，支持长期保留历史数据，可通过内置报表或自定义查询分析过去几天的等待情况。

• 自定义代理作业+历史表存储
  创建SQL Server代理作业，定期从sys.dm_exec_session_wait_stats、sys.dm_exec_sessions关联sys.dm_exec_sql_text提取数据，插入自定义历史表。示例表结构：

  CREATE TABLE dbo.HistoricalSessionWaits (
      RecordTime DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
      SessionID INT NOT NULL,
      WaitType NVARCHAR(128) NOT NULL,
      WaitTimeMs BIGINT NOT NULL,
      SignalWaitTimeMs BIGINT NOT NULL,
      WaitCount BIGINT NOT NULL,
      LoginName NVARCHAR(128),
      DatabaseName NVARCHAR(128),
      SqlText NVARCHAR(MAX)
  );
  

  后续直接查询该表即可获取历史等待统计，需自行维护数据清理策略避免表体积过大。

注意事项 #

• 扩展事件性能开销极低，适合长期运行；MDW适合整合多类性能数据的场景；自定义作业灵活性最高，但需自行维护。
• 所有方案均需提前部署，无法回溯部署前的历史等待数据。

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