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 detailsif object_id('tempdb..#t1') is not null  drop table #t1SELECT   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 usernameINTO #t1FROM (    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_tableCROSS 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 detailsif object_id('tempdb..#t2') is not null  drop table #t2SELECT   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_typeINTO #t2FROM (    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 #t3SELECT   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 WaitDurationINTO #t3FROM #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,  WaitDurationfrom (  select ROW_NUMBER()over(partition by SessionId,SQLTEXT,TotalExecuteTime,CompletedTime order by CompletedTime,WaitTypeStartTime) as rn,  * FROM #t3)