[SQL Server] Capturing SQL Server Deadlocks using Extended Events and select data with T-SQL

How to capture and select SQL Server Deadlocks using Extended Events with T-SQL Statements?

Requirements/Situation

• You want to capture Deadlocks on SQL Server instances.
• You want to implement this via scripts in order to use for many servers efficiently.
• You want to avoid using TraceFlag 1222 and 1204 as startup parameters.

General Steps

Give permission to the target folder.

SQL Server Service account(e.g. NT Service\MSSQLSERVER) must have Read/Write permission on this folder.

• Path: C:\Xevents

Creating a new XEvent session to Capture Deadlocks.

• Session Name: xe_deadlock
• FilePath: C:\Xevents\xe_deadlock.xel

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

CREATE EVENT SESSION [xe_deadlock] ON SERVER ADD EVENT sqlos.scheduler_monitor_deadlock_ring_buffer_recorded( ACTION(package0.callstack,package0.collect_cpu_cycle_time,package0.collect_system_time,package0.process_id,sqlos.scheduler_address,sqlos.scheduler_id,sqlos.worker_address,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.context_info,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.request_id,sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.session_resource_group_id,sqlserver.session_server_principal_name,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)), ADD EVENT sqlserver.database_xml_deadlock_report( ACTION(package0.callstack,package0.collect_cpu_cycle_time,package0.collect_system_time,package0.process_id,sqlos.scheduler_address,sqlos.scheduler_id,sqlos.worker_address,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.context_info,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.request_id,sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.session_resource_group_id,sqlserver.session_server_principal_name,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)), ADD EVENT sqlserver.lock_deadlock( ACTION(package0.callstack,package0.collect_cpu_cycle_time,package0.collect_system_time,package0.process_id,sqlos.scheduler_address,sqlos.scheduler_id,sqlos.worker_address,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.context_info,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.request_id,sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.session_resource_group_id,sqlserver.session_server_principal_name,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)), ADD EVENT sqlserver.lock_deadlock_chain( ACTION(package0.callstack,package0.collect_cpu_cycle_time,package0.collect_system_time,package0.process_id,sqlos.scheduler_address,sqlos.scheduler_id,sqlos.worker_address,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.context_info,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.request_id,sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.session_resource_group_id,sqlserver.session_server_principal_name,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)), ADD EVENT sqlserver.xml_deadlock_report( ACTION(package0.callstack,package0.collect_cpu_cycle_time,package0.collect_system_time,package0.process_id,sqlos.scheduler_address,sqlos.scheduler_id,sqlos.worker_address,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.context_info,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.request_id,sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.session_resource_group_id,sqlserver.session_server_principal_name,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)) ADD TARGET package0.event_file(SET filename=N'C:\Xevents\xe_deadlock.xel') WITH (STARTUP_STATE=ON) GO ALTER EVENT SESSION [xe_deadlock] ON SERVER STATE = START; GO

Some Deadlocks occurred (let's assume).

Select Deadlock xml reports for recent 7 days.

1 2 3 4 5 6 7 8 9

SELECT CONVERT(XML, event_data).query('/event/data/value/child::*') AS XmlReport, CONVERT(XML, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]', 'datetime') AS DeadlcokTime FROM sys.fn_xe_file_target_read_file('C:\Xevents\xe_deadlock*.xel', NULL, NULL, NULL) WHERE 1 = 1 AND object_name LIKE 'xml_deadlock_report' AND CONVERT(DATETIME2, timestamp_utc) >= DATEADD(d, -7, GETDATE()) ORDER BY timestamp_utc DESC ;

Save Deadlock xml report.

• FileName: XmlReport3.xml

Open Deadlock xml report and analyze it.

What's the issue of TF1204 and TF1222?

TraceFlag 1204/1222

References

• Extracting Deadlock information using SYSTEM_HEALTH Extended Events
• sys.fn_xe_file_target_read_file (Transact-SQL)
• Capturing SQL Server Deadlocks using Extended Events
• SQL Server: Capture database deadlock by xevent & job alerts and send email to you !