sql server使用T-SQL读取扩展事件

SQL Server中使用扩展事件捕捉所需的信息后，可以选择存放的位置。比如说内存或文件中，但无论存在哪里，其本质都是一个大XML。因此在SQL Server中读取该XML就是解析扩展事件结果的方式。

微软官方或者一些SQL Server论坛提供了使用SQL XML解析扩展事件的脚本，如代码清单1所示。

【基本办法--读取扩展事件文件的脚本】

;WITH events_cte
AS (
    SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP), xevents.event_data.value('(event/@timestamp)[1]', 'datetime2')) AS [event time]
        ,xevents.event_data.value('(event/@name)[1]', 'nvarchar(128)') AS [Event Name]
        ,xevents.event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(128)') AS [client app name]
        ,xevents.event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(max)') AS [client host name]
        ,xevents.event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS [sql_text]
        ,xevents.event_data.value('(event/action[@name="database_name"]/value)[1]', 'nvarchar(max)') AS [database name]
        ,xevents.event_data.value('(event/action[@name="username"]/value)[1]', 'nvarchar(max)') AS [username]
        ,xevents.event_data.value('(event/action[@name="duration"]/value)[1]', 'bigint') AS [duration (ms)]
        ,xevents.event_data.value('(event/action[@name="cpu_time"]/value)[1]', 'bigint') AS [cpu time (ms)]
        ,xevents.event_data.value('(event/data[@name="object_name"]/value)[1]', 'nvarchar(max)') AS [OBJECT_NAME]
    FROM sys.fn_xe_file_target_read_file('D:XeventResutlDDLAudit*.xel', NULL, NULL, NULL)
    CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) AS xevents
    )
SELECT *
FROM events_cte
ORDER BY [event time] DESC;

other:

with d as (
SELECT CONVERT(XML,event_data) AS data
from sys.Fn_xe_file_target_read_file(N'E:dba_toolseventlogslow_query*.xel',NULL,NULL,NULL)
)
select
--DATEADD(hh,DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),CONVERT(DATETIME,data.value('(/event/@timestamp)[1]','datetime'))) AS [event_timestamp] ,
dateadd(hour,8,data.value('(/event/@timestamp)[1]','datetime')) as record_time, --获取最上方标题行的内容
data.value('(/event/@name)[1]','nvarchar(128)') as operation_name, --获取最上方标题行的内容
data.value('(/event/action[@name="database_name"]/value)[1]','nvarchar(400)') as 'database_name',--获得 event=>action name=database_name 的 value
data.value('(/event/data[@name="cpu_time"]/value)[1]','int')/1000 as 'cpu_time(ms)',--获得 event=>data name=cpu_time 的 value
data.value('(/event/data[@name="duration"]/value)[1]','int')/1000 as 'exec_time(ms)',--获得 event=>data name=duration 的 value
data.value('(/event/data[@name="physical_reads"]/value)[1]','int') as 'physical_reads',--获得 event=>data name=physical_reads 的 value
data.value('(/event/data[@name="logical_reads"]/value)[1]','int') as 'logical_reads',--获得 event=>data name=logical_reads 的 value
data.value('(/event/data[@name="writes"]/value)[1]','int') as 'writes',--获得 event=>data name=writes 的 value
data.value('(/event/data[@name="row_count"]/value)[1]','int') as 'row_count',--获得 event=>data name=row_count 的 value
data.value('(/event/data[@name="result"]/value)[1]','int') as 'result_flag',--获得 event=>data name=result 的 value
data.value('(/event/data[@name="result"]/text)[1]','nvarchar(128)') as 'result_desc',--获得 event=>data name=result 的 text
data.value('(/event/data[@name="batch_text"]/value)[1]','nvarchar(max)') as 'batch_text',--获得 event=>data name=batch_text 的 text
data.value('(/event/action[@name="client_app_name"]/value)[1]','nvarchar(4000)') as 'client_app_name',--获得 event=>action name=client_app_name 的 value
data.value('(/event/action[@name="client_hostname"]/value)[1]','nvarchar(4000)') as 'client_hostname',--获得 event=>action name=client_hostname 的 value
data.value('(/event/action[@name="sql_text"]/value)[1]','nvarchar(4000)') as 'current_sql',--获得 event=>action name=sql_text 的 value
data.value('(/event/action[@name="task_time"]/value)[1]','int')/1000 as 'task_time(ms)',--获得 event=>action name=task_time 的 value
data.value('(/event/action[@name="transaction_id"]/value)[1]','nvarchar(400)') as 'transaction_id',--获得 event=>action name=transaction_id 的 value
data.value('(/event/action[@name="username"]/value)[1]','nvarchar(400)') as 'username',--获得 event=>action name=transaction_id 的 value
data.value('(/event/action[@name="nt_username"]/value)[1]','nvarchar(400)') as 'nt_username'--获得 event=>action name=transaction_id 的 value
from d

但代码清单1的脚本使用的是XQuery，XQuery在使用Xml的节点属性作为删选条件时，数据上千以后就会变得非常慢。

因此我对上述脚本进行了改写，将XML读取出来后，变为节点的集合以关系数据格式存放，再用子查询进行筛选，这种方式读取数据基本上是秒出，如下所示。

【高效查询--ring_buffer】

;WITH tt
AS (
    SELECT MIN(event_name) AS event_name
        ,DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), CONVERT(DATETIME, MIN(CASE 
                        WHEN d_name = 'collect_system_time'
                            AND d_package IS NOT NULL
                            THEN d_value
                        END))) AS [event_timestamp]
        ,CONVERT(VARCHAR(MAX), MIN(CASE 
                    WHEN d_name = 'client_hostname'
                        AND d_package IS NOT NULL
                        THEN d_value
                    END)) AS [Client_hostname]
        ,CONVERT(VARCHAR(MAX), MIN(CASE 
                    WHEN --event_name = 'sql_batch_completed'
                        d_name = 'client_app_name'
                        THEN d_value
                    END)) AS [Client_app_name]
        ,CONVERT(VARCHAR(MAX), MIN(CASE 
                    WHEN d_name = 'database_name'
                        AND d_package IS NOT NULL
                        THEN d_value
                    END)) AS [database_name]
        ,CONVERT(VARCHAR(MAX), MIN(CASE 
                    WHEN d_name = 'object_name'
                        THEN d_value
                    END)) AS [object_name]
        ,CONVERT(BIGINT, MIN(CASE 
                    WHEN event_name = 'sql_batch_completed'
                        AND d_name = 'duration'
                        AND d_package IS NULL
                        THEN d_value
                    END)) AS [sql_statement_completed.duration]
        ,CONVERT(VARCHAR(MAX), MIN(CASE 
                    WHEN d_name = 'sql_text'
                        THEN d_value
                    END)) AS [sql_statement_completed.sql_text]
        ,CONVERT(VARCHAR(MAX), MIN(CASE 
                    WHEN d_name = 'username'
                        AND d_package IS NOT NULL
                        THEN d_value
                    END)) AS [username]
    FROM (
        SELECT *
            ,CONVERT(VARCHAR(400), NULL) AS attach_activity_id
        FROM (
            SELECT event.value('(@name)[1]', 'VARCHAR(400)') AS event_name
                ,DENSE_RANK() OVER (
                    ORDER BY event
                    ) AS unique_event_id
                ,n.value('(@name)[1]', 'VARCHAR(400)') AS d_name
                ,n.value('(@package)[1]', 'VARCHAR(400)') AS d_package
                ,n.value('((value)[1]/text())[1]', 'VARCHAR(MAX)') AS d_value
                ,n.value('((text)[1]/text())[1]', 'VARCHAR(MAX)') AS d_text
            FROM (
                SELECT (
                        SELECT CONVERT(XML, target_data)
                        FROM sys.dm_xe_session_targets st
                        JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
                        WHERE s.name = 'DDL'
                            AND st.target_name = 'ring_buffer'
                        ) AS [x]
                FOR XML PATH('')
                    ,TYPE
                ) AS the_xml(x)
            CROSS APPLY x.nodes('//event') e(event)
            CROSS APPLY event.nodes('*') AS q(n)
            ) AS data_data
        ) AS activity_data
    GROUP BY unique_event_id
    )
SELECT *
FROM tt

代码清单2.对扩展事件结果的优化读取方式

参考资料：http://blog.wharton.com.au/2011/06/13/part-5-openxml-and-xquery-optimisation-tips/

【高效查询--event_file】

--get event_file path
declare @file1 Nvarchar(1000)
SELECT @file1=cast(value as nvarchar(1000)) FROM sys.server_event_sessions t1 
join sys.server_event_session_targets t2 on t1.event_session_id=t2.event_session_id
join sys.server_event_session_fields t3 on t1.event_session_id=t3.event_session_id
where t1.name='slow_query'
and t2.name='event_file'
and t3.name='filename'
set @file1=stuff(@file1,charindex('.',@file1),4,'*.xel')

;WITH   tt
AS ( SELECT   MIN(event_name) AS event_name ,
    DATEADD(hh,DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),CONVERT(DATETIME, MIN(record_time))) AS [event_timestamp] ,
    CONVERT(VARCHAR(MAX), MIN(CASE WHEN  d_name = 'client_hostname'  THEN d_value  END)) AS [Client_hostname] ,
    CONVERT(VARCHAR(MAX), MIN(CASE WHEN  d_name = 'client_app_name'   THEN d_value  END)) AS [Client_app_name] ,
    CONVERT(VARCHAR(MAX), MIN(CASE WHEN  d_name = 'database_name'  THEN d_value  END)) AS [database_name] ,
    CONVERT(VARCHAR(MAX), isnull(MIN(CASE WHEN  d_name = 'object_name' THEN d_value  END),'SQL')) AS [object_name] ,
    CONVERT(bigint, MIN(CASE WHEN  d_name = 'duration' THEN d_value  END))/1000 AS [duration_ms] ,
    CONVERT(VARCHAR(MAX), MIN(CASE WHEN d_name = 'username'   THEN d_value END)) AS [username] ,
    CONVERT(bigint, MIN(CASE WHEN d_name = 'cpu_time'   THEN d_value END))/1000 AS [cpu_time_ms] ,
    CONVERT(bigint, MIN(CASE WHEN d_name = 'task_time'   THEN d_value END))/1000 AS [task_time_ms] ,
    CONVERT(bigint, MIN(CASE WHEN d_name = 'physical_reads'   THEN d_value END)) AS [physical_reads] ,
    CONVERT(bigint, MIN(CASE WHEN d_name = 'logical_reads'   THEN d_value END)) AS [logical_reads] ,
    CONVERT(bigint, MIN(CASE WHEN d_name = 'writes'   THEN d_value END)) AS [writes] ,
    CONVERT(bigint, MIN(CASE WHEN d_name = 'row_count'   THEN d_value END)) AS [row_count] ,
    CONVERT(VARCHAR(MAX), MIN(CASE WHEN d_name = 'result'   THEN d_value END)) AS [result] ,
    CONVERT(VARCHAR(MAX), MIN(CASE WHEN d_name = 'nt_username'   THEN d_value END)) AS [nt_username] ,
    CONVERT(VARCHAR(MAX), MIN(CASE WHEN d_name = 'transaction_id'   THEN d_value END)) AS [transaction_id] ,
    CONVERT(VARCHAR(MAX), MIN(CASE WHEN d_name = 'sql_text'  THEN d_value END)) AS [sql_text] ,
    CONVERT(VARCHAR(MAX), MIN(CASE WHEN d_name = 'batch_text'  THEN d_value END)) AS [batch_text] 
    FROM ( SELECT *,CONVERT(VARCHAR(400), NULL) AS attach_activity_id
           FROM  
                (       select 
                        event.value('(@name)[1]','VARCHAR(400)') AS event_name ,
                        id AS unique_event_id ,
                        event.value('(@timestamp)[1]','varchar(400)') as record_time,
                        n.value('(@name)[1]','VARCHAR(400)') AS d_name ,
                        n.value('(@package)[1]','VARCHAR(400)') AS d_package ,
                        n.value('((value)[1]/text())[1]','VARCHAR(MAX)') AS d_value ,
                        n.value('((text)[1]/text())[1]','VARCHAR(MAX)') AS d_text
                        from (
                            SELECT convert(xml, event_data) as xml_data,row_number() over(order by event_data) as id
                            FROM master.sys.fn_xe_file_target_read_file(@file1, NULL, NULL, NULL)
                        ) t(x,id)
                        CROSS APPLY x.nodes('/event') e ( event )
                        CROSS APPLY event.nodes('*') as q(n)
                        
                ) AS data_data
            ) AS activity_data
    GROUP BY unique_event_id
)
   SELECT  *
   FROM    tt
   order by event_timestamp desc

【总结】

用子查询方式，的确效率高了非常多；

对比如下：同是338行，一个2秒多，一个毫秒级