What if the extended events in SQL Server are read correctly?

After you use extended events to capture the required information in SQL Server, you can choose where you want to store it. For example, in memory or file, but no matter where it exists, it is essentially a large XML. Therefore, reading the XML in SQL Server is the way to parse the extended event results.

Microsoft official or some SQL Server forums provide scripts to parse extended events using SQL XML, as shown in Listing 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 (+) ') as [Event Name],

   8:                         xevents.event_data.value (' (event/action[@name = "Client_app_name"]/value) [1] ',

   9:                                                  ' nvarchar (+) ') as [client app name],

  Ten:                         xevents.event_data.value (' (event/action[@name = "Client_hostname"]/value) [1] ',

  One:                                                  ' nvarchar (max) ') as [client host name],

  :                         xevents.event_data.value (' (event/action[@name = "Sql_text"]/value) [1] ',

  :                                                  ' nvarchar (max) ') as [Sql_text],

  14:           

  :                         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)],

  At:                         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  *

  To:     from    events_cte

  :     ORDER by [Event Time] DESC;

Code Listing 1. Read the script for the extended event file

However, the script in Listing 1 uses xquery,xquery when using the node attribute of XML as a delete condition, the data will become very slow after thousands of years. So I rewrite the above script, after reading the XML, into the collection of nodes to be stored in relational data format, and then filtered with a subquery, this way to read the data is basically seconds out, as shown in Listing 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 isn't                                                          NULL then D_value

   6:                                                      END)) as [Event_timestamp],

   7:                        

   8:        (VARCHAR (MAX), MIN (case when  d_name = ' Client_hostname '

   9: And D_package is isn't                                     NULL then D_value

                             (END)) as [Client_hostname],

  11:                        

  :        (VARCHAR (MAX), MIN (case when--event_name = ' sql_batch_completed ')

  :                                d_name = ' Client_app_name '

  : Then                               d_value

                       (END)) as [Client_app_name],

  16:                        

  :        (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],

  24:                        

  :        (BIGINT, MIN (case when event_name = ' sql_batch_completed ')

  : and                               d_name = ' duration '

  ISA: And                               d_package are NULL then D_value

                       (END)) as [Sql_statement_completed.duration],

  29:             

  30:                        

  :        (VARCHAR (MAX), MIN (case when d_name = ' sql_text ')

  : Then                                      d_value

                             (END)) as [Sql_statement_completed.sql_text],

  34:                        

  :        (VARCHAR (MAX), MIN (case when d_name = ' username ')

  D_package: And is not                                     NULL then D_value

  37:                           

  A:               from     (SELECT    *,

  All:                                    CONVERT (VARCHAR (+), NULL) as attach_activity_id

  Max:                          from      (SELECT    event.value (' (@name) [1] ',

  A:                                                            ' VARCHAR ') as Event_Name,

  :                                                Dense_rank () over (ORDER by event) as UNIQUE_EVENT_ID,

  :                                                n.value (' (@name) [1] ',

  :                                                        ' VARCHAR ') as D_name,

  :                                                n.value (' (@package) [1] ',

  :                                                        ' VARCHAR ') as D_package,

  A:                                                n.value (' (value) [1]/text ()] [1] ',

  :                                                        ' VARCHAR (MAX) ') as D_value,

  A:                                                n.value (' (text) [1]/text ()] [1] ',

  :                                                        ' VARCHAR (MAX) ') as D_text

  Wuyi:                                      from      (select    (select

  :                                                              CONVERT (XML, Target_data)

  : From                                                              

  Wu:                                                              sys.dm_xe_session_targets St

  Mon:                                                              JOIN sys.dm_xe_sessions s on s.address = st.event_session_address

  A:                                                              WHERE

  £ º                                                              s.name = ' DDL '

                                                                st.target_name = ' Ring_buffer '

  :                                                            ) as [x]

  : For                                                

  A:                                                  XML PATH ("),

  +:                                                      TYPE

  :                                                ) as The_xml (x)

  +: Cross                                                APPLY x.nodes ('//event ') e (event)

  +: Cross                                                APPLY event.nodes (' * ')

  NET: as                                                q (n)

  :                                    ) as Data_data

  :                        ) as Activity_data

  :               GROUP by unique_event_id

  :             )

  :    SELECT  *

  A:    from    TT

Code Listing 2. Optimizing read for Extended event results

Reference: http://blog.wharton.com.au/2011/06/13/part-5-openxml-and-xquery-optimisation-tips/

What if the extended events in SQL Server are read correctly?