Reading Extended Event File Session Data

Jason Brimhall, 2015-06-21

Using Extended Events to trap/trace information allows the trapping of that information to various targets. One of the targets I will frequently tell people to use is the file target. The reasoning for this is the opportunity this affords to be able to review the output  at a later time whether the event session is running or not.

Along  with that recommendation, I also show some quick code to pull the data back from the session target (the file on disk). This code is written to dynamically pull the file information out of the XE Dynamic Management Views to make it a little easier on the user (and a bit less likely to have a typo). That code looks a bit like the following:

SELECT
    event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    CAST(event_data.value('(event/action[@name="plan_handle"]/value)[1]', 'varchar(max)') AS XML) as plan_handle,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'varchar(max)') AS sql_text
	,xmlplan.query('.') AS plan_xml
 	FROM ( SELECT CONVERT(XML, t2.event_data) AS event_data--,xmlplan.query('.') AS event_plan
				FROM ( SELECT target_data = CONVERT(XML, target_data)
							FROM sys.dm_xe_session_targets t
								INNER JOIN sys.dm_xe_sessions s
									ON t.event_session_address = s.address
							WHERE t.target_name = 'event_file'
								AND s.name = 'TrapEstExecPlans'
						) cte1
					CROSS APPLY cte1.target_data.nodes('//EventFileTarget/File') FileEvent ( FileTarget )
					CROSS APPLY sys.fn_xe_file_target_read_file(FileEvent.FileTarget.value('@name',
																'varchar(1000)'),
																NULL, NULL, NULL) t2
			) AS evts ( event_data )
			CROSS APPLY event_data.nodes('(event/data[@name="showplan_xml"]/value)[last()]/*') AS showplan(xmlplan);

This works really well when the session is running. It pulls the desired file path and name back from the DMVs and one is able to parse the session data. But what if that session is stopped?

Well, if the session is stopped, we have a problem. With a stopped session, the session above will not produce any results. The short of this is that SQL Server removes the entries from the sys.dm_xe_session_targets and sys.dm_xe_sessions DMVs. Due to that, there is a bit of a tweak to be able to query those files for the sessions that have been stopped.

To query those files, one could rewrite the previous query to something like this:

SELECT evts.*, xmlplan.query('.') AS plan_xml 
INTO #parseevent
FROM (
SELECT CONVERT(XML, t2.event_data) AS event_data
				FROM sys.fn_xe_file_target_read_file('C:\Database\XE\TrapEstExecPlans*.xel',
																NULL, NULL, NULL) t2
			) AS evts ( event_data )
			CROSS APPLY event_data.nodes('(event/data[@name="showplan_xml"]/value)[last()]/*') AS showplan(xmlplan)
SELECT
    event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    CAST(event_data.value('(event/action[@name="plan_handle"]/value)[1]', 'varchar(max)') AS XML) as plan_handle,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'varchar(max)') AS sql_text,
	CAST(pe.event_data.value('(event/action[@name="query_hash"]/value)[1]', 'varchar(max)') AS XML) AS statementlevel_query_hash 
	,pe.plan_xml AS plan_xml
 	FROM #parseevent pe;
DROP TABLE #parseevent;

Now, I bet you may be asking why stop an Extended Event session from running. Afterall, extended events is extremely light weight and has little impact on the server, right? True that may be, there is still a cost for traces. If you can just run a trace for a targeted time period, then why not limit the amount of tracing?

Looking at the code, you can see that I reverted to a less dynamic approach to read those event files. Instead of trying to get that info direct from the database, I am just telling SQL Server where to find the files on disk (note the filepath that is passed).

There you have it. A means to query these trace files whether the XE trace is running or stopped.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads