mssqltips logo

View SQL Server Extended Events Data



By:
Overview

There are many different methods you can use for viewing the data collected by an extended events session.  As with most things using the SSMS GUI is the easiest way to view data.  Even within the GUI there are multiple ways to view the data. 

View Extended Events Data Using Watch Live Data

The first method we will look at is using the "Watch Live Data" menu item.  This can be found by expanding the "Management" node with Object Explorer.  You then expand "Extended Events" followed by "Sessions" which will give you a list of your sessions.  After right-clicking on the session you want to view you can then select "Watch Live Data" as shown below.

extended events captured data using watch live data

After selecting this the following tab will open which will display your event session data as it is collected.

extended events captured data using watch live data

This method will only display new data as it is collected.

View Extended Events Data Using View Target Data

If you want to view previously collected data you can also do this by using the "View Target Data" menu item.  For this item you follow the same steps as above but you'll also need expand the actual session you are interested in viewing and then right-click on the target type you wish to view as shown below.

extended events captured data using view target data

Once selected the following tab will open.  Depending on the target type this tab will display differently.  For the event_file type it will look just as it did when we selected "Watch Live Data".  For the ring buffer target we selected above we get the following output.

extended events captured data using view target data

We can click on the column data returned in the output window and it will display the formatted XML output in a new tab as shown below.

extended events captured data xml view

View Extended Events Data Using XEvent Profiler

Finally, starting with SSMS ver17.3, we can use the XEvent Profiler.  This tool is very similar to SQL Profiler in that it gives you a live streaming view of events you have configured.  The difference with this tool though is that it is integrated directly into SSMS (not a separate tool like SQL Profiler was) and is built on top of the Extended Events framework. 

To start the XEvent Profiler we can open SSMS and under the main instance tree in Object Explorer we can expand the "XEvent Profiler" menu item and we should see two sessions as shown below.  "Standard" will show all extended events and "TSQL" will show all logged SQL statements.  You can launch a session by right-clicking one of these and then select "Launch Session" as shown below.

extended events captured data using xevent profiler

Once launched the following window will open which as you can see looks very much like the old SQL Profiler output window.

extended events captured data using xevent profiler

Also note that this output window is configurable.  If you right-click on the column headings you can add/remove columns or search a column.

extended events captured data using xevent profiler

Also, if you right-click on a column value you can setup filters on the output.

extended events captured data using xevent profiler

View Extended Events Data Using T-SQL

We can also use TSQL to pull event data.  The extended events DMVs contain the event data for most of the target types.  All types except the event file type (in which case the file name is in the target_data column XML) and the ETW type have their data in the target_data column of the sys.dm_xe_session_targets view.  We can join this view with the sys.dm_xe_sessions to get the event data for each target of an extended events session. 

The below example shows how we can query this data from the system_health session.

SELECT s.name, st.target_name,st.target_data
FROM sys.dm_xe_sessions s
  INNER JOIN sys.dm_xe_session_targets st ON s.address = st.event_session_address
WHERE s.NAME = 'system_health';
extended events captured data

Although it's not in the sys.dm_xe_session_targets view, if you want to get the event file target data using TSQL you can use the sys.fn_xe_file_target_read_file function to read the files.  The following example shows how we can use this function to read the system_health event session file data.

SELECT object_name,event_data
FROM sys.fn_xe_file_target_read_file('system_health*.xel',null,null,null);
extended events captured data
Additional Information

Last Update: 6/14/2019




More SQL Server Solutions











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.






download





get free sql tips

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.



Learn more about SQL Server tools