One of the things that’s a major concern for DBAs is monitoring deadlocks. And that’s really easy. The more difficult part can be to sift through all the info once you’ve got it. The deadlock graph in Profiler is my favorite tool for gathering them, and I prefer to shred the XML results into a table so I have all the info right there in front of me.
So here I’ve got a script I’ve written that shreds your deadlock XML into a relational table. Actually, I modified this from the one Trevor Barkhouse did for his deadlocking presentation. I just wanted some friendlier cols, and more info in the table. Once you get the data into this format though, you can see how easy it is to turn it into a real process.
What I love the most about this is how easy it is to go through a high number of deadlocks and query them for anything you like. You can easily get a picture of your entire environment with very little effort. Now, for this, you’ll have to get the deadlock graphs into XML to begin with and while I could show it to you here, Trevor does a good job of it so I’ll just point you to his blog on the topic. However, once you have the XML, then here’s my query to mine it.
Here’s the code:
USE [tempdb];
GO
DECLARE @DeadlockCount INT;
DECLARE @DeadlockGraph XML;
DECLARE @DeadlockGraphFileName NVARCHAR(255);
DECLARE @DynamicSQL NVARCHAR(1024);
SET @DeadlockGraphFileName = 'G:\DeadlockFiles\DeleteCV3OrderGenerationQueue\AllEvents.xml'
--'G:\DeadlockFiles\20110405-20110418_1.xdl';
-- The OpenRowset() T-SQL function requires a string literal for the file
-- name, so use dynamic SQL to avoid hardcoding a path (other than in the
-- above assignment to the @DeadlockGraphFileName variable... but that's
-- a little clearer).
SET @DynamicSQL = N'SELECT @XMLDocument = Cast([BulkColumn] AS XML)' +
NCHAR(10) +
N'FROM OpenRowset' +
NCHAR(10) +
N'(' +
NCHAR(10) +
N' BULK ''' +
@DeadlockGraphFileName +
N''',' +
NCHAR(10) +
N' SINGLE_BLOB' +
NCHAR(10) +
N') AS [XMLDocument];';
PRINT @DynamicSQL
-- Execute the dynamic SQL and pull the result into the @XMLDocument
-- local variable.
EXECUTE [master].[sys].[sp_executesql]
@stmt = @DynamicSQL,
@params = N'@XMLDocument XML OUTPUT',
@XMLDocument = @DeadlockGraph OUTPUT;
-- Count the number of deadlock graphs in the XDL file.
SELECT @DeadlockCount = DeadlockList.Graphs.value('count(deadlock)', 'Int')
FROM @DeadlockGraph.nodes('/deadlock-list') AS DeadlockList(Graphs);
PRINT 'File : ' +
@DeadlockGraphFileName +
CHAR(10) +
'Deadlock count : ' +
CAST(@DeadlockCount AS VARCHAR);
-- Extract the parties to the deadlock.
DECLARE @PartiesToTheDeadlocks TABLE
(
[VictimProcessID] VARCHAR(25),
[VictimLastBatchStarted] datetime2,
[VictimLockMode] VARCHAR(15),
[VictimXActID] VARCHAR(15),
[VictimClientApp] VARCHAR(50),
[LiveProcessID] VARCHAR(25),
[LiveLastBatchStarted] NVARCHAR(23),
[LiveLockMode] VARCHAR(15),
[LiveXActID] VARCHAR(15),
[LiveClientApp] VARCHAR(50),
[LiveFileID] VARCHAR(15),
[LivePageID] VARCHAR(15),
[LiveObjName] VARCHAR(50),
[LiveLockModeHeld] VARCHAR(50),
[VictimLockModeRequest] VARCHAR(50),
[VictimFileID] VARCHAR(15),
[VictimPageID] VARCHAR(15),
[VictimObjName] VARCHAR(50),
[VictimLockModeHeld] VARCHAR(50),
[LiveLockModeRequest] VARCHAR(50),
[VictimProcName] VARCHAR(100),
[VictimExecStack] VARCHAR(MAX),
[LiveProcName] VARCHAR(100),
[LiveExecStack] VARCHAR(MAX),
[VictimInputBuffer] VARCHAR(2048),
[LiveInputBuffer] VARCHAR(2048)
);
INSERT INTO @PartiesToTheDeadlocks
SELECT VictimProcessID,
VictimLastBatchStarted,
VictimLockMode,
VictimXActID,
VictimClientApp,
LiveProcessID,
LiveLastBatchStarted,
LiveLockMode,
LiveXActID,
LiveClientApp,
LiveFileID,
LivePageID,
LiveObjName,
LiveLockModeHeld,
VictimLockModeRequest,
VictimFileID,
VictimPageID,
VictimObjName,
VictimLockModeHeld,
LiveLockModeRequest,
VictimProcName,
VictimExecStack,
LiveProcName,
LiveExecStack,
VictimInputBuffer,
LiveInputBuffer
FROM
(
SELECT
--Victim
DeadlockList.Graphs.value('(process-list/process[1]/@spid)[1]', 'NVarChar(15)') AS VictimProcessID,
CAST(REPLACE(DeadlockList.Graphs.value('(process-list/process[1]/@lastbatchstarted)[1]', 'NChar(23)'), N'T', N' ') AS DATETIME) AS VictimLastBatchStarted,
DeadlockList.Graphs.value('(process-list/process[1]/@lockMode)[1]', 'NVarChar(15)') AS VictimLockMode,
DeadlockList.Graphs.value('(process-list/process[1]/@xactid)[1]', 'NVarChar(15)') AS VictimXActID,
DeadlockList.Graphs.value('(process-list/process[1]/@clientapp)[1]', 'NVarChar(50)') AS VictimClientApp,
--Live
DeadlockList.Graphs.value('(process-list/process[2]/@spid)[1]', 'NVarChar(15)') AS LiveProcessID,
CAST(REPLACE(DeadlockList.Graphs.value('(pprocess-list/process[2]/@lastbatchstarted)[1]', 'NChar(23)'), N'T', N' ') AS DATETIME) AS LiveLastBatchStarted,
DeadlockList.Graphs.value('(process-list/process[2]/@lockMode)[1]', 'NVarChar(15)') AS LiveLockMode,
DeadlockList.Graphs.value('(process-list/process[2]/@xactid)[1]', 'NVarChar(15)') AS LiveXActID,
DeadlockList.Graphs.value('(process-list/process[2]/@clientapp)[1]', 'NVarChar(50)') AS LiveClientApp,
--Live resource.
DeadlockList.Graphs.value('(resource-list/pagelock[1]/@fileid)[1]', 'NVarChar(15)') AS LiveFileID,
DeadlockList.Graphs.value('(resource-list/pagelock[1]/@pageid)[1]', 'NVarChar(15)') AS LivePageID,
DeadlockList.Graphs.value('(resource-list/pagelock[1]/@objectname)[1]', 'NVarChar(50)') AS LiveObjName,
DeadlockList.Graphs.value('(resource-list/pagelock[1]/@mode)[1]', 'NVarChar(50)') AS LiveLockModeHeld,
DeadlockList.Graphs.value('(resource-list/pagelock[1]/waiter-list/waiter/@mode)[1]', 'NVarChar(50)') AS VictimLockModeRequest,
--Victim resource.
DeadlockList.Graphs.value('(resource-list/pagelock[2]/@fileid)[1]', 'NVarChar(15)') AS VictimFileID,
DeadlockList.Graphs.value('(resource-list/pagelock[2]/@pageid)[1]', 'NVarChar(15)') AS VictimPageID,
DeadlockList.Graphs.value('(resource-list/pagelock[2]/@objectname)[1]', 'NVarChar(50)') AS VictimObjName,
DeadlockList.Graphs.value('(resource-list/pagelock[2]/@mode)[1]', 'NVarChar(50)') AS VictimLockModeHeld,
DeadlockList.Graphs.value('(resource-list/pagelock[2]/waiter-list/waiter/@mode)[1]', 'NVarChar(50)') AS LiveLockModeRequest,
--Inputbuffers
DeadlockList.Graphs.value('(process-list/process[1]/executionStack/frame/@procname)[1]', 'NVarChar(100)') AS VictimProcName,
DeadlockList.Graphs.value('(process-list/process[1]/executionStack/frame)[1]', 'VarChar(max)') AS VictimExecStack,
DeadlockList.Graphs.value('(process-list/process[2]/executionStack/frame/@procname)[1]', 'NVarChar(max)') AS LiveProcName,
DeadlockList.Graphs.value('(process-list/process[2]/executionStack/frame)[1]', 'VarChar(max)') AS LiveExecStack,
RTRIM(LTRIM(REPLACE(DeadlockList.Graphs.value('(process-list/process[1]/inputbuf)[1]', 'NVarChar(2048)'), NCHAR(10), N''))) AS VictimInputBuffer,
RTrim(LTrim(Replace(DeadlockList.Graphs.value('(process-list/process[2]/inputbuf)[1]', 'NVARCHAR(2048)'), NChar(10), N''))) AS LiveInputBuffer
FROM @DeadlockGraph.nodes('/deadlock-list/deadlock') AS DeadlockList(Graphs)
) AS [_ProcessDetails];
-- Display the parties to the deadlock.
SELECT *
FROM @PartiesToTheDeadlocks
ORDER BY VictimLastBatchStarted, VictimXActID;'
Now, the code formatter messed up the coloring, but the code works.
Now, I’ll list the cols and explain them cause I know that’s the next thing you’re gonna want. It’s broken up into 2 categories… the Victim process and the Live process. I didn’t know what else to call it other than live and still keep it short enough to prefix the cols with.
VictimProcessID – spid of victim.
VictimLastBatchStarted – date/time of last batch for victim.
VictimLockMode – lock held by victim.
VictimXActID – trans ID for victim.
VictimClientApp – Victim client app.
LiveProcessID – spid of live process.
LiveLastBatchStarted – date/time of last batch for live process.
LiveLockMode – lock held by live process.
LiveXActID – trans ID within the batch for victim (I think).
LiveClientApp – client app for live process.
LiveFileID – file ID where the locked resource for live process is held.
LivePageID – page ID the processes are fighting over. The live process currently holds the lock on this one.
LiveObjName – object the processes are fighting over. The live process currently holds the lock on this one.
LiveLockModeHeld – lock held on the above object by the live process.
VictimLockModeRequest – lock being requested on the above object by the victim.
VictimFileID – file ID where the object the victim process is holding is held.
VictimPageID – Page ID the processes are fighting over. The victim currently holds the lock on this one.
VictimObjName – object the processes are fighting over. the victim currently holds the lock on this one.
VictimLockModeHeld – lock held on the above object by the victim.
LiveLockModeRequest – lock being requested on the above object by the live process.
VictimProcName – the SP called by the victim.
VictimExecStack – the stmt in the SP that caused the deadlock.
LiveProcName – the SP called by the live process.
LiveExecStack – the stmt in the SP that caused the deadlock.
VictimInputBuffer – the exact SQL that was sent to SQL engine.
LiveInputBuffer – the exact SQL that was sent to SQL engine.
Now you can see at a glance where your problem deadlocks are, how often they’re occurring, and which resources are under the most contention.
Good stuff that.
Awesome!!!! Just made a minor change to have victim and live columns next to each other like VictimProcessID, LiveProcessID just makes analysing a bit easier
Also, I noticed in the deadlock graph you see the HbBt ID(??), Index Name etc, could we get them too ?
Great script and information! Deepest thanks!