sql - sp_reset_connection 的作用是什么？

Question

sp_reset_connection 似乎由 SQL Server 连接池调用，以确保从池中重用的连接重置大部分设置。有谁确切知道它做什么和不做什么？

例如我从 this post 看到它不会重置事务隔离级别

Answer 1

数据访问 API 层(如 ODBC、OLE-DB 和 SqlClient)在重新使用连接池中的连接时调用(内部)存储过程 sp_reset_connection。这样做是为了在重新使用连接之前重置连接的状态。

似乎没有关于重置哪些内容的官方文档，但这里有一个非官方列表。

sp_reset_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 不会重置:

• 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