How Does SQL Server's `sp_reset_connection` Prepare Connections for Reuse?

Understanding the Function of sp_reset_connection in SQL Server Connection Pooling

SQL Server's connection pooling mechanism utilizes the internal stored procedure sp_reset_connection to prepare reused connections from the pool for subsequent use. This process entails resetting various aspects of the connection to ensure its pristine state.

Scope of sp_reset_connection

While comprehensive documentation on sp_reset_connection's functionality remains elusive, the following aspects undergo modification:

• Error states and numbers (e.g., @@error)
• Interrupting parallel query execution
• I/O operations completion
• Release of server-held buffers
• Buffer resource unlocking
• Connection memory deallocation
• Cleanup of temporary tables
• Termination of global cursors
• SQL-XML handle and work table removal

Additionally, it resets:

• System and user tables
• Temporary objects
• Open transactions
• Distributed transactions
• User reference count for database locking
• Acquired locks and handles
• SET options to default values
• @@rowcount and @@identity values
• Session-level trace options (dbcc traceon())

Exceptions to sp_reset_connection

Certain aspects are not subject to sp_reset_connection's reset operation, including:

• Security context (ensuring connection string matching)
• Application roles
• Transaction isolation level

The above is the detailed content of How Does SQL Server's `sp_reset_connection` Prepare Connections for Reuse?. For more information, please follow other related articles on the PHP Chinese website!