Issue shredding Deadlock Graph XML
-
I'm working on a script that shreds deadlock graph information. Using the deadlock graph that was posted here, I'm running the following select statements. The first select statement does retrieve information from the exchangeEvent node, while the second select statement does not (though it does return the data from the pagelock node). I cannot figure out why the second one isn't working correctly. Any help in getting this figured out is greatly appreciated!
DECLARE @DeadlockGraph XML;SET @DeadlockGraph = '<deadlock-list>
<deadlock victim="processa2db88">
<process-list>
<process id="processa2db88" taskpriority="0" logused="0" waitresource="PAGE: 5:1:6824253" waittime="10933" ownerId="1493304634" transactionname="implicit_transaction" lasttranstarted="2012-04-04T15:19:42.193" XDES="0x20cf52730" lockMode="S" schedulerid="3" kpid="68992" status="suspended" spid="81" sbid="0" ecid="3" priority="0" trancount="0" lastbatchstarted="2012-04-04T15:19:44.810" lastbatchcompleted="2012-04-04T15:19:44.807" clientapp="jTDS" hostname="hostname" hostpid="123" isolationlevel="read committed (2)" xactid="1493304634" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="40" sqlhandle="0x020000007ecd772bc607c482a24c941ad99dd40ef170ae1e">
select pssiteitem0_.REFERENCE_ID as REFERENCE1_37_, pssiteitem0_.CONTEXT_ID as CONTEXT2_37_, pssiteitem0_.SITE_ID as SITE3_37_,
pssiteitem0_.UNPUBLISH_INFO as UNPUBLISH4_37_, pssiteitem0_.VERSION as VERSION37_ from database.dbo.PSX_PUBLICATION_SITE_ITEM
pssiteitem0_, database.dbo.RXSITES pssite1_, database.dbo.PSX_PUBLICATION_DOC pspubitem2_
where pssiteitem0_.REFERENCE_ID=pspubitem2_.REFERENCE_ID and pssiteitem0_.SITE_ID=pssite1_.SITEID and pssiteitem0_.SITE_ID= @P0
and pssiteitem0_.CONTEXT_ID= @P1 and pspubitem2_.STATUS=pspubitem2_.OPERATION
</frame>
</executionStack>
<inputbuf />
</process>
<process id="process21a958748" taskpriority="0" logused="10000" waittime="10430" schedulerid="2" kpid="67784" status="suspended" spid="81" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2012-04-04T15:19:44.810" lastbatchcompleted="2012-04-04T15:19:44.807" clientapp="jTDS" hostname="hostname" hostpid="123" loginname="LOGIN" isolationlevel="read committed (2)" xactid="1493304634" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="40" sqlhandle="0x020000007ecd772bc607c482a24c941ad99dd40ef170ae1e">
select pssiteitem0_.REFERENCE_ID as REFERENCE1_37_, pssiteitem0_.CONTEXT_ID as CONTEXT2_37_, pssiteitem0_.SITE_ID as SITE3_37_,
pssiteitem0_.UNPUBLISH_INFO as UNPUBLISH4_37_, pssiteitem0_.VERSION as VERSION37_ from database.dbo.PSX_PUBLICATION_SITE_ITEM pssiteitem0_,
database.dbo.RXSITES pssite1_, database.dbo.PSX_PUBLICATION_DOC pspubitem2_ where pssiteitem0_.REFERENCE_ID=pspubitem2_.REFERENCE_ID
and pssiteitem0_.SITE_ID=pssite1_.SITEID and pssiteitem0_.SITE_ID= @P0 and pssiteitem0_.CONTEXT_ID= @P1
and pspubitem2_.STATUS=pspubitem2_.OPERATION
</frame>
</executionStack>
<inputbuf>
(@P0 bigint,@P1 int)select pssiteitem0_.REFERENCE_ID as REFERENCE1_37_, pssiteitem0_.CONTEXT_ID as CONTEXT2_37_,
pssiteitem0_.SITE_ID as SITE3_37_, pssiteitem0_.UNPUBLISH_INFO as UNPUBLISH4_37_, pssiteitem0_.VERSION as VERSION37_
from database.dbo.PSX_PUBLICATION_SITE_ITEM pssiteitem0_, database.dbo.RXSITES pssite1_, database.dbo.PSX_PUBLICATION_DOC pspubitem2_
where pssiteitem0_.REFERENCE_ID=pspubitem2_.REFERENCE_ID and pssiteitem0_.SITE_ID=pssite1_.SITEID and pssiteitem0_.SITE_ID= @P0
and pssiteitem0_.CONTEXT_ID= @P1 and pspubitem2_.STATUS=pspubitem2_.OPERATION
</inputbuf>
</process>
<process id="process4e48bc8" taskpriority="0" logused="98960" waitresource="PAGE: 5:1:6968174" waittime="24" ownerId="1493312850" transactionname="implicit_transaction" lasttranstarted="2012-04-04T15:19:52.987" XDES="0x26163eb60" lockMode="IX" schedulerid="8" kpid="63104" status="suspended" spid="90" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-04-04T15:20:06.870" lastbatchcompleted="2012-04-04T15:20:06.863" clientapp="jTDS" hostname="hostname" hostpid="123" loginname="LOGIN" isolationlevel="read committed (2)" xactid="1493312850" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="118" sqlhandle="0x020000003e76203748e31becf98a2dd2b3c36d3f9d5e5ae2">
insert into database.dbo.PSX_PUBLICATION_SITE_ITEM (CONTEXT_ID, SITE_ID, UNPUBLISH_INFO, VERSION, REFERENCE_ID) values
( @P0 , @P1 , @P2 , @P3 , @P4 )
</frame>
</executionStack>
<inputbuf>
(@P0 int,@P1 bigint,@P2 varbinary(8000),@P3 int,@P4 bigint)insert into database.dbo.PSX_PUBLICATION_SITE_ITEM
(CONTEXT_ID, SITE_ID, UNPUBLISH_INFO, VERSION, REFERENCE_ID) values ( @P0 , @P1 , @P2 , @P3 , @P4 )
</inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="6824253" dbid="5" objectname="database.dbo.PSX_PUBLICATION_SITE_ITEM" id="lock1b208e400" mode="IX" associatedObjectId="72057594180927488">
<owner-list>
<owner id="process4e48bc8" mode="IX" />
</owner-list>
<waiter-list>
<waiter id="processa2db88" mode="S" requestType="wait" />
</waiter-list>
</pagelock>
<exchangeEvent id="Pipe225726400" WaitType="e_waitPipeGetRow" nodeId="0">
<owner-list>
<owner id="processa2db88" />
</owner-list>
<waiter-list>
<waiter id="process21a958748" />
</waiter-list>
</exchangeEvent>
<pagelock fileid="1" pageid="6968174" dbid="5" objectname="database.dbo.PSX_PUBLICATION_SITE_ITEM" id="locke1d49980" mode="S" associatedObjectId="72057594180927488">
<owner-list>
<owner id="process21a958748" mode="S" />
</owner-list>
<waiter-list>
<waiter id="process4e48bc8" mode="IX" requestType="wait" />
</waiter-list>
</pagelock>
</resource-list>
</deadlock>
</deadlock-list>';
select
ExchangeLock.Process.value('../../@id', 'varchar(100)') AS LockID,
ExchangeLock.Process.value('@id', 'varchar(200)') AS ProcessId,
CASE WHEN ExchangeLock.Process.value('@id', 'varchar(200)') IS NOT NULL THEN 'ExchangeEvent'
ELSE 'Unknown' END AS LockType,
ExchangeLock.Process.value('../../@objectname','sysname') AS ObjectName,
ExchangeLock.Process.value('@mode', 'varchar(10)') AS LockMode
from @DeadlockGraph.nodes('/deadlock-list/deadlock/resource-list') AS Lock(list)
OUTER APPLY Lock.list.nodes('exchangeEvent/owner-list/owner') AS ExchangeLock(Process)
;
SELECT --DeadlockID,
COALESCE(PageLock.Process.value('../../@id', 'varchar(100)'),
KeyLock.Process.value('../../@id', 'varchar(100)'),
RIDLock.Process.value('../../@id', 'varchar(100)'),
ObjLock.Process.value('../../@id', 'varchar(100)'),
ExchangeLock.Process.value('../../@id', 'varchar(100)')
) AS LockID,
COALESCE(PageLock.Process.value('@id', 'varchar(200)'),
KeyLock.Process.value('@id', 'varchar(200)'),
RIDLock.Process.value('@id', 'varchar(200)'),
ObjLock.Process.value('@id', 'varchar(200)'),
ExchangeLock.Process.value('@id', 'varchar(200)')
) AS ProcessId,
CASE WHEN PageLock.Process.value('@id', 'varchar(200)') IS NOT NULL THEN 'Page'
WHEN KeyLock.Process.value('@id', 'varchar(200)') IS NOT NULL THEN 'Key'
WHEN RIDLock.Process.value('@id', 'varchar(200)') IS NOT NULL THEN 'RID'
WHEN ObjLock.Process.value('@id', 'varchar(200)') IS NOT NULL THEN 'Object'
WHEN ExchangeLock.Process.value('@id', 'varchar(200)') IS NOT NULL THEN 'ExchangeEvent'
ELSE 'Unknown' END AS LockType,
COALESCE(PageLock.Process.value('../../@objectname', 'sysname'),
KeyLock.Process.value('../../@objectname', 'sysname') + '.' + KeyLock.Process.value('../../@indexname', 'sysname'),
RIDLock.Process.value('../../@objectname', 'sysname'),
ObjLock.Process.value('../../@objectname', 'sysname'),
ExchangeLock.Process.value('../../@objectname','sysname')
) AS ObjectName,
COALESCE(PageLock.Process.value('@mode', 'varchar(10)'),
KeyLock.Process.value('@mode', 'varchar(10)'),
RIDLock.Process.value('@mode', 'varchar(10)'),
ObjLock.Process.value('@mode', 'varchar(10)'),
ExchangeLock.Process.value('@mode', 'varchar(10)')
) AS LockMode
FROM @DeadlockGraph.nodes('/deadlock-list/deadlock/resource-list') AS Lock(list)
OUTER APPLY Lock.list.nodes('pagelock/owner-list/owner') AS PageLock(Process)
OUTER APPLY Lock.list.nodes('keylock/owner-list/owner') AS KeyLock(Process)
OUTER APPLY Lock.list.nodes('ridlock/owner-list/owner') AS RIDLock(Process)
OUTER APPLY Lock.list.nodes('objectlock/owner-list/owner') AS ObjLock(Process)
OUTER APPLY Lock.list.nodes('exchangeEvent/owner-list/owner') AS ExchangeLock(Process)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
I moved the check for ExchangeLock process value = ID earlier in your CASE statement.
SELECT --DeadlockID,
COALESCE(PageLock.Process.value('../../@id', 'varchar(100)'),
KeyLock.Process.value('../../@id', 'varchar(100)'),
RIDLock.Process.value('../../@id', 'varchar(100)'),
ObjLock.Process.value('../../@id', 'varchar(100)'),
ExchangeLock.Process.value('../../@id', 'varchar(100)')
) AS LockID,
COALESCE(PageLock.Process.value('@id', 'varchar(200)'),
KeyLock.Process.value('@id', 'varchar(200)'),
RIDLock.Process.value('@id', 'varchar(200)'),
ObjLock.Process.value('@id', 'varchar(200)'),
ExchangeLock.Process.value('@id', 'varchar(200)')
) AS ProcessId,
CASE WHEN ExchangeLock.Process.value('@id', 'varchar(200)') IS NOT NULL THEN 'ExchangeEvent'
WHEN PageLock.Process.value('@id', 'varchar(200)') IS NOT NULL THEN 'Page'
WHEN KeyLock.Process.value('@id', 'varchar(200)') IS NOT NULL THEN 'Key'
WHEN RIDLock.Process.value('@id', 'varchar(200)') IS NOT NULL THEN 'RID'
WHEN ObjLock.Process.value('@id', 'varchar(200)') IS NOT NULL THEN 'Object'
ELSE 'Unknown' END AS LockType,
COALESCE(PageLock.Process.value('../../@objectname', 'sysname'),
KeyLock.Process.value('../../@objectname', 'sysname') + '.' + KeyLock.Process.value('../../@indexname', 'sysname'),
RIDLock.Process.value('../../@objectname', 'sysname'),
ObjLock.Process.value('../../@objectname', 'sysname'),
ExchangeLock.Process.value('../../@objectname','sysname')
) AS ObjectName,
COALESCE(PageLock.Process.value('@mode', 'varchar(10)'),
KeyLock.Process.value('@mode', 'varchar(10)'),
RIDLock.Process.value('@mode', 'varchar(10)'),
ObjLock.Process.value('@mode', 'varchar(10)'),
ExchangeLock.Process.value('@mode', 'varchar(10)')
) AS LockMode
FROM @DeadlockGraph.nodes('/deadlock-list/deadlock/resource-list') AS Lock(list)
OUTER APPLY Lock.list.nodes('pagelock/owner-list/owner') AS PageLock(Process)
OUTER APPLY Lock.list.nodes('keylock/owner-list/owner') AS KeyLock(Process)
OUTER APPLY Lock.list.nodes('ridlock/owner-list/owner') AS RIDLock(Process)
OUTER APPLY Lock.list.nodes('objectlock/owner-list/owner') AS ObjLock(Process)
OUTER APPLY Lock.list.nodes('exchangeEvent/owner-list/owner') AS ExchangeLock(Process)
Not sure how this will affect the overall results that you seek but it may give you a hint about what you need to do.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
Wayne, believe it or not, I've been playing with the script in your blog in the last few days and I have noticed that in some cases the deadlock XML doesn't get shredded correctly.
Basically, it's the COALESCE/CROSS APPLY chain that works only when the same type of locks/waits is found on both processes, but doesn't work in all cases.
This is what I came up with:
IF OBJECT_ID('tempdb..#deadlockinfo') IS NOT NULL
DROP TABLE #deadlockinfo;
DECLARE @DeadlockGraph XML;
SET @DeadlockGraph =
'<deadlock-list>
<deadlock victim="processa2db88">
<process-list>
<process id="processa2db88" taskpriority="0" logused="0" waitresource="PAGE: 5:1:6824253" waittime="10933" ownerId="1493304634" transactionname="implicit_transaction" lasttranstarted="2012-04-04T15:19:42.193" XDES="0x20cf52730" lockMode="S" schedulerid="3" kpid="68992" status="suspended" spid="81" sbid="0" ecid="3" priority="0" trancount="0" lastbatchstarted="2012-04-04T15:19:44.810" lastbatchcompleted="2012-04-04T15:19:44.807" clientapp="jTDS" hostname="hostname" hostpid="123" isolationlevel="read committed (2)" xactid="1493304634" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="40" sqlhandle="0x020000007ecd772bc607c482a24c941ad99dd40ef170ae1e">
select pssiteitem0_.REFERENCE_ID as REFERENCE1_37_, pssiteitem0_.CONTEXT_ID as CONTEXT2_37_, pssiteitem0_.SITE_ID as SITE3_37_,
pssiteitem0_.UNPUBLISH_INFO as UNPUBLISH4_37_, pssiteitem0_.VERSION as VERSION37_ from database.dbo.PSX_PUBLICATION_SITE_ITEM
pssiteitem0_, database.dbo.RXSITES pssite1_, database.dbo.PSX_PUBLICATION_DOC pspubitem2_
where pssiteitem0_.REFERENCE_ID=pspubitem2_.REFERENCE_ID and pssiteitem0_.SITE_ID=pssite1_.SITEID and pssiteitem0_.SITE_ID= @P0
and pssiteitem0_.CONTEXT_ID= @P1 and pspubitem2_.STATUS=pspubitem2_.OPERATION
</frame>
</executionStack>
<inputbuf />
</process>
<process id="process21a958748" taskpriority="0" logused="10000" waittime="10430" schedulerid="2" kpid="67784" status="suspended" spid="81" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2012-04-04T15:19:44.810" lastbatchcompleted="2012-04-04T15:19:44.807" clientapp="jTDS" hostname="hostname" hostpid="123" loginname="LOGIN" isolationlevel="read committed (2)" xactid="1493304634" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="40" sqlhandle="0x020000007ecd772bc607c482a24c941ad99dd40ef170ae1e">
select pssiteitem0_.REFERENCE_ID as REFERENCE1_37_, pssiteitem0_.CONTEXT_ID as CONTEXT2_37_, pssiteitem0_.SITE_ID as SITE3_37_,
pssiteitem0_.UNPUBLISH_INFO as UNPUBLISH4_37_, pssiteitem0_.VERSION as VERSION37_ from database.dbo.PSX_PUBLICATION_SITE_ITEM pssiteitem0_,
database.dbo.RXSITES pssite1_, database.dbo.PSX_PUBLICATION_DOC pspubitem2_ where pssiteitem0_.REFERENCE_ID=pspubitem2_.REFERENCE_ID
and pssiteitem0_.SITE_ID=pssite1_.SITEID and pssiteitem0_.SITE_ID= @P0 and pssiteitem0_.CONTEXT_ID= @P1
and pspubitem2_.STATUS=pspubitem2_.OPERATION
</frame>
</executionStack>
<inputbuf>
(@P0 bigint,@P1 int)select pssiteitem0_.REFERENCE_ID as REFERENCE1_37_, pssiteitem0_.CONTEXT_ID as CONTEXT2_37_,
pssiteitem0_.SITE_ID as SITE3_37_, pssiteitem0_.UNPUBLISH_INFO as UNPUBLISH4_37_, pssiteitem0_.VERSION as VERSION37_
from database.dbo.PSX_PUBLICATION_SITE_ITEM pssiteitem0_, database.dbo.RXSITES pssite1_, database.dbo.PSX_PUBLICATION_DOC pspubitem2_
where pssiteitem0_.REFERENCE_ID=pspubitem2_.REFERENCE_ID and pssiteitem0_.SITE_ID=pssite1_.SITEID and pssiteitem0_.SITE_ID= @P0
and pssiteitem0_.CONTEXT_ID= @P1 and pspubitem2_.STATUS=pspubitem2_.OPERATION
</inputbuf>
</process>
<process id="process4e48bc8" taskpriority="0" logused="98960" waitresource="PAGE: 5:1:6968174" waittime="24" ownerId="1493312850" transactionname="implicit_transaction" lasttranstarted="2012-04-04T15:19:52.987" XDES="0x26163eb60" lockMode="IX" schedulerid="8" kpid="63104" status="suspended" spid="90" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-04-04T15:20:06.870" lastbatchcompleted="2012-04-04T15:20:06.863" clientapp="jTDS" hostname="hostname" hostpid="123" loginname="LOGIN" isolationlevel="read committed (2)" xactid="1493312850" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="118" sqlhandle="0x020000003e76203748e31becf98a2dd2b3c36d3f9d5e5ae2">
insert into database.dbo.PSX_PUBLICATION_SITE_ITEM (CONTEXT_ID, SITE_ID, UNPUBLISH_INFO, VERSION, REFERENCE_ID) values
( @P0 , @P1 , @P2 , @P3 , @P4 )
</frame>
</executionStack>
<inputbuf>
(@P0 int,@P1 bigint,@P2 varbinary(8000),@P3 int,@P4 bigint)insert into database.dbo.PSX_PUBLICATION_SITE_ITEM
(CONTEXT_ID, SITE_ID, UNPUBLISH_INFO, VERSION, REFERENCE_ID) values ( @P0 , @P1 , @P2 , @P3 , @P4 )
</inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="6824253" dbid="5" objectname="database.dbo.PSX_PUBLICATION_SITE_ITEM" id="lock1b208e400" mode="IX" associatedObjectId="72057594180927488">
<owner-list>
<owner id="process4e48bc8" mode="IX" />
</owner-list>
<waiter-list>
<waiter id="processa2db88" mode="S" requestType="wait" />
</waiter-list>
</pagelock>
<exchangeEvent id="Pipe225726400" WaitType="e_waitPipeGetRow" nodeId="0">
<owner-list>
<owner id="processa2db88" />
</owner-list>
<waiter-list>
<waiter id="process21a958748" />
</waiter-list>
</exchangeEvent>
<pagelock fileid="1" pageid="6968174" dbid="5" objectname="database.dbo.PSX_PUBLICATION_SITE_ITEM" id="locke1d49980" mode="S" associatedObjectId="72057594180927488">
<owner-list>
<owner id="process21a958748" mode="S" />
</owner-list>
<waiter-list>
<waiter id="process4e48bc8" mode="IX" requestType="wait" />
</waiter-list>
</pagelock>
</resource-list>
</deadlock>
</deadlock-list>'
;
DECLARE @deadlock TABLE (
DeadlockID INT IDENTITY PRIMARY KEY CLUSTERED
,DeadlockGraph XML
);
-- use below to load a deadlock trace file
INSERT INTO @deadlock (DeadlockGraph)
VALUES (@DeadlockGraph);
/*
-- use below to load individual deadlocks.
INSERT INTO @deadlock VALUES ('');
-- Insert the deadlock XML in the above line!
-- Duplicate as necessary for additional graphs.
*/
WITH CTE
AS (
SELECT DeadlockID
,DeadlockGraph
FROM @deadlock
)
,Locks (DeadlockID, LockId, ProcessID, LockType, ObjectName, LockDbid, LockMode, LockPtnId)
AS (
-- Merge all of the lock information together.
SELECT DeadlockID, lockinfo.*
FROM CTE
CROSS APPLY CTE.DeadlockGraph.nodes('/deadlock-list/deadlock') AS Lock(list)
CROSS APPLY (
SELECT LockNode.Process.value('@id', 'varchar(200)')
,LockType.Process.value('@id', 'varchar(200)')
,REPLACE(LockNode.Process.value('local-name(.)', 'varchar(50)'), 'lock', SPACE(0))
,LockNode.Process.value('@objectname', 'sysname')
,LockNode.Process.value('@dbid', 'int')
,LockType.Process.value('@mode', 'varchar(10)')
,LockNode.Process.value('@associatedObjectId', 'bigint')
FROM Lock.list.nodes('resource-list/pagelock,resource-list/keylock,resource-list/ridlock,resource-list/objlock,resource-list/objectlock,resource-list/exchangeEvent') AS LockNode(Process)
CROSS APPLY LockNode.Process.nodes('owner-list/owner') AS LockType(Process)
) AS lockinfo(LockId, ProcessID, LockType, ObjectName, dbid, LockMode, associatedObjectId)
WHERE lockinfo.ProcessID IS NOT NULL
)
,Waits (DeadlockID, LockId, ProcessID, WaitType, ObjectName, WaitDbid, WaitMode, WaitPtnId)
AS (
-- Merge all of the wait information together.
SELECT DeadlockID, waitinfo.*
FROM CTE
CROSS APPLY CTE.DeadlockGraph.nodes('/deadlock-list/deadlock') AS Lock(list)
CROSS APPLY (
SELECT WaitNode.Process.value('@id', 'varchar(200)')
,WaitType.Process.value('@id', 'varchar(200)')
,REPLACE(WaitNode.Process.value('local-name(.)', 'varchar(50)'), 'lock', SPACE(0))
,WaitNode.Process.value('@objectname', 'sysname')
,WaitNode.Process.value('@dbid', 'int')
,WaitType.Process.value('@mode', 'varchar(10)')
,WaitNode.Process.value('@associatedObjectId', 'bigint')
FROM Lock.list.nodes('resource-list/pagelock,resource-list/keylock,resource-list/ridlock,resource-list/objlock,resource-list/objectlock,resource-list/exchangeEvent') AS WaitNode(Process)
CROSS APPLY WaitNode.Process.nodes('waiter-list/waiter') AS WaitType(Process)
) AS waitinfo(LockId, ProcessID, WaitType, ObjectName, WaitDbid, WaitMode, associatedObjectId)
WHERE waitinfo.ProcessID IS NOT NULL
)
,Process
AS (
-- get the data from the process node
SELECT CTE.DeadlockID
,[Victim] = CONVERT(BIT, CASE
WHEN Deadlock.Process.value('@id', 'varchar(50)') = Lock.list.value('@victim', 'varchar(50)')
THEN 1
ELSE 0
END)
,[LockMode] = Deadlock.Process.value('@lockMode', 'varchar(10)')
,-- how is this different from in the resource-list section?
[ProcessID] = Deadlock.Process.value('@id', 'varchar(50)')
,[KPID] = Deadlock.Process.value('@kpid', 'int')
,-- kernel-process id / thread ID number
[SPID] = Deadlock.Process.value('@spid', 'int')
,-- system process id (connection to sql)
[SBID] = Deadlock.Process.value('@sbid', 'int')
,-- system batch id / request_id (a query that a SPID is running)
[ECID] = Deadlock.Process.value('@ecid', 'int')
,-- execution context ID (a worker thread running part of a query)
[IsolationLevel] = Deadlock.Process.value('@isolationlevel', 'varchar(200)')
,[WaitResource] = Deadlock.Process.value('@waitresource', 'varchar(200)')
,[LogUsed] = Deadlock.Process.value('@logused', 'int')
,[ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)')
,[HostName] = Deadlock.Process.value('@hostname', 'varchar(20)')
,[LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)')
,[TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime')
,[BatchStarted] = Deadlock.Process.value('@lastbatchstarted', 'datetime')
,[BatchCompleted] = Deadlock.Process.value('@lastbatchcompleted', 'datetime')
,[InputBuffer] = Input.Buffer.query('.')
,CTE.[DeadlockGraph]
,es.ExecutionStack
,[QueryStatement] = Execution.Frame.value('.', 'varchar(max)')
,ProcessQty = SUM(1) OVER (PARTITION BY CTE.DeadlockID)
FROM CTE
CROSS APPLY CTE.DeadlockGraph.nodes('/deadlock-list/deadlock') AS Lock(list)
CROSS APPLY Lock.list.nodes('process-list/process') AS Deadlock(Process)
CROSS APPLY Deadlock.Process.nodes('inputbuf') AS Input(Buffer)
CROSS APPLY Deadlock.Process.nodes('executionStack') AS Execution(Frame)
-- get the data from the executionStack node as XML
CROSS APPLY (
SELECT ExecutionStack = (
SELECT ProcNumber = ROW_NUMBER() OVER (
PARTITION BY CTE.DeadlockID
,Deadlock.Process.value('@id', 'varchar(50)')
,Execution.Stack.value('@procname', 'sysname')
,Execution.Stack.value('@code', 'varchar(MAX)')
ORDER BY (SELECT 1)
)
,ProcName = Execution.Stack.value('@procname', 'sysname')
,Line = Execution.Stack.value('@line', 'int')
,SQLHandle = Execution.Stack.value('@sqlhandle', 'varchar(64)')
,Code = LTRIM(RTRIM(Execution.Stack.value('.', 'varchar(MAX)')))
FROM Execution.Frame.nodes('frame') AS Execution(Stack)
ORDER BY ProcNumber
FOR XML PATH('frame')
,ROOT('executionStack')
,TYPE
)
) es
)
-- get the columns in the desired order
SELECT p.DeadlockID
,p.Victim
,p.ProcessQty
,p.LockMode
,p.ProcessID
,p.KPID
,p.SPID
,p.SBID
,p.ECID
,l.LockType
,LockedObject = NULLIF(l.ObjectName, '')
,LockPtnId
,LockDbid
,LockedMode = l.LockMode
,w.WaitType
,WaitObject = NULLIF(w.ObjectName, '')
,WaitPtnId
,WaitDbid
,w.WaitMode
,p.WaitResource
,p.IsolationLevel
,p.LogUsed
,p.ClientApp
,p.HostName
,p.LoginName
,p.TransactionTime
,p.BatchStarted
,p.BatchCompleted
,p.INPUTBUFFER
,p.DeadlockGraph
,p.ExecutionStack
INTO #deadlockinfo
FROM Process p
LEFT JOIN Locks l
ON p.ProcessID = l.ProcessID
AND p.DeadlockID = l.DeadlockID
LEFT JOIN Waits w
ON p.DeadlockID = w.DeadlockID
AND p.ProcessID = w.ProcessID
ORDER BY p.DeadlockId
,p.Victim DESC
,p.ProcessId;
DECLARE @sql nvarchar(max);
SET @sql = (
SELECT queries AS [text()]
FROM (
SELECT 'EXEC ' + QUOTENAME(DB_NAME(LockDbid)) + '.sys.sp_executesql N''' + REPLACE('
UPDATE #deadlockinfo
SET LockedObject = (
SELECT QUOTENAME(DB_NAME()) + ''.'' +
QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) + ''.'' +
QUOTENAME(OBJECT_NAME([object_id]))
FROM sys.partitions
WHERE partition_id = LockPtnId
UNION ALL
SELECT QUOTENAME(DB_NAME()) + ''.'' +
QUOTENAME(OBJECT_SCHEMA_NAME([LockPtnId])) + ''.'' +
QUOTENAME(OBJECT_NAME([LockPtnId]))
FROM sys.objects
WHERE object_id = LockPtnId
);','''', '''''') + '''' + CHAR(10)
FROM #deadLockInfo
WHERE LockedObject IS NULL
UNION ALL
SELECT 'EXEC ' + QUOTENAME(DB_NAME(WaitDbid)) + '.sys.sp_executesql N''' + REPLACE('
UPDATE #deadlockinfo
SET WaitObject = (
SELECT QUOTENAME(DB_NAME()) + ''.'' +
QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) + ''.'' +
QUOTENAME(OBJECT_NAME([object_id]))
FROM sys.partitions
WHERE partition_id = WaitPtnId
UNION ALL
SELECT QUOTENAME(DB_NAME()) + ''.'' +
QUOTENAME(OBJECT_SCHEMA_NAME([WaitPtnId])) + ''.'' +
QUOTENAME(OBJECT_NAME([WaitPtnId]))
FROM sys.objects
WHERE object_id = WaitPtnId
);','''', '''''') + '''' + CHAR(10)
FROM #deadLockInfo
WHERE WaitObject IS NULL
) AS src(queries)
FOR XML PATH('')
);
SET @sql = REPLACE(@sql, ' ', CHAR(13));
EXEC sp_executesql @sql;
SELECT *
FROM #deadLockInfo;
Hope this helps,
Gianluca
P.S.: thanks for the great script!
-- Gianluca Sartori
-
See if this helps
select
MainLock.Process.value('@id', 'varchar(100)') AS LockID,
OwnerList.Owner.value('@id', 'varchar(200)') AS ProcessId,
MainLock.Process.value('local-name(.)','varchar(100)') AS LockType,
MainLock.Process.value('@objectname','sysname') AS ObjectName,
OwnerList.Owner.value('@mode', 'varchar(10)') AS LockMode
from @DeadlockGraph.nodes('/deadlock-list/deadlock/resource-list') AS Lock(list)
OUTER APPLY Lock.list.nodes('(pagelock,keylock,ridlock,objectlock,exchangeEvent)') AS MainLock(Process)
OUTER APPLY MainLock.Process.nodes('owner-list/owner') AS OwnerList(Owner)
;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Gianluca,
I think I've run across the same limitation, and I've been revamping this script from being process-based to being lock-based. I wanted to add the intra-query parallel processing items, and was having trouble.
I posted a newer script over the weekend, and thanks to Mark's code below, it looks like I'll be having a newer version yet soon.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
Mark-101232 (9/4/2012)
See if this helpsselect
MainLock.Process.value('@id', 'varchar(100)') AS LockID,
OwnerList.Owner.value('@id', 'varchar(200)') AS ProcessId,
MainLock.Process.value('local-name(.)','varchar(100)') AS LockType,
MainLock.Process.value('@objectname','sysname') AS ObjectName,
OwnerList.Owner.value('@mode', 'varchar(10)') AS LockMode
from @DeadlockGraph.nodes('/deadlock-list/deadlock/resource-list') AS Lock(list)
OUTER APPLY Lock.list.nodes('(pagelock,keylock,ridlock,objectlock,exchangeEvent)') AS MainLock(Process)
OUTER APPLY MainLock.Process.nodes('owner-list/owner') AS OwnerList(Owner)
;
Mark,
This is so seriously cool. I didn't know that you could specify the various elements like that, or retrieve back the node name. And best of all... it works!
Time to go revamp my script yet again.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
WayneS (9/4/2012)
Gianluca,I think I've run across the same limitation, and I've been revamping this script from being process-based to being lock-based. I wanted to add the intra-query parallel processing items, and was having trouble.
I posted a newer script over the weekend, and thanks to Mark's code below, it looks like I'll be having a newer version yet soon.
Thanks for the update!
If you look at the code I posted, you'll see that I used the same technique as Mark. It's incredibly useful when you have to work with multiple nodes at once.
Another thing that I added is the object name lookup on sys.partitions on the originating database.
I was planning to blog it, but I'd be happy to help you with your post instead.
-- Gianluca Sartori
-
Mark-101232 (9/4/2012)
See if this helpsselect
MainLock.Process.value('@id', 'varchar(100)') AS LockID,
OwnerList.Owner.value('@id', 'varchar(200)') AS ProcessId,
MainLock.Process.value('local-name(.)','varchar(100)') AS LockType,
MainLock.Process.value('@objectname','sysname') AS ObjectName,
OwnerList.Owner.value('@mode', 'varchar(10)') AS LockMode
from @DeadlockGraph.nodes('/deadlock-list/deadlock/resource-list') AS Lock(list)
OUTER APPLY Lock.list.nodes('(pagelock,keylock,ridlock,objectlock,exchangeEvent)') AS MainLock(Process)
OUTER APPLY MainLock.Process.nodes('owner-list/owner') AS OwnerList(Owner)
;
Mark,
FYI: I changed the one line to:
select
MainLock.Process.value('@id', 'varchar(100)') AS LockID,
OwnerList.Owner.value('@id', 'varchar(200)') AS ProcessId,
MainLock.Process.value('local-name(.)','varchar(100)') AS LockType,
MainLock.Process.value('@objectname','sysname') AS ObjectName,
OwnerList.Owner.value('@mode', 'varchar(10)') AS LockMode
from @DeadlockGraph.nodes('/deadlock-list/deadlock/resource-list') AS Lock(list)
OUTER APPLY Lock.list.nodes('*') AS MainLock(Process)
OUTER APPLY MainLock.Process.nodes('owner-list/owner') AS OwnerList(Owner)
;
so that it will handle any node at that level. Looking great, and now it will handle the threadpool and resourceWait locks.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
Gianluca Sartori (9/4/2012)
WayneS (9/4/2012)
Gianluca,I think I've run across the same limitation, and I've been revamping this script from being process-based to being lock-based. I wanted to add the intra-query parallel processing items, and was having trouble.
I posted a newer script over the weekend, and thanks to Mark's code below, it looks like I'll be having a newer version yet soon.
Thanks for the update!
If you look at the code I posted, you'll see that I used the same technique as Mark. It's incredibly useful when you have to work with multiple nodes at once.
Another thing that I added is the object name lookup on sys.partitions on the originating database.
I was planning to blog it, but I'd be happy to help you with your post instead.
I didn't catch that - neat idea. I don't think I want to include it in my script though, since I use this when I don't have access to the database. If you blog it, I'll link to it.
Did you see the change I made where it is based off of the locks instead of the process? What do you think of that?
Edit: I will include the dbid and associatedObjectId though.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
WayneS (9/4/2012)
I didn't catch that - neat idea. I don't think I want to include it in my script though, since I use this when I don't have access to the database.Yes, when you don't have access to the database the script could be misleading. I didn't think of it.
If you blog it, I'll link to it.
Thanks, it's very kind of you!
Did you see the change I made where it is based off of the locks instead of the process? What do you think of that?
Not yet. I'll give it a look tomorrow. Looks very promising!
-- Gianluca Sartori
-
This is so COOL! I was starting to look at how to do this and thought "I'll just search it to see if anyone has done this..." and WOW! Works right out of the gate, no questions asked. Very nice!
Now to work through getting the developers to fix the issue... Anyone have a script for that? 😀
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
David Benoit (2/14/2014)
This is so COOL! I was starting to look at how to do this and thought "I'll just search it to see if anyone has done this..." and WOW! Works right out of the gate, no questions asked. Very nice!Now to work through getting the developers to fix the issue... Anyone have a script for that? 😀
Hi David!
The latest version of this script is at: http://blog.waynesheffield.com/wayne/code-library/deadlocks/shred-deadlock-graph/[/url]
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
Thanks, Wayne. I'll check that out. I have a working version from what was shared earlier but will try to implement this in the next few days. Since we are using SQL Sentry I have all the deadlock graphs logged, and can work through those, parse, save to another table, and analyze in greater detail. A gold mine of analysis data... 🙂
Thanks again.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply