How is an idle SQL Server connection causing blocking?

Question

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.

Answer 1

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!

Answer 2

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.

Hmmmm. Are you sure about that? When the connection is made, assuming that the Connection String keyword "database" (or one of its synonyms) is used (i.e. database=_dummy; ), then you wouldn't see a USE [_dummy] statement. No command is executed to set the connecting database if specified as part of the connection. If, however, you use SqlConnection.ChangeDatabase() after connecting, then you would see the USE statement. So, either something is not set up correctly and the code truly isn't connecting to _dummy, but is instead changing to _dummy upon connecting (not quite the same thing, especially if you are trying to avoid Connection Pool Fragmentation), OR someone or some process is executing that USE statement.

Also, I'm not sure why, if this is really the application doing this that you would even see the USE statement. You should be seeing what the SqlCommand object is executing. If this Session is really part of the application's Connection Pool, then even with the connection being "closed" you should still see the last query batch submitted when executing DBCC INPUTBUFFER. Of course, the app code could be switching the Database right before closing the Connection, though I don't see why anyone would intentionally do that.

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.

Assuming that the Transaction was started using BEGIN TRAN; via a SqlCommand, then yes, up until the part about "resetting the database context before problems occurred due to the open transaction". This is a common misunderstanding of how Connection Pooling works. The Session is not reset when the Connection is closed. It is reset upon the Connection being opened again, and the first SqlCommand is executed. Simply opening the Connection is not enough to call sp_reset_connection.

So, it is possible that, if the app code try / catch / finally logic is such that the Timeout Exception is handled cleanly and the Connection is a) returned to the Pool, and b) not re-used again for several minutes (or however long you are seeing the blocking occur for), then that Transaction and its locks could very well be sitting out there for a bit, until either the connection is re-opened and something is executed, OR the application ends, OR the connection in the Pool times out, OR the Connection Pool is manually cleared.

When I get the time I was going to blog about all of the various behaviors and technicalities related to Connection Pooling. But for now I have documented at least some of it in relation to temporary objects: Sessions, Temporary Objects, and the Afterlife

Answer 3

WHY DO I SEE AN "IDLE BLOCKER" AS A HEAD BLOCKER? HOW CAN I FIND THE CAUSE?:

Idle blocking occurs when you have a session that opened a transaction (establishing a lock on a resource) and then did not commit or rollback explicitly. The transaction stays open, even though no work is currently being done. Imagine you do a begin transaction and issue an update to a table. The results will come back, but you haven't closed (committed or rolled back) the transaction yet. In another session, query against the table/row used in the first session, it will just spin until the lock has been released. The second session will start to accrue wait time in the LCK wait type and the scenario will show the first session as idle blocker in the blocking tab. There can be a couple reasons for this - an abnormally terminated session that was doing the transaction initially that didn't close it out.
The other thing I've seen is application logic that opens a transaction in the database, then goes external to do a piece of work. Think like an OS command, parse a file, make a web service call, etc. Then based on the results of that external call, it will come back into the transaction and commit or rollback. If something occurs during that external call, the application may never come back into the transaction and complete it.