Extended Events : Leggere il contenuto del file .xel

Come è possibile leggere il contenuto di un trace su file .Xel prodotto dagli Extended Events di SQL Server 2012 ?
Utilizzando il T-SQL ed un poco di sano XML !! ;-)
Facciamo un esempio.

Creo una sessione denominata [MySession] il cui target è il file E:\MySession.xel

USE MASTER
GO

CREATE EVENT SESSION [MySession] ON SERVER 
ADD EVENT sqlserver.query_post_execution_showplan
(
    ACTION
  (
   sqlserver.client_app_name
   ,sqlserver.client_hostname
   ,sqlserver.nt_username
   ,sqlserver.sql_text
  )
),
ADD EVENT sqlserver.sql_statement_completed
(
    ACTION
  (
   sqlserver.client_app_name
   ,sqlserver.client_hostname
   ,sqlserver.nt_username
   ,sqlserver.sql_text
  )
)
ADD TARGET package0.asynchronous_file_target
 (
  SET filename=N'E:\MySession.xel'
 )
WITH (STARTUP_STATE=ON)
GO

ALTER EVENT SESSION [MySession] ON SERVER STATE=START
GO

Il file .xel prodotto dalla trace ha questa struttura

Dato che il file è umanamente illeggibile possiamo consultarlo:

1. Utilizzando l'interfaccia grafica
2. Utilizzando T-SQL e XQuery

Se scelgo la prima opzione posso usufruire di un'interfaccia come la seguente dove nella parte superiore della finestra ho l'elenco degli eventi, nella parte inferiore il dettaglio del singolo evento selezionato.

Se però il contenuto della trace è molto grosso e ho necessità di effettuare filtri, ricerche e aggregazioni forse mi conviene scegliere la strada del T-SQL utilizzando la dmv sys.fn_xe_file_target_read_file.

SELECT 
    CAST(event_data AS XML) AS event_data 
 FROM 
    sys.fn_xe_file_target_read_file('E:\MySession*.xel', NULL, NULL, NULL) 

Il carattere "*" nel nome del file indica alla dmv di leggere tutti i file xel denominati MySession presenti nella cartella. (Nella configurazione della session potremmo aver configurato anche il file rollover....)

La dmv trasforma il/i file da incomprensibili a quasi comprensibili.
Per ogni evento ne reperisce l'intero set di dati sottoforma di XML.

Ogni riga rappresenta un evento la cui struttura è indicativamente questa.

Il numero dei nodi associati all'evento dipende dal numero di Action e Fields selezionati per l'evento stesso....

Per reperire tutti i dati di un evento a questo punto devo conoscerne il path ed il tipo e creare una XQuery.
Esempio:
Per reperire il'id del database all'interno del quale s'è verificato l'esempio devo seguire il path "./data[@name="source_database_id]".
Per reperirne il valore utilizzo il metodo value o il metodo query.

Dovrei quindi scrivere la query seguente:

;WITH BaseXml AS --Leggo il file
( 
    SELECT 
        CAST(event_data AS XML) AS event_data 
    FROM 
        sys.fn_xe_file_target_read_file('E:\MySession*.xel', NULL, NULL, NULL) 
), 
XmlQuery AS --Leggo gli eventi
( 
 SELECT
  N.x.value('@name', 'varchar(250)') event_name
  ,N.x.value('@timestamp', 'datetime') event_time
  ,event_data 
    FROM 
        BaseXml    
    CROSS APPLY 
        event_data.nodes('/event') N(x) 
)
SELECT Row_number()Over(Order By event_time) Sequence
  ,event_time UTC
  ,dateadd(minute, datepart(TZoffset, sysdatetimeoffset()),event_time) event_time
  ,event_name
  ,event_data.value('(/event/action[@name=''client_app_name'']/value)[1]','varchar(max)') AS [client_app_name],
  event_data.value('(/event/action[@name=''client_hostname'']/value)[1]','varchar(max)') AS [client_hostname],
  event_data.value('(/event/action[@name=''cpu_id'']/value)[1]','bigint') AS [cpu_id],
  event_data.value('(/event/data[@name=''cpu_time'']/value)[1]','bigint') AS [cpu_time],
  event_data.value('(/event/action[@name=''database_id'']/value)[1]','bigint') AS [database_id],
  event_data.value('(/event/action[@name=''database_name'']/value)[1]','varchar(max)') AS [database_name],
  event_data.value('(/event/data[@name=''database_name'']/value)[1]','varchar(max)') AS [database_name],
  event_data.value('(/event/data[@name=''duration'']/value)[1]','bigint') AS [duration],
  event_data.value('(/event/data[@name=''estimated_cost'']/value)[1]','bigint') AS [estimated_cost],
  event_data.value('(/event/data[@name=''estimated_rows'']/value)[1]','bigint') AS [estimated_rows],
  event_data.value('(/event/data[@name=''last_row_count'']/value)[1]','bigint') AS [last_row_count],
  event_data.value('(/event/data[@name=''line_number'']/value)[1]','bigint') AS [line_number],
  event_data.value('(/event/data[@name=''logical_reads'']/value)[1]','bigint') AS [logical_reads],
  event_data.value('(/event/data[@name=''nest_level'']/value)[1]','bigint') AS [nest_level],
  event_data.value('(/event/action[@name=''nt_username'']/value)[1]','varchar(max)') AS [nt_username],
  event_data.value('(/event/data[@name=''object_id'']/value)[1]','bigint') AS [object_id],
  event_data.value('(/event/data[@name=''object_name'']/value)[1]','varchar(max)') AS [object_name],
  event_data.value('(/event/data[@name=''object_type'']/value)[1]','varchar(max)') AS [object_type],
  event_data.value('(/event/data[@name=''offset'']/value)[1]','bigint') AS [offset],
  event_data.value('(/event/data[@name=''offset_end'']/value)[1]','bigint') AS [offset_end],
  event_data.value('(/event/data[@name=''parameterized_plan_handle'']/value)[1]','varchar(max)') AS [parameterized_plan_handle],
  event_data.value('(/event/data[@name=''physical_reads'']/value)[1]','bigint') AS [physical_reads],
  event_data.value('(/event/data[@name=''row_count'']/value)[1]','bigint') AS [row_count],
  event_data.query('(/event/data[@name=''showplan_xml'']/value/*)[1]') AS [showplan_xml],
  event_data.value('(/event/data[@name=''source_database_id'']/value)[1]','bigint') AS [source_database_id],
  event_data.value('(/event/action[@name=''sql_text'']/value)[1]','varchar(max)') AS [sql_text],
  event_data.value('(/event/data[@name=''statement'']/value)[1]','varchar(max)') AS [statement],
  event_data.value('(/event/action[@name=''username'']/value)[1]','varchar(max)') AS [username],
  event_data.value('(/event/data[@name=''writes'']/value)[1]','bigint') AS [writes] 
FROM 
 XmlQuery
Order By 1

Il problema è che devo conoscere esattamente il path di ogni dato necessario e compilare il codice a mano per ogni colonna che desidero reperire..... Non è proprio molto comodo.

Fortunatamente, grazie al T-SQL dinamico posso risolvere questo inconveniente sfruttando CTE e tabelle temporanee per reperire, direttamente dall'XML la struttura ed il path di ogni singolo nodo.

DECLARE @FileName Varchar(256) = 'E:\MySession*.xel';

IF (OBJECT_ID('tempdb..#XQueryDef') IS NOT NULL) DROP TABLE #XQueryDef
IF (OBJECT_ID('tempdb..#XQuery') IS NOT NULL) DROP TABLE #XQuery

;WITH cteGetEventsFromFile AS --Leggo il file
( 
    SELECT 
        CAST(event_data AS XML) AS event_data 
    FROM 
        sys.fn_xe_file_target_read_file(@FileName, NULL, NULL, NULL) 
), 
cteGetEventsFather AS --Leggo gli eventi
( 
 SELECT
  N.x.value('@name', 'varchar(250)') event_name
  ,N.x.value('@timestamp', 'datetime') event_time
  ,event_data 
    FROM 
        cteGetEventsFromFile    
    CROSS APPLY 
        event_data.nodes('/event') N(x) 
)
,cteGetEventsFields AS --Leggo tutto ciò che riguarda ogni singolo evento
( 
    SELECT 
  L.event_name
        ,L.event_time
  ,N2.x.value('@name', 'varchar(100)') data_name
    FROM 
        cteGetEventsFather L 
    CROSS APPLY 
        L.event_data.nodes('event/*') N2(x) 
) 
,cteWhereToFind AS
(
SELECT 
 FL.event_name
 ,FL.event_time
 ,FL.data_name
 ,'''(/event/'+ ISNULL(C.column_type, O.object_type) +'[@name=''''' + FL.data_name + ''''']/value' +IIF(C.type_name like '%xml%','/*','') + ')[1]''' + 
   + IIF(C.type_name IS NULL
      ,CASE WHEN O.Type_name like '%string%' THEN ',''varchar(max)'''
        WHEN  O.type_name like '%int%' THEN ',''bigint'''
        WHEN  O.type_name like '%xml%' THEN ''
        ELSE ',''varchar(max)''' END 

      ,CASE WHEN C.type_name like '%string%' THEN ',''varchar(max)'''
        WHEN C.type_name like '%int%' THEN ',''bigint'''
        WHEN C.type_name like '%xml%' THEN ''
        ELSE ',''varchar(max)''' END 
  ) WhereToFind
FROM cteGetEventsFields FL
 left Join sys.dm_xe_object_columns C
  On FL.data_name = C.name 
    and FL.event_name = object_name
 left Join sys.dm_xe_objects O
  On FL.data_name = O.name 
   and O.object_type = 'Action'
)
SELECT DISTINCT * 
 INTO #XQueryDef
FROM cteWhereToFind

;WITH cteGetEventsFromFile AS --Leggo il file
( 
    SELECT 
        CAST(event_data AS XML) AS event_data 
    FROM 
        sys.fn_xe_file_target_read_file(@FileName, NULL, NULL, NULL) 
), 
cteGetEventsFather AS --Leggo gli eventi
( 
 SELECT
        N.x.value('@name', 'varchar(250)') event_name
  ,N.x.value('@timestamp', 'datetime') event_time
  ,event_data 
    FROM 
        cteGetEventsFromFile    
    CROSS APPLY 
        event_data.nodes('/event') N(x) 
)
SELECT * 
 INTO #XQuery
FROM cteGetEventsFather

DECLARE @strCmd VARCHAR(max)='SELECT Row_number()Over(Order By event_time) Sequence,dateadd(minute, datepart(TZoffset, sysdatetimeoffset()),event_time) event_time,event_name,'
SELECT 
 @strCmd+= 'event_data.' + IIF(data_name like '%xml%','query','value') + '(' + WhereToFind + ') AS ' + QUOTENAME(data_name) +  ',' + char(10)
FROM 
( 
 SELECT  DISTINCT  data_name, WhereToFind FROM #XQueryDef  
)A

SET @strCmd = reverse(stuff(reverse(Rtrim(@strCmd)), 1, 2, '')) + ' FROM #XQuery Order By Sequence'
EXEC(@strCmd)

Il risultato è una tabella composta da una colonna per ogni Action e Fields e una riga per ogni evento intercettato e contenuto nell'Xml.
A questo punto posso salvare il risultato in una tabella temporanea su cui fare, con codice T-SQL semplice tutte le query, le ricerche, le aggregazioni etc necessarie all'analisi della trace.

Ciao

Luca