The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded
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.
My 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.
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())', '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:
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.