4

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?

  • 1
    Did you look into beta_lock as suggested in my answer ? – Kin Shah Mar 13 '18 at 19:56
6

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.

0

You can get blocking information from the DMVs:

select  getdate() Run_Date,
        r.session_id,
        r.blocking_session_id,
        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.

  • 2
    You should look at the OP's original question where we talked about this already. – Max Vernon Mar 13 '18 at 14:55
  • 1
    Thanks Max, should I delete my answer? – MJH Mar 13 '18 at 15:01
  • 6
    I'd say no, but don't trust me; I'm Canadian. – Max Vernon Mar 13 '18 at 15:02
  • 2
    @MJH I appreciate your tip, but your query will only show the SQL on the inputbuffer (the last one), which is not necessarily the one that caused blocking: other statements (that ran before it under the same SPID) still are holding locks. In other words: how to identify these statements causing the minimum overhead on the production system? If I don't answer this until friday I'll likely be fired :) – tanitelle Mar 13 '18 at 19:06
  • 1
    @tanitelle Yes, Max kindly pointed me in that direction, I should have read it before posting. If you don't have any luck with the Extended Events route, and you know what Stored Procedures are causing the blocking, you may be able to implement your own logging of the sys.dm_tran_locks DMV (request_session_id = @@spid) into a table along with the SP step and a timestamp. Sorry I can't be of more help, good luck. – MJH Mar 13 '18 at 20:03

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.