How to capture the exact SQL text involved in deadlock (locks) using extended events?

Question

I have setup extended event session with the following event: xml_deadlock_report

When I look at the deadlock graph, it shows me the processes asking or requesting for the resource, and the type of locks requested and owned by the respective process. Hovering over the process shows me the SQL text. I can also see more details and export the xml/xdl.

However, the SQL text shown is the query in the buffer at the time of the deadlock. This is not the same as the query that owns/requests the lock. I want to see the actual SQL query in this process that owns the lock or is requesting for the lock.

Is there a way to capture the exact SQL text involved in deadlock (locks) using extended events?

Answer 1

A session could hold a lock for a very long time, and run many queries before deadlocking. In order to provide what you're describing, SQL Server would need to keep many prior/completed batches in memory for every session, AND keep track of how locks relate to specific queries.

It doesn't do that.

SQL Server only keeps track of the currently running (or most recent when idle) batch for a session.

If you have a process that is frequently deadlocking, the best thing to do is to identify that session (application name, login, server, time of day, etc) and use an extended event session to trace broader activity for the process to get the full picture, if tracing back through code isn't an option.