Yesterday i ran the "SQL Server Profiler" tool and outputted the data to a table, now when i look in it i find many "exec sp_reset_connection" rows and thats expected with our data access layer!

But what i can't explain is that some times it takes 8 seconds to execute this query...

What could cause the sp_reset_connection to delay that much?

  • Does this link over in stackoverflow help? stackoverflow.com/questions/641120/… – jl01 Jan 24 '12 at 16:05
  • 8 seconds would be way too long-- can you see what commands the SPID issued before the sp_reset_connection? – Andomar Jan 24 '12 at 18:41
  • I checked and there seems to be no direct pattern to this, some times its a select and some times its a update and its diffrent tables.. – Peter Jan 25 '12 at 8:55
  • @jl01 well its not bad but it does not explain a 8 sec execution time.. – Peter Jan 25 '12 at 14:16

You sure it is 8 seconds?

Starting with SQL Server 2005 (as stated in BOL) when a trace is saved to either a file or a database table, the duration column value is in microseconds

Came across this post on SO: sp_reset_connection taking a long time to run

| improve this answer | |
  • yes Duration = "8192947" 8192947 / 1000000 = 8.1 correct me if my math is wrong.. cpu, reads, writes are all 0 – Peter Jan 25 '12 at 7:25
  • Reply to update, interesting but still there are 2 or 3 queries between the 2 "sp_reset_connection" why would that result. – Peter Jan 25 '12 at 14:57
  • Are the 2 or 3 queries between the sp_reset_connection on the same SPID? – user507 Jan 25 '12 at 15:18
  • yes one sp_reset_connection executes then 2 or 3 queries are executed and then one more "sp_reset_connection" is executed all with the same SPID – Peter Jan 25 '12 at 15:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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