Enough talk of the GUI with Extended Events (see previous articles in the series if you want to revisit the stickiness of the GUI – here). It is time for a bit of a diversion and something quick.

While it may be a quick traipse today, it won’t be the last article on the subject. It is merely an interesting bird walk into a corner of the Extended Events garden that is seldom visited. Today I will explore the little function called sys.fn_MSxe_read_event_stream.

This function, as the name implies, is used to read the event stream and is most commonly seen when watching the live stream of events for a session. I will show a little bit of that.

Gradually Descending into the Stream

First, let’s set the stage so you can confirm that I am not entirely off my rocker here.

By selecting “Watch Live Data” from the context menu after right clicking on the “system_health” session, I will be given a window just like the window discussed in the past few articles when discussing the GUI. After launching that window, a quick query to your favorite requests and sessions DMVs will reveal a little bit about this function that we are diving into today.

If I click on the query text in that previous result set, I can see the following to be the query that is executing for the “live data” stream.

(@source nvarchar(256),@sourceopt int)
SELECT type, data 
FROM sys.fn_MSxe_read_event_stream (@source, @sourceopt)

Cleaning it up a bit and giving some useful values to the parameters, I might have something like this:

/* read from livestream */DECLARE @source NVARCHAR(256) = 'system_health'
, @sourceopt INT = 0;
SELECT type
, data
FROM sys.fn_MSxe_read_event_stream(@source, @sourceopt);

Running that particular query from a management studio window would be rather pointless. It doesn’t ever return unless you cancel the query. The key to this one though is the second parameter. The second parameter tells us what kind of source we want to use for the stream of data. There are two values (that I have been able to find) that can be used: 0 and 1. A value of 0 pulls from the live stream target. A value of 1 pulls from the file target. If using a value of 1, then the first parameter needs to be adjusted accordingly as well. If the two parameters do not match, then an error is thrown.

/* read from file */DECLARE @source NVARCHAR(256) = 'system_health_*.xel'
, @sourceopt INT = 1;
SELECT type
, data
FROM sys.fn_MSxe_read_event_stream(@source, @sourceopt)
;
GO

As it happens, I have several log files in the default directory for the system_health and the HKEngine sessions that are deployed and running on my instance of SQL Server. Here is a sample of those files:

So far so good, right? Let’s go ahead and execute that second query (that reads from the file target) and see what happens:

This to me is far from useful as of yet. But there are a couple of rabbit holes to dig into from here. The first being the different types that we can see here. Let’s refashion the query to restrict the types being returned and see what happens:

First, in blue we see that type 2 is exactly the same every single time. In my environment I have exactly four of that type. If I look on the file system, I can see that I have four files as well. This seems to indicate (not yet validated in my plucking at it) that this represents a file. Then type 1 is identical up until the point where I have it highlighted in orange. Just an interesting side note there is all.

If I perform the same thing for the HKEngine session, I see something like the following:

Notice the difference here? Now I have two type 1 entries for each file that has been created. In addition, one of the type 1 entries is exactly the same for all files created.

But without being able to translate the data returned, this is really just a fun exercise into the live stream. That said, I give you one last interesting tidbit for today while leaving the second rabbit hole (translating the data into human readable text) for another time (besides just using the event file function to read it).

DBCC OUTPUTBUFFER(53)

Recall from an earlier result that my spid for the live data viewer was spid 53. If run an output buffer for that spid, I will see something like the following:

Not the friendliest of outputs to peruse and try to figure out, but it does give you a sense of what is going on there. If you know how to convert the data output from sys.fn_MSxe_read_event_stream, I would be interested in seeing what you do for it.

This has been another article in the 60 Days of XE series. If you have missed any of the articles, or just want a refresher, check out the TOC.