Greetings. I've been using third party tools for monitoring for a very long time. However, I recently switched jobs and need to get up the learning curve with DMV's now. 

I know how to capture which sprocs are involved in deadlocks. I also know I can query some DMV's and Extended Events to get deadlock info. However, how do I get the actual statement involved in the sproc that caused the deadlock? I know the exec plan shows what percentage each statement of a sproc is, but that isn't necessarily the same as the statement causing a deadlock. 

Thanks!

Greetings. I've been using third party tools for monitoring for a very long time. However, I recently switched jobs and need to get up the learning curve with DMV's now. 

I know how to capture which sprocs are involved in deadlocks. I also know I can query some DMV's and Extended Events to get deadlock info. However, how do I get the actual statement involved in the sproc that caused the deadlock? I know the exec plan shows what percentage each statement of a sproc is, but that isn't necessarily the same as the statement causing a deadlock. 

Thanks!

Greetings. I've been using third party tools for monitoring for a very long time. However, I recently switched jobs and need to get up the learning curve with DMV's now. 

I know how to capture which sprocs are involved in deadlocks. I also know I can query some DMV's and Extended Events to get deadlock info. However, how do I get the actual statement involved in the sproc that caused the deadlock? I know the exec plan shows what percentage each statement of a sproc is, but that isn't necessarily the same as the statement causing a deadlock. 

Thanks!

You need to turn on some trace flags.

Please see:

http://support.microsoft.com/kb/832524

You need to turn on some trace flags.

Please see:

http://support.microsoft.com/kb/832524

I know how to do that, thanks. However what I'm after is being able to use DMV's for deadlocks that have already occurred. 

you can check it through trace also...check this link for detail

Finding SQL Server Deadlocks Using Trace Flag 1222

There are no DMVs for looking at deadlocks.  Once you have the trace flags turned on, all data you will ever get on a deadlock is written to the SQL Server error log.

• Marked as answer by ChrisRDBA Monday, December 16, 2013 7:03 PM

I use a server-side trace capturing only the deadlock graph event. You can query the name of the trace file using sys.traces. This can be cross-applied (CROSS APPLY) to fn_trace_gettable(). In there you have the deadlock info as XML, which you can mine using methods such as .query(), .exists() etc (which uses XPATH and XQUERY).

I have the first part setup on a lot of servers, and I'm getting to the point where I need to query the data (whcih database is most frequently involved in deadlocks, which table and stuff like that. I suggest you start by checking of the SQL command avalable in the Deadlock Graph event is what you are looking for. If it is, then the rest is just a matter of do it... :-)

There are no DMVs for looking at deadlocks.  Once you have the trace flags turned on, all data you will ever get on a deadlock is written to the SQL Server error log.

This is not quite true

Since SQL Server 2008 all deadlocks are captured and stored inside the system health session from Extended Events.
By default, for free (!) - there barely is a need for enabling those old and costly Trace Flags.

You can get some more information from this thread: Alert me Inquiring a past deadlock - SQL Server 2008 R2/2012

The DMV would be sys.dm_xe_session_targets. In SQL Server 2012 you can also use the file target and the GUI

Since SQL Server 2008 all deadlocks are captured and stored inside the system health session from Extended Events.
By default, for free (!) - there barely is a need for enabling those old and costly Trace

...

Be aware that the system health session is a ring buffer so only persists results for a limited amount of time (until it is full) - so having a job to query it could be a good

Interesting, Andreas. Let us know, if you want, when it is

Cool. In case you are open for a wish list, or rather a desired usage scenario:

I think my earlier statement pretty much nailed it down. I would be great to be able to "mine" deadlocks after the fact. Thinks like what database, what table, what statement. Or rather, whatever information the x/event provide. This is very common for me and a server-side trace gives me that capability - but I'd rather use x/events. I don't know what information the deadlock x/event provides, but if it is very light-weight, then perhaps your solution (in case it is at all in this direction) can work with a customized x/event session where we add some other actions/columns (assuming this is doable...). Many words, but I think you get the picture... 

Cool. In case you are open for a wish list, or rather a desired usage scenario:

I think my earlier statement pretty much nailed it down. I would be great to be able to "mine" deadlocks after the fact. Thinks like what database, what table, what statement. Or rather, whatever information the x/event provide. This is very common for me and a server-side trace gives me that capability - but I'd rather use x/events. I don't know what information the deadlock x/event provides, but if it is very light-weight, then perhaps your solution (in case it is at all in this direction) can work with a customized x/event session where we add some other actions/columns (assuming this is doable...). Many words, but I think you get the picture...

Good good :)

I am really curious on what the feeedback will be, once it's out.

Until then.. happy tracing & parsing :)

- I'll also post it here, once it's out.

Andreas

What I am doing is essentially parsing the XML deadlock graph and putting the most commonly needed information in a column-structure.

Its all based only on the system_health session, on purpose, because this is whats running on ALL Servers beginning with SQL Server 2008.

What I am doing is essentially parsing the XML deadlock graph and putting the most commonly needed information in a column-structure.

Its all based only on the system_health session, on purpose, because this is whats running on ALL Servers beginning with SQL Server 2008.

Genius! But then I would say that as that is exactly how my software (see signature) does i

Interesting, Andreas. Let us know, if you want, when it is done

You are welcome

let me know if you find any issues with it - or if it works well for you as well :-)

have a nice week-end