Two Scripts That Help You Monitor Deadlocks with Extended Events

January 24, 2016

Two Scripts That Help You Monitor Deadlocks with Extended Events

Filed under: Miscelleaneous SQL,SQL Scripts,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 8:00 am

I want to use extended events to store recent deadlock graphs. And I want to create an asynchronous file target instead of using the existing system_health session. I don’t like the system_health target for a couple reasons. It’s too slow to query and it rolls over too quickly and it disappears after server restarts.

So I searched the web for a solution and when I couldn’t find one, I wrote my own solution, I tested it and I decided to blog about it.

Guess what? Apparently I “reinvented the wheel”. The extended events session I created is equivalent to one that Jeremiah Peschka wrote two years ago in Finding Blocked Processes and Deadlocks using SQL Server Extended Events. The embarrassing thing is that in Jeremiah’s article, he references a tool I wrote. And the first comment was written by yours truly.

So go read Jeremiah’s article, it’s really well written. What follows is my solution. The only difference is that mine only focuses on deadlocks. Jeremiah’s focuses on both deadlocks and blocked processes.

Create The Session

Here’s the session that I use. It

has five rollover files so that a couple server restarts don’t lose any recent deadlock graphs
uses an asynchronous_file_target which I prefer over the ring buffer,
and it cleans itself up over time. I don’t need a maintenance job to remove ancient data

CREATE EVENT SESSION [capture_deadlocks] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report( ACTION(sqlserver.database_name) ) 
ADD TARGET package0.asynchronous_file_target(
  SET filename = 'capture_deadlocks.xel',
      max_file_size = 10,
      max_rollover_files = 5)
WITH (
    STARTUP_STATE=ON,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=15 SECONDS,
    TRACK_CAUSALITY=OFF
    )
 
ALTER EVENT SESSION [capture_deadlocks] ON SERVER
    STATE=START;

Query the Results

Oh great. Now I’ve got to dig through several files. That’s a lot of work.
… but not if you have this query:

declare @filenamePattern sysname;
 
SELECT @filenamePattern = REPLACE( CAST(field.value AS sysname), '.xel', '*xel' )
FROM sys.server_event_sessions AS [session]
JOIN sys.server_event_session_targets AS [target]
  ON [session].event_session_id = [target].event_session_id
JOIN sys.server_event_session_fields AS field 
  ON field.event_session_id = [target].event_session_id
  AND field.object_id = [target].target_id    
WHERE
    field.name = 'filename'
    and [session].name= N'capture_deadlocks'
 
SELECT deadlockData.*
FROM sys.fn_xe_file_target_read_file ( @filenamePattern, null, null, null) 
    as event_file_value
CROSS APPLY ( SELECT CAST(event_file_value.[event_data] as xml) ) 
    as event_file_value_xml ([xml])
CROSS APPLY (
    SELECT 
        event_file_value_xml.[xml].value('(event/data/value/deadlock/process-list/process/@spid)[1]', 'int') as first_process_spid,
        event_file_value_xml.[xml].value('(event/@name)[1]', 'varchar(100)') as eventName,
        event_file_value_xml.[xml].value('(event/@timestamp)[1]', 'datetime') as eventDate,
        event_file_value_xml.[xml].query('//event/data/value/deadlock') as deadlock    
  ) as deadlockData
WHERE deadlockData.eventName = 'xml_deadlock_report'
ORDER BY eventDate DESC

-- Comments (8)

8 Comments »

Thanks for this! I’ll be taking a closer look at it to improve what I’m currently using to monitor deadlocks.

Comment by Aaron Cooper — January 24, 2016 @ 10:17 pm
That’s good to hear Aaron, I’m sure what you have is fine. Let me know what differences (if any) you find.

Comment by Michael J. Swart — January 25, 2016 @ 8:47 am
[…] Michael J. Swart has an Extended Event and a query to help monitor deadlocks: […]

Pingback by Monitoring Deadlocks – Curated SQL — January 26, 2016 @ 8:14 am
I’ve been using this today to debug deadlocks caused by a proposed new trigger.
Makes it easy to open the xml, save it as an .xdl and reopen it to get the visual graph.
Really nice.

Comment by James Anderson — February 1, 2016 @ 6:34 am
That’s great to hear James. I’ve had the same experience and I’m glad I could share.

Comment by Michael J. Swart — February 1, 2016 @ 10:41 am
I use something very similar, but mine uses event_file instead of asynchronous_file_target. Apparently they renamed it for SQL Server 2012.

One cool thing to add would be a histogram that would show the number of deadlocks per day per database, but I haven’t found anything that shows how to accomplish that. Any ideas?

Comment by Mark Freeman — February 9, 2016 @ 3:14 pm
No, Nothing comes to mind other than the obvious SQL solution which would look like:
SELECT COUNT(*) as [count], CAST(eventDate as DATE) as [date], DATEPART(hour, eventDate) as [hour] FROM ... GROUP BY CAST(eventDate as DATE) as [date], DATEPART(hour, eventDate) as [hour]
SELECT COUNT(*) as [count], CAST(eventDate as DATE) as [date], DATEPART(hour, eventDate) as [hour] FROM ... GROUP BY CAST(eventDate as DATE) as [date], DATEPART(hour, eventDate) as [hour]
Followed by a cut and paste into excel in order to create a chart.

But I also see the histogram target for extended events. Perhaps that’s what you were hinting at. I’ve never used it but I notice that Kendra Little once wrote about it here: Tip for Learning Extended Events – Use “New Session” (Not “New Session Wizard”)

Comment by Michael J. Swart — February 9, 2016 @ 3:24 pm
Yes, the histogram target is what I had in mind. Thanks for the link.

Comment by Mark Freeman — February 9, 2016 @ 3:36 pm