Monitoring Ring Buffers with XML
Monitoring in SQL Server 2008 is different that monitoring in all previous releases of SQL Server. While the fundamentals around what to look for and how to respond remain much like they have been in the past, collecting the information needed to make the right choices is not at all the same. With SQL Server 2008 we have DMVs to show us which queries are performing poorly, which tables have indexing issues and which processes are having trouble getting to the data they need; a Management Data Warehouse to automatically maintain a history of overall system performance; disk usage and query performance, a default trace to keep tabs on some problem behaviors; Policies to help attain consistent configurations, Auditing to help track changes across the server, and the extended events engine to collect run time details without the performance costs of SQL Profiler and SQL Trace.
It can almost boggle the mind how much has changed for those of us that have been administering SQL Server databases for many years. In some ways the SQL Server of today is unrecognizable as an evolution of the SQL Server of 10 years ago. Other than the fact the SELECT, INSERT, UPDATE and DELETE are still the things SQL Server does best, not much else is remained the same.
Consider how far and fast XML and XQuery have moved forward as viable database developer tools within SQL Server beginning with SQL Server 2000.
At last with SQL Server 2008 it is time for DBAs to exploit this dimension. A DBA with Knowledge of XML is more relevant in the post SQL Server 2000 environment.
On a SQL Server 2000 database engine all the DBA need know about intrinsic XML functionality is that any flavor of XML implementation can further stress the already abused memtoleave memory region. The DBA has only the choice to discourage the preparation of XML documents at the database server in the effort to avoid “out of memory” conditions. Developers can take limited advantage of server side SQL Server extensions for XML data provided the scalability and performance requirements are modest. The DBA has no real need to understand how to work with XML documents and can support them like any other BLOB type: very reluctantly and with great trepidation.
Beginning with SQL Server 2005, the need for the DBA to acquire expertise with SQL Server’s XML capabilities is stronger. The abilities for the DBA to recognize the need for XML Indexes, configure a database to support XML, create XML indexes and maintenance needs of XML indexes for better XML query performance not to mention how to capture XML Schemas to support typed XML documents are necessary DBA skills for SQL Server 2005 and beyond. Additionally the DBA’s ability to consume and manipulate semi-structured data as XML offers a few worthwhile views into system performance, security, and low level system interoperability characteristics of a SQL Server instance. For example, a record set of the most recent system performance events useful to document performance bottlenecks are exposed as XML data in the sys.dm_os_ring_buffers Dynamic Management View (DMV). To thoroughly decipher the content of this resource monitor event queue as Slava Oks describes it, it is necessary to query the XML typed column found in each row of the DMV result set. While there are examples loose in the wild using LIKE argument filters applied to XML represented as character data to parse the record column in the ring buffer DMV, using XML methods provides a considerably more efficient, readable, reusable, and powerful access path to the embedded XML information.
It is worth emphasizing here that event data collected to a ring buffer resides in volatile memory. Ring buffers are not durable. When a SQL Server database engine service is stopped, the data in the ring buffer is gone. When a SQL Server is started, the ring buffers is initialized and begins collecting new events presented to the ring buffer. Once a becomes aware of the content of a ring buffers or possibly even implements one, it can become a pain point that the buffered information is no longer available when the SQL Server is restarted. Consider whether a ring buffer is the correct way to collect data if this is the case. Also consider the alternative of saving ring buffered events to a table if they prove interesting or require resolution efforts.
SQL Server 2008 further expands and formalizes event ring buffering and the options to target other collectors with the Extended Events Engine. In the same manner that the record column of the sys.dm_os_ring_buffer DMV contains the XML representation of predetermined system events, the target_data column of the sys.dm_xe_session_targets DMV contains the XML representation of user selected system events.
SQL Audit is a special case implementation within the Extended Events Engine. Unlike other Extended Events, SQL Audit cannot target a ring buffer. It is restricted to file and Windows Event Log targets. Unlike ring buffers, the valid SQL Audit targets have implicit durability between instantiations of the SQL Server service. The data can still be easily configured with a valid lifetime and then will be automatically purged when expired, but the limitation of losing the data at each SQL Server service restart is avoided.
Any other Extended Event session can be configured to use a ring buffer or one of these more durable Extended Events Targets as is appropriate. This is an important administrative design question to be answered for each Extended Events session when conceived.
There are a few useful examples of DBA implemented Extended Events in SQL Server 2008 Book Online. Check “Extended Events how-to Topics” for locking and system monitoring examples.
Around the Internet there are many other good examples of successful monitoring efforts using Extended Events. Paul Randal was one of the first to write about general troubleshooting with Extended Events. More recently he blogged about tracking expensive queries using Extended Events. Sankar Reddy has written about tuning tempdb with the help of Extended Event Monitoring. Adam Mechanic has also been speaking and blogging about Extended Events. It may be necessary to hunt a little but the determined reader can find Extended Event examples for monitoring page splits, waits and locks from Mr. Mechanic along with a couple different versions of a tool kit for analysis of collected events that works for most Extended Event target types.
At the behest of the product support team, one Extended Event session is preconfigured and active on every SQL Server 2008 instance by default: the “system” health session. This session provided a collection of events identified by Microsoft’s CSS engineers as useful to identify an unhappy SQL Server. Given that the vendor support folks find useful information in this data and given that this information is being collected continuously at all times; it stands to reason that DBAs can access this data and will likely find the event information queued in the “system health” session helpful. To be sure there is a ton of information available in the ring buffer that underlies the sys.dm_xe_session_targets DMV’s target_data column. A DBA that wants to extract this information for maximum utility and with optimum precision will need to be able to query XML data.
Some readers may be surprised to learn that deadlock reporting is available as an always on zero impact output of the “system health” session. Others may already be familiar with Jonathan Kehayias white paper that describe the xml_deadlock_report session event in detail. (Author’s note: Mr. Kehayias is also responsible for bring us the Extended Event Add-in for SSMS that ports Extended Event management into the primary SQL Server UI.)
Wait analysis is also available out of the box in the “system health” session. The Microsoft SQLOS team has blogged about how to use the Extended Events Sessions for wait analysis, though not from the “system health” session but from custom sessions. The Microsoft CSS is beginning to share some information with the community that should help every DBA get a better understanding of exactly where the query bottlenecks are on each system and how to most effectively use the wait data in the “system health” ring buffer and in the sys.dm_os_wait_stats DMV. There is reason to expect that more information specific to the “system health” session will be forthcoming from that group since, as was already mentioned, the system heath session is their baby.
From personal experience, some interesting errors have surfaced in the error_reported session type that have uncovered mis-configured application servers and unexpected SQL Server port scanning behaviors from supposedly innocuous monitoring tools. The error information found by monitoring the system heath session has proven invaluable for “system health” by effortlessly providing details not readily available from other sources.
As is true with the sys.dm_os_ring_buffer DMV, the event data in a sys.dm_xe_session_targets DMV ring buffer target is XML manifest as character data (NVARCHAR(MAX)) .With SQL Server 2008 Management Studio (SSMS), viewing the XML of an instance of any XML document interactively is reasonably elegant. Any column of type XML returned to a grid result set in the SSMS query tool is rendered as a link that can be followed to the XML document in the SSMS integrated XML editor. Nonetheless, the need to extract, organize and analyze interesting XML elements and attributes leaves the XML XPath query methods as a most compelling augmentation to the DBA tool kit.
Considering the non-standard number of elements and attributes inherent in semi-structured XML document, the amount of information that can be loaded into any particular element, as well as the potentially large number of entries - or sessions of event collections in the case of Extended Events - that can be loaded into a ring buffer leave the ad hoc analysis of individual elements an exercise in futility for most DBAs. There simply is not enough time in a work day to browser the XML documents in sys.dm_os_ring_buffer.result or sys.dm_xe_session_events.target_data. Using other custom extended events and the assortment of other DMVs with XML column contents is also not realistic via ad hoc analytical tactics. Instead, competence with XQuery/XPath and a small set of reusable queries is a more effective approach to SQL Server monitoring of the ring buffer staged events.
Whether qualifying those XML documents that deserve such individual attention or - usually more practical - identifying those document types or categories that deserves further action and extraction and then programmatically identifying atomic data values that are interesting, a set based query that can examine many rows containing XML document content is preferred. In some cases - Extended Events target data being a particularly relevant example - shredding the event XML into rows of columns will be an unavoidable step when analyzing ring buffer content.
Both sys.dm_os_ring_buffer or the sys.dm_xe_session_events can be filtered or aggregated by type. Typing sys.dm_os_ring_buffer XML documents is very simple. The DMV result set contains a typing column so all that is needed to determine the number if each type of event currently held in the buffer is to group by the type column:
SELECT [ring_buffer_type]
, COUNT(*) AS [type_count]
FROM sys.dm_os_ring_buffers
GROUP BY [ring_buffer_type]
Execution of the query will return information similar to the following:
ring_buffer_type type_count
---------------------------- ----------
RING_BUFFER_RESOURCE_MONITOR 1
RING_BUFFER_SCHEDULER_MONITOR 256
RING_BUFFER_MEMORY_BROKER 6
RING_BUFFER_XE_BUFFER_STATE 4
RING_BUFFER_SCHEDULER 855
RING_BUFFER_EXCEPTION 169
RING_BUFFER_XE_LOG 1
There are several more ring_buffer_types,
Getting type feedback from the sys.dm_xe_session_targets ring buffer requires a bit more effort because the typing data is an element of the XML document. While there are some advantages of efficiency in getting the types directly from the XML, a short cut to the type information using the DMVs that describe Extended Events is possible.
The following query will tell you what types of events will be written to the “system health” session target. By default this target is a ring buffer.
SELECT e.event_name AS [Event Type]
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_events e
ON s.address = e.event_session_address
WHERE s.name = 'system_health'
Executing the query on an unmodified “system health” session configuration on a SQL Server 2008 R2 (Nov CTP) produces this output:
Event Type
---------------------------------------------------
error_reported
xml_deadlock_report
scheduler_monitor_non_yielding_ring_buffer_recorded
wait_info
wait_info_external
The following query can be used to verify that the session target is a ring buffer. Also be aware that the session can be redirected to any of the other valid Extended Event targets.
SELECT target_name
FROM sys.dm_xe_session_targets t
JOIN sys.dm_xe_sessions s
ON t.event_session_address = s.address
WHERE s.name = 'system_health'
As previously stipulated, if the session is unmodified, the result will be:
target_name
---------------------
ring_buffer
While querying the Extended Events DMVs is very easy, getting type information directly from the XML document is recommended because it is the only way to identify the type of the events present:
Not all events will be present in the XML image of the ring buffer at any given time.
A count of elemental entries cannot be determined without shredding the target_data column.
Any wasted overhead in trying to shred and compute aggregates on event types that have no current entries or too many entries to make shredding worthwhile can be avoided.
Before an additive event type aggregate is demonstrated, it is worthwhile to introduce the XQuery nodes() method used to shred XML into tabular output. The usefulness of using this method to extract rows from the ring buffer will then become more obvious. The nodes() method is useful for queries that derive tabular data from the XML columns in sys.dm_os_ring_buffer.result and sys.dm_xe_session_targets.target_data or any other table or variable. Mastery of the method should be considered a fundamental requirement for those using XML data in T-SQL expressions.
First, consider the syntax of the XQuery nodes() method:
XMLDocument.nodes (XPath) AS Table(Column)
XMLDocument is any variable or column of SQL Server type XML. This value can be an XML document or a fragment of an XML document provided it follows the syntax requirements of any well formed XML Document.
XPath is any XQuery (XPath) expression that produces a valid XML fragment.
Table is any user supplied literal that will be the name of the tabular context of the nodes produced. This literal should conform to all table alias naming guidelines.
Column is any user supplied literal that will be the name of the columnar context within the Table specified. The literal should conform to all column naming guidelines.
Applying the nodes() method to valid XML data shreds the collection of elements at the level specified by the XQuery’s XPath expression into a one column wide table where the column contain one node that satisfies the expression per table row. Once shredded by the method, the Table and Column are used like any derived table in the proper context in relation to the T-SQL statement.
Consider this example of shredding a simple XML document. The example contains just enough XML to demonstrate shredding elements to rows and shredding attribute values and atomic elemental values to tabular values:
DECLARE @XMLDocument [NVARCHAR] (MAX)
SET @XMLDocument = '<root>
<item id="1"><type>A</type></item>
<item id="2"><type>B</type></item>
<item id="3"><type>C</type></item>
</root>'
SELECT Document.Element.query('.') AS [Shredded Node]
, Document.Element.value('@id', 'int') AS [Id]
, Document.Element.value('type[1]', 'VARCHAR(20)') AS [Type]
FROM (SELECT CAST(@XMLDocument AS XML) AS XMLDoc) AS Data
CROSS APPLY XMLDoc.nodes ('//root/item,') AS Document(Element)
Executing the statements above produces this result set:
Shredded Node Id Type
---------------------------------- ---- ----
<item id="1"><type>A</type></item> 1 A
<item id="2"><type>B</type></item> 2 B
<item id="3"><type>C</type></item> 3 C
Notice that each returned node is recognized by SSMS as valid XML. This is shown by the link presented for drilldown viewing of the column in the SSMS XML Editor. (Sorry, the links don't work here, you will have to try it on your SQL Server 2008 test box to get working links.)
The use of the XML working variable, CAST(@XMLDocument AS XML) in this example provides yet another opportunity to lay a bit of foundational groundwork to help in understanding how to query ring buffers. Although I have not seen the assertion documented anywhere, it is evident that a ring buffer is not optimized for read. From this we may infer that ring buffers must somehow be optimized for inserts. Even if that is not true, the ring buffer writes are event actions that can be de-coupled from the thread of execution that originated the event. That is all admittedly something of a guess. It is more certain that an XQuery against a ring buffer can be slow and therefore could potentially contribute to degraded system performance. It is necessary to consider some steps for optimizing queries of this and all other Extended Event ring buffer queries.
Linchi Shea has demonstrated that there is no significant system throughput impact to query the sys.dm_os_ring_buffer DMV every four seconds on a SQL Server 2005 instance installed on server hardware. I have every reason to believe this continues to hold true for a SQL Server 2008 instance. Unfortunately, querying the sys.dm_xe_session_targets DMV at anything near that frequency could make the SQL Server 2008 instance unhappy. The advice here could only be to test.
The reason for the query performance difference between the two ring buffer DMVs is primarily due to the difference in how the XML is presented in the two DMVs. Each node in the sys.dm_os_ring_buffer is already shredded into a table row before it is ever queried externally while the target_data column of the sys.dm_xe_session_targets table is a single - and potentially large - XML document of all events for the session. If nothing is being written to the “system health” ring buffer and no other extended events are configured, shredding the empty document will be fast. If the system is seeing sporadic bouts of deadlocking or connection failures and waits then the number and types of nodes will be more. In most cases, expect there to be a lot of data in the “system health” ring buffer at least part of the time.
Since the target_data column is manifested as character data in the DMV the XML is not indexed. Because a scan is necessary to either index the XML, scan the XML or parse the character data, the more nodes - and the more types of nodes -, the longer it will take to query this ring buffer. The risk of pesky asynchronous waiting that causes system sluggishness will likely increase directly in correlation with the scan time. Just like you may see ever increasing user connections when trying to trace too many events, you may see processes server wide begin to slow and clients using pooled connection begin to ask for many new connections from the pool. To mitigate the performance risks from this behavior, copying the ring buffer content into a local variable or table when it is first touched is recommended. Not only is this the quickest way to get out of the way of the system and minimize the potential for asynchronous waiting, but if also presents an opportunity to build an index and to re-query the snapshot image as many times and in as many ways as might be desired. Even using the snapshot copy can require a slow performance time for the analysis query, but it should see much better isolation from the overall system than direct manipulation of XML data from the ring buffer.
After moving a copy of the ring buffer XML to a work container, the next important step will be to place a primary XML index on that container if warranted. The possibility alone that query performance of an XML column may be better with an index makes a strong case for using a table, whether temporary or permanent, as the target of the copy operation rather than a local variable.
Secondary XML indexes may also be helpful though in testing secondary XML indexes in the “system health” extended events ring buffer do not show an appreciable benefit for the types of queries that will be shown in the article.
It is worth mentioning –though as has already been mentioned for Secondary XML indexing , it is not likely to add benefit in this scenario - that XML columns can be full text indexed. No special syntax is needed. As long as the column is typed as XML, the tags will be ignored during full text indexing for a column of tyoe XML specified in a full text index.
The primary XML index seems to generally cut the query time of the first query by about 50% on a multi-cire server when compared to a single scan of a large ring buffer document. The second query of the primary XML indexed table column will be many times faster. Conversely, a second query directly against the ring buffer is almost as slow as the first.
In order to create the primary full text index, that table must have a primary key. In the T-SQL below, a temp table is created expressly to hold the XML data, and then event nodes of the ring buffer are shredded into the table.
CREATE TABLE #ringbuffer
( Id INT IDENTITY(1,1) PRIMARY KEY
, XEventXML XML)
INSERT #ringbuffer(XEventXML)
SELECT XEventData.XEvent.query('.')
FROM (SELECT CAST(xet.target_data AS xml) AS TargetData
FROM sys.dm_xe_session_targets xet
INNER JOIN sys.dm_xe_sessions xe
ON (xe.address = xet.event_session_address)
WHERE xe.name = 'system_health' ) AS Data
CROSS APPLY
TargetData.nodes ('//RingBufferTarget/event')
AS XEventData (XEvent)
Either before or after the table is populate a primary XML index is created.
CREATE PRIMARY XML INDEX xxp_#ringbuffer
ON #ringbuffer(XEventXML)
With the application of these additional considerations the sys.dm_xe_session_targets DMV can be queried to produce an additive summarization of the buffer content by type. In fact, since the indexing work is done and a table of XML nodes is in place, the document data is now well prepared for atomic shredding to identify, aggregate and analyze the ring buffer data in a tabular form using familiar query methods,
Links to scripts for a small collection of stored procedures that provide summary and detail level reporting from the sys.dm_os_ring_buffer and the sys.dm_xe_session_targets “system health” ring buffer are provided below. The procedures are useful for routine monitoring through SSMS and also as building blocks for other queries that help the DBA target specific issues in the local data environment.
[dbo].[GetRingBufferDetails] – Produces a summary report by type of the current ring buffer contents and then a result set detailing the buffer entries for each type present
[dbo].[GetXESystemHealthSummary] – Builds an aggregate distribution by event type of the “system health” session ring buffer content over the most recent days. The number of days that are shown is parameterized with a default of 2. This query is useful when run from a Central Management Server against multiple SQL Server instances at the same time.
[dbo].[GetXESystemHealthDetails] – Break down of “system health” session ring buffer event entries by type. Adequate XML column manipulation is completed to render the results immediately useful for actionable purposes of problem analysis and metrics collection.
While these queries are what I use for daily ring buffer monitoring, they should be considered as templates for the monitoring queries appropriate for the server being monitored.
Thanks for reading!
Bill
This page was last modified on Friday, December 11, 2009 08:39:45 AM