June 29, 2012 at 5:27 pm
I am losing my mind here,
I successfully created an extended event to trap a deadlock, I can display the XML using the samples I found but it never shows an actual graph such as a profilercreated xdl file.
How can I display the actual graph? Somebody please help me
Here is the query I am running to create the graph:
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 = 'deadlock_capture') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'
and here is the output I am getting based on that:
<deadlock> <victim-list> <victimProcess id="process8071ddc8"/> </victim-list> <process-list> <process id="process8071ddc8" taskpriority="0" logused="208" waitresource="RID: 2:1:179:2" waittime="4666" ownerId="2371749" transactionname="user_transaction" lasttranstarted="2012-06-29T15:51:01.240" XDES="0xaed91950" lockMode="U" schedulerid="2" kpid="1076" status="suspended" spid="65" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-06-29T15:51:01.240" lastbatchcompleted="2012-06-29T15:50:25.997" clientapp="Microsoft SQL Server Management Studio - Query" hostname="W7-MXL1211QWS" hostpid="2204" loginname="SWNA\HORNT014" isolationlevel="read committed (2)" xactid="2371749" currentdb="1" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200"> <executionStack> <frame procname="" line="9" stmtstart="38" sqlhandle="0x0200000025654b079306c876e8ed2a3e2deab6d53c04a8b4"> </frame> <frame procname="" line="9" stmtstart="540" stmtend="626" sqlhandle="0x020000002c0e7a07a9d05a94dd07ab5baa89296869876f14"> </frame> </executionStack> <inputbuf> begin tran update ##temp1 set col1 = 4 where col1 = 3 --delay long enough to lock ##temp1 in this process --and allow ##temp2 to be locked in other process waitfor delay '0:0:10' --this proc is holding lock on ##temp1 waiting for ##temp2 to be released update ##temp2 set col1 = 4 where col1 = 3 commit tran </inputbuf> </process> <process id="process8071c988" taskpriority="0" logused="208" waitresource="RID: 2:1:177:2" waittime="9593" ownerId="2371699" transactionname="user_transaction" lasttranstarted="2012-06-29T15:50:56.297" XDES="0xb0093950" lockMode="U" schedulerid="2" kpid="4044" status="suspended" spid="60" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-06-29T15:50:56.297" lastbatchcompleted="2012-06-29T15:50:39.317" clientapp="Microsoft SQL Server Management Studio - Query" hostname="W7-MXL1211QWS" hostpid="2204" loginname="SWNA\HORNT014" isolationlevel="read committed (2)" xactid="2371699" currentdb="1" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200"> <executionStack> <frame procname="" line="9" stmtstart="38" sqlhandle="0x0200000016ea1517116cb206943b357ad8385f6577f7b55e"> </frame> <frame procname="" line="9" stmtstart="540" stmtend="626" sqlhandle="0x02000000ad0cd2120cc304a718eefb9db8aa587211391668"> </frame> </executionStack> <inputbuf> begin tran update ##temp2 set col1 = 4 where col1 = 3 --delay long enough to lock ##temp2 in this process --and allow ##temp1 to be locked in other process waitfor delay '0:0:10' --this proc is holding lock on ##temp2 waiting for ##temp1 to be released update ##temp1 set col1 = 4 where col1 = 3 commit tran </inputbuf> </process> </process-list> <resource-list> <ridlock fileid="1" pageid="179" dbid="2" objectname="" id="lock8b2e0080" mode="X" associatedObjectId="3314650481022337024"> <owner-list> <owner id="process8071c988" mode="X"/> </owner-list> <waiter-list> <waiter id="process8071ddc8" mode="U" requestType="wait"/> </waiter-list> </ridlock> <ridlock fileid="1" pageid="177" dbid="2" objectname="" id="lock83558d00" mode="X" associatedObjectId="3242592886943449088"> <owner-list> <owner id="process8071ddc8" mode="X"/> </owner-list> <waiter-list> <waiter id="process8071c988" mode="U" requestType="wait"/> </waiter-list> </ridlock> </resource-list> </deadlock>
June 29, 2012 at 6:01 pm
I figured it out...why the sample code is not doing it the same I don't get...
here is the fix:
The file has to start and end with a
<deadlock-list>
</deadlock-list>
Tag.
Then it needs to say <deadlock victim="xxxxx"> instead of <victim-list><victimProcess id="xxxx">
after that save it in.xdl and it works like a charm.
Again I do not get why none of the sample code reflects that....I am perplexed but happy I have a workaround
July 3, 2012 at 2:49 pm
This sounds like a bug thathas been fixed. What build of SQL Server are you on?
Connect Item: Invalid XML in Extended Events xml_deadlock_report output by Jonathan Kehayias
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 13, 2012 at 4:44 pm
opc.three (7/3/2012)
This sounds like a bug thathas been fixed. What build of SQL Server are you on?Connect Item: Invalid XML in Extended Events xml_deadlock_report output by Jonathan Kehayias
I am on 10.50.2500.0 which should be the latest...no?
I'll check out the article thanks!
July 13, 2012 at 4:59 pm
The build you're on should include the fix for the connect item I linked to. You may have run into a new bug. If you can reproduce it submit a connect item with the repro code.
Build 10.50.2500 is the latest Service Pack Build, but is not the latest build available. At present 10.50.2817 is the latest per a Cumulative Update package. Other hot fixes could bring the build higher than that but I only apply CUs as they come out, and hotfixes in between only if I am seeing a specific issue.
2008 R2 SP1 CU 7 - Build 10.50.2817
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 14, 2012 at 4:46 am
I am still a long way from understanding a deadlock and all related aspects very well and therefore a post related to this topic is very helpful in this regard.
Could you please confirm whether you enabled a trace flag (1204 or 1222) to get the deadlock graph that was posted?
Thanks.
July 15, 2012 at 9:03 am
If you're going to use trace flags I would recommend 1222 over 1204. 1222 will write deadlock graph info to the SQL Server error log but you won't see valid XML there, only something that sort of resembles a deadlock graph. That said, 1222 is very useful in tracking deadlocks.
To get XML formatted text straightaway you either need to use Profiler or Extended Events (EE). EE is the preferred way in 2008 or above as I think Microsoft is preparing to usher out Profiler in favor of EE:
Retrieving Deadlock Graphs with SQL Server 2008 Extended Events By Jonathan Kehayias[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 16, 2012 at 12:54 pm
Thanks for providing this useful information.
July 17, 2012 at 6:48 am
Some fantastic info on deadlocks and DL graphiong can be found here:
http://blogs.msdn.com/b/bartd/archive/2006/09/09/deadlock-troubleshooting_2c00_-part-1.aspx
July 17, 2012 at 8:16 am
Indeed useful, Thanks for sharing.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply