What "exec sp_reset_connection" shown in Sql Profiler means?

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?

Thanks, Ray.

Answer 1

A quick Google-search for sp_reset_connection had this page as the first result.

Essentially it looks like it does some general "cleaning up" tasks so that any previous users of that connection won't affect the current query (when using connection pooling).

Answer 2

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

Answer 3

As the other answers above, sp_reset_connection indicates that connection pool is reused. You need to be aware of its consequences. We should know that

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

Source: http://blogs.msdn.com/b/jimmymay/archive/2009/02/02/sp-reset-connection-does-not-reset-transaction-isolation-level-unexpected-behavior-by-design.aspx

from:http://sqldev.net/articles/sp_reset_connection/default.html

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:

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

Note: Pasting the content as I do not want it to be lost in the ever transient web