Pooled Connections are not a new concept of SQL Client. It reuses pool of opened connections for any new T-SQL requests. It provides a logically fresh new connection to application as you physically open a new connection. The fact behind it is that when you close a pooled connection, the connection is merely returned back to the pool without physically closing the tunnel(SQL Server doesn’t know the connection is closed by the client), and, when you open a new connection, SQL Client will open a new connection if there is no available connections in the pool. Otherwise SQL Client will get the connection from the pool. In this case, SQL Server still does not know that the connection is assigned for new purposes. Whenever you run the first command through a connection which assigned from the pool, SQL Client will “inject” execution of sp_reset_connection before the execution of your SQL statement or procedure to reset resources and mimik a brand new connection. A lot of discussions regarding sp_reset_connection in the internet. I will excerpt them at the end of this blog post. Apart from that, you must know few other things about it.
- Duration: As explain above, the duration from connnection close to the first command issued after connection reacquired, SQL Server is aware of nothing of the connection, which means if you have any open transaction before connection closed, that transaction will be completed only when the connection get acquired and the first command of re-acquire connection run. The duration of the transaction might be extremely long when you have a huge connection pool. So, programming over pooled connection should be very defensive. Unclosed transactoin will not be carried over to the new commnad but it will last longer and potentially cause blocking issue. The transaction log will not be shrunk to expected size due to active long run transactions.
- Disconnection: Connections in the pool might be reset by any reason (network, DBA kill the session, etc). In those cases, SQL Server does close the connection, but SQL Client does not know anything about it. When a physically closed connection get re-assigned. You will not get error when you open the connection, this is different from opening a non-pooled connection. The error will arise when the first command is executed.
- Isolation Level: This is very important. The Isolation level will not be reset by sp_reset_connection procedure. If you accidentially set more restrictive isolation level before closing a connection without setting them back, that isolation level will be carried over for other purpose. For instance, if you use TransactionScope class to control the transaction at C# code level, it automatically escalates Isolation Level to Read Serializable by default. As a result, you may see a lot of deadlocks in your system. From deadlock graph, you will see the Serializable Isolation Level in place. Back to the code, you can’t find any SQL Statement sets the Isolation Level.
- Blocking: sp_reset_connection might be blocked or blocking! If you have a pooled connection that creates a global temp table, if this global temp table is locked by other sessions for very long time, when the Global Temp Table creator connection closed and get acquired by other code, when the first command run, sp_reset_connection will try to release the global temp table ( which is currently locked by other sessions). Procedure sp_reset_connection will WAIT for Sch-M lock on the Global temp table until all the blockers leave. In this case, the Sch-M wait might lead command timeout. Any commands accessing that Global Temp Table while Sch-M lock is waiting will be blocked.
At the end of this blog post, I would like to excerpt text form here for future reference.
sp_reset_connection resets the following aspects of a connection:
- It resets all error states and numbers (like @@error)
- It stops all EC’s (execution contexts) that are child threads of a parent EC executing a parallel query
- It will wait for any outstanding I/O operations that is outstanding
- It will free any held buffers on the server by the connection
- It will unlock any buffer resources that are used by the connection
- It will release all memory allocated owned by the connection
- It will clear any work or temporary tables that are created by the connection
- It will kill all global cursors owned by the connection
- It will close any open SQL-XML handles that are open
- It will delete any open SQL-XML related work tables
- It will close all system tables
- It will close all user tables
- It will drop all temporary objects
- It will abort open transactions
- It will defect from a distributed transaction when enlisted
- It will decrement the reference count for users in current database; which release shared database lock
- It will free acquired locks
- It will releases any handles that may have been acquired
- It will reset all SET options to the default values
- It will reset the @@rowcount value
- It will reset the @@identity value
- It will reset any session level trace options using dbcc traceon()
sp_reset_connection will NOT reset:
- Security context, which is why connection pooling matches connections based on the exact connection string
- If you entered an application role using sp_setapprole, since application roles can not be reverted