Yesterday’s post, Querying the Session Definition and Active Session DMV’s, showed how to find information about the Event Sessions that exist inside a SQL Server and how to find information about the Active Event Sessions that are running inside a SQL Server using the Session Definition and Active Session DMV’s.  With the background information now out of the way, and since this post falls on the start of a new week I’ve decided to make this Targets Week, where each day we’ll look at a different target in Extended Events starting with the ring_buffer today.

What is the ring_buffer?

The ring_buffer is one of two targets available in Extended Events that captures event data in its raw format.  The ring_buffer is a memory resident target that holds event data in a single XML document while the Event Session is active on the SQL Server.  When the Event Session is stopped, the memory buffers allocated to the ring_buffer target are freed and all data contained in the target disappears.  The ring_buffer collects events in a First In First Out (FIFO) manner that can be configured to be strict, where the oldest event is removed when the memory allocated to the target become full and new events arrive, or per event, allowing you to specify the maximum number of occurrences that will be retained for each event defined in the event session.  The default configuration for event flushing is strict FIFO.

Configuration Options

The ring_buffer like most of the targets has configuration options that can be found in the sys.dm_xe_object_columns DMV.

-- Target Configurable Fields
SELECT 
    oc.name AS column_name,
    oc.column_id,
    oc.type_name,
    oc.capabilities_desc,
    oc.description
FROM sys.dm_xe_packages AS p
INNER JOIN sys.dm_xe_objects AS o 
    ON p.guid = o.package_guid
INNER JOIN sys.dm_xe_object_columns AS oc 
    ON o.name = oc.OBJECT_NAME 
    AND o.package_guid = oc.object_package_guid
WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
  AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
  AND o.object_type = N'target'
  AND o.name = N'ring_buffer';

In SQL Server 2008, 2008R2, and SQL Server Denali CTP1, the configuration options for the ring buffer are the max_memory option, which sets the maximum amount of memory to be used by the target buffers to hold Event data, and the occurrence_number, which is used to configure the FIFO operation of the target per Event, specifying the number of events by type that the target will retain.

Extended Events Series (5 of 31)   Targets Week   ring buffer   image thumb

Both of the configurable options for the ring_buffer target are optional, and when they are not specified as a part of the Event Session definition, they take on their default values.  As previously stated, the default configuration for the ring_buffer is to use strict FIFO if no value is specified for the occurrence_number in the Event Session.  The default max_memory value is 4MB for the ring_buffer, which is covered in slightly more detail in the Considerations for Usage section of this post.

Understanding the Target Data Format

As previously mentioned, the ring_buffer stores Event data in its raw format.  Inside the Extended Events Engine, the Event data is maintained in a binary format that minimizes the amount of memory necessary to store the Events, maximizing the number of Events that can be stored inside the Targets memory buffers.  The Event data is materialized into an XML document when the Target information is queried using the sys.dm_xe_session_targets DMV, allowing it to be used for Event analysis.  The ring_buffer XML document contains a parent XML <RingBufferTarget> Node that contains attributes about the Targets operation since the Event Session was started including the number of Events processed per second, the amount of time the Target has spent processing Events, the total number of Events that have been processed by the Target, the current number of Events in the target, the number of Events dropped due to full buffers, and the amount of memory used by the Target.

<RingBufferTarget eventsPerSec="" processingTime="" totalEventsProcessed="" eventCount="" droppedCount="" memoryUsed="" /> 

Inside of the <RingBufferTarget> parent node, are the Event data XML nodes which contain the information returned by the Events defined in the Event Session.  While the XML returned for the Event data does not conform to any published XML Schema, it does have a predictable format based on the Event Session definition.  The root <event> node contains attributes for the Event name, the Package that loaded the Event metadata and that fired the Event for the Event Session, an id associated with the Event, a version associated with the Event, and the timestamp for the date and time in GMT that the Event fired on the server.  Each <event> node will have one or more <data> nodes that contain the information for each of the Event Data Elements returned by the Events default payload.  If Actions have been defined for the Event in the Event Session the <event> node will have an <action> node for each of the Actions that were added to the Event.

The <data> nodes and <action> nodes share a common XML schema, with one exception.  These <data> nodes contain a single attribute containing the name of the Data Element contained by that node, whereas the <action> nodes contain two attributes; one containing the name of the Action contained by that node, and the other the Package for the Action.  Each <data> or <action> node will have a <type> node that contains two attributes; the name of the Type for data type of the value being returned by the parent node, and the Package for the Type.  The <data> or <action> node will also have two additional nodes; a <value> node which contains the value for the data being returned in the Data Element, and a <text> node which will contain the Map lookup text for Data Elements that correspond to Maps in the Extended Events Metadata.  The basic XML definition of an <event> node would be:

<event name="" package="" id="" version="" timestamp="">
  <data name="">
    <type name="" package="" />
    <value />
    <text />
  </data>
  <action name="" package="">
    <type name="" package="" />
    <value />
    <text />
  </action>
</event>

Below is an example Event from the default system_health Event Session that is running on every installation of SQL Server 2008, and SQL Server Denali CTP1 for the wait_info Event.

<event name="wait_info" package="sqlos" id="48" version="1" timestamp="2010-12-03T15:29:00.578Z">
  <data name="wait_type">
    <type name="wait_types" package="sqlos" />
    <value>98</value>
    <text>ASYNC_IO_COMPLETION</text>
  </data>
  <data name="opcode">
    <type name="event_opcode" package="sqlos" />
    <value>1</value>
    <text>End</text>
  </data>
  <data name="duration">
    <type name="uint64" package="package0" />
    <value>44598</value>
    <text />
  </data>
  <data name="max_duration">
    <type name="uint64" package="package0" />
    <value>44598</value>
    <text />
  </data>
  <data name="total_duration">
    <type name="uint64" package="package0" />
    <value>44598</value>
    <text />
  </data>
  <data name="signal_duration">
    <type name="uint64" package="package0" />
    <value>0</value>
    <text />
  </data>
  <data name="completed_count">
    <type name="uint64" package="package0" />
    <value>1</value>
    <text />
  </data>
  <action name="callstack" package="package0">
    <type name="callstack" package="package0" />
    <value>0x0000000001829555
0x0000000000CEA584
0x000000000233FA28
0x0000000002E2FA0C
0x0000000002F10CB9
0x0000000002F112D7
0x0000000002F1B90B
0x0000000002D8C59A
0x0000000000B0F6D2
0x000000000065C59B
0x000000000065C25A
0x000000000065BF35
0x0000000000BE6410
0x0000000000BE64E0
0x0000000000BD87A0
0x0000000000BE5F9F</value>
    <text />
  </action>
  <action name="session_id" package="sqlserver">
    <type name="uint16" package="package0" />
    <value>87</value>
    <text />
  </action>
  <action name="sql_text" package="sqlserver">
    <type name="unicode_string" package="package0" />
    <value>Unable to retrieve SQL text</value>
    <text />
  </action>
</event>

In this Event, the wait_type and opcode Data Elements correspond to the Maps wait_types and opcode respectively, and the text value for the Map that corresponds to the <value> node is in the <text> node.  It is possible to lookup the Maps in the DMV’s separately though not necessary in this example as follows:

SELECT 
    p.name AS package_name,
    mv.name AS map_name,
    mv.map_key,
    mv.map_value
FROM sys.dm_xe_packages p
JOIN sys.dm_xe_map_values mv 
ON p.guid = mv.object_package_guid
WHERE (p.name = 'sqlos' AND mv.name = 'wait_types')
   OR (p.name = 'sqlos' AND mv.name = 'event_opcode') ;

Querying/Parsing the Target Data

Since the ring_buffer target returns the Event data as XML, obviously we are going to have to do a little bit of work to shred the XML into actionable data using XQuery.  For those new to XQuery, the best I can recommend is to jump over to my good friend Jacob Sebastian’s blog and work your way through his series of XQuery Labs, which have been an amazing resource along my way to learning XQuery.  If you are not interested in learning XQuery, but still want to work with Extended Events, all is not lost.  Adam Machanic wrote the Extended Events Code Generator, which you can use to generate a TSQL statement that will parse out the target data for the ring_buffer, as well as for tomorrows topic, the asynchronous_file_target.  It also includes a SQLCLR helper TVF that optimizes shredding the XML by leveraging the power of .NET that you can optionally deploy in your environment.  Another option is to use the Extended Events SSMS Addin for SQL Server 2008 which includes a TargetDataViewer that shreds the XML for every target available in Extended Events and displays the Event data in a SQL Profiler like GridView inside of SQL Server Management Studio.  Beyond these two tools, you can also use the code available in this blog series.

One of the nuances of working with XML inside of SQL Server, especially with Extended Events, is that sometimes it is better for performance to use a XML variable to hold the XML data for shredding, rather than attempting to shred the XML directly from the DMV’s.  I’ve never quite figured out why this is the case, but it tends to make a bigger impact on larger XML documents, specifically those in the 2MB+ size range.

Since every server running SQL Server 2008, 2008R2, or Denali CTP1 has the system_health session running by default in it, I am going to use that event session to demonstrate how to query the information from the ring_buffer target.  To get the Target data into an XML variable, we’ll need to query the target_data column of the sys.dm_xe_session_targets DMV and CAST the value returned to the XML data type.

DECLARE @target_data XML;
SELECT @target_data = CAST(target_data AS XML)
FROM sys.dm_xe_sessions AS s 
JOIN sys.dm_xe_session_targets AS t 
    ON t.event_session_address = s.address
WHERE s.name = N'system_health';

With that variable, we can then parse out the Targets header information from the <RingBufferTarget> node attributes:

SELECT 
    @target_data.value('(RingBufferTarget/@eventsPerSec)[1]', 'int') AS eventsPerSec,
    @target_data.value('(RingBufferTarget/@processingTime)[1]', 'int') AS processingTime,
    @target_data.value('(RingBufferTarget/@totalEventsProcessed)[1]', 'int') AS totalEventsProcessed,
    @target_data.value('(RingBufferTarget/@eventCount)[1]', 'int') AS eventCount,
    @target_data.value('(RingBufferTarget/@droppedCount)[1]', 'int') AS droppedCount,
    @target_data.value('(RingBufferTarget/@memoryUsed)[1]', 'int') AS memoryUsed;

We can also parse out the individual <event> data nodes from the Target data by using .nodes() method and specifying the XPath to the event nodes and then using .query() method to materialize each node returned by .nodes() as a separate XML document for output.

SELECT 
    n.query('.') AS event_data
FROM @target_data.nodes('RingBufferTarget/event') AS q(n);

We can also use an XPath filter in the .nodes() method to query specific Events only

(Note: if your server hasn’t encountered waits that exceed the predicates for the system_health session, this query will not return results).

SELECT 
    n.query('.') AS event_data
FROM @target_data.nodes('RingBufferTarget/event[@name=''wait_info'']') AS q(n);

Building on this, we can shred the <event> nodes for the wait_info Events based on the Event definition in the Event Session to turn the XML data into a tabular output that is easier to read.

SELECT 
    n.value('(@name)[1]', 'varchar(50)') AS event_name,
    n.value('(@package)[1]', 'varchar(50)') AS package_name,
    n.value('(@id)[1]', 'int') AS id,
    n.value('(@version)[1]', 'int') AS version,
    DATEADD(hh, 
            DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 
            n.value('(@timestamp)[1]', 'datetime2')) AS [timestamp],
    n.value('(data[@name="wait_type"]/text)[1]', 'varchar(250)') as wait_type,
    n.value('(data[@name="opcode"]/text)[1]', 'varchar(250)') as opcode,
    n.value('(data[@name="duration"]/value)[1]', 'bigint') as duration,
    n.value('(data[@name="max_duration"]/value)[1]', 'bigint') as max_duration,
    n.value('(data[@name="total_duration"]/value)[1]', 'bigint') as total_duration,
    n.value('(data[@name="completed_count"]/value)[1]', 'bigint') as completed_count,
    n.value('(action[@name="callstack"]/value)[1]', 'varchar(max)') as callstack,
    n.value('(action[@name="session_id"]/value)[1]', 'int') as session_id,
    n.value('(action[@name="sql_text"]/value)[1]', 'varchar(max)') as sql_text
FROM @target_data.nodes('RingBufferTarget/event[@name=''wait_info'']') AS q(n);

Now we could do a lot more actionable work with this data by turning this query into a derived table, or changing it to be a SELECT INTO a temporary table that we query a number of other ways after shredding the XML information.  I’ll leave the possibilities for how to consume this data after shredding the XML up to your imagination, and for another post in this series.

Considerations for Usage

While the ring_buffer target may seem like the ideal Target for short term analysis, there are a number of considerations that must be made in determining whether or not it is the correct Target to use in an Event Session.  The first of these is the number of Events that the Target can actually hold based on the max_memory.  If the Event Session is expected to generate a large quantity of Events, the ring_buffer will probably not meet your needs, depending on the Events definitions in the Event Session.  In addition to this consideration, there is a known issue related to the ring_buffer Target associated with it returning XML through the sys.dm_xe_session_targets DMV as discussed by Bob Ward in his blog post: You may not see the data you expect in Extended Event Ring Buffer Targets….  Essentially, the DMV can only return 4MB of materialized XML, which becomes problematic for a Target that is defined to retain 4MB of Event data in binary form.  The binary representation of the Event data can easily exceed 4MB when materialized as XML for the DMV to output.  When this occurs the output from the DMV is a malformed XML document, as detailed in the Connect item referenced in Bob’s blog post.  According to the the Connect item, this problem has been addressed in SQL Server 2008 Service Pack 2.  In addition to this issue, Adam Machanic filed a slightly different Connect item regarding the failure of the ring_buffer target to return all of the Events captured, which according to the feedback comments is also fixed in SQL Server 2008 Service Pack 2 and SQL Server 2008 R2 Cumulative Update 1.

One of the other considerations for using the ring_buffer Target is that the information captured by an Event Session is memory resident only.  This means that if you are capturing Events in the Event Session that are critical and require persistence in the event of that the SQL Server instance encounters a crash the information captured by the target will not be available when the SQL Server instance restarts.  However, when you are doing analysis of a specific workload while the server is online and available, the ring_buffer can still be useful for capturing Events specific to the workload being analyzed.  To accommodate this, and capture Events and maintain them in a static nature without performing a DROP SESSION on the Event Session, it is necessary to remove the Events from the Event Session by performing an ALTER EVENT SESSION in conjunction with the DROP EVENT DDL command.  To demonstrate this, we can create an Event Session that captures the error_reported Event.

-- Create an Event Session to capture Errors Reported
CREATE EVENT SESSION DemoPersistedEvents
ON SERVER
ADD EVENT sqlserver.error_reported
ADD TARGET package0.ring_buffer
WITH (MAX_DISPATCH_LATENCY = 1 SECONDS);
GO
-- Alter the Event Session and Start it.
ALTER EVENT SESSION DemoPersistedEvents
ON SERVER
STATE=START;
GO
-- SELECT from a non-existent table to create Event
SELECT *
FROM master.schema_doesnt_exist.table_doesnt_exist;
GO
-- Drop the Event to halt Event collection
ALTER EVENT SESSION DemoPersistedEvents
ON SERVER
DROP EVENT sqlserver.error_reported;
GO
-- Wait for Event buffering to Target
WAITFOR DELAY '00:00:01';
GO
-- Create XML variable to hold Target Data
DECLARE @target_data XML;
SELECT @target_data = CAST(target_data AS XML)
FROM sys.dm_xe_sessions AS s 
JOIN sys.dm_xe_session_targets AS t 
    ON t.event_session_address = s.address
WHERE s.name = N'DemoPersistedEvents'
  AND t.target_name = N'ring_buffer';

-- Query XML variable to get Event Data
SELECT 
    @target_data.value('(RingBufferTarget/@eventsPerSec)[1]', 'int') AS eventsPerSec,
    @target_data.value('(RingBufferTarget/@processingTime)[1]', 'int') AS processingTime,
    @target_data.value('(RingBufferTarget/@totalEventsProcessed)[1]', 'int') AS totalEventsProcessed,
    @target_data.value('(RingBufferTarget/@eventCount)[1]', 'int') AS eventCount,
    @target_data.value('(RingBufferTarget/@droppedCount)[1]', 'int') AS droppedCount,
    @target_data.value('(RingBufferTarget/@memoryUsed)[1]', 'int') AS memoryUsed;

SELECT 
    n.value('(@name)[1]', 'varchar(50)') AS event_name,
    n.value('(@package)[1]', 'varchar(50)') AS package_name,
    n.value('(@id)[1]', 'int') AS id,
    n.value('(@version)[1]', 'int') AS version,
    DATEADD(hh, 
            DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 
            n.value('(@timestamp)[1]', 'datetime2')) AS [timestamp],
    n.value('(data[@name="error"]/value)[1]', 'int') as error,
    n.value('(data[@name="severity"]/value)[1]', 'int') as severity,
    n.value('(data[@name="duration"]/value)[1]', 'int') as state,
    n.value('(data[@name="user_defined"]/value)[1]', 'varchar(5)') as user_defined,
    n.value('(data[@name="message"]/value)[1]', 'varchar(max)') as message
FROM @target_data.nodes('RingBufferTarget/event') AS q(n);
GO
-- Drop the Event Session to cleanup Demo
DROP EVENT SESSION DemoPersistedEvents
ON SERVER;

If your specific requirements allow for the loss of Events due to the FIFO nature of the ring_buffer Target, and you have applied the necessary patches to your SQL Server instance to ensure that invalid XML is not returned by the sys.dm_xe_session_targets DMV, the ring_buffer Target may provide the required functionality for your specific implementation.  When using an Event Session in the short term, or an Event Session that is Predicated to minimize the number of Events that will actually be fired, the ring_buffer is a maintenance free method of collecting raw Event data for further analysis when the Events are dropped from the Event Session to ensure that unnecessary Events are not captured by the Event Session.

What’s next?

Now that we have looked at the ring_buffer Target, in the next post we’ll look at the asynchronous_file_target which can be used to capture raw Event data into a persistent file in the Operating System allowing for long term Event analysis and capturing Events in a persistent manner that exists beyond SQL Server Instance failures and service restarts and in a manner than can be provided for external analysis similar to SQL Trace files.