What does "exec sp_reset_connection" mean in Sql Server Profiler? [duplicate]

Question

Trying to understand what Sql Profiler means by emitting "sp_reset_connection".

I have the following, "exec sp_reset_connection" line followed by BatchStarting and Completed,

RPC:Completed       exec sp_reset_connection
SQL:BatchStarting   SELECT [c].[TestID] AS [TestID], [c].[Description] AS [Description] FROM [dbo].[Test] AS [c]
SQL:BatchCompleted  SELECT [c].[TestID] AS [TestID], [c].[Description] AS [Description] FROM [dbo].[Test] AS [c]    

Basically does first line "exec sp_reset_connection" mean the whole process (my connection was opened, the select stmt is run, then the connection is closed and released back to pool) just take place? Or my connection is still in open stage.

And, why does the sp_reset_connection executed before my own select statement, shouldn't it the reset come after user's sql?

I'm trying to know is there a way to know in more detail when a connection is opened and closed?

By seeing "exec sp_reset_connection", does that mean my connection is closed?

Palmate

Answer 1

Like the other answers said, sp_reset_connection indicates that connection pool is being reused. Be aware of one particular consequence!

Jimmy Mays' MSDN Blog said:

sp_reset_connection does NOT reset the transaction isolation level to the server default from the previous connection's setting.

UPDATE: Starting with SQL 2014, for client drivers with TDS version 7.3 or higher, the transaction isolation levels will be reset back to the default.

ref: SQL Server: Isolation level leaks across pooled connections

Here is some additional information:

What does sp_reset_connection do?

Data access API's layers like ODBC, OLE-DB and System.Data.SqlClient all 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, however nowhere is documented what things get reset. This article tries to document the parts of the connection that get reset.

sp_reset_connection resets the following aspects of a connection:

• All error states and numbers (like @@error)

• Stops all EC's (execution contexts) that are child threads of a parent EC executing a parallel query

• Waits for any outstanding I/O operations that is outstanding

• Frees any held buffers on the server by the connection

• Unlocks any buffer resources that are used by the connection

• Releases all allocated memory owned by the connection

• Clears any work or temporary tables that are created by the connection

• Kills all global cursors owned by the connection

• Closes any open SQL-XML handles that are open

• Deletes any open SQL-XML related work tables

• Closes all system tables

• Closes all user tables

• Drops all temporary objects

• Aborts open transactions

• Defects from a distributed transaction when enlisted

• Decrements the reference count for users in current database which releases shared database locks

• Frees acquired locks

• Releases any acquired handles

• Resets all SET options to the default values

• Resets the @@rowcount value

• Resets the @@identity value

• Resets any session level trace options using dbcc traceon()

• Resets CONTEXT_INFO to NULL in SQL Server 2005 and newer _{[ not part of the original article ]}

sp_reset_connection will NOT reset:

• Security context, which is why connection pooling matches connections based on the exact connection string

• Application roles entered using sp_setapprole, since application roles could not be reverted at all prior to SQL Server 2005. Starting in SQL Server 2005, app roles can be reverted, but only with additional information that is not part of the session. Before closing the connection, application roles need to be manually reverted via sp_unsetapprole using a "cookie" value that is captured when sp_setapprole is executed.

Note: I am including the list here as I do not want it to be lost in the ever transient web.

Exum

Good thinking on including the essential info here. Your second link is now dead. – Vivle

it also causes a Audit Login/Audit Logout event, which will show in SQL Server Profiler, and will trigger the associated trigger events for those. It looks like the client disconnected and reconnected, when it actually didn't. This had me chasing my tail for a while so though I'd let people now – Bowser

Does it reset what i put into CONTEXT_INFO ? – Rutile

Actually, application roles can be reverted using sys.sp_unsetapprole - it's just that it needs to be called manually prior to closing the connection (returning the connection back to the pool). – Unstrained

@RobertNiestroj Earlier versions (definitely SQL Server 2000) did not reset CONTEXT_INFO. This lead us to have to specific steps before we dispose any connection to clear the CONTEXT_INFO manually. i noticed recently that, at least, SQL Server 2008 R2 fixed the bug; CONTEXT_INFO is automatically cleared. – Pothunter

@IanBoyd I just tested with SQL Server 2005 SP3 and it does indeed reset CONTEXT_INFO to NULL. I will update the answer with that detail. And for what it's worth, I did confirm that TRANSACTION ISOLATION LEVEL was not reset. – Srini

Do you know if sp_reset_connection is actually sent down the wire as a separate statement? I have a vague recollection that it's just a flag on the TDS (?) communications to avoid additional overhead of parsing it and treating it like a 'normal' proc to be executed. I'm not sure where I got that idea from though... and haven't pulled out wireshark to check it out. – Filefish

@Filefish looks like it is called explicitly referencesource.microsoft.com/#System.Data/System/Data/… – Dorise

@Filefish According to Bob Beauchemin ( SQL Server and Pooled vs. Non-pooled connections ): "the first batch of SQL executed causes a RESETCONNECTION bit to be set on the first TDS packet to be sent". Also, the reference code linked by Dan is misleading: it is just telling what should be done in the TDS stream, but does not physically make the call. See referencesource.microsoft.com/#System.Data/System/Data/… and scroll down a little to see the actual bit being set. – Srini

@DanDef That reference code that you linked to is misleading: it is just telling what should be done in the TDS stream, but does not physically make the call. See referencesource.microsoft.com/#System.Data/System/Data/… and scroll down a little to see the actual bit being set. – Srini

It's also interesting to note that connection pooling provided by ADO.net and OLE DB will try to issue reset connection when the connection is drawn from the pool. If the connection was using an Application Role: not only will RESET not revert the context, but the server will terminate the connection, and log the error in the server log: Error: 18059, Severity: 20, State: 1. The connection has been dropped because the principal that opened it subsequently assumed a new security context, and then tried to reset the connection under its impersonated security context. – Pothunter

@IanBoyd I just updated that last point to clarify that the issue not the fact that an Application Role was used, but that it wasn't manually reverted prior to the app closing the connection (i.e. returning it to the pool). I'm not sure if sp_reset_connection will issue a generic REVERT to undo EXECUTE AS since that doesn't require a cookie value and won't error if there is no context to revert from. Still, Module Signing is a far better choice as it is more secure, more granular, and there's nothing to revert :-). – Srini

Answer 2

It's an indication that connection pooling is being used (which is a good thing).

Paronychia

Answer 3

Note however:

If you issue SET TRANSACTION ISOLATION LEVEL in a stored procedure or trigger, when the object returns control the isolation level is reset to the level in effect when the object was invoked. For example, if you set REPEATABLE READ in a batch, and the batch then calls a stored procedure that sets the isolation level to SERIALIZABLE, the isolation level setting reverts to REPEATABLE READ when the stored procedure returns control to the batch.

http://msdn.microsoft.com/en-us/library/ms173763.aspx

Corinecorinna

but if you do the same on the client side, using client methods, it does not reset... – Brickyard