Assume that you use Microsoft SQL Server 2016 and 2017. If you enable the event xml_deadlock_report to collect data for deadlocks, many xml_deadlock_report events are reported for one single intra-query deadlock occurrence.
As a workaround for this issue, you can collect the error_reported XEvent by using a filter error_number = 1205 instead of xml_deadlock_report, as seen in the following example:
CREATE EVENT SESSION [Deadlock_Collection] ON SERVER
ADD EVENT sqlserver.error_reported(
ADD TARGET package0.histogram(SET filtering_event_name=N'sqlserver.lock_acquired',source=N'sqlserver.query_hash')
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=ON,STARTUP_STATE=OFF)
This issue is fixed in the following cumulative updates for SQL Server:
Note: With this fix, no xml_deadlock_report events are reported for intra-query deadlock when the deadlock can be resolved without killing a thread.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Learn about the terminologythat Microsoft uses to describe software updates.