0

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?

4
  • Does this link over in stackoverflow help? stackoverflow.com/questions/641120/…
    – jl01
    Jan 24, 2012 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, 2012 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, 2012 at 8:55
  • @jl01 well its not bad but it does not explain a 8 sec execution time..
    – Peter
    Jan 25, 2012 at 14:16

1 Answer 1

1

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

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

4
  • yes Duration = "8192947" 8192947 / 1000000 = 8.1 correct me if my math is wrong.. cpu, reads, writes are all 0
    – Peter
    Jan 25, 2012 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, 2012 at 14:57
  • Are the 2 or 3 queries between the sp_reset_connection on the same SPID?
    – user507
    Jan 25, 2012 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, 2012 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.