Retrieving Deadlock Graphs with SQL Server 2008 Extended Events

Question

Post reply

Retrieving Deadlock Graphs with SQL Server 2008 Extended Events

Viewing 15 posts - 46 through 60 (of 83 total)

You must be logged in to reply to this topic. Login to reply

Mahesh Hello Mr or Mrs. 500 Points: 580 More actions · Answer 1

We had couple of deadlock issues,

but nothing is captured with this query..

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

douglar SSC Rookie Points: 43 More actions · Answer 2

hello mahesh (9/17/2012)
We had couple of deadlock issues,
but nothing is captured with this query..

First try this:

WITH SessionData as (

SELECT

XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') VarcharResults

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[@name="xml_deadlock_report"]') AS XEventData (XEvent)

)

SELECT

substring(VarcharResults, charindex( 'lasttranstarted="', VarcharResults) + len('lasttranstarted="'),19) ApproxDateGuess

, cast (

REPLACE(REPLACE(

case

-- bad tag, should be <victimProcess id="process_______"/>

when patindex( '%<victimProcess id="process_______">%', VarcharResults ) > 1 then

stuff( VarcharResults , patindex( '%<victimProcess id="process_______">%', VarcharResults )+34,0,'/')

else VarcharResults

end

, '<victim-list>', '<deadlock><victim-list>'), '<process-list>','</victim-list><process-list>')

as XML ) DeadlockTree

FROM SessionData

ORDER BY 1;

if that doesn't work, I can think of two things--

1) If you have a lot of extended events, the ring buffer may have lapped itself

2) I've seen cases where it appears like an event with bad XML gets stuck in the ring buffer and you can't see past it until the bad event gets over written.

Mahesh Hello Mr or Mrs. 500 Points: 580 More actions · Answer 3

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

john.m.couch@gmail.com SSC Veteran Points: 232 More actions · Answer 4

Attached is the script I created using Jonathon's provided code. If you replace all the values I have marked with <xxx in the script and run it, it will setup the deadlock queue for you. It works on all my 2008 and 2008 R2 environments. Change the extension to .sql

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 5

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.

Mahesh Hello Mr or Mrs. 500 Points: 580 More actions · Answer 6

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

john.m.couch@gmail.com SSC Veteran Points: 232 More actions · Answer 7

Did you look at the script I provided? IT has everything you need to do scripted in it.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 8

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.

john.m.couch@gmail.com SSC Veteran Points: 232 More actions · Answer 9

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.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 10

Hey John,

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

Mahesh Hello Mr or Mrs. 500 Points: 580 More actions · Answer 11

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.

Mahesh Hello Mr or Mrs. 500 Points: 580 More actions · Answer 12

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>

<executionStack>

<frame procname="" line="245" stmtstart="20606" stmtend="23180" sqlhandle="0x030011008ad0af3e987a070149a000000100000000000000" />

</executionStack>

<inputbuf>

Proc [Database Id = 17 Object Id = 1051709578] </inputbuf>

</process>

<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

john.m.couch@gmail.com SSC Veteran Points: 232 More actions · Answer 13

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.

Jared SSC Guru Points: 61793 More actions · Answer 14

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?

Jared
CE - Microsoft

douglar SSC Rookie Points: 43 More actions · Answer 15

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)