T-SQL Tuesday #17: Using Apply to Demystify DeadlocksThis blog entry is participating in T-SQL Tuesday #17, hosted this month by Matt Velic
(Blog|@mvelic). You are invited to visit his blog to join the blog party. You are welcome to write your own participating blog post for the party or just to read more blogs participating in this month’s theme: APPLY Knowledge.
For my part in this month’s T-SQL Tuesday, I’m going to talk about a script I wrote recently to parse out deadlock information from trace files. A couple of Sundays ago, I was called in to help out an engineering team with diagnosing some deadlocks. They had captured several traces with deadlock information, but they were having a hard time interpreting the data. Part of their problem was that none of them really knew how to read the deadlock data and was resorting to trying to decipher the deadlock graphs as images.
I gave them a written walk-through on how to decipher the deadlock data. I also wrote a little T-SQL script that broke down the deadlock graphs into easy to read columns. Fortunately, the APPLY command isvery handy for shredding XML data into nodes. Typical use of APPLY in this scenario is to provide the full XML path to the node on which you want to shred. But here are a couple of cool tricks that make using APPLY so much easier for XML.
The first node I shredded, I use a relative path. By specifying two slashes at the beginning of the path, it finds the first occurence of the specified path without having to supply the whole path to that point.
Cross Apply XMLData.nodes('//resource-list/keylock') ResList(KeyLock)
For the second and third nodes I shredded, I based their paths off of the results of the first shredded node. The “.” is an indicator that it starts at the root of the defined node and is basically a replacement for the function self::node
-- Shred the owner node beneath the resource-list/keylock node Cross Apply ResList.KeyLock.nodes('./owner-list/owner') OwnList(Own) -- Shred the waiter node beneath the resource-list/keylock node Cross Apply ResList.KeyLock.nodes('./waiter-list/waiter') WaitList(Wait);
Declare @TraceFile nvarchar(256) Declare @XMLData Table ( GraphID int identity(1, 1) not null primary key, XMLData XML not null) -- Provide path and name of first trace file Set @TraceFile = N'P:athToTraceFileTraceFile.trc' -- Returns only the entries with the deadlock XML Insert Into @XMLData(XMLData) Select Cast(TextData As XML) As XMLData From fn_trace_gettable (@TraceFile, default) Where TextData Like '<deadlock-list>%' /* Returns the following columns: GraphID: multiple rows returned for the same deadlock event, 1 per resource participating in the deadlock XMLData: the full XML of the deadlock event, click on returned value to open as XML in a new window ObjectName: name of the resource that is locked IndexName: if not null, name of index that is locked ResultingLockMode: Lock mode after deadlock victim resolved OwnerID: ID of the process that owns the existing lock on the resource at the time of the deadlock event OwnerLockMode: lock mode of the owner's lock WaiterID: ID of the process that is waiting for a lock on the resource at the time of the deadlock event WaiterLockMode: requested lock mode of the waiter's lock */ Select GraphID, XMLData, ObjectName = ResList.KeyLock.value('@objectname', 'sysname'), IndexName = ResList.KeyLock.value('@indexname', 'sysname'), ResultingLockMode = ResList.KeyLock.value('@mode', 'varchar(10)'), OwnerID = OwnList.Own.value('@id', 'sysname'), OwnerLockMode = OwnList.Own.value('@mode', 'varchar(10)'), WaiterID = WaitList.Wait.value('@id', 'sysname'), WaiterLockMode = WaitList.Wait.value('@mode', 'varchar(10)') From @XMLData -- Shred keylock node of the resource-list Cross Apply XMLData.nodes('//resource-list/keylock') ResList(KeyLock) -- Shred the owner node beneath the resource-list/keylock node Cross Apply ResList.KeyLock.nodes('./owner-list/owner') OwnList(Own) -- Shred the waiter node beneath the resource-list/keylock node Cross Apply ResList.KeyLock.nodes('./waiter-list/waiter') WaitList(Wait);
The results I had handy all contain data that I can’t share, so I’m providing a screenshot ofwhat the results look like.