By: Aaron Bertrand | Updated: 2015-05-21 | Comments (3) | Related: > Extended Events
Problem
In my last tip, I encouraged people to convert existing server-side traces to Extended Events. I admitted in that tip that, since data from Extended Events is returned as XML, querying the data can be a lot more cumbersome than existing methods to query trace data. I wanted to provide a little guide that you might find useful as a follow-up.
Solution
For now, we will just look at the default system_health session,
which records a variety of events on the system, including any events with a severity
>= 20 (the
full list for 2008 is available here; the list has expanded since then
as you can see in the documentation).
To generate a bit of data to look at, run the following code to raise a severity
20 error (you'll need to be a member of the sysadmin server role):
RAISERROR(N'Something to talk about.', 20, 1) WITH LOG;
So now, we can go look of this event in our Extended Events data. I always recommend to just dump the XML data into a #temporary table first, so that you aren't performing expensive processing against XML data as you're converting it. On busy systems, you will almost certainly notice a substantial difference in performance.
On SQL Server 2012 and up, that will look like this:
SELECT event_data = CONVERT(XML, event_data) INTO #t FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL);
Arguably, you could filter the data first, but that would be kind of chicken and egg for my intentions here. I'd like to explain how to query and filter against the XML before I give you the code to do so. :-)
In SQL Server 2008 and 2008 R2, there was no event file for the default
system_health session, so instead you needed to rely on slightly more verbose
code, pulling the data from the ring buffer target (and in this case I will
have to do a little bit of querying against XML in order to pull the data in the
same format as above):
;WITH cte AS
(
SELECT ed = CONVERT(XML, target_data)
FROM sys.dm_xe_session_targets xet
INNER JOIN sys.dm_xe_sessions xe
ON xe.[address] = xet.event_session_address
WHERE xe.name = N'system_health'
AND xet.target_name = N'ring_buffer'
)
SELECT event_data = x.ed.query('.')
INTO #t
FROM cte
CROSS APPLY cte.ed.nodes(N'RingBufferTarget/event') AS x(ed);
Now we have a new table, #t, which has a single column, event_data,
which represents each individual event in XML format.
Note that the ring buffer may not maintain as much history as event files, so you won't necessarily see the same amount of data if you use these two methods on the same system. Jonathan Kehayias has described why you want to avoid the ring buffer target when you can.
In any case, from this point on, nothing should be different whether you initially derived this data from SQL Server 2008, 2008 R2, 2012, or higher, or if the same events ultimately came from the event file or the ring buffer.
Now that you have the XML data in your #t temporary table, you can query it to see what it looks like. Make sure you have SSMS set to "Results to Grid":
SELECT event_data FROM #t;
This may return a lot of data, but ultimately you are looking for something like this in the results:
In Management Studio, you can click on that hyperlink, and it will open a new window full of XML. The abbreviated output in my case (I've left out much of the uninteresting parts of the XML for brevity):
<event name="error_reported" package="sqlserver" timestamp="2015-04-30T18:01:09.143Z">
<data name="error_number">
<value>50000</value>
</data>
<data name="severity">
<value>20</value>
</data>
<data name="message">
<value>Something to talk about.</value>
</data>
<action name="session_id" package="sqlserver">
<value>53</value>
</action>
<action name="sql_text" package="sqlserver">
<value>RAISERROR(N'Something to talk about.',20,1) WITH LOG;</value>
</action>
</event>
In most cases, the pieces of data we're going to want to pull out (or filter
on) are going to be the names of the event/data/action, the timestamp of the event,
and the values of the data/action. Some of this information comes from attributes
(e.g. name="error_reported") and others come from node values (e.g.
<value>53</value>).
A basic differentiation between data and action
is that data is natively collected with the event in all cases (e.g. the error number
in this case), while an action is essentially what caused the event to happen (and
the information collected there must be retrieved extraneously from the native event,
for example the sql_text can only be retrieved when it is explicitly
requested through an action).
Common events are error_reported, xml_deadlock_report, page_split, and wait_info. Common data values you will collect, while they will vary based on the event specifics, include error_number, duration, and wait_type. Common actions are sql_text, session_id, and database_id.
For more information about Extended Events concepts, please start with Jonathan Kehayias' excellent series, "An XEvent A Day."
Let's take an even simpler representation of the XML we have, and show how to pull that information out:
The important things to call out here are the differences between querying attributes
of the top-level node (<event name="foo" -> event/@name),
and values inside child nodes (<event><data name="message"><value>
-> event/data[@name="message"]/value). The [1] denotes
a singleton - it means take the first element (in some cases you may have multiple
values represented at a specific node, but for everything here there is only one
value).
So expanding on that, we can go back to our original query against #t
and enhance it. Rather than just pulling the XML, we can pull all of the individual
pieces of information we're interested in, and filter on only the rows we're interested
in. Obviously for this exercise we want to focus on only the error_reported
events, and let's say we only care about severity 20 errors specifically. We can
run a query like this:
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)'), spid = event_data.value(N'(event/action[@name="session_id"]/value)[1]', N'int') FROM #t WHERE event_data.value(N'(event/@name)[1]', N'sysname') = N'error_reported' AND event_data.value(N'(event/data[@name="severity"]/value)[1]', N'int') = 20;
It looks rather messy, but once you get the hang of it and have a few sample queries to work from, it should become quite a bit less painful. While it will likely never become as easy as querying trace data, since trace has been deprecated, at some point you simply won't have a choice.
I hope that is a useful starting point to querying against the XML data from Extended Events. There are some more elaborate things you can do, for sure, which I'll dig into in a future tip. In the meantime, Robert Sheldon has laid out a worthwhile primer.
Next Steps
- Review the following tips and other resources:
About the author
Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2015-05-21
