Framework to effectively identify blocking queries 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. 



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?
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:


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.
perform some long-running process on each row.
Close the query.


What should happen is:


Connect to SQL Server
Run a query, caching the results of the query locally, and closing the connection.
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.