How Does sp_reset_connection Reset SQL Server Connections and What Doesn't It Affect?

Delving into the Functionality of sp_reset_connection in SQL Server Connection Pooling

SQL Server employs connection pooling to manage database connections efficiently. Central to this mechanism is the stored procedure sp_reset_connection, which is invoked when reusing connections from the pool. Its primary objective is to reset the connection's state to ensure readiness for subsequent usage.

Comprehensive Resetting

sp_reset_connection resets various aspects of a connection, including:

• Error states and numbers
• Execution contexts (ECs)
• Outstanding I/O operations
• Held server buffers
• Locked buffer resources
• Memory allocated by the connection
• Temporary tables
• Global cursors
• Open SQL-XML handles and related work tables
• System and user tables
• Temporary objects
• Open transactions
• Distributed transaction enlistments
• Shared database locks
• Acquired locks
• Handles
• SET options
• @@rowcount
• @@identity
• Session-level trace options

Exclusions

However, sp_reset_connection specifically avoids resetting:

• Security context, which is crucial for matching connections based on the connection string
• Application roles, as they cannot be reverted
• Transaction isolation level

Practical Implications

• Application roles established via sp_setapprole remain in effect.
• Transactions must be explicitly managed by the application, as sp_reset_connection does not reset them.
• Avoid relying on SET options being preserved across connection reuse, as they are reset to their defaults.

The above is the detailed content of How Does sp_reset_connection Reset SQL Server Connections and What Doesn't It Affect?. For more information, please follow other related articles on the PHP Chinese website!