Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Issue shredding Deadlock Graph XML Expand / Collapse
Author
Message
Posted Sunday, September 2, 2012 2:27 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 5,367, Visits: 8,986
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1353278
Posted Sunday, September 2, 2012 6:41 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:53 AM
Points: 3,422, Visits: 5,368
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1353294
Posted Tuesday, September 4, 2012 2:47 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:47 AM
Points: 4,613, Visits: 11,014
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1353762
Posted Tuesday, September 4, 2012 3:15 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 3:53 AM
Points: 1,678, Visits: 19,555
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)
;



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1353781
Posted Tuesday, September 4, 2012 8:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 5,367, Visits: 8,986
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1353905
Posted Tuesday, September 4, 2012 8:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 5,367, Visits: 8,986
Mark-101232 (9/4/2012)
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)
;



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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1353910
Posted Tuesday, September 4, 2012 8:23 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:47 AM
Points: 4,613, Visits: 11,014
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1353930
Posted Tuesday, September 4, 2012 8:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 5,367, Visits: 8,986
Mark-101232 (9/4/2012)
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)
;



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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1353944
Posted Tuesday, September 4, 2012 9:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 5,367, Visits: 8,986
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1354008
Posted Tuesday, September 4, 2012 9:56 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:47 AM
Points: 4,613, Visits: 11,014
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1354018
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse