Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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
add comment

1 Answer

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

share|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? –  Shawn Melton 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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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