I need a routine to effectively identify which queries caused blocking. This is related to my previous question How to find the query that is still holding a lock?.

I know there is a bunch of material online regarding this, but all of them are based on the premise that the last SQL statement on an active session is most likely the one who acquired the lock (hence generating the blocking), which is not always true (in my case, never).

I've set the blocking-process-threshold to 30 seconds and started analysing the Blocking Process Reports (BPR).
These reports are fired every time a blocking occurs, when the threshold is reached.
It contains information about the blocked spid and the blocking spid.

BPR example

Often the blocking spid runs a couple of statements after the one that acquired and is holding the lock on a resource (table, page or row): so despite of the report content, I remain clueless about which query exactly caused that block.

Usually the SQL Server DMVs show only the last SQL text for each session_id, and the DMVs related to active locks (such as sys.dm_tran_locks) also don't address this issue.

Tuning the blocked queries is not the best approach here: our application is all based on dynamic SQL embedded on client code, we don't use stored procedures and based on the blockings that I saw until now, all of the blocked queries were correctly indexed and written.

I think an option to solve this would be to collect candidate queries, which could have generated a blocking and then lookup on this info using timestamp and spid gathered on BPR. Do you agree? If so, can you point a way to do this with the least overhead possible using xEvents?

  • 2
    Did you look into beta_lock as suggested in my answer ?
    – Kin Shah
    Commented Mar 13, 2018 at 19:56

2 Answers 2


I'd suggest looking for long-running sessions, using an XEvents session.

The problem you're describing sounds like you have client code that is performing row-by-agonizing-row (RBAR) processing instead of using efficient set-based approaches.

Poorly designed client applications may do something like this:

  1. Connect to SQL Server to get a list of items that need processing. The query result is held open until all rows have been processed.
  2. perform some long-running process on each row.
  3. Close the query.

What should happen is:

  1. Connect to SQL Server
  2. Run a query, caching the results of the query locally, and closing the connection.
  3. Run the long-running process against each row without keeping the original query open, thereby preventing blocking.

A workaround for SQL Server can be implemented using snapshot isolation row versioning. See this Technet document for details. Essentially, row-versioning allows writers to not block readers, vastly reducing blocking.


You can get blocking information from the DMVs:

select  getdate() Run_Date,
        st.text SQL_Text
from    sys.dm_exec_requests r
        cross apply sys.dm_exec_sql_text(r.sql_handle) st
where   r.blocking_session_id <> 0
        or r.session_id in( select r2.blocking_session_id
                            from sys.dm_exec_requests r2
                            where r2.blocking_session_id <> 0)

You can run it inside a loop with a waitfor time of your choosing and output the results to a table.

It will not capture every block your system encounters, but if you are encountering a lot of blocking, it should pick up enough for you to start troubleshooting.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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