What Does Sp_Reset_Connection Do

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.

What does sp_reset_connection do?

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.

There does not appear to be official documentation on what things get reset, but here is an unofficial 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

Can I stop sp_reset_connection being called to improve performance?

If you're using .NET to connect to SQL Server, disabling of the extra reset call was disabled as of .NET 3.5 -- see here. (The property remains, but it does nothing.)

I guess Microsoft realized (as someone did experimentally here) that opening the door to avoid the reset was far more dangerous than it was to get a (likely) small performance gain. Can't say I blame them.

---

Does the client send exec sp_reset_connection, wait for a response, then send the real sql?

EDIT: I was wrong -- see here -- the answer is no.

Summary: there is a special bit set in a TDS message that specifies that the connection should be reset, and SQL Server executes sp_reset_connection automatically. It appears as a separate batch in Profiler and would always be executed before the actual query you wanted to execute, so my test was invalid.

Yes, it's sent in a separate batch.

I put together a little C# test program to demonstrate this because I was curious:

using System.Data.SqlClient;

(...)

private void Form1_Load(object sender, EventArgs e)
{
    SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
    csb.DataSource = @"MyInstanceName";
    csb.IntegratedSecurity = true;
    csb.InitialCatalog = "master";
    csb.ApplicationName = "blarg";

    for (int i = 0; i < 2; i++)
        _RunQuery(csb);
}

private void _RunQuery(SqlConnectionStringBuilder csb)
{
    using (SqlConnection conn = new SqlConnection(csb.ToString()))
    {
        conn.Open();

        SqlCommand cmd = new SqlCommand("WAITFOR DELAY '00:00:05'", conn);

        cmd.ExecuteNonQuery();
    }
}

Start Profiler and attach it to your instance of choice, filtering on the dummy application name I provided. Then, put a breakpoint on the cmd.ExecuteNonQuery(); line and run the program.

The first time you step over, just the query runs, and all you get is the SQL:BatchCompleted event after the 5 second wait. When the breakpoint hits the second time, all you see in profiler is still just the one event. When you step over again, you immediately see the exec sp_reset_connection event, and then the SQL:BatchCompleted event shows up after the delay.

The only way to get rid of the exec sp_reset_connection call (which may or may not be a legitimate performance problem for you) would be to turn off .NET's connection pooling. And if you're planning to do that, you'd likely want to build your own connection pooling mechanism, because just turning it off and doing nothing else will probably hurt more overall than taking the hit of the extra roundtrip, and you will have to deal with the correctness issues manually.

Sql Server 2014 sp_reset_connection behavior inside transaction

The sp_reset_connection stored procedure is not well documented since it's an internal stored procedure and never called directly. Be aware that it may change or be removed in the future without notice. That being said, it is useful to know a little about the internals for troubleshooting and correlating activity trace data.

The purpose of the proc is to restore connection environment state to that of a newly opened connection to support connection pooling. The client API specifies whether a connection is to be reset by setting either the RESETCONNECTION or RESETCONNECTIONSKIPTRAN bits of the TDS protocol status field when a request is issued on a newly reused connection from the pool. SQL Server internally invokes the sp_reset_connection RPC you see in a trace when either of those flags are set.

The TDS protocol documentation desribes these flags so we can infer this is what sp_reset_connection does under the covers. Below is the an excerpt from the documentation:

RESETCONNECTION (Introduced in TDS 7.1) (From client to server) Reset
this connection before processing event. Only set for event types
Batch, RPC, or Transaction Manager request. If clients want to set
this bit, it MUST be part of the first packet of the message. This
signals the server to clean up the environment state of the connection
back to the default environment setting, effectively simulating a
logout and a subsequent login, and provides server support for
connection pooling. This bit SHOULD be ignored if it is set in a
packet that is not the first packet of the message.

This status bit MUST NOT be set in conjunction with the
RESETCONNECTIONSKIPTRAN bit. Distributed transactions and isolation
levels will not be reset. 0x10

RESETCONNECTIONSKIPTRAN (Introduced in TDS 7.3) (From client to
server) Reset the connection before processing event but do not modify
the transaction state (the state will remain the same before and after
the reset). The transaction in the session can be a local transaction
that is started from the session or it can be a distributed
transaction in which the session is enlisted. This status bit MUST NOT
be set in conjunction with the RESETCONNECTION bit. Otherwise
identical to RESETCONNECTION.

The low-level client API (SqlClient here) indirectly controls the behavior of sp_reset_connection by setting those flags as needed. When a connection is reused outside a TransactionScope, the RESETCONNECTION flag is set as can be seen in this network packet trace (Wireshark):

Below is a trace of the first request issued on a reused connection from within the scope of a TransactionScope using block:

As you can see, the reset within TransactionScope will not rollback the transaction. Consequently, even if your ORM follows an open/execute/close pattern for data access, your outer TransactionScope transaction context will be honored. The transction will be committed as long as you invoke TransactionScope.Complete before exiting the transaction scope. You can include a TM Commit Tran completed event to your Profiler trace to see the commit when the TransactionScope exits.

BTW, Profiler/SQL trace is deprecated. Use Extended Events instead going forward. Also, as suggested in the other answer, be sure to specify the desired transaction isolation level (e.g. read committed) when you use TransactionScope. The default is serializable which can have side effects like unnecessary blocking and deadlocks. I suggest you use serializable only if you specifically need that strict level of isolation.

Why so many sp_resetconnections for C# connection pooling?

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(!)

Trying to get procedure name from SQL Server Profiler, 'sp_reset_connection' shows instead

I added filter in SQL Profiler 'Stored Procedures -> SP:Complited' and now I can see my procedures. Thanks, Damien_The_Unbeliever!

---

Related Topics