SQL Server › SQL Server Profiler & Extended Events

How would you design an Extended Events session to monitor for a very specific, hard to reproduce issue, such as intermittent deadlocks, in a production environment?

Advanced

Answer

You would capture the xml_deadlock_report event, which automatically fires whenever a deadlock occurs, providing full details about the transactions and resources involved, and configure the session to run continuously with minimal overhead using an appropriate target like a ring buffer or event file, so you have complete deadlock details captured automatically the next time the issue occurs, without needing to catch it happening live.
CREATE EVENT SESSION DeadlockMonitor ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file (SET filename = 'Deadlocks.xel')
WITH (STARTUP_STATE = ON);
Real-world example A team investigating occasional, hard to reproduce deadlocks sets up a permanent Extended Events session capturing the deadlock report event, finally capturing full details the next time the issue occurs in production without needing to actively watch for it.

Common follow-ups: How do you read and interpret the captured deadlock report once you have one?;What is the difference between a ring buffer and an event file target?