sql server 执行中sp_reset_connection系统存储过程执行的duration值很大是怎么回事？

Question

如下是sql profiler抓取的sql执行情况：
以下四列分别为sql语句，duration，开始时间，结束时间，可以看到sp_reset_connection的duration值很大

exec sp_reset_connection 	2798828	2009-12-29 15:12:07.803	2009-12-29 15:12:10.600
exec sp_reset_connection 	5041015	2009-12-29 15:12:05.553	2009-12-29 15:12:10.600
exec sp_reset_connection 	2246093	2009-12-29 15:11:57.527	2009-12-29 15:11:59.777
exec sp_reset_connection 	2324218	2009-12-29 15:11:57.447	2009-12-29 15:11:59.777
exec sp_reset_connection 	2368164	2009-12-29 15:11:57.403	2009-12-29 15:11:59.777
exec sp_reset_connection 	3754882	2009-12-29 15:11:46.123	2009-12-29 15:11:49.873
exec sp_reset_connection 	3875000	2009-12-29 15:11:46.003	2009-12-29 15:11:49.877
exec sp_reset_connection 	3913085	2009-12-29 15:11:45.967	2009-12-29 15:11:49.877
exec sp_reset_connection 	3963867	2009-12-29 15:11:45.913	2009-12-29 15:11:49.873
SELECT TOP 500 ID,BoardID FROM BBS_Post WHERE ID > 23604	3906	2009-12-29 15:11:41.200	2009-12-29 15:11:41.203
exec sp_reset_connection 	1664062	2009-12-29 15:11:38.063	2009-12-29 15:11:39.733
exec sp_reset_connection 	1751953	2009-12-29 15:11:37.973	2009-12-29 15:11:39.733
exec sp_reset_connection 	603515	2009-12-29 15:11:29.100	2009-12-29 15:11:29.703
exec sp_reset_connection 	2535156	2009-12-29 15:11:27.173	2009-12-29 15:11:29.703
exec sp_reset_connection 	2647460	2009-12-29 15:11:27.050	2009-12-29 15:11:29.703
exec sp_reset_connection 	2689453	2009-12-29 15:11:27.010	2009-12-29 15:11:29.703
exec sp_reset_connection 	2687500	2009-12-29 15:11:27.010	2009-12-29 15:11:29.703
SELECT TOP 500 ID,BoardID FROM BBS_Post WHERE ID > 23139	3906	2009-12-29 15:11:10.333	2009-12-29 15:11:10.337
exec sp_reset_connection 	4955078	2009-12-29 15:11:00.743	2009-12-29 15:11:05.700
exec sp_reset_connection 	4955078	2009-12-29 15:11:00.743	2009-12-29 15:11:05.700

---

赵玉开

Answer 1

mark

Answer 2

Number of ms between start time and end time.

Answer 3

Hi,

关于 sp_reset_connection的信息，可以看看http://decipherinfosys.wordpress.com/2008/02/01/sp_reset_connection/

引用MS的原话是：The sp_reset_connection stored procedure is used by SQL Server to support remote stored procedure calls in a transaction. This stored procedure also causes Audit Login and Audit Logout events to fire when a connection is reused from a connection pool.

所以这个sproc本应该是没什么问题的，你需要检查的是你的application端，看看你的application pool以及db connection 是不是有什么问题。

还有你的这个trace，看是去似乎sql执行的不多，大部分都是在reset connection，看看这些process都在做什么。

Answer 4

我过滤掉了duration超过1000的记录，就是说duration超过1000的记录大都是sp_reset_connection这个存储过程。

---

赵玉开

Answer 5

还是先检查application 和db 的connection, 检查sql的服务器属性中的"连接"选项卡。