I’m a big fan of using SQL guru’s queries and products.  Why am I going to dig through SQL DMVs if someone a lot smarter than I am has already done it for me?  My favorites are scripts from Brent Ozar’s people and Steve Stedman’s Database Health Monitor.  While running Brent Ozar’s sp_blitz I saw an entry for deadlocks, quite a large number of them.  My first thought was ‘Ok, now how am I going to track this down?’.  I just want to find the problem database for now, once I have that I can dig into that and see what issues the database has.

First I need to look into Extended Events, these are event-handling systems for servers and were introduced in SQL 2008.

Here is my 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’

That gives me the following:

UPDATE [dbo].[DeadLockTest2] set [col1] = @1
UPDATE dbo.DeadLockTest2 SET col1 = 1
UPDATE dbo.DeadLockTest2 SET col1 = 1
UPDATE [dbo].[DeadLockTest] set [col1] = @1
UPDATE dbo.DeadLockTest SET col1 = 1
BEGIN TRAN
UPDATE dbo.DeadLockTest2 SET col1 = 1
UPDATE dbo.DeadLockTest SET col1 = 1

For this example I didn’t use my production database, but instead loaded some deadlocks into AdventureWorks2012 for some test data.

If, for example, I had hundreds of databases this wouldn’t tell me which database has this table in it so I need one more query to fully track down where the deadlock came from.

We will use this:

create table #t ( DBName sysname not null ) go exec sp_msforeachdb ‘use [?]; if OBJECT_ID(”dbo.mytable”) is not null insert into #t (DBName) select ”?”’ go select * from #t go drop table #t

Obviously we have to change ”dbo.mytable” to ”DeadLockTest2”.

Here are the results:

DBName
AdventureWorks2012

Now we can further look under the hood into that database and see what may be causing problems.