Why so many sp_resetconnections for C# connection pooling?

Question

We have a web service coded in C# that makes many calls to MS SQL Server 2005 database. The code uses Using blocks combined with C#'s connection pooling.

During a SQL trace, we saw many, many calls to "sp_resetconnection". Most of these are short < 0.5 sec, however sometimes we get calls lasting as much as 9 seconds.

From what I've read sp_resetconnection is related to connection pooling and basically resets the state of an open connection. My questions:

• Why does an open connection need its state reset?
• Why so many of these calls!
• What could cause a call to sp_reset connection to take a non-trivial amount of time.

This is quite the mystery to me, and I appreciate any and all help!

Answer 1

The reset simply resets things so that you don't have to reconnect to reset them. It wipes the connection clean of things like SET or USE operations so each query has a clean slate.

The connection is still being reused. Here's an extensive list:

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
• The transaction isolation level(!)

Answer 2

Here's an explanation of What does sp_reset_connection do? which says, in part "Data access API's layers like ODBC, OLE-DB and SqlClient call the (internal) stored procedure sp_reset_connection when re-using a connection from a connection pool. It does this to reset the state of the connection before it gets re-used." Then it gives some specifics of what that system sproc does. It's a good thing.

Answer 3

sp_resetconnection will get called everytime you request a new connection from a pool. It has to do this since the pool cannot guarantee the user (you, the programmer probably :) have left the connection in a proper state. e.g. Returning an old connection with uncommited transactions would be ..bad.

The nr of calls should be related to the nr of times you fetch a new connection.

As for some calls taking non-trivial amount of time, I'm not sure. Could be the server is just very busy processing other stuff at that time. Could be network delays.

Answer 4

Basically the calls are the clear out state information. If you have ANY open DataReaders it will take a LOT longer to occur. This is because your DataReaders are only holding a single row, but could pull more rows. They each have to be cleared as well before the reset can proceed. So make sure you have everything in using() statements and are not leaving things open in some of your statements.

How many total connections do you have running when this happens?

If you have a max of 5 and you hit all 5 then calling a reset will block - and it will appear to take a long time. It really is not, it is just blocked waiting on a pooled connection to become available.

Also if you are running on SQL Express you can get blocked due to threading requirements very easily (could also happen in full SQL Server, but much less likely).

What happens if you turn off connection pooling?