SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Retrieving Deadlock Graphs with SQL Server 2008 Extended Events


Retrieving Deadlock Graphs with SQL Server 2008 Extended Events

Author
Message
hello mahesh
hello mahesh
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 255
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
john.m.couch@gmail.com
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 339
Did you look at the script I provided? IT has everything you need to do scripted in it.
Jonathan Kehayias
Jonathan Kehayias
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6199 Visits: 1818
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
john.m.couch@gmail.com
john.m.couch@gmail.com
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 339
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
Jonathan Kehayias
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6199 Visits: 1818
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
hello mahesh
hello mahesh
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 255
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.
hello mahesh
hello mahesh
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 255
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 Smile

...

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
john.m.couch@gmail.com
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 339
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 Karney
Jared Karney
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12920 Visits: 3697
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
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
douglar
douglar
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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)





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search