如果正确读取SQL Server中的扩展事件？

2022-07-11 17:57:07 浏览数 (6)

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

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

代码语言：javascript复制
   1: WITH    events_cte
代码语言：javascript复制
   2:           AS ( SELECT   DATEADD(mi,
代码语言：javascript复制
   3:                                 DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
代码语言：javascript复制
   4:                                 xevents.event_data.value('(event/@timestamp)[1]',
代码语言：javascript复制
   5:                                                          'datetime2')) AS [event time] ,
代码语言：javascript复制
   6:                                                             xevents.event_data.value('(event/@name)[1]',
代码语言：javascript复制
   7:                                                  'nvarchar(128)') AS [Event Name],
代码语言：javascript复制
   8:                         xevents.event_data.value('(event/action[@name="client_app_name"]/value)[1]',
代码语言：javascript复制
   9:                                                  'nvarchar(128)') AS [client app name] ,
代码语言：javascript复制
  10:                         xevents.event_data.value('(event/action[@name="client_hostname"]/value)[1]',
代码语言：javascript复制
  11:                                                  'nvarchar(max)') AS [client host name] ,
代码语言：javascript复制
  12:                         xevents.event_data.value('(event/action[@name="sql_text"]/value)[1]',
代码语言：javascript复制
  13:                                                  'nvarchar(max)') AS [sql_text] ,
代码语言：javascript复制
  14:
代码语言：javascript复制
  15:                         xevents.event_data.value('(event/action[@name="database_name"]/value)[1]',
代码语言：javascript复制
  16:                                                  'nvarchar(max)') AS [database name] ,
代码语言：javascript复制
  17:                         xevents.event_data.value('(event/action[@name="username"]/value)[1]',
代码语言：javascript复制
  18:                                                  'nvarchar(max)') AS [username] ,
代码语言：javascript复制
  19:                         xevents.event_data.value('(event/action[@name="duration"]/value)[1]',
代码语言：javascript复制
  20:                                                  'bigint') AS [duration (ms)] ,
代码语言：javascript复制
  21:                         xevents.event_data.value('(event/action[@name="cpu_time"]/value)[1]',
代码语言：javascript复制
  22:                                                  'bigint') AS [cpu time (ms)] ,
代码语言：javascript复制
  23:                         xevents.event_data.value('(event/data[@name="object_name"]/value)[1]',
代码语言：javascript复制
  24:                                                  'nvarchar(max)') AS [OBJECT_NAME]
代码语言：javascript复制
  25:                FROM     sys.fn_xe_file_target_read_file('D:XeventResutlDDLAudit*.xel',
代码语言：javascript复制
  26:                                                         NULL, NULL, NULL)
代码语言：javascript复制
  27:                         CROSS APPLY ( SELECT    CAST(event_data AS XML) AS event_data
代码语言：javascript复制
  28:                                     ) AS xevents
代码语言：javascript复制
  29:              )
代码语言：javascript复制
  30:     SELECT  *
代码语言：javascript复制
  31:     FROM    events_cte
代码语言：javascript复制
  32:     ORDER BY [event time] DESC;

代码清单1.读取扩展事件文件的脚本

但代码清单1的脚本使用的是XQuery，XQuery在使用Xml的节点属性作为删选条件时，数据上千以后就会变得非常慢。因此我对上述脚本进行了改写，将XML读取出来后，变为节点的集合以关系数据格式存放，再用子查询进行筛选，这种方式读取数据基本上是秒出，如代码清单2所示。

代码语言：javascript复制
   1: WITH   tt
代码语言：javascript复制
   2:          AS ( SELECT   MIN(event_name) AS event_name ,
代码语言：javascript复制
   3:               DATEADD(hh,DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),
代码语言：javascript复制
   4:                                CONVERT(DATETIME, MIN(CASE WHEN d_name = 'collect_system_time'
代码语言：javascript复制
   5:                                                          AND d_package IS NOT NULL THEN d_value
代码语言：javascript复制
   6:                                                      END))) AS [event_timestamp] ,
代码语言：javascript复制
   7:                        CONVERT
代码语言：javascript复制
   8:        (VARCHAR(MAX), MIN(CASE WHEN  d_name = 'client_hostname'
代码语言：javascript复制
   9:                                     AND d_package IS NOT NULL THEN d_value
代码语言：javascript复制
  10:                           END)) AS [Client_hostname] ,
代码语言：javascript复制
  11:                        CONVERT
代码语言：javascript复制
  12:        (VARCHAR(MAX), MIN(CASE WHEN --event_name = 'sql_batch_completed'
代码语言：javascript复制
  13:                                d_name = 'client_app_name'
代码语言：javascript复制
  14:                               THEN d_value
代码语言：javascript复制
  15:                     END)) AS [Client_app_name] ,
代码语言：javascript复制
  16:                        CONVERT
代码语言：javascript复制
  17:        (VARCHAR(MAX), MIN(CASE WHEN  d_name = 'database_name'
代码语言：javascript复制
  18:                                     AND d_package IS NOT NULL THEN d_value
代码语言：javascript复制
  19:                           END)) AS [database_name] ,
代码语言：javascript复制
  20:                           CONVERT
代码语言：javascript复制
  21:                                   (VARCHAR(MAX), MIN(CASE WHEN  d_name = 'object_name'
代码语言：javascript复制
  22:                                      THEN d_value
代码语言：javascript复制
  23:                           END)) AS [object_name] ,
代码语言：javascript复制
  24:                        CONVERT
代码语言：javascript复制
  25:        (BIGINT, MIN(CASE WHEN event_name = 'sql_batch_completed'
代码语言：javascript复制
  26:                               AND d_name = 'duration'
代码语言：javascript复制
  27:                               AND d_package IS NULL THEN d_value
代码语言：javascript复制
  28:                     END)) AS [sql_statement_completed.duration] ,
代码语言：javascript复制
  29:
代码语言：javascript复制
  30:                        CONVERT
代码语言：javascript复制
  31:        (VARCHAR(MAX), MIN(CASE WHEN d_name = 'sql_text'
代码语言：javascript复制
  32:                                      THEN d_value
代码语言：javascript复制
  33:                           END)) AS [sql_statement_completed.sql_text] ,
代码语言：javascript复制
  34:                        CONVERT
代码语言：javascript复制
  35:        (VARCHAR(MAX), MIN(CASE WHEN d_name = 'username'
代码语言：javascript复制
  36:                                     AND d_package IS NOT NULL THEN d_value
代码语言：javascript复制
  37:                           END)) AS [username]
代码语言：javascript复制
  38:               FROM     ( SELECT    * ,
代码语言：javascript复制
  39:                                    CONVERT(VARCHAR(400), NULL) AS attach_activity_id
代码语言：javascript复制
  40:                          FROM      ( SELECT    event.value('(@name)[1]',
代码语言：javascript复制
  41:                                                            'VARCHAR(400)') AS event_name ,
代码语言：javascript复制
  42:                                                DENSE_RANK() OVER ( ORDER BY event ) AS unique_event_id ,
代码语言：javascript复制
  43:                                                n.value('(@name)[1]',
代码语言：javascript复制
  44:                                                        'VARCHAR(400)') AS d_name ,
代码语言：javascript复制
  45:                                                n.value('(@package)[1]',
代码语言：javascript复制
  46:                                                        'VARCHAR(400)') AS d_package ,
代码语言：javascript复制
  47:                                                n.value('((value)[1]/text())[1]',
代码语言：javascript复制
  48:                                                        'VARCHAR(MAX)') AS d_value ,
代码语言：javascript复制
  49:                                                n.value('((text)[1]/text())[1]',
代码语言：javascript复制
  50:                                                        'VARCHAR(MAX)') AS d_text
代码语言：javascript复制
  51:                                      FROM      ( SELECT    ( SELECT
代码语言：javascript复制
  52:                                                              CONVERT(XML, target_data)
代码语言：javascript复制
  53:                                                              FROM
代码语言：javascript复制
  54:                                                              sys.dm_xe_session_targets st
代码语言：javascript复制
  55:                                                              JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
代码语言：javascript复制
  56:                                                              WHERE
代码语言：javascript复制
  57:                                                              s.name = 'DDL'
代码语言：javascript复制
  58:                                                              AND st.target_name = 'ring_buffer'
代码语言：javascript复制
  59:                                                            ) AS [x]
代码语言：javascript复制
  60:                                                FOR
代码语言：javascript复制
  61:                                                  XML PATH('') ,
代码语言：javascript复制
  62:                                                      TYPE
代码语言：javascript复制
  63:                                                ) AS the_xml ( x )
代码语言：javascript复制
  64:                                                CROSS APPLY x.nodes('//event') e ( event )
代码语言：javascript复制
  65:                                                CROSS APPLY event.nodes('*')
代码语言：javascript复制
  66:                                                AS q ( n )
代码语言：javascript复制
  67:                                    ) AS data_data
代码语言：javascript复制
  68:                        ) AS activity_data
代码语言：javascript复制
  69:               GROUP BY unique_event_id
代码语言：javascript复制
  70:             )
代码语言：javascript复制
  71:    SELECT  *
代码语言：javascript复制
  72:    FROM    tt
代码语言：javascript复制
  73:

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

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

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/119752.html原文链接：https://javaforall.cn

sql sqlserver 数据库 java xml

0 人点赞