What does exec sp reset connection mean in Sql Server Profiler

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

User · Answer

It s an indication that connection pooling is being used  which is a good thing

User · Answer

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

User · Answer

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

[sql-server] What does "exec sp_reset_connection" mean in Sql Server Profiler?

Examples related to sql-server

Examples related to database-connection

Examples related to sql-server-profiler

Examples related to sp-reset-connection