Issue shredding Deadlock Graph XML

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Issue shredding Deadlock Graph XML

Issue shredding Deadlock Graph XML

Rate Topic

Display Mode

Topic Options

Author

Message

WayneS

Posted Sunday, September 02, 2012 2:27 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:35 PM
Points: 6,369, Visits: 8,232

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

dwain.c

Posted Sunday, September 02, 2012 6:41 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 12:39 AM
Points: 2,345, Visits: 3,189

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.

No loops! No CURSORs! No RBAR! Hoo-uh!

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?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1353294

spaghettidba

Posted Tuesday, September 04, 2012 2:47 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 6:45 AM
Points: 4,804, Visits: 8,068

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!

Get your two-cent-answer quickly
The Spaghetti DBA

Post #1353762

Mark-101232

Posted Tuesday, September 04, 2012 3:15 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 4:09 AM
Points: 1,500, Visits: 18,191

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

WayneS

Posted Tuesday, September 04, 2012 8:08 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:35 PM
Points: 6,369, Visits: 8,232

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!

Post #1353905

WayneS

Posted Tuesday, September 04, 2012 8:11 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:35 PM
Points: 6,369, Visits: 8,232

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!

Post #1353910

spaghettidba

Posted Tuesday, September 04, 2012 8:23 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 6:45 AM
Points: 4,804, Visits: 8,068

WayneS (9/4/2012)

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.

Get your two-cent-answer quickly
The Spaghetti DBA

Post #1353930

WayneS

Posted Tuesday, September 04, 2012 8:37 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:35 PM
Points: 6,369, Visits: 8,232

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!

Post #1353944

WayneS

Posted Tuesday, September 04, 2012 9:47 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:35 PM
Points: 6,369, Visits: 8,232

Gianluca Sartori (9/4/2012)

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. 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!

Post #1354008

spaghettidba

Posted Tuesday, September 04, 2012 9:56 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 6:45 AM
Points: 4,804, Visits: 8,068

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!

Get your two-cent-answer quickly
The Spaghetti DBA

Post #1354018

« Prev Topic | Next Topic »

Permissions