SQL Server 如何查询历史已执行查询的事务隔离级别

现有公开的查询语句仅支持查询当前正在运行的请求对应的事务隔离级别，语句如下：

SELECT session_id, start_time, status,
total_elapsed_time,
CASE transaction_isolation_level
WHEN 1 THEN 'ReadUncomitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
ELSE 'Unspecified' END AS transaction_isolation_level,
sh.text, ph.query_plan
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle) sh
CROSS APPLY sys.dm_exec_query_plan(plan_handle) ph

上述语句无法查询已完成历史查询的核心原因是：sys.dm_exec_requests是动态管理视图，仅存储当前数据库引擎正在处理的请求数据，请求执行完成后相关记录会被立即移除，因此无法通过该视图回溯历史查询属性。

以下是获取已完成历史查询事务隔离级别的可行方案：

• 方案一：使用扩展事件捕获（生产环境推荐）
  扩展事件是SQL Server自带的轻量跟踪工具，你可以创建自定义扩展事件会话，捕获sql_statement_completed、rpc_completed事件，事件自带transaction_isolation_level字段，同时可关联捕获SQL文本、执行时间、会话ID等信息，配置完成后所有执行完成的查询属性都会被持久化存储，后续可直接查询回溯。
  示例创建扩展事件会话语句：

  CREATE EVENT SESSION [Capture_Isolation_Level] ON SERVER 
  ADD EVENT sqlserver.sql_statement_completed(
      ACTION(sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_isolation_level))
  ADD TARGET package0.event_file(SET filename=N'Capture_Isolation_Level.xel',max_file_size=(50),max_rollover_files=(5))
  WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
  GO
  -- 启动会话
  ALTER EVENT SESSION [Capture_Isolation_Level] ON SERVER STATE = START
  GO
  

  后续查询已记录历史数据的语句：

  SELECT 
    event_data.value('(event/@timestamp)[1]', 'datetime2') AS EventTime,
    event_data.value('(event/action[@name="session_id"]/value)[1]', 'int') AS SessionID,
    CASE event_data.value('(event/action[@name="transaction_isolation_level"]/value)[1]', 'int')
        WHEN 1 THEN 'ReadUncomitted'
        WHEN 2 THEN 'ReadCommitted'
        WHEN 3 THEN 'Repeatable'
        WHEN 4 THEN 'Serializable'
        WHEN 5 THEN 'Snapshot'
        ELSE 'Unspecified' END AS TransactionIsolationLevel,
    event_data.value('(event/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('Capture_Isolation_Level*.xel', NULL, NULL, NULL)) AS x
  ORDER BY EventTime DESC
  

• 方案二：查询存储配合扩展事件
  如果你已经开启了数据库的查询存储功能，可以将扩展事件捕获的查询哈希值和查询存储中的数据关联，获取更完整的查询性能、执行计划等历史维度数据。
• 方案三：SQL Trace（仅短期测试可用）
  也可以使用传统的SQL Server Profiler或者服务器端跟踪捕获隔离级别，但该工具性能开销远高于扩展事件，不建议在生产环境长期开启。

注意：所有历史数据捕获方案都需要提前配置对应的跟踪会话，如果你没有提前开启任何捕获工具，无法回溯配置之前已经执行完成的查询的事务隔离级别。

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