0

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?

2

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.

4
  • By session do you mean Process?
    – variable
    Sep 9 '21 at 12:09
  • When I say session, I mean session.
    – AMtwo
    Sep 9 '21 at 13:15
  • The deadlock graph shows Process - so what is relation between process and session? Do you mean that a session runs inside the process? And the same process can run multiple sessions, one at a time?
    – variable
    Sep 9 '21 at 13:19
  • 1
    In a deadlock graph Process belongs to a Session, but a parallel plan can have many Processes for a single Session. Sep 9 '21 at 17:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.