Why would the sp_reset_connection system stored procedure be taking longer than a few milliseconds to execute, as viewed through SQL Server Profiler?

I took a simple trace from a production system using SQL Server Profiler and then used SqlNexus to analyze it. SqlNexus indicates that sp_reset_connection has the highest cumulative duration -- 33% of the overall trace. The observed duration ranges from 0-7 seconds (12 to 6,833,270 microseconds) but averages at 0.956s.

I understand that sp_reset_connection is being called when a pooled connection gets reused. I have seen a suggestion that this can be happening due to extraneous traces, but that doesn't seem to be the case.

I have read what the server is doing when the sproc is called but I don't believe any of those would be problematic in this case -- the code is not leaving open transaction or huge temporary tables that would need to be cleaned up.

I also looked at https://serverfault.com/questions/199974/sp-reset-connection-taking-a-long-time-to-run but it wasn't helpful.

EDIT (2013-12-23): In all cases, reads and writes are 0 and CPU is almost always 0 (only two instances of non-zero CPU, both at 16ms).

  • What kind of values are you seeing for reads and writes in that event? Commented Dec 20, 2013 at 23:30
  • Can you provide more info about what kind of queries you run. Specifically interesting details like, long or complex transactions, XML processing, temp tables? Commented Dec 21, 2013 at 9:32
  • @Martin reads and writes are 0. Updated the question. (Didn't have access to the data over the weekend.) Commented Dec 23, 2013 at 14:47
  • @EdwardDortland most of the queries are fairly simple selects and updates without explicit transactions or use of temp tables. In fact, usually the actual queries executed on these connections are pretty quick -- only a few ms. Commented Dec 23, 2013 at 15:11
  • @HolisticDeveloper - I experimented with leaving an open transaction and could see non zero reads and writes there so agree it doesn't look like that then. Is this situation more or less permanent? if so I'd run an extended events trace capturing RPC:Starting, RPC:Completed and wait types for a short period then look through the data to see what wait types the spids are encountering during that time. Commented Dec 23, 2013 at 15:32

2 Answers 2


Finally got some time to write a more detailed answer.

There are typically three main reasons a simple procedure like sp_reset_connection will take a long time to run.

  1. You are waiting for CPU resources
  2. You are blocked on a lock somewhere (perhaps as a result of DML or a competing transaction)
  3. Your network is slow and it takes a long time to return the result back to the client

Ad 1) If you are waiting for CPU resources, this should show up as signal waits. Please see my comment on your question on how to diagnose if this is the issue

Ad 2) If you are waiting for a lock, this is best diagnosed by comparing two snapshots of sys.dm_os_wait_stats. See this article on how to do this:

If you see long waits for LCK_[Something], query sys.dm_tran_locks to track down which objects are being locked. In your case, I would expect to see some form of SCH-[Something]> locks blocking you.

Ad 3) The easiest way to diagnose network issues to first look for OLEDB and ASYNC_NETWORK_IO waits in step 2 (if you wait a long time for network, one of those show up). If those waits are high, use xperf -on latency or a network monitoring program like netmon or wireshark to check your latencies. If the network looks slow, this could also be caused by the calling application server not responding fast enough to the connection being recycled.

  • I haven't seen the issue recur yet so I cannot use the provided answer to diagnose further at this point. However, I am accepting the answer based on your reputation as a SQL Server performance expert. Commented Jan 6, 2014 at 20:25

I just came across a KB article for a bug that may be related to this issue. In FIX: Performance problems occur when database lock activity increases in SQL Server (KB 2926217), one of the symptoms described is that sp_reset_connection may take a long time to complete. The hotfix is included in the following updates:

  • Cumulative Update 17 for SQL Server 2008 SP3
  • Cumulative Update 13 for SQL Server 2008 R2 SP2
  • Cumulative Update 9 for SQL Server 2012 SP1
  • Cumulative Update 1 for SQL Server 2014

The server on which I observed this behavior was running SQL Server 2008 SP3 with Cumulative Update 5, so it is possible it was experiencing this bug. I haven't tried the cumulative update yet (the issue does not recur all the time) so I cannot verify if it would fix it or not. However, I wanted to provide the info in case anyone had the same symptoms.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.