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.

What precisely does the query duration measure when conducting a profiler trace?

I have a stored procedure which performs data access (SELECT), and it is taking an average of around 30 seconds to return results to the application. But when I run a trace on it I get an average duration of around 8 seconds, max duration 12 seconds, average CPU 5.5 seconds.

What could cause it to take so much longer to return the result set? I am not accumulating large NETWORKIO waits. The result set is only around 270 rows of simple text data, around 50 columns total.

share|improve this question
1  
This does not include the time taken to deliver and render the results on the client. –  Aaron Bertrand Jul 20 '12 at 11:29
    
That makes sense, but if there was a large delay (3x the execution time to get results rendered) wouldn't you expect to see large NETWORKIO waits? My last restart was just yesterday, with NETWORKIO waits of 230 seconds total (doesn't seem like much for one day to me). –  sqlbattsman Jul 20 '12 at 15:01
    
Did you try executing the command with SET STATISTICS TIME etc? Also if time is spent rendering you wouldn't see that in network. Any differences shown if you render all that data in results to text vs. grid? –  Aaron Bertrand Jul 20 '12 at 15:05
    
Results to text is not significantly different. Now when I run it with SET STATISTICS TIME the total duration I get is much more accurate –  sqlbattsman Jul 20 '12 at 15:21
add comment

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.