T-SQL Tuesday #17: Using Apply to Demystify Deadlocks
This 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);
The Query
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);
Sample Results
The results I had handy all contain data that I can’t share, so I’m providing a screenshot ofwhat the results look like.
T-SQL Tuesday #17: Using Apply to Demystify Deadlocks
[…] You are invited to visit his blog to join the blog party. You are welcome to write your own… [full post] SQLSoldier SQLSoldier sql servert-sqlt-sql tuesdaytips & tricks 0 0 […]
Matt Velic
APPLY, XML, trace files… very nice work! Thanks for joining in, Robert!
SQLSoldier
Thanks Matt!! Thanks for hosting!
Kendra Little
Very cool! This is awesome!
SQLSoldier
Thanks!! The poor developers trying to fix the dozens of deadlocks were very happy.
John Sansom
Hat tip to you sir. A fine example of T-SQL art.
Something for the Weekend – SQL Server Links 15/04/11 | John Sansom - SQL Server DBA in the UK
[…] T-SQL Tuesday #17: Using Apply to Demystify Deadlocks – Brilliant and novel use of the APPLY operator here, shared with us by Robert L. Davis(Blog|Twitter). The APPLY operator was the theme for this months T-SQL Tuesday in case you had not noticed. Normally I try to avoid sharing multiple posts from the same topic pool but this week there was just too much good content that it would be wrong to deny you of it. […]
T-SQL Tuesday #17 Roundup - APPLY Knowledge | Matt Velic
[…] Davis ( Blog | @SQLSoldier ) wrote on how he used APPLY when parsing deadlock information from trace […]
Analyze deadlock graph | SQL Server database engine knowledge
[…] Source: http://www.sqlsoldier.com/wp/sqlserver/tsqltuesday17usingapplytodemystifydeadlocks […]
Ahmad Osama
nice work.. I too did it in a different output format… but this is shorter and simpler than my one..
SQLSoldier
Thanks Ahmad! I’d love to see your version. Feel free to post a link to it.
Analyze Deadlock Graph | AmazingSQL
[…] Source: http://www.sqlsoldier.com/wp/sqlserver/tsqltuesday17usingapplytodemystifydeadlocks […]