RE: Issue shredding Deadlock Graph XML

SSC Guru

Points: 105732

September 4, 2012 at 2:47 am

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>

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

<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