正確讀取SQL Server中的擴充套件事件
SQL Server中使用擴充套件事件捕捉所需的資訊後，可以選擇存放的位置。比如說記憶體或檔案中，但無論存在哪裡，其本質都是一個大XML。因此在SQL Server中讀取該XML就是解析擴充套件事件結果的方式。
微軟官方或者一些SQL Server論壇提供了使用SQL XML解析擴充套件事件的指令碼，如程式碼清單1所示。
1: WITH events_cte
2: AS ( SELECT DATEADD(mi,
3: DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
4: xevents.event_data.value('(event/@timestamp)[1]',
5: 'datetime2')) AS [event time] ,
6: xevents.event_data.value('(event/@name)[1]',
7: 'nvarchar(128)') AS [Event Name],
8: xevents.event_data.value('(event/action[@name="client_app_name"]/value)[1]',
9: 'nvarchar(128)') AS [client app name] ,
10: xevents.event_data.value('(event/action[@name="client_hostname"]/value)[1]',
11: 'nvarchar(max)') AS [client host name] ,
12: xevents.event_data.value('(event/action[@name="sql_text"]/value)[1]',
13: 'nvarchar(max)') AS [sql_text] ,
14:
15: xevents.event_data.value('(event/action[@name="database_name"]/value)[1]',
16: 'nvarchar(max)') AS [database name] ,
17: xevents.event_data.value('(event/action[@name="username"]/value)[1]',
18: 'nvarchar(max)') AS [username] ,
19: xevents.event_data.value('(event/action[@name="duration"]/value)[1]',
20: 'bigint') AS [duration (ms)] ,
21: xevents.event_data.value('(event/action[@name="cpu_time"]/value)[1]',
22: 'bigint') AS [cpu time (ms)] ,
23: xevents.event_data.value('(event/data[@name="object_name"]/value)[1]',
24: 'nvarchar(max)') AS [OBJECT_NAME]
25: FROM sys.fn_xe_file_target_read_file('D:XeventResutlDDLAudit*.xel',
26: NULL, NULL, NULL)
27: CROSS APPLY ( SELECT CAST(event_data AS XML) AS event_data
28: ) AS xevents
29: )
30: SELECT *
31: FROM events_cte
32: ORDER BY [event time] DESC;
程式碼清單1.讀取擴充套件事件檔案的指令碼
但程式碼清單1的指令碼使用的是XQuery，XQuery在使用Xml的節點屬性作為刪選條件時，資料上千以後就會變得非常慢。因此我對上述指令碼進行了改寫，將XML讀取出來後，變為節點的集合以關係資料格式存放，再用子查詢進行篩選，這種方式讀取資料基本上是秒出，如程式碼清單2所示。
1: WITH tt
2: AS ( SELECT MIN(event_name) AS event_name ,
3: DATEADD(hh,DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),
4: CONVERT(DATETIME, MIN(CASE WHEN d_name = 'collect_system_time'
5: AND d_package IS NOT NULL THEN d_value
6: END))) AS [event_timestamp] ,
7: CONVERT
8: (VARCHAR(MAX), MIN(CASE WHEN d_name = 'client_hostname'
9: AND d_package IS NOT NULL THEN d_value
10: END)) AS [Client_hostname] ,
11: CONVERT
12: (VARCHAR(MAX), MIN(CASE WHEN --event_name = 'sql_batch_completed'
13: d_name = 'client_app_name'
14: THEN d_value
15: END)) AS [Client_app_name] ,
16: CONVERT
17: (VARCHAR(MAX), MIN(CASE WHEN d_name = 'database_name'
18: AND d_package IS NOT NULL THEN d_value
19: END)) AS [database_name] ,
20: CONVERT
21: (VARCHAR(MAX), MIN(CASE WHEN d_name = 'object_name'
22: THEN d_value
23: END)) AS [object_name] ,
24: CONVERT
25: (BIGINT, MIN(CASE WHEN event_name = 'sql_batch_completed'
26: AND d_name = 'duration'
27: AND d_package IS NULL THEN d_value
28: END)) AS [sql_statement_completed.duration] ,
29:
30: CONVERT
31: (VARCHAR(MAX), MIN(CASE WHEN d_name = 'sql_text'
32: THEN d_value
33: END)) AS [sql_statement_completed.sql_text] ,
34: CONVERT
35: (VARCHAR(MAX), MIN(CASE WHEN d_name = 'username'
36: AND d_package IS NOT NULL THEN d_value
37: END)) AS [username]
38: FROM ( SELECT * ,
39: CONVERT(VARCHAR(400), NULL) AS attach_activity_id
40: FROM ( SELECT event.value('(@name)[1]',
41: 'VARCHAR(400)') AS event_name ,
42: DENSE_RANK() OVER ( ORDER BY event ) AS unique_event_id ,
43: n.value('(@name)[1]',
44: 'VARCHAR(400)') AS d_name ,
45: n.value('(@package)[1]',
46: 'VARCHAR(400)') AS d_package ,
47: n.value('((value)[1]/text())[1]',
48: 'VARCHAR(MAX)') AS d_value ,
49: n.value('((text)[1]/text())[1]',
50: 'VARCHAR(MAX)') AS d_text
51: FROM ( SELECT ( SELECT
52: CONVERT(XML, target_data)
53: FROM
54: sys.dm_xe_session_targets st
55: JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
56: WHERE
57: s.name = 'DDL'
58: AND st.target_name = 'ring_buffer'
59: ) AS [x]
60: FOR
61: XML PATH('') ,
62: TYPE
63: ) AS the_xml ( x )
64: CROSS APPLY x.nodes('//event') e ( event )
65: CROSS APPLY event.nodes('*')
66: AS q ( n )
67: ) AS data_data
68: ) AS activity_data
69: GROUP BY unique_event_id
70: )
71: SELECT *
72: FROM tt
程式碼清單2.對擴充套件事件結果的最佳化讀取方式
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/200/viewspace-2800058/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- laradock 中安裝 Redis 擴充套件的正確姿勢Redis套件
- laradock 中安裝 Imagick 擴充套件的正確姿勢套件
- sql中的擴充套件學習SQL套件
- 正則的擴充套件套件
- Zabbix-server SNMPTrap擴充套件Server套件
- 進行SQL Server縱向擴充套件的必備條件KVSQLServer套件
- JMeter 擴充套件開發：擴充套件 TCP 取樣器JMeter套件TCP
- 如何正確讀取RTI中enum
- ES6之正則的擴充套件套件
- IOS setOnclick - 點選事件完美擴充套件iOS事件套件
- kotlin 擴充套件（擴充套件函式和擴充套件屬性）Kotlin套件函式
- shell中擴充套件命令套件
- Flink SQL 在快手的擴充套件和實踐SQL套件
- C#學習筆記（補充）——擴充套件方法、事件C#筆記套件事件
- 編寫"谷歌擴充套件"便捷"自測"埋點上報資訊是否正確谷歌套件
- ES6入門之正則的擴充套件套件
- go get 拉取擴充套件報錯Go套件
- T-SQL——透視PIVOT動態獲取待擴充套件元素集SQL套件
- 進行Windows Azure SQL Database的水平擴充套件GVWindowsSQLDatabase套件
- 【Kotlin】擴充套件屬性、擴充套件函式Kotlin套件函式
- 從EventBus學習擴充套件Weex事件機制套件事件
- 【SpringBoot】分析 SpringBoot 中的擴充套件點Spring Boot套件
- ?用Chrome擴充套件管理器, 管理你的擴充套件Chrome套件
- 深入理解ES6 ---- 正則擴充套件套件
- 用 PHP 讀取檔案的正確方法PHP
- Logstash : 從 SQL Server 讀取資料SQLServer
- C#中的擴充套件類的理解C#套件
- SQL Server大分割槽表沒有空分割槽的情況下如何擴充套件分割槽的方法SQLServer套件
- EFCore之SQL擴充套件元件BeetleX.EFCore.ExtensionSQL套件元件
- [擴充套件]laravel-xlswriter 一款基於xlswriter的laravel擴充套件包 excel極速讀寫套件LaravelExcel
- PHP-擴充套件-安裝前讀我PHP套件
- PHP擴充套件開發就是一個自己的PHP擴充套件PHP套件
- 擴充套件工具套件
- Sanic 擴充套件套件
- Mybatis擴充套件MyBatis套件
- SpringMVC 擴充套件SpringMVC套件
- ORACLE 擴充套件Oracle套件
- 「Android」分析EventBus原始碼擴充套件Weex事件機制Android原始碼套件事件