sql-server - "exec sp_reset_connection"在 Sql Server Profiler 中意味着什么？

Question

这个问题已经有答案了:

What does sp_reset_connection do? (2 个回答)

已关闭 4 年前。

社区去年审查了是否重新开放此问题，并将其关闭:

原始关闭原因未解决

尝试通过发出“sp_reset_connection”来理解 Sql Profiler 的含义。

我有以下“exec sp_reset_connection”行，后跟 BatchStarting 和 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]    

基本上第一行“exec sp_reset_connection”意味着整个过程(我的连接被打开，select stmt运行，然后连接被关闭并释放回池)刚刚发生？或者我的连接仍处于开放阶段。

而且，为什么 sp_reset_connection 在我自己的 select 语句之前执行，难道重置不应该在用户的 sql 之后执行吗？

我想知道是否有办法更详细地了解连接何时打开和关闭？

看到“exec sp_reset_connection”，是否意味着我的连接已关闭？

Answer 1

就像其他答案所说，sp_reset_connection表示连接池正在被重用。请注意一个特定的后果!

Jimmy Mays' MSDN Blog said:

sp_reset_connection does NOT reset thetransaction isolation level to theserver default from the previousconnection's setting.

更新:从 SQL 2014 开始，对于 TDS 版本 7.3 或更高版本的客户端驱动程序，事务隔离级别将重置回默认值。

引用:SQL Server: Isolation level leaks across pooled connections

以下是一些附加信息:

What does sp_reset_connection do?

Data access API's layers like ODBC,OLE-DB and System.Data.SqlClient allcall the (internal) stored proceduresp_reset_connection when re-using aconnection from a connection pool. Itdoes this to reset the state of theconnection before it gets re-used,however nowhere is documented whatthings get reset. This article triesto document the parts of theconnection that get reset.

sp_reset_connection resets thefollowing aspects of a connection:

• All error states and numbers(like @@error)

• Stops all EC's (execution contexts)that are child threads of a parent ECexecuting a parallel query

• Waits for any outstanding I/Ooperations that is outstanding

• Frees any held buffers on theserver by the connection

• Unlocks any buffer resourcesthat are used by the connection

• Releases all allocated memoryowned by the connection

• Clears any work or temporarytables that are created by theconnection

• Kills all global cursors owned by theconnection

• 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 countfor users in current database whichreleases 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 traceoptions 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 whyconnection pooling matches connectionsbased 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.

注意:我将列表包含在此处，因为我不希望它在瞬息万变的网络中丢失。