September 17, 2012 at 3:11 pm
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'
September 17, 2012 at 4:00 pm
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.
September 17, 2012 at 4:11 pm
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
September 17, 2012 at 4:20 pm
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
September 17, 2012 at 4:35 pm
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.
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[/url]
September 17, 2012 at 6:15 pm
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
September 17, 2012 at 6:48 pm
Did you look at the script I provided? IT has everything you need to do scripted in it.
September 17, 2012 at 7:52 pm
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[/url]
September 17, 2012 at 8:10 pm
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.
September 18, 2012 at 12:34 am
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[/url]
September 18, 2012 at 1:21 pm
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.
September 18, 2012 at 1:41 pm
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
September 18, 2012 at 1:56 pm
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.
February 27, 2013 at 9:28 am
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
June 21, 2013 at 7:42 am
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)
Viewing 15 posts - 46 through 60 (of 83 total)
You must be logged in to reply to this topic. Login to reply