Today we had an instance of deadlock and I was able to see that using the below code..
select CONVERT( xml, XEventData.XEvent.value('(data/value)[1]', 'varchar(max)')) as DeadlockGraph
FROM (select CAST(target_data as xml) as TargetData
from sys.dm_xe_session_targets st
join sys.dm_xe_sessions s on s.address = st.event_session_address
where name = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'
And the output:
<deadlock>
<victim-list>
<victimProcess id="process6430748" />
</victim-list>
<process-list>
<process id="process6430748" taskpriority="0" logused="0" waitresource="PAGE: 17:22:8689" waittime="2293" ownerId="2733945332" transactionname="INSERT" lasttranstarted="2012-09-18T09:37:34.633" XDES="0x29f702a900" lockMode="S" schedulerid="13" kpid="2916" status="suspended" spid="137" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-09-18T09:37:34.533" lastbatchcompleted="2012-09-18T09:37:34.533" clientapp="cogent.asset" hostname="-1:war-cog-tml-01v.asset.local" hostpid="2584" loginname="CogentUser" isolationlevel="read committed (2)" xactid="2733945332" currentdb="17" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128568">
<executionStack>
<frame procname="" line="245" stmtstart="20606" stmtend="23180" sqlhandle="0x030011008ad0af3e987a070149a000000100000000000000" />
</executionStack>
<inputbuf>
Proc [Database Id = 17 Object Id = 1051709578] </inputbuf>
</process>
<process id="process7c33288" taskpriority="0" logused="27340" waitresource="PAGE: 17:22:8434" waittime="2623" ownerId="2733257730" transactionname="INSERT" lasttranstarted="2012-09-18T09:37:13.997" XDES="0x39e9f11950" lockMode="IX" schedulerid="11" kpid="12084" status="suspended" spid="69" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-09-18T09:37:13.890" lastbatchcompleted="2012-09-18T09:37:13.887" lastattention="2012-09-18T05:37:50.263" clientapp="cogent.asset" hostname="-1:WAR-COG-TML-03V.asset.local" hostpid="3740" loginname="CogentUser" isolationlevel="read committed (2)" xactid="2733257730" currentdb="17" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128568">
<executionStack>
<frame procname="" line="245" stmtstart="20606" stmtend="23180" sqlhandle="0x030011008ad0af3e987a070149a000000100000000000000" />
</executionStack>
<inputbuf>
Proc [Database Id = 17 Object Id = 1051709578] </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="22" pageid="8689" dbid="17" objectname="" id="lock3676adf200" mode="IX" associatedObjectId="72057607072907264">
<owner-list>
<owner id="process7c33288" mode="IX" />
</owner-list>
<waiter-list>
<waiter id="process6430748" mode="S" requestType="wait" />
</waiter-list>
</pagelock>
<pagelock fileid="22" pageid="8434" dbid="17" objectname="" id="lock36772e3700" mode="S" associatedObjectId="72057607072907264">
<owner-list>
<owner id="process6430748" mode="S" />
</owner-list>
<waiter-list>
<waiter id="process7c33288" mode="IX" requestType="wait" />
</waiter-list>
</pagelock>
</resource-list>
</deadlock>
It was case the same SP is being called by different App/User and deadlocking in somewhere. Its a complex SP, we are investigating to optimize the code.
This is awesome! without having to setup anything. I wish we had pictorial representation as well for presentation 🙂
...
We are having multiple deadlock issues on different jobs/Procs, so will it be able to show all that occurrence or just the most recent one?
Thanks