Tell me more ×
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.
up vote 0 down vote favorite

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.. – Petoj Jan 25 '12 at 8:55
@jl01 well its not bad but it does not explain a 8 sec execution time.. – Petoj Jan 25 '12 at 14:16

1 Answer

up vote 1 down vote

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 – Petoj 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. – Petoj 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 – Petoj Jan 25 '12 at 15:42

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.