Reading Session Data from Memory

In the last few articles I took you through a progression of working with the event_file target from the most basic concepts on through the more advanced. While working with the event_file target, I showed that XML is central to working with the session data for that target type. XML is not just a fundamental component of the event_file target, I have also shown that it is ingrained throughout Extended Events. That indoctrination includes the topic of this article – the ring_buffer target.

I took you on a bit of a journey while discussing the event_file target. The journey for the ring_buffer is going to be much shorter. A big part of the reason is that I have already laid a big part of the foundation due to the similarities with the event_file target. This is just another step in the progression and continues to build on principles already shown.

Before diving in to the session data, a quick glance at what the ring_buffer is would be helpful. The ring_buffer is a memory target. In addition, as the name implies, the ring_buffer can be overwritten due to the nature of the ring. Once the buffer fills, then the new events will have to go somewhere, and that means something gets purged or overwritten.

The next important note is that it is a memory target and that means it is volatile. If the session is not running, well then the target won’t be there. This means that the only way to read the data in the target is to parse it while the session is running. In addition, since it is volatile, if the server is bounced then the data in the target is purged.

These are also a few of the reasons contributing to a shorter journey about this particular target. When working with this type of target, great care needs to be taken to ensure the session data is scraped and that the target is configured with attention to detail.

Reading Session Data from Memory

Since the target is only available while the session is running, I will only be focusing on the DMVs where the metadata for running sessions resides. In this case, the key DMV will be sys.dm_xe_session_targets. From there, I can run a very similar query as was done for the event_file, with the exception that I do not need to rely on a function to get the session data.

Unlike the event_file target where a bit of XML held the information for the file name and path is exposed via the target_data column, the entirety of the session data will be exposed for the ring_buffer via that column. This means I can use a query such as the following to retrieve all of the session data.

SELECT CAST ([target_data] AS XML) AS Target_Data
FROM sys.dm_xe_session_targets AS xt
INNER JOIN sys.dm_xe_sessions AS xs
ON xs.address = xt.event_session_address
WHERE xs.name = N'demosession'
AND xt.target_name = N'ring_buffer';

And now, much in the same fashion as the event_file target, I can throw that into a query to parse the session data into an easier to read format.

SELECT CAST ([target_data] AS XML) AS target_data
INTO #xmlpreprocess
FROM sys.dm_xe_session_targets AS xt
INNER JOIN sys.dm_xe_sessions AS xs
ON xs.address = xt.event_session_address
WHERE xs.name = N'demosession'
AND xt.target_name = N'ring_buffer'
;
SELECT event_data.value('(@name)[1]', 'varchar(50)') AS event_name
, event_data.value('(@timestamp)[1]', 'varchar(50)') AS [TIMESTAMP]
,event_data.value('(data[@name="collect_database_name"]/value)[1]','bit') AS collect_database_name
,event_data.value('(data[@name="database_id"]/value)[1]','int') AS database_id
,event_data.value('(data[@name="object_id"]/value)[1]','int') AS object_id
,event_data.value('(data[@name="index_id"]/value)[1]','int') AS index_id
,event_data.value('(data[@name="job_id"]/value)[1]','int') AS job_id
,event_data.value('(data[@name="job_type"]/value)[1]','varchar(max)') AS job_type
,event_data.value('(data[@name="status"]/value)[1]','varchar(max)') AS status
,event_data.value('(data[@name="duration"]/value)[1]','int') AS duration
,event_data.value('(data[@name="retries"]/value)[1]','int') AS retries
,event_data.value('(data[@name="success"]/value)[1]','bit') AS success
,event_data.value('(data[@name="last_error"]/value)[1]','int') AS last_error
,event_data.value('(data[@name="count"]/value)[1]','int') AS EventCount
,event_data.value('(data[@name="statistics_list"]/value)[1]','varchar(max)') AS statistics_list
,event_data.value('(data[@name="database_name"]/value)[1]','varchar(max)') AS database_name
FROM #xmlpreprocess AS evts
CROSS APPLY target_data.nodes('//RingBufferTarget/event') AS XEventData(event_data)
ORDER BY [TIMESTAMP];
DROP TABLE #xmlpreprocess;

As you can see, I went straight to the more optimal means of parsing the data. I chose to dump the session data into a temp table first and then from there I can more efficiently parse the XML into a more friendly format. Notice that I did need to use one more trick to parse the XML. I implemented the APPLY operator in order to break down  the session data into the various event nodes.

I want to return to the behavior of the ring_buffer in order to help underscore the potential for missing any events that may have been trapped.

I mentioned that the target is volatile due to the nature of it being a memory target. What I did not mention was that unless there is a consumer to fetch the events from the session, it is highly probable that the events will be missed.

This goes hand in hand with the circular nature and volatility of the target. In order to effectively use the target, you have to watch it and retrieve the events from it on a regular basis. Otherwise, it would be as if the events were never recorded.

I have shown in this article how to read session data from memory. I have also pointed out some of the less risky pitfalls of this particular target. Similar to the event_file, one will need to become familiar with how to parse the data with XML.

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.