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

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='SessionWaitStats')
  DROP EVENT session SessionWaitStats ON SERVER;
GO

--  创建事件会话  
CREATE EVENT SESSION SessionWaitStats ON SERVER   
ADD EVENT sqlserver.rpc_completed
(  
    ACTION
    (
        sqlos.task_time,
        sqlserver.database_name,
        sqlserver.nt_username,
        sqlserver.username,
        sqlserver.client_hostname,
        sqlserver.client_app_name,
        sqlserver.sql_text,
        sqlserver.session_id,
        sqlserver.transaction_id
    )  WHERE session_id>50 
        and [duration]>=3000000
),
--ADD EVENT sqlserver.sql_statement_completed
--(  
--    ACTION
--    (
--        sqlos.task_time,
--        sqlserver.database_name,
--        sqlserver.nt_username,
--        sqlserver.client_hostname,
--        sqlserver.client_app_name,
--        sqlserver.username,
--        sqlserver.sql_text,
--        sqlserver.session_id,
--        sqlserver.transaction_id
--    )  WHERE session_id>50 
--        and [duration]>=3000000
--),  
ADD EVENT sqlserver.sql_batch_completed
(  
    ACTION
    (
        sqlos.task_time,
        sqlserver.database_name,
        sqlserver.nt_username,
        sqlserver.client_hostname,
        sqlserver.client_app_name,
        sqlserver.username,
        sqlserver.sql_text,
        sqlserver.session_id,
        sqlserver.transaction_id
    ) WHERE session_id>50 
        and [duration]>=3000000
),
ADD EVENT sqlos.wait_info
(
    ACTION 
    (
        sqlos.task_time,
        sqlserver.database_name,
        sqlserver.nt_username,
        sqlserver.client_hostname,
        sqlserver.client_app_name,
        sqlserver.sql_text,
        sqlserver.username,
        sqlserver.session_id,
        sqlserver.transaction_id
    ) WHERE  session_id>50 
            and opcode=1 
            and duration>1
            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.client_hostname,
        sqlserver.client_app_name,
        sqlserver.sql_text,
        sqlserver.session_id,
        sqlserver.transaction_id
    ) WHERE   session_id>50 
            and opcode=1 
            and duration>1
            and sql_text not like '%sp_MScdc_capture_job%'
            --and username = ''
)
ADD TARGET package0.event_file
(  
    SET filename=N'F:\XEvent Files\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

/*

SELECT event_table.xml_event_data
FROM (
        SELECT CAST(event_data AS XML) xml_event_data 
        FROM sys.fn_xe_file_target_read_file(N'F:\XEvent Files\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','sql_statement_completed')

*/


-- 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('(./action[@name="sql_text"]/value)[1]', 'varchar(max)') as sql_text,
    event_xml.value('(./action[@name="client_hostname"]/value)[1]', 'varchar(max)') as client_hostname,
    event_xml.value('(./action[@name="client_app_name"]/value)[1]', 'varchar(max)') as client_app_name,
    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'F:\XEvent Files\SessionWaitStats*', 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','sql_statement_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'F:\XEvent Files\SessionWaitStats*', 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,
    a.sql_text                        AS SqlText,
    a.username                        AS UserName,
    a.client_hostname                AS ClientHostname,
    a.client_app_name                as ClientAppName,
    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
                     )



if object_id('tempdb..#t4') is not null
    drop table #t4

SELECT    DISTINCT
        SessionId            ,
        UserName            ,
        SqlText                ,
        TotalExecuteTime    ,
        CompletedTime        ,
        WaitType            ,
        COUNT(WaitType)    over(partition by SessionId,SqlText,TotalExecuteTime,CompletedTime) WaitTypeCOUNT,
        SUM(WaitDuration)   over(partition by SessionId,SqlText,TotalExecuteTime,CompletedTime) WaitTypeTimes
INTO #t4
FROM #t3


SELECT  CASE WHEN t.Rn = 1 THEN  CAST(SessionId AS VARCHAR(20))    ELSE '' END AS SessionId,
        CASE WHEN t.Rn = 1 THEN  UserName    ELSE '' END AS UserName,
        CASE WHEN t.Rn = 1 THEN  SqlText    ELSE '' END AS SqlText,
        CASE WHEN t.Rn = 1 THEN  TotalExecuteTime ELSE '' END AS TotalExecuteTime,
        CASE WHEN t.Rn = 1 THEN  CAST(CompletedTime AS VARCHAR(20))    ELSE '' END AS CompletedTime,
        WaitType            ,
        WaitTypeCOUNT        ,
        WaitTypeTimes
FROM 
(
    SELECT 
    ROW_NUMBER()OVER(PARTITION BY SessionId,CompletedTime ORDER BY CompletedTime) AS Rn,*
    FROM #t4
)t