InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 21:04:02 GMT20http://www.sqlservercentral.com/Forums/Topic1353278-391-1.aspx[quote][b]WayneS (9/4/2012)[/b][hr]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. [/quote]Yes, when you don't have access to the database the script could be misleading. I didn't think of it.[quote]If you blog it, I'll link to it.[/quote]Thanks, it's very kind of you![quote]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?[/quote]Not yet. I'll give it a look tomorrow. Looks very promising!Tue, 04 Sep 2012 09:56:26 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1353278-391-1.aspx[quote][b]Gianluca Sartori (9/4/2012)[/b][hr][quote][b]WayneS (9/4/2012)[/b][hr]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.[/quote]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.[/quote]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.Tue, 04 Sep 2012 09:47:33 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1353278-391-1.aspx[quote][b]Mark-101232 (9/4/2012)[/b][hr]See if this helps[code="sql"]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 LockModefrom @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);[/code][/quote]Mark,FYI: I changed the one line to:[code="sql"]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 LockModefrom @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);[/code]so that it will handle any node at that level. Looking great, and now it will handle the threadpool and resourceWait locks.Tue, 04 Sep 2012 08:37:41 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1353278-391-1.aspx[quote][b]WayneS (9/4/2012)[/b][hr]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.[/quote]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.Tue, 04 Sep 2012 08:23:34 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1353278-391-1.aspx[quote][b]Mark-101232 (9/4/2012)[/b][hr]See if this helps[code="sql"]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 LockModefrom @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);[/code][/quote]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.Tue, 04 Sep 2012 08:11:38 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1353278-391-1.aspxGianluca,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.Tue, 04 Sep 2012 08:08:55 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1353278-391-1.aspxSee if this helps[code="sql"]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 LockModefrom @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);[/code]Tue, 04 Sep 2012 03:15:58 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1353278-391-1.aspxWayne, 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:[code]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 fileINSERT 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 CTEAS ( 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),ProcessAS ( -- 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 orderSELECT 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.ExecutionStackINTO #deadlockinfoFROM Process pLEFT JOIN Locks l ON p.ProcessID = l.ProcessID AND p.DeadlockID = l.DeadlockIDLEFT JOIN Waits w ON p.DeadlockID = w.DeadlockID AND p.ProcessID = w.ProcessIDORDER 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;[/code]Hope this helps,GianlucaP.S.: thanks for the great script!Tue, 04 Sep 2012 02:47:53 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1353278-391-1.aspxI moved the check for ExchangeLock process value = ID earlier in your CASE statement.[code="sql"]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)[/code]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.Sun, 02 Sep 2012 18:41:18 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1353278-391-1.aspxI'm working on a script that shreds deadlock graph information. Using the deadlock graph that was posted [url=http://www.sqlservercentral.com/Forums/FindPost1278601.aspx][u]here[/u][/url], 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![code="sql"]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 LockModefrom @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)[/code]Sun, 02 Sep 2012 14:27:12 GMTWayneS