sql server使用T-SQL读取扩展事件
大部分参考转自：如何正确读取SQL Server中的扩展事件？
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:\XeventResutl\DDLAudit*.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_tools\eventlog\slow_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秒多，一个毫秒级
