SQLServer中使用扩展事件获取Session级别的等待信息及SQLServer 2016中Session级别等待信息的增强

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='CollectionSessionWaitStats')
 DROP EVENT session CollectionSessionWaitStats ON SERVER;
GO
-- 创建事件会话 
CREATE EVENT SESSION CollectionSessionWaitStats ON SERVER  
ADD EVENT sqlserver.rpc_completed
( 
  ACTION
  (
    sqlos.task_time,
    sqlserver.database_name,
    sqlserver.nt_username,
    sqlserver.username,
    sqlserver.sql_text,
    sqlserver.session_id,
    sqlserver.transaction_id
  ) WHERE [duration]>=3000000
), 
ADD EVENT sqlserver.sql_batch_completed
( 
  ACTION
  (
    sqlos.task_time,
    sqlserver.database_name,
    sqlserver.nt_username,
    sqlserver.username,
    sqlserver.sql_text,
    sqlserver.session_id,
    sqlserver.transaction_id
  ) WHERE [duration]>=3000000
),
ADD EVENT sqlos.wait_info
(
  ACTION 
  (
    sqlos.task_time,
    sqlserver.database_name,
    sqlserver.nt_username,
    sqlserver.sql_text,
    sqlserver.username,
    sqlserver.session_id,
    sqlserver.transaction_id
  ) WHERE session_id>50 
      and opcode=1 
      and duration>0
      and sql_text not like '%sp_MScdc_capture_job%' --排除某些信息
      --and username = '' --限定只记录某些信息
),
ADD EVENT sqlos.wait_info_external
(
  ACTION
  (
    sqlos.task_time,
    sqlserver.database_name,
    sqlserver.nt_username,
    sqlserver.username,
    sqlserver.sql_text,
    sqlserver.session_id,
    sqlserver.transaction_id
  ) WHERE  session_id>50 
      and opcode=1 
      and duration>0
      and sql_text not like '%sp_MScdc_capture_job%'
      --and username = ''
)
ADD TARGET package0.event_file
( 
  SET filename=N'D:/XEventFiles/CollectionSessionWaitStats',
  max_file_size=(1024),  
  max_rollover_files=(10) 
)
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
-- 启用（停止）事件会话（START / STOP） 
ALTER EVENT SESSION CollectionSessionWaitStats ON SERVER STATE=START 
GO

-- Parse the XML to show rpc_completed,sql_batch_completed details
if object_id('tempdb..#t1') is not null
  drop table #t1
SELECT 
  event_xml.value('(./action[@name="session_id"]/value)[1]', 'INT') as session_id,
  event_xml.value('(./@timestamp)', 'varchar(1000)') as timestamp,
  event_xml.value('(./data[@name="statement"]/value)[1]', 'varchar(max)') as statement,
  event_xml.value('(./data[@name="batch_text"]/value)[1]', 'varchar(max)') as batch_text,
  event_xml.value('(./@name)', 'varchar(1000)') as Event_Name,
  event_xml.value('(./data[@name="duration"]/value)[1]', 'bigint') as Duration,
  event_xml.value('(./data[@name="cpu_time"]/value)[1]', 'bigint') as cpu_time,
  event_xml.value('(./data[@name="physical_reads"]/value)[1]', 'bigint') as physical_reads,
  event_xml.value('(./data[@name="logical_reads"]/value)[1]', 'bigint') as logical_reads,
  event_xml.value('(./action[@name="username"]/value)[1]', 'varchar(max)') as username
INTO #t1
FROM (
    SELECT CAST(event_data AS XML) xml_event_data 
    FROM sys.fn_xe_file_target_read_file(N'D:/XEventFiles/CollectionSessionWaitStats*', NULL, NULL, NULL)
   ) AS event_table
CROSS APPLY xml_event_data.nodes('//event') n (event_xml)
WHERE event_xml.value('(./@name)', 'varchar(1000)') in ('rpc_completed','sql_batch_completed')
order by Event_Name
-- Parse the XML to show wait_info,wait_info_external details
if object_id('tempdb..#t2') is not null
  drop table #t2
SELECT 
  cast(event_xml.value('(./@timestamp)', 'varchar(1000)') as DATETIME2) as timestamp,
  event_xml.value('(./data[@name="duration"]/value)[1]', 'bigint') as duration,
  event_xml.value('(./action[@name="session_id"]/value)[1]', 'INT') as session_id,
  event_xml.value('(./data[@name="wait_type"]/text)[1]', 'VARCHAR(200)') as wait_type
INTO #t2
FROM (
    SELECT CAST(event_data AS XML) xml_event_data 
    FROM sys.fn_xe_file_target_read_file(N'D:/XEventFiles/CollectionSessionWaitStats*', NULL, NULL, NULL)
   ) AS event_table
   CROSS APPLY xml_event_data.nodes('//event') n (event_xml)
WHERE event_xml.value('(./@name)', 'varchar(1000)') in ('wait_info','wait_info_external')
if object_id('tempdb..#t3') is not null
  drop table #t3
SELECT 
  a.session_id          AS SessionId,
  isnull(statement,batch_text)  AS SQLTEXT,
  a.Duration            AS TotalExecuteTime,
  CAST(a.timestamp AS DATETIME2)  AS CompletedTime,
  CAST(b.timestamp AS DATETIME2)  AS WaitTypeStartTime,
  b.wait_type            AS WaitType,
  b.duration            AS WaitDuration
INTO #t3
FROM #t1 a INNER JOIN #t2 b on a.session_id = b.session_id
  and b.timestamp < a.timestamp 
  and b.timestamp>(
             select top 1 timestamp from #t1 c 
             where a.session_id = a.session_id and a.timestamp > b.timestamp
             order by a.timestamp
          )
select 
  case when rn = 1 then SessionId else NULL end as SessionId,
  case when rn = 1 then SQLTEXT else NULL end as SQLTEXT,
  case when rn = 1 then TotalExecuteTime else NULL end as TotalExecuteTime,
  CompletedTime as CompletedTime,
  WaitType,
  WaitTypeStartTime as WaitTypeStartTime,
  WaitDuration
from 
(
  select ROW_NUMBER()over(partition by SessionId,SQLTEXT,TotalExecuteTime,CompletedTime order by CompletedTime,WaitTypeStartTime) as rn,
  * FROM #t3
)