0

I have a small xEvent that is sending the results as .xel to an event_file in G:\XEvents and I want to explore the data with SSMS. I have been looking for a solution and found several posts about reading the XML created by ring buffers. But I can't get it to work on my event_file.

I read Jes Schultz Borland's post How to Query Extended Events Target XML but It is not working for me. I have not done anything significant with XML, and when I open the event_file with word, it does not look like XML.

I can display it by using SSMS; > Instance > Management > Extended Events > Sessions > [MyEvent] > Package > View Target data, but I can't really query it.

0

You are working under a false assumption, event_files do not store the data as XML, that is what ring_buffer does.

event_file: Data from an event_file target is displayed very well, with rich features available.

ring_buffer: Data from a ring-buffer target is displayed as raw XML.

There are a few options but for me the simplest is to put the data in a SQL database.

Once you have the data displayed in SSMS: Instance > Management > Extended Events > Sessions > [MyEvent] > Package > View Target data

Use the new toolbar menu item 'extended events' and export to a table in SQL database.

The database needs to exist when you export but the table does not. I used a database named xEvent on a test/sandbox instance.

From the Toolbar: Extended Events > Export to > Table.. > (Connect to 'test/sandbox instance' > Existing database > (Type in any name you like)

Use standard SQL to query.

ToolBar

Reference Advanced Viewing of Target Data from Extended Events in SQL Serve

0

You don't access that file directly. Rather, you use the fn_xe_file_target_read_file(9 function to let SQL Server access the file. That function returns one row per even wit h the data (fields, if you want) packaged as XML. To shred that XML, you use the built-in xquery support in SQL Server. Here's an example:

SELECT CAST(event_Data AS xml) AS event_data FROM sys.fn_xe_file_target_read_file('R:\SqlStatements*.xel', NULL, NULL, NULL)

DROP TABLE IF EXISTS #myXeData

SELECT CAST(event_Data AS XML) AS StatementData
INTO #myXeData
FROM sys.fn_xe_file_target_read_file('R:\SqlStatements*.xel', NULL, NULL, NULL)

SELECT * FROM #myXeData

SELECT 
 StatementData.value('(event/@name)[1]', 'varchar(50)') AS event_name
,StatementData.value('(event/@timestamp)[1]', 'datetime2(0)') AS time_stamp
,StatementData.value('(event/action[@name="database_name"]/value)[1]','nvarchar(128)') AS database_name_
,StatementData.value('(event/data[@name="duration"]/value)[1]','bigint') / 1000 AS duration_ms
,StatementData.value('(event/data[@name="cpu_time"]/value)[1]','bigint') /1000 AS cpu_ms
,StatementData.value('(event/data[@name="physical_reads"]/value)[1]','bigint') AS physical_reads
,StatementData.value('(event/data[@name="logical_reads"]/value)[1]','bigint') AS logical_reads
,StatementData.value('(event/data[@name="row_count"]/value)[1]','bigint') AS row_count
,StatementData.value('(event/data[@name="statement"]/value)[1]','nvarchar(50)') AS statement_
FROM #myXeData AS evts
--ORDER BY time_stamp
ORDER BY duration_ms DESC
  • I don't understand how this is helpful. I can use 'View Target data' to get one row per event. Out of the box, works fine unless I want to query it. And I don't necessarily want to put the data in XML if I don't have to. – James Jenkins Jan 10 at 12:15
  • When you say "query it", I assumed you want to see the data in a relational format, with the XML elements/attributes extracted to values in columns. So you then can query it using regular T-SQL without messing about with the XQuery stuff. I.e., the final SELECT in my example can just include an INTO so you get that result into a table and then just query that table. The data is delivered as XML when you work with XEvents, so you need to transform that XML to "table" format, which is what my code does. If I misunderstood your requirements, then just ignore my reply. – Tibor Karaszi Jan 11 at 13:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.