Russ Thomas – SQL Judo

The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded

XQuery for the SQL Server DBA

A few weeks ago I embarked on a series to present the basics of XQuery and XPath for the SQL Server database administrator.  This is the final post and will also serve as the master post for this series.

Start with the XPath tutorial.  95% of the queries a DBA might issue against XML stores are pure XPath.  XQuery adds additional features, but these features continue to rely heavily on XPath statements.  After the first tutorial the others can be read in any order.

madnessMy goal in this series was not to make you an expert on XQuery/XPath but provide a foundational understanding of the languages.  There is a lot such as aggregation, all the methods related to the XML data type, the complete collection of supported syntax that I don’t really cover.

You’ll also find reading these posts that I am not an XQuery cheerleader.  There is a lot of frustrating behaviors of this language.  I am however a realist.  XML is a attractive option for storing semi-structured data (though I’d prefer JSON).  XQuery and XPath, right now, are our options for querying it.

For an incredibly insightful discussion on Microsoft’s support for XPath vs XQuery and the future of semi-structured data such as support for JSON – I highly recommend listening to SQL Down Under’s podcast with Dr David Dewitt (13:30 – 16:30).


Update from the future (5/11/2015) – SQL Server 2016 support for JSON!  Aaron Bertrand gives an excellent preview of what this will look like in future releases.

In this final post let me leave you a realistic DBA type scenario you can setup yourself to work with XQuery.  Though I recommend all the tutorials above, the one on XPath is really the only pre-requisite to the exercise below:

EXERCISE: Analyzing Deadlocks with Extended Events and XQuery

To setup the example we’ll use the deadlock information captured by the system_health extended event.  For an excellent series on extended events I would refer you to the 31 day series authored by Jonathan Kehayias.  His article on the ring_buffer target in particular has specific interest to how XQuery effects the DBA.

I had originally written this post using a dedicated XEvent to capture deadlocks in an attempt to reduce the size and complexity of the XML document produced by the system_health event.  But taking the advice of Jonathan Kehayias below I decided to re-write in a manner more closely related to how you would actually do this in production.

To set up this example we need to cause a couple deadlocks.  For this we’ll create a couple tables.

create table table1 (col1 varchar(5));
create table table2 (col1 varchar(5));

Then begin a transaction to insert a value in the first:

begin tran
insert into table1 values ('stuff')

In a separate query window begin a second transaction to insert a value into table2 and request a lock on the first:

begin tran
insert into table2 values ('junk')
select * from table1

And finally return the first window and complete the deadly embrace by adding a lock request back to the second table:

select * from table2

If you did it right SSMS will report that a deadlock has occurred and that one of your transactions has been chosen as a deadlock victim.  I opened several windows and caused more than one to populate the session target with several deadlock events.

With the system_health event session running, these deadlocks have now been captured into the ring buffer target.  You can query the target directly using this slightly modified sample query I pulled from books online to verify the existence of actual data from the event.

SELECT CAST(xet.target_data AS xml)
FROM sys.dm_xe_session_targets AS xet
JOIN sys.dm_xe_sessions AS xe
   ON (xe.address = xet.event_session_address)
WHERE xe.name = 'system_health'

Running this query you’ll notice all event details retained within the ring buffer target are stored within an XML document.  Gaining a more concise representation of the deadlock events captured requires XQuery.

Practical Exercise

Time to be creative.  At this point you can create your own XQuery statements using what you’ve learned to extract information pertaining to the event or the deadlocks themselves.

To do this I’ll extract the information related to deadlocks into it’s own xml variable.  If you run the query below with the final select statement you can inspect the XML document and identify paths of information that might be of interest to you.

Hint: Depending on a variety of activites that your preferred MCM could explain; it might take a few moments for the deadlock info to appear in the ring buffer.  On my own test box it took about 90 seconds to arrive.

DECLARE @x XML;

WITH deadlock_cte ( target_data )
AS ( SELECT   CAST(target_data AS XML)
     FROM     sys.dm_xe_session_targets xet
     JOIN sys.dm_xe_sessions xe 
     ON xe.address = xet.event_session_address
     WHERE    name = 'system_health'
     AND target_data LIKE '<RingBufferTarget%' )
SELECT  @x = target_data.query('

     RingBufferTarget/event/data/value/deadlock

') FROM deadlock_cte;

SELECT @x;

With the XML portion related to deadlocks pulled into it’s own variable let’s say that I want to capture all the victim hosts, batch start time, and victim SQL statements into a set.  I could run the following:

SELECT  C.value('@hostname', 'varchar(50)') AS [host] ,
        C.value('@lastbatchstarted', 'datetime') AS [batch_started] ,
        C.value('@id', 'varchar(50)') AS [victim_process] ,
        C.value('(executionStack/frame/text())[1]', 
                    'varchar(max)') AS [statement]
FROM    @x.nodes('

 RingBufferTarget/event/data/value/deadlock/process-list/process

                 ')
        AS T ( C )
WHERE   C.value('@id', 'varchar(50)') 
        IN (
        SELECT  V.value('@id', 'varchar(50)') AS victims
        FROM @x.nodes('

 RingBufferTarget/event/data/value/deadlock/victim-list/victimProcess

                      ')
         AS T ( V ) )

This query uses the nodes method as a FROM source to select out the details of all processes that are identified as victims.  On my own machine the results look like this:

sample query

At this point you are only limited by your own imagination.  Look over the document – write up some queries of your own.  If you want to extend this knowledge to the other information stored within the RingBufferTarget you have the tools at your disposal.

To clean up after yourself you’ll want to make sure your scripts to create deadlocks didn’t leave any open transactions.


For an impressive collection of XQuery samples against a myriad of scenarios I would encourage you to browse the labs provided by Jacob Sebastian on his blog as well as the additional samples section within the XQuery BOL topic.

Well, there you go – a complete series on the fundamental mysteries of XQuery, XPath, XML, and how it relates to the world of the database administrator.  This has been fun.  At some point in the future I plan to do another series on how XML relates to the world of the database developer, but give me a few months.  I’m ready to step away from this topic for a while.  My brain hurts.

Marble game image credits from series to EA, Milton Bradley, Parker Brothers, Jedco.com.au, Brio Labyrinth.

2 comments on “XQuery for the SQL Server DBA

  1. jmkehayias
    December 31, 2013

    The xml_deadlock_report is already captured by default in the system_health event session. Creating another event session to capture the same event is just added overhead without any benefit. See: http://www.sqlservercentral.com/articles/deadlock/65658/

    • RThomas
      December 31, 2013

      For production use – you are absolutely right. The dedicated XEvent I originally used as an example was totally superfluous. I started to edit this post to defend why I was using a dedicated extended event ( to create an XML document that was smaller and easier to work with ). After thinking it over again however I decided to take your implied advice and re-write using the system_health event again. I added a CTE to extract the deadlock info from the system_health event in the same way my previous example was with it’s own event. In the end I think it worked out better. Thanks for the feedback.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Information

This entry was posted on December 31, 2013 by in XPath XQuery XML.
%d bloggers like this: