{"id":233,"date":"2016-05-31T20:09:03","date_gmt":"2016-05-31T19:09:03","guid":{"rendered":"https:\/\/sqldoubleg.live-website.com\/?p=233"},"modified":"2017-06-08T11:25:28","modified_gmt":"2017-06-08T10:25:28","slug":"extended-events-file-target-reader","status":"publish","type":"post","link":"https:\/\/www.sqldoubleg.com\/es\/2016\/05\/31\/extended-events-file-target-reader\/","title":{"rendered":"Extended Events File Target Reader"},"content":{"rendered":"

Getting useful information from Extended Event files is sometimes very tedious as you have to use XML queries and even PIVOT, what can be a better plan? It’s been some time since I started with my first SQL-G Hack<\/a>, and I think those are precisely the kind of posts you might want to find when searching for a specific problem you have right now.<\/p>\n

It’s true that most of the time your answer is posted already in StackOverflow, but that’s another story.<\/p>\n

This time I wanted to post this stored procedure that might help people start using more Extended Events, because, to be honest, I still find easier jump onto SQL Profiler when it’s something about checking some sort of activity or what happens in the database when I click that button (and I don’t have any developer handy) in the application.<\/p>\n

But the output is not very friendly and once you have a target, you cannot change it, and persisting the information in a file happens to be binary, and using T-SQL you can only see it as XML and everyone hates XML, specially when you have to query it \ud83d\ude42<\/p>\n

So the other day I had to do exactly this and I ended up writing a query that reads from the file, parses the XML and PIVOT the results to get meaningful data to look at. Then I said to myself… never again, so I kept writing to have a more flexible one, so I don’t have to do it again for good.<\/p>\n

 
\nFirst things first<\/strong><\/p>\n

So I’m going to show you how I did create my Extended Events session to track \u00abWhat I do\u00bb on my SQL Server instance. You may want to customize the output file path and [server_principal_name]<\/p>\n

\r\nCREATE EVENT SESSION [What I do] ON SERVER \r\n\r\nADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)\r\n    ACTION(package0.collect_system_time,sqlserver.client_hostname,sqlserver.database_name,sqlserver.query_plan_hash\r\n\t\t\t,sqlserver.server_principal_name,sqlserver.session_id)),\r\n\r\nADD EVENT sqlserver.sql_statement_completed(SET collect_parameterized_plan_handle=(0),collect_statement=(1)\r\n    ACTION(package0.collect_system_time,sqlserver.client_hostname,sqlserver.database_name,sqlserver.query_plan_hash\r\n\t\t\t,sqlserver.server_principal_name,sqlserver.session_id)\r\n    WHERE ([sqlserver].[server_principal_name]=N'SQLDoubleG')) \r\n\r\nADD TARGET package0.event_file(SET filename=N'C:\\Program Files\\Microsoft SQL Server\\MSSQL12.MSSQL2014\\MSSQL\\Log\\WhatIdo.xel')\r\n\r\nWITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB\r\n\t\t,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)\r\nGO\r\n<\/pre>\n
There are a couple of events I’m interested on, [rpc_completed] and [sql_statement_completed], that’s enough for my purpose, but you can choose whatever you feel like, it shouldn’t be any problem if you want to go for something different.<\/p>\n
 
\nThe tools<\/strong><\/p>\n
Thankfully SQL Server is full of tools, so good news is there is a function we can call to read the target file, sys.fn_xe_file_target_read_file<\/a> bad news though is that all the meat inside a XML column. <\/p>\n
Once I’ve started my session, I run a few commands.<\/p>\n
\r\nUSE AdventureWorks2014\r\n\r\nSELECT TOP 100 * FROM Person.Address\r\n\r\nSELECT TOP 100 * FROM Person.Person\r\n\r\nSELECT TOP 100 * FROM Production.Product\r\n<\/pre>\n
I stopped it and find the file in location specified, please note that there is a suffix in addition to the given name.<\/p>\n
So if we see the file using the function, it looks like<\/p>\n
\r\nSELECT *\r\nFROM sys.fn_xe_file_target_read_file(N'C:\\Program Files\\Microsoft SQL Server\\MSSQL12.MSSQL2014\\MSSQL\\Log\\WhatIdo_0_131086770842200000.xel', null, null, null);\r\n<\/pre>\n
\"01_fn_xe_file_target_read_file\"<\/a><\/p>\n
Not very useful I guess, the information we are looking for is contained in XML<\/p>\n
 
\nThe XML<\/strong><\/p>\n
The XML that contains the information we want to analyse might look like the one below.<\/p>\n
\r\n<event name="rpc_completed" package="sqlserver" timestamp="2016-05-25T19:11:24.230Z">\r\n  <data name="cpu_time">\r\n    <value>0<\/value>\r\n  <\/data>\r\n  <data name="duration">\r\n    <value>3633<\/value>\r\n  <\/data>\r\n  <data name="physical_reads">\r\n    <value>0<\/value>\r\n  <\/data>\r\n  <data name="logical_reads">\r\n    <value>24<\/value>\r\n  <\/data>\r\n  <data name="writes">\r\n    <value>0<\/value>\r\n  <\/data>\r\n  <data name="result">\r\n    <value>0<\/value>\r\n    <text>OK<\/text>\r\n  <\/data>\r\n  <data name="row_count">\r\n    <value>1<\/value>\r\n  <\/data>\r\n  <data name="connection_reset_option">\r\n    <value>0<\/value>\r\n    <text>None<\/text>\r\n  <\/data>\r\n  <data name="object_name">\r\n    <value>sp_executesql<\/value>\r\n  <\/data>\r\n  <data name="statement">\r\n    <value>exec sp_executesql N'SELECT\r\nsession.name AS [Name],\r\n''XEStore[@Name='' + quotename(CAST(SERVERPROPERTY(N''ServerName'') AS sysname),'''''''') + '']'' + ''\/Session[@Name='' + quotename(session.name,'''''''') + '']'' AS [Urn],\r\nsession.startup_state AS [AutoStart],\r\nrunning.create_time AS [StartTime],\r\n(CASE WHEN (running.create_time IS NULL) THEN 0 ELSE 1 END) AS [IsRunning]\r\nFROM\r\nsys.server_event_sessions AS session\r\nLEFT OUTER JOIN sys.dm_xe_sessions AS running ON running.name = session.name\r\nWHERE\r\n(session.name=@_msparam_0)and((CAST(SERVERPROPERTY(N''ServerName'') AS sysname)=@_msparam_1))\r\nORDER BY\r\n[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'What I do',@_msparam_1=N'DESKTOP-P6AOTMB\\MSSQL2014'<\/value>\r\n  <\/data>\r\n  <data name="data_stream">\r\n    <value \/>\r\n  <\/data>\r\n  <data name="output_parameters">\r\n    <value \/>\r\n  <\/data>\r\n  <action name="session_id" package="sqlserver">\r\n    <value>51<\/value>\r\n  <\/action>\r\n  <action name="server_principal_name" package="sqlserver">\r\n    <value>SQLDoubleG<\/value>\r\n  <\/action>\r\n  <action name="query_plan_hash" package="sqlserver">\r\n    <value>0<\/value>\r\n  <\/action>\r\n  <action name="database_name" package="sqlserver">\r\n    <value>master<\/value>\r\n  <\/action>\r\n  <action name="client_hostname" package="sqlserver">\r\n    <value>DESKTOP-P6AOTMB<\/value>\r\n  <\/action>\r\n  <action name="collect_system_time" package="package0">\r\n    <value>2016-05-25T19:11:24.229Z<\/value>\r\n  <\/action>\r\n  <action name="attach_activity_id_xfer" package="package0">\r\n    <value>B05B6BEA-DA76-4283-94EC-4EEAAF43EA87-0<\/value>\r\n  <\/action>\r\n  <action name="attach_activity_id" package="package0">\r\n    <value>33E553E3-FF64-4A8D-BD2F-1C061195AB96-1<\/value>\r\n  <\/action>\r\n<\/event>\r\n<\/pre>\n
Using SQL Server, we can query our XML column to get all the juice, but first we have to convert it to XML because it comes as plain text. So we can use a temporary table to do the trick.<\/p>\n
\r\nIF OBJECT_ID('tempdb..#ExEvent') IS NOT NULL DROP TABLE #ExEvent\r\n\r\nSELECT IDENTITY(INT,1,1) AS RowId, object_name AS event_name, CONVERT(XML,event_data) AS event_data\r\n\tINTO #ExEvent\r\nFROM sys.fn_xe_file_target_read_file(N'C:\\Program Files\\Microsoft SQL Server\\MSSQL12.MSSQL2014\\MSSQL\\Log\\WhatIdo_0_131086770842200000.xel', null, null, null);\r\n<\/pre>\n
 
\nParsing the XML<\/strong><\/p>\n
Once we have the table, we can start querying the XML to extract all that info<\/p>\n
\r\nSELECT RowId\r\n\t\t, event_name\r\n\t\t, T2.Loc.query('.').value('(\/action\/@name)[1]', 'varchar(max)')AS att_name\r\n\t\t, T2.Loc.query('.').value('(\/action\/value)[1]', 'varchar(max)')AS att_value\r\n\tFROM   #ExEvent\r\n\tCROSS APPLY event_data.nodes('\/event\/action') as T2(Loc) \r\n\tWHERE T2.Loc.query('.').value('(\/action\/@name)[1]', 'varchar(max)') \r\n\t\tIN ('client_hostname', 'collect_system_time', 'database_name', 'query_plan_hash'\r\n\t\t\t, 'server_principal_name', 'session_id')\r\n\r\n<\/pre>\n
I just picked up a few columns I am interested on, but there are some others you can check.<\/p>\n
The thing is that as you’ve seen above there are 2 different elements in the XML, the <data> and <action>, so I’ll have to do more magic to get all of them. <\/p>\n
\r\nSELECT RowId\r\n\t\t, event_name\r\n\t\t, T3.Loc.query('.').value('(\/data\/@name)[1]', 'varchar(max)') AS att_name\r\n\t\t, T3.Loc.query('.').value('(\/data\/value)[1]', 'varchar(max)') AS att_value\r\n\tFROM   #ExEvent\r\n\tCROSS APPLY event_data.nodes('\/event\/data') as T3(Loc) \r\n\tWHERE T3.Loc.query('.').value('(\/data\/@name)[1]', 'varchar(max)') \r\n\t\tIN ('collect_statement', 'connection_reset_option', 'cpu_time', 'data_stream', 'duration'\r\n\t\t\t, 'last_row_count', 'line_number', 'logical_reads', 'object_name', 'offset'\r\n\t\t\t, 'offset_end', 'output_parameters', 'parameterized_plan_handle', 'physical_reads'\r\n\t\t\t, 'result', 'row_count', 'statement', 'writes')\r\n<\/pre>\n
Each of those queries above will return a list of actions and events respectively along the current value for each time the event was triggered during the session.<\/p>\n
\"02_select_cross_apply_nodes\"<\/a><\/p>\n
Still not very useful, I’d rather see this like one event one line, so another top favourite for each DBA, PIVOT!!! there we go<\/p>\n
\r\nSELECT RowId, event_name, [client_hostname], [collect_system_time], [database_name], [query_plan_hash], [server_principal_name], [session_id]\t\t\t\t\t\r\n\tFROM (\r\n\t\t\tSELECT RowId\r\n\t\t\t\t\t, event_name\r\n\t\t\t\t\t, T2.Loc.query('.').value('(\/action\/@name)[1]', 'varchar(max)')AS att_name\r\n\t\t\t\t\t, T2.Loc.query('.').value('(\/action\/value)[1]', 'varchar(max)')AS att_value\r\n\t\t\tFROM   #ExEvent\r\n\t\t\tCROSS APPLY event_data.nodes('\/event\/action') as T2(Loc) \r\n\t\t\tWHERE T2.Loc.query('.').value('(\/action\/@name)[1]', 'varchar(max)') \r\n\t\t\t\tIN ('client_hostname', 'collect_system_time', 'database_name', 'query_plan_hash', 'server_principal_name', 'session_id')\r\n\t\t) AS SourceTable\r\n\t\t\tPIVOT(\r\n\t\t\t\tMAX(att_value)\r\n\t\t\t\tFOR att_name IN ([client_hostname], [collect_system_time], [database_name], [query_plan_hash], [server_principal_name], [session_id])\r\n\t\t) AS PivotTable\r\n<\/pre>\n
\"03_PIVOT\"<\/a>
\nThat looks much better, thanks! <\/p>\n
 
\nFinal output<\/strong><\/p>\n
But to get all information I have used 2 different queries, so now if you were wondering why I generated an IDENTITY column, this is why, so I can join both, <data> and <action> and generate a unique row per event in the file.<\/p>\n
Let’s go for it!<\/p>\n
\r\nSELECT ISNULL(t_action.RowId, t_data.RowId) AS RowId\r\n\t\t, ISNULL(t_action.event_name, t_data.event_name) AS event_name\r\n\t\t, t_action.[client_hostname], t_action.[collect_system_time], t_action.[database_name], t_action.[query_plan_hash], t_action.[server_principal_name], t_action.[session_id]\r\n\t\t, t_data.[collect_statement], t_data.[connection_reset_option], t_data.[cpu_time], t_data.[data_stream], t_data.[duration], t_data.[last_row_count], t_data.[line_number], t_data.[logical_reads], t_data.[object_name], t_data.[offset], t_data.[offset_end], t_data.[output_parameters], t_data.[parameterized_plan_handle], t_data.[physical_reads], t_data.[result], t_data.[row_count], t_data.[statement], t_data.[writes]\r\n\tFROM (\r\n\t\t\tSELECT RowId, event_name, [client_hostname], [collect_system_time], [database_name], [query_plan_hash], [server_principal_name], [session_id]\t\t\t\t\t\r\n\t\t\t\tFROM (\r\n\t\t\t\t\t\tSELECT RowId\r\n\t\t\t\t\t\t\t\t, event_name\r\n\t\t\t\t\t\t\t\t, T2.Loc.query('.').value('(\/action\/@name)[1]', 'varchar(max)')AS att_name\r\n\t\t\t\t\t\t\t\t, T2.Loc.query('.').value('(\/action\/value)[1]', 'varchar(max)')AS att_value\r\n\t\t\t\t\t\tFROM   #ExEvent\r\n\t\t\t\t\t\tCROSS APPLY event_data.nodes('\/event\/action') as T2(Loc) \r\n\t\t\t\t\t\tWHERE T2.Loc.query('.').value('(\/action\/@name)[1]', 'varchar(max)') \r\n\t\t\t\t\t\t\tIN ('client_hostname', 'collect_system_time', 'database_name', 'query_plan_hash', 'server_principal_name', 'session_id')\r\n\t\t\t\t\t) AS SourceTable\r\n\t\t\t\t\t\tPIVOT(\r\n\t\t\t\t\t\t\tMAX(att_value)\r\n\t\t\t\t\t\t\tFOR att_name IN ([client_hostname], [collect_system_time], [database_name], [query_plan_hash], [server_principal_name], [session_id])\r\n\t\t\t\t\t) AS PivotTable\r\n\t\t\t) AS t_action\r\n\t\t\r\n\t\t-- Full outer because it might be no events selected only the payload\r\n\t\tFULL OUTER JOIN (\r\n\t\t\tSELECT RowId, event_name, [collect_statement], [connection_reset_option], [cpu_time], [data_stream], [duration], [last_row_count], [line_number], [logical_reads], [object_name], [offset], [offset_end], [output_parameters], [parameterized_plan_handle], [physical_reads], [result], [row_count], [statement], [writes]\r\n\t\t\t\tFROM (\r\n\t\t\t\t\t\tSELECT RowId\r\n\t\t\t\t\t\t\t\t, event_name\r\n\t\t\t\t\t\t\t\t, T3.Loc.query('.').value('(\/data\/@name)[1]', 'varchar(max)') AS att_name\r\n\t\t\t\t\t\t\t\t, T3.Loc.query('.').value('(\/data\/value)[1]', 'varchar(max)') AS att_value\r\n\r\n\t\t\t\t\t\t\tFROM   #ExEvent\r\n\t\t\t\t\t\t\tCROSS APPLY event_data.nodes('\/event\/data') as T3(Loc) \r\n\t\t\t\t\t\t\tWHERE T3.Loc.query('.').value('(\/data\/@name)[1]', 'varchar(max)') \r\n\t\t\t\t\t\t\t\tIN ('collect_statement', 'connection_reset_option', 'cpu_time', 'data_stream', 'duration', 'last_row_count', 'line_number', 'logical_reads', 'object_name', 'offset', 'offset_end', 'output_parameters', 'parameterized_plan_handle', 'physical_reads', 'result', 'row_count', 'statement', 'writes')\r\n\t\t\t\t\t\t) AS SourceTable\r\n\t\t\t\t\t\t\tPIVOT (\r\n\t\t\t\t\t\t\tMAX(att_value)\r\n\t\t\t\t\t\t\t\tFOR att_name IN ([collect_statement], [connection_reset_option], [cpu_time], [data_stream], [duration], [last_row_count], [line_number], [logical_reads], [object_name], [offset], [offset_end], [output_parameters], [parameterized_plan_handle], [physical_reads], [result], [row_count], [statement], [writes])\r\n\t\t\t\t\t\t) AS PivotTable\r\n\r\n\t\t\t) AS t_data\r\n\t\t\tON t_data.RowId = t_action.RowId\r\n<\/pre>\n
And finally we can get some meaningful info, just one line per event and all the columns where had defined for the session.<\/p>\n
\"04_PIVOT_and_JOINS\"<\/a><\/p>\n
Yes! that works! <\/p>\n
 
\nThe SQL-G Hack!<\/strong><\/p>\n
Hopefully you have read all the process through and you have now a better understanding of how painful it was. But that’s what this post is for, to relieve the pain \ud83d\ude42<\/p>\n
Using DMV’s we can see how any session is defined hence dynamically we can do all the hard work and leave for us just analysing the results. Cool, innit<\/em>?<\/p>\n
\r\nUSE [DBA]\r\nGO\r\nSET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\nIF OBJECT_ID('dbo.sqlg_ExtendedEventFileReader') IS NULL EXECUTE sp_executesql N'CREATE PROCEDURE dbo.sqlg_ExtendedEventFileReader AS RETURN'\r\nGO\r\n-- =============================================\r\n-- Author:      Raul Gonzalez @SQLDoubleG\r\n-- Create date: 25\/05\/2016\r\n-- Description: Returns the content of a Extended Event target file in a table format according to the current definition of the session\r\n--\r\n-- Remarks: \r\n--              - The session must exist in the server and so must the file in a place we can access\r\n--              - Depending on the amount of data this process may take very long, it is recommended not to use in production systems\r\n--              - You can offload this task by generating the query using the parameter @debugging = 1 and copying the file to another server.\r\n--\r\n-- Parameters:\r\n--              - @sessionName  -> name of the extended event session\r\n--              - @filePath     -> full path (including name) of the target file\r\n--              - @debugging    -> will print the statement or execute it\r\n--\r\n-- Log History: \r\n--              25\/05\/2016 RAG - Created\r\n--              31\/03\/2017 RAG - Allow 'xml_deadlock_report' events to be returned in a way you can copy\/paste and save as (.xdl) file\r\n--\t\t\t\t\t\t\t\t\tfor further analysis.\r\n--              07\/06\/2017 RAG - Added event_timestamp column\r\n--              08\/06\/2017 RAG - Return all events that return XML in XML format\r\n--\r\n-- Copyright:   (C) 2016 Raul Gonzalez (@SQLDoubleG https:\/\/sqldoubleg.live-website.com)\r\n--\r\n--              THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF \r\n--              ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED \r\n--              TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND\/OR FITNESS FOR A\r\n--              PARTICULAR PURPOSE.\r\n--\r\n--              THE AUTHOR SHALL NOT BE LIABLE TO YOU OR ANY THIRD PARTY FOR ANY INDIRECT, \r\n--              SPECIAL, INCIDENTAL, PUNITIVE, COVER, OR CONSEQUENTIAL DAMAGES OF ANY KIND\r\n--\r\n--              YOU MAY ALTER THIS CODE FOR YOUR OWN *NON-COMMERCIAL* PURPOSES. YOU MAY\r\n--              REPUBLISH ALTERED CODE AS LONG AS YOU INCLUDE THIS COPYRIGHT AND GIVE DUE CREDIT. \r\n--\r\n-- =============================================\r\nALTER PROCEDURE dbo.sqlg_ExtendedEventFileReader\r\n    @sessionName    SYSNAME       \r\n    , @filePath     NVARCHAR(512) \r\n    , @debugging    BIT             = 0 -- Set to 0 to run the query, otherwise PRINT the statement\r\nAS\r\n   \r\nDECLARE @sql\t\t\t\tNVARCHAR(MAX)\r\n  \r\nDECLARE @actionsList\t\tNVARCHAR(MAX)\r\nDECLARE @actionsListFilter\tNVARCHAR(MAX)\r\nDECLARE @actionsListColumns\tNVARCHAR(MAX)\r\n  \r\nDECLARE @eventsList\t\t\tNVARCHAR(MAX)\r\nDECLARE @eventsListFilter\tNVARCHAR(MAX)\r\nDECLARE @eventsListColumns\tNVARCHAR(MAX)\r\n   \r\n-- Get actions from the Session\r\nSET @actionsList\t\t= (SELECT STUFF((SELECT DISTINCT ', ' + QUOTENAME(a.name)\r\n                                FROM sys.server_event_sessions AS s\r\n                                    LEFT JOIN sys.server_event_session_events AS e\r\n                                        ON e.event_session_id = s.event_session_id\r\n                                    INNER JOIN sys.server_event_session_actions AS a\r\n                                        ON a.event_id = e.event_id\r\n                                            AND e.event_session_id = a.event_session_id \r\n                                WHERE s.name = @sessionName\r\n                            FOR XML PATH('')), 1, 2, ''))\r\n   \r\nSET @actionsListFilter\t= REPLACE(REPLACE(@actionsList, '[', ''''), ']', '''')\r\nSET @actionsListColumns = REPLACE(@actionsList, '[', 't_action.[')\r\n   \r\n-- Get Payload \r\n-- This to return events that return XML in XML format, just for the final query\r\nSET @eventsListColumns\t= (SELECT STUFF(\r\n\t\t\t\t\t\t\t\t(SELECT DISTINCT ', ' + \r\n\t\t\t\t\t\t\t\t\t\tCASE WHEN c.type_name = 'XML' THEN 'CONVERT(XML, ' + QUOTENAME(c.name) + ') AS ' + QUOTENAME(c.name)\r\n\t\t\t\t\t\t\t\t\t\t\tELSE 't_data.' + QUOTENAME(c.name)\r\n\t\t\t\t\t\t\t\t\t\tEND\r\n\t\t\t\t\t\t\t\t\tFROM sys.server_event_sessions AS s\r\n\t\t\t\t\t\t\t\t\t\tLEFT JOIN sys.server_event_session_events AS e\r\n\t\t\t\t\t\t\t\t\t\t\tON e.event_session_id = s.event_session_id\r\n\t\t\t\t\t\t\t\t\t\tLEFT JOIN sys.dm_xe_objects AS o\r\n\t\t\t\t\t\t\t\t\t\t\tON o.name = e.name\r\n\t\t\t\t\t\t\t\t\t\tLEFT JOIN sys.dm_xe_object_columns AS c\r\n\t\t\t\t\t\t\t\t\t\t\tON c.object_name = o.name\r\n\t\t\t\t\t\t\t\t\tWHERE s.name = @sessionName\r\n\t\t\t\t\t\t\t\t\t\tAND c.column_type <> 'readonly'\r\n\t\t\t\t\t\t\t\t\t\tAND ISNULL(column_value, 'true') = 'true'\r\n\t\t\t\t\t\t\t\tFOR XML PATH('')), 1, 2, ''))\r\n\r\nSET @eventsList\t\t\t= (SELECT STUFF(\r\n\t\t\t\t\t\t\t\t(SELECT DISTINCT ', ' + QUOTENAME(c.name)\r\n\t\t\t\t\t\t\t\t\tFROM sys.server_event_sessions AS s\r\n\t\t\t\t\t\t\t\t\t\tLEFT JOIN sys.server_event_session_events AS e\r\n\t\t\t\t\t\t\t\t\t\t\tON e.event_session_id = s.event_session_id\r\n\t\t\t\t\t\t\t\t\t\tLEFT JOIN sys.dm_xe_objects AS o\r\n\t\t\t\t\t\t\t\t\t\t\tON o.name = e.name\r\n\t\t\t\t\t\t\t\t\t\tLEFT JOIN sys.dm_xe_object_columns AS c\r\n\t\t\t\t\t\t\t\t\t\t\tON c.object_name = o.name\r\n\t\t\t\t\t\t\t\t\tWHERE s.name = @sessionName\r\n\t\t\t\t\t\t\t\t\t\tAND c.column_type <> 'readonly'\r\n\t\t\t\t\t\t\t\t\t\tAND ISNULL(column_value, 'true') = 'true'\r\n\t\t\t\t\t\t\t\tFOR XML PATH('')), 1, 2, ''))\r\n   \r\nSET @eventsListFilter   = REPLACE(REPLACE(@eventsList, '[', ''''), ']', '''')\r\n   \r\nSET @sql = N'\r\n   \r\nIF OBJECT_ID(''tempdb..#ExEvent'') IS NOT NULL DROP TABLE #ExEvent\r\n   \r\nSELECT IDENTITY(INT,1,1) AS RowId, object_name AS event_name, CONVERT(XML,event_data) AS event_data\r\n    INTO #ExEvent\r\nFROM sys.fn_xe_file_target_read_file(N''' + @filePath + ''', null, null, null);\r\n   \r\n--=======================================================================================================\r\n-- Usually here I would remove events I am not interested on, because the next query can take very long\r\n--\r\n--SELECT TOP 100 * FROM #ExEvent ORDER BY event_name\r\n--\r\n-- DELETE #ExEvent\r\n--WHERE event_name [NOT] IN (N''rpc_completed'')\r\n--=======================================================================================================\r\n   \r\nSELECT ISNULL(t_action.RowId, t_data.RowId) AS RowId\r\n        , ISNULL(t_action.event_name, t_data.event_name) AS event_name\r\n        , ISNULL(t_action.event_timestamp, t_data.event_timestamp) AS event_timestamp\r\n        , ' + @actionsListColumns + N'\r\n        , ' + @eventsListColumns + N'\r\n    FROM (\r\n            SELECT RowId, event_name, event_timestamp, ' + @actionsList + N'                 \r\n                FROM (\r\n                    SELECT RowId\r\n                            , event_name\r\n                            , T1.Loc.query(''.'').value(''(\/event\/@timestamp)[1]'', ''varchar(max)'') AS event_timestamp\r\n                            , T2.Loc.query(''.'').value(''(\/action\/@name)[1]'', ''varchar(max)'')AS att_name\r\n                            , T2.Loc.query(''.'').value(''(\/action\/value)[1]'', ''varchar(max)'')AS att_value\r\n\t\t\t\t\t\tFROM   #ExEvent\r\n\t\t\t\t\t\t\tCROSS APPLY event_data.nodes(''\/event'') as T1(Loc) \r\n\t\t\t\t\t\t\tCROSS APPLY event_data.nodes(''\/event\/action'') as T2(Loc) \r\n\t\t\t\t\t\tWHERE T2.Loc.query(''.'').value(''(\/action\/@name)[1]'', ''varchar(max)'') \r\n\t\t\t\t\t\t\tIN (' + @actionsListFilter  + N')\r\n                    ) AS SourceTable\r\n                        PIVOT(\r\n                            MAX(att_value)\r\n                            FOR att_name IN (' + @actionsList + N')\r\n                    ) AS PivotTable\r\n            ) AS t_action\r\n           \r\n        -- Full outer because it might be no events selected only the payload\r\n        FULL OUTER JOIN (\r\n            SELECT RowId, event_name, event_timestamp, ' + @eventsList + N'\r\n                FROM (\r\n                    SELECT RowId\r\n                            , event_name\r\n                            , T1.Loc.query(''.'').value(''(\/event\/@timestamp)[1]'', ''varchar(max)'') AS event_timestamp\r\n                            , T3.Loc.query(''.'').value(''(\/data\/@name)[1]'', ''varchar(max)'') AS att_name\r\n                            , CASE \r\n\t\t\t\t\t\t\t\t\t-- deadlock report in xml to allow it to be saved as xdl\r\n\t\t\t\t\t\t\t\t\tWHEN event_name = ''xml_deadlock_report'' \r\n\t\t\t\t\t\t\t\t\t\tTHEN CONVERT(NVARCHAR(MAX), event_data.query(''\/event\/data\/value\/deadlock'')) \r\n\t\t\t\t\t\t\t\t\t-- rest of events that return xml\r\n\t\t\t\t\t\t\t\t\tWHEN event_name IN (SELECT object_name FROM sys.dm_xe_object_columns\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE type_name = ''xml'' AND name NOT IN (''xml_deadlock_report''))\r\n\t\t\t\t\t\t\t\t\t\tTHEN CONVERT(NVARCHAR(MAX), event_data.query(''\/event'')) \r\n\t\t\t\t\t\t\t\t\t-- rest of events\r\n\t\t\t\t\t\t\t\t\tELSE T3.Loc.query(''.'').value(''(\/data\/value)[1]'', ''varchar(max)'') \r\n\t\t\t\t\t\t\t\tEND AS att_value\r\n                        FROM   #ExEvent\r\n\t\t\t\t\t\t\tCROSS APPLY event_data.nodes(''\/event'') as T1(Loc) \r\n\t\t\t\t\t\t\tCROSS APPLY event_data.nodes(''\/event\/data'') as T3(Loc) \r\n                        WHERE T3.Loc.query(''.'').value(''(\/data\/@name)[1]'', ''varchar(max)'') \r\n                            IN (' + @eventsListFilter  + N')\r\n                        ) AS SourceTable\r\n                            PIVOT (\r\n                            MAX(att_value)\r\n                                FOR att_name IN (' + @eventsList + N')\r\n                        ) AS PivotTable\r\n            ) AS t_data\r\n            ON t_data.RowId = t_action.RowId\r\n'\r\n   \r\nIF @debugging = 1 \r\n    SELECT CONVERT(XML, @sql)\r\nELSE\r\n    EXECUTE sp_executesql @sql\r\nGO\r\n<\/pre>\n
And that was all folks! Hope you find it useful and any feedback is always welcome.<\/p>\n","protected":false},"excerpt":{"rendered":"
Getting useful information from Extended Event files is sometimes very tedious as you have to use XML queries and even PIVOT, what can be a better plan? It’s been some time since I…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,19],"tags":[39,16,5],"_links":{"self":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/233"}],"collection":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/comments?post=233"}],"version-history":[{"count":1,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/233\/revisions"}],"predecessor-version":[{"id":438,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/233\/revisions\/438"}],"wp:attachment":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/media?parent=233"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/categories?post=233"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/tags?post=233"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}