Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Query Data from Extended Events in SQL Server


By:   |   Updated: 2015-05-21   |   Comments (1)   |   Related: More > 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:

Clickable XML in SSMS grid 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:

Query against sample XML

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


Last Updated: 2015-05-21


get scripts

next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a Product Manager at SentryOne, 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 serves as a community moderator for the Database Administrators Stack Exchange.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, May 21, 2015 - 2:24:22 PM - Greg Robidoux Back To Top

Aaron, congrats on your 75th tip.

-Greg


Learn more about SQL Server tools