On my way back from SQLBits this past Sunday I spent some time drafting a handful of blog posts. One of the posts was on troubleshooting deadlocks in Windows Azure SQL Database (WASD).
Imagine my surprise when I went through my RSS feeds today and found this blog post from Chris Skorlinski, written just last week.
Great minds, right?
I decided that instead of tossing my post altogether, or waiting a few weeks to publish an updated version, that I would just go ahead and share with you the one thing that Chris forgot to mention in his post.
First let’s talk about why I felt this topic was important enough for a post: because people fear change.
I mean…I *hate* it when someone moves my cheese or my wine. Especially when one or both of those items are just out of my reach.
When the topic of WASD is mentioned in certain circles I often hear comments about how WASD is lacking the features and functionality that exist in the on-premises version of SQL Server.
Two of the most common examples that get mentioned to me are the lack of Extended Events and/or SQL Profiler. It doesn’t matter if the person raising the objection uses those tools, they just want them available if needed. When I ask what they want to do with those tools that they cannot do with DMVs currently I get back a fairly common response:
While I highly doubt these same people are going to start using WASD even if those xEvents or Profiler were available I thought it was worth sharing that WASD makes it EASIER to troubleshoot deadlocks that a traditional on-prem instance of SQL Server.
That’s what Chris has done for the most part, except for one thing.
What Chris Is Missing
As much as people fear change, people love to look at pictures even more.
Chris shows how to view the XML for the deadlock event. If you want to see the graph then you just need to save that file as an .rdl file and then re-open. It’s so easy I decided to make a video to show you how it is done.
The end result will be this:
Consider how you have to capture and review deadlock details in on-prem SQL Server 2012 today and I think you’ll find WASD to be much simpler. You don’t need to do anything in advance to capture deadlock information, or historical deadlock information. For more information on how to resolve deadlocking I will point you to Bart Duncan’s blog posts as a great first step (and likely the only step you will need).
Reducing the amount of administrative overhead for tracking and analyzing deadlocks is one example of how Microsoft is making an effort to reduce the total cost of ownership (TCO) with WASD.
Great article, there’s also a nice one on database deadlocks over here:
http://www.programmerinterview.com/index.php/database-sql/database-deadlock/
great addition, however you have a little typo there – it is xdl instead of rdl
Thanks!
Could do with an update for v12 as additional_data is always null
Thanks!
For Azure SQL Database v12 you can grab the deadlock events using the following query:
WITH CTE AS (
SELECT CAST(event_data AS XML) AS [target_data_XML]
FROM sys.fn_xe_telemetry_blob_target_read_file(‘dl’, null, null, null)
)
SELECT target_data_XML.value(‘(/event/@timestamp)[1]’, ‘DateTime2’) AS Timestamp,
target_data_XML.query(‘/event/data[@name=”xml_report”]/value/deadlock’) AS deadlock_xml,
target_data_XML.query(‘/event/data[@name=”database_name”]/value’).value(‘(/value)[1]’, ‘nvarchar(100)’) AS db_name
FROM CTE
Source: https://msdn.microsoft.com/en-us/library/dn270018.aspx
Thanks Phil, much appreciated!