Monday, 15 June 2015

SQL Server 2016 - Extended Events - Deadlocks Tracking

Below is some code to track deadlocks in your server using Extended Events

CREATE EVENT SESSION [Deadlock Tracking] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report(
    ACTION(sqlos.task_time,sqlos.worker_address,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.username)) 
ADD TARGET package0.ring_buffer(SET max_memory=(102400))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

Deadlock tracking is already tracked under the default 'system_health' Session, but if you just wanted  just to track deadlocks by itself, then the above code will work.

Once a deadlock occurs, it will produce a graph like the following so that you can investigate the problem further. 



You can also hover over the symbols to get more information about the deadlocks.


No comments: