I have a busy, transactional SQL Server (2012) which is having intermittent problems with blocking (every couple of days or so). I've been using extended events to capture blocked-process reports, but these show that the blocking-process seems to be idle, with the input buffer being use [_dummy].
The application connecting to this server uses multiple databases, so _dummy is a blank database which connection pools open connections to; the database context is switched as required.
What I thought was happening was that a query was timing out after obtaining locks, not rolling back the transaction and the connection was being released by the application back to the connection pool, which was resetting the database context before problems occurred due to the open transaction. To try and resolve that I changed the user connection settings so that XACT_ABORT is on by default, thinking that this would cause timed out transaction to rollback automatically, but this doesn't seem to have resolved the problem.
Is there anything else which could cause an idle connection (part of a .NET connection pool) to block other queries? Is there any way I can capture more information about the last query run on the blocking-process?
Note: I'm also monitoring deadlocks and the blocked-processes are sometimes blocked for several minutes but do not become deadlocks.
Best Answer
If you're comfortable with Data Collector, you could download and install ExtendedTSQLCollector and set up a collection set to capture Blocking and Deadlocking events with XE. I have a blog post that explains how to do that.
In order to collect sleeping SPIDs that cause blocking, you could set up another collection set (or an additional collection item in the same collection set) that captures information about those SPIDs. You can use the query found in this answer. You can also tweak the query to extract information about the objects the sleeping SPID is holding locks on.
With this setup you should easily identify blocking SPIDs. What you will not identify easily is what was the sleeping SPID doing, except for the last command issued. With the information collected you can however set up something more specific like a XE session filtered for application name and/or hostname (or whatever makes sense) using a ring buffer target and dig through the batches it ran before becoming idle.
Good luck!