Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Star 4 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save brazilnut2000/f9e9859f095219b1a17d151292efc278 to your computer and use it in GitHub Desktop.
Save brazilnut2000/f9e9859f095219b1a17d151292efc278 to your computer and use it in GitHub Desktop.
Convert an Extended Events .xel file into a queryable table in SQL Server
-- convert all .xel files in a given folder to a single-column table
-- (alternatively specify an individual file explicitly)
select event_data = convert(xml, event_data)
into #eeTable
from sys.fn_xe_file_target_read_file(N'd:\killme\extended events\*.xel', null, null, null);
-- select from the table
select * from #eeTable
-- and click on a hyperlink value to see the structure of the xml
-- create multi-column table from single-column table, explicitly adding needed columns from xml
SELECT
ts = event_data.value(N'(event/@timestamp)[1]', N'datetime'),
[sql] = event_data.value(N'(event/action[@name="sql_text"]/value)[1]', N'nvarchar(max)'),
cpu = event_data.value(N'(event/data[@name="cpu_time"]/value)[1]', N'nvarchar(max)'),
duration = event_data.value(N'(event/data[@name="duration"]/value)[1]', N'nvarchar(max)'),
result = event_data.value(N'(event/action[@name="result"]/value)[1]', N'int'),
row_count = event_data.value(N'(event/data[@name="row_count"]/value)[1]', N'nvarchar(max)'),
spid = event_data.value(N'(event/action[@name="session_id"]/value)[1]', N'int'),
physical_reads = event_data.value(N'(event/action[@name="physical_reads"]/value)[1]', N'int'),
logical_reads = event_data.value(N'(event/action[@name="logical_reads"]/value)[1]', N'int'),
writes = event_data.value(N'(event/action[@name="writes"]/value)[1]', N'int'),
user_nm = event_data.value(N'(event/action[@name="username"]/value)[1]', N'nvarchar(max)')
into PexTrace_20170526
FROM #eeTable
-- add id
ALTER TABLE PexTrace_20170526
ADD eventId INT IDENTITY;
-- set id as PK
ALTER TABLE PexTrace_20170526
ADD CONSTRAINT PK_PexTrace_20170526 PRIMARY KEY(eventId);
-- now query all you like
SELECT *
FROM PexTrace_20170526
where sql like '%springer%'
order by ts
@tahseen631
Copy link

Hi,
While running the below statement :
select event_data = convert(xml, event_data) into eeTable from sys.fn_xe_file_target_read_file(N'C:\Users\user.name\Desktop*.xel', null, null, null);

I am getting the below error:
A valid URL beginning with 'https://' is required as value for any filepath specified.

Can you let me know the solution.

@djpirra
Copy link

djpirra commented Nov 2, 2020

Also this does not work if the Extended Events are running and storing data in an Analysis Services

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment