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
DECLARE @ExtendedEventsTargetPath sysname = 'Change this string to something like "D:\XEvents\Traces"'; DECLARE @SQL nvarchar(max) = N' 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 = ''' + @ExtendedEventsTargetPath + N'\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'; exec sp_executesql @SQL;
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/@name)', 'varchar(100)') as eventName, event_file_value_xml.[xml].value('(event/@timestamp)', '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