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 «««45678»»»

Retrieving Deadlock Graphs with SQL Server 2008 Extended Events Expand / Collapse
Author
Message
Posted Monday, September 17, 2012 6:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 12, 2014 1:10 PM
Points: 16, Visits: 205
Jonathan Kehayias (9/17/2012)
hello mahesh (9/17/2012)
Still noluck. I tried searching for key words: 'dead' , 'victim' in xml and not found any.

May be it got overlapped as the deadlock event occured couple of weeks back.

Thanks


If you restarted the instance since the deadlock occurred it won't be in the system_health event session since it is a memory resident target and not persisted across service failures/restarts.


The system was not restarted; but it is a very active OLTP system so I m guessing the event might got flushed out. Whats the amount of time or data that it hold in the queue?

The deadlocks are occurring in batch jobs and is a repeated phenomenon in the system, though for some reason it has not occurred in last two weeks. Do I need to set/check any settings in the server to capture deadlock?

Thanks
Post #1360508
Posted Monday, September 17, 2012 6:48 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 9:00 PM
Points: 14, Visits: 283
Did you look at the script I provided? IT has everything you need to do scripted in it.
Post #1360515
Posted Monday, September 17, 2012 7:52 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 1:10 PM
Points: 1,708, Visits: 1,793
hello mahesh (9/17/2012)
Jonathan Kehayias (9/17/2012)
hello mahesh (9/17/2012)
Still noluck. I tried searching for key words: 'dead' , 'victim' in xml and not found any.

May be it got overlapped as the deadlock event occured couple of weeks back.

Thanks


If you restarted the instance since the deadlock occurred it won't be in the system_health event session since it is a memory resident target and not persisted across service failures/restarts.


The system was not restarted; but it is a very active OLTP system so I m guessing the event might got flushed out. Whats the amount of time or data that it hold in the queue?

The deadlocks are occurring in batch jobs and is a repeated phenomenon in the system, though for some reason it has not occurred in last two weeks. Do I need to set/check any settings in the server to capture deadlock?

Thanks


If it's not in the session and the server has been restarted, you are either:

A: on RTM version and not collecting the graph from a bug that was fixed and should apply the latest SP
B: having tons of other resource contention issues in the instance that you should look at

You can skip all of the Extended Events stuff if you use Event Notifications, which is what John sent you the code to do. I haven't looked at his solution, but if it is based on what I did at PASS I am guessing that he just added to the solution and it should allow you to persist any deadlock to a table for later review.


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #1360524
Posted Monday, September 17, 2012 8:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 9:00 PM
Points: 14, Visits: 283
Yes Jonathon, it is from the code you provided at Pass. It persists it by sending an email notification and writing it out to a table. Either way.

On a side note, it is documented in the script (in the procedure comment section) that the code was pulled from your pass summit files and provides a link to where you had them posted.
Post #1360528
Posted Tuesday, September 18, 2012 12:34 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 1:10 PM
Points: 1,708, Visits: 1,793
Hey John,

No worries, I just haven't had the time to go look at the code with teaching at Redmond this week.


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #1360579
Posted Tuesday, September 18, 2012 1:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 12, 2014 1:10 PM
Points: 16, Visits: 205
john.m.couch (9/17/2012)
Did you look at the script I provided? IT has everything you need to do scripted in it.


Thanks for providing the script.
I have limited access to production env, hence could not use it. But have passed it to our DBA for review and implementation.
Post #1360987
Posted Tuesday, September 18, 2012 1:41 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 12, 2014 1:10 PM
Points: 16, Visits: 205
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
Post #1361004
Posted Tuesday, September 18, 2012 1:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 9:00 PM
Points: 14, Visits: 283
If you are able to implement the code I attached you will get a picture representation emailed to you and a XML representation logged to a table.
Post #1361008
Posted Wednesday, February 27, 2013 9:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 5:39 AM
Points: 2,694, Visits: 3,387
Any time I am running any of the suggested code to grab the deadlock graphs, I am looking at the xml and seeing that object_id in the resource-list is blank. Is it supposed to be this way? The reason I ask is because I found another snippet of code that parses the graph out to log the essential information to a table.

1. Script to produce graph:
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'
OPTION(MAXDOP 1)

I use the MAXDOP because parallelism makes my execution time huge the first time I run this.

2. Add in my parsing script:
;WITH log_deadlocks
AS (
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'
)
Select
--DeadlockDateTime,
DeadlockGraph,
DbName = DB_NAME(ResList.KeyLock.value('@dbid', 'INT')),
ObjectName = ResList.KeyLock.value('@objectname', 'sysname'),
IndexName = ResList.KeyLock.value('@indexname', 'sysname'),
ResultingLockMode = ResList.KeyLock.value('@mode', 'varchar(10)'),
OwnerID = OwnList.Own.value('@id', 'sysname'),
OwnerLockMode = OwnList.Own.value('@mode', 'varchar(10)'),
WaiterID = WaitList.Wait.value('@id', 'sysname'),
WaiterLockMode = WaitList.Wait.value('@mode', 'varchar(10)')
From Log_Deadlocks
-- Shred keylock node of the resource-list
Cross Apply DeadlockGraph.nodes('//resource-list/keylock') ResList(KeyLock)
-- Shred the owner node beneath the resource-list/keylock node
Cross Apply ResList.KeyLock.nodes('./owner-list/owner') OwnList(Own)
-- Shred the waiter node beneath the resource-list/keylock node
Cross Apply ResList.KeyLock.nodes('./waiter-list/waiter') WaitList(Wait);

When I look at Jonathan's Extended Event Session Explorer (We are on 2008 R2) I DO see object names for the deadlocks. Any thoughts?


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1424598
Posted Friday, June 21, 2013 7:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 5:57 AM
Points: 5, Visits: 71
SQLKnowItAll (2/27/2013)

When I look at Jonathan's Extended Event Session Explorer (We are on 2008 R2) I DO see object names for the deadlocks. Any thoughts?


I've always had to look things up by HobtId, depending on whether the object is a heap or a btree

I usually prefer to get the output with one line per deadlocked process sort of like this:

WITH log_deadlocks
AS (
SELECT CONVERT( xml, XEventData.XEvent.value('(data/value)[1]', 'varchar(max)')) as DeadlockGraph
, xEventData.xEvent.value('@timestamp', 'datetime') StartDate
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 s.name = 'system_health'
) AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name', 'varchar(400)') = 'xml_deadlock_report'
)
SELECT
DeadlockTime = StartDate
, ObjectDbName = DB_NAME( isnull(Reslist.KeyLock.value('@dbid', 'int'), Reslist2.RidLock.value('@dbid', 'int') ) )
, ObjectName = (
select quotename(s.name) + '.' + quotename(o.name)
from sys.partitions p with (nolock)
join sys.objects o with (nolock)
on o.object_id = p.object_id
join sys.schemas s with (nolock)
on s.schema_id = o.schema_id
where p.hobt_id = isnull(
Reslist.KeyLock.value('@associatedObjectId', 'bigint') -- lock is on an index
, Reslist2.RidLock.value('@associatedObjectId', 'bigint') -- lock is on a heap
)
)
, Spid = ProList.Process.value('@spid', 'int')
, Terminated = case when ProList.Process.value('@lastattention', 'datetime') is not null then 'Y' else 'N' end
, LastTranStarted = ProList.Process.value('@lasttranstarted', 'datetime')
, LoginName = ProList.Process.value('@loginname', 'sysname')
, IsolationLevel = ProList.Process.value('@isolationlevel', 'nvarchar(400)')
, InputBuf = ProList.Process.value('./inputbuf[1]', 'varchar(max)')
, InputBufLine = ProList.Process.value('./executionStack[1]/frame[1]/@line', 'int')
, SessionDbName = DB_NAME( ProList.Process.value('@currentdb', 'int') )
, HostName = ProList.Process.value('@hostname', 'nvarchar(400)')
, ClientApp = ProList.Process.value('@clientapp', 'nvarchar(400)')
, DeadlockGraph
FROM Log_Deadlocks
-- One row for each <process> node in <process-list>
CROSS APPLY DeadlockGraph.nodes('//process-list/process') ProList(Process)
-- Grab the first <keylock> from <resource-list> for an index lock
OUTER APPLY DeadlockGraph.nodes('//resource-list/keylock[1]') ResList(KeyLock)
-- Grab the first <ridlock> from <resource-list> for a heap lock
OUTER APPLY DeadlockGraph.nodes('//resource-list/ridlock[1]') ResList2(RidLock)




Post #1466183
« Prev Topic | Next Topic »

Add to briefcase «««45678»»»

Permissions Expand / Collapse