What does "exec sp_reset_connection" mean in Sql Server Profiler?
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.
It's an indication that connection pooling is being used (which is a good thing).