8

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.

Improve this question
4
  • 4
    This does not include the time taken to deliver and render the results on the client.
    – Aaron Bertrand
    Jul 20, 2012 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, 2012 at 15:01
  • 3
    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, 2012 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, 2012 at 15:21

1 Answer 1

Reset to default
1

Duration represents the total time from start to finish the request by SQL Server, which represents time to send over network, cpu time, waits, and execution. As Aaron mentioned once the client has received the data SQL Server has finished it's timing, and the client is then processing. This should not be part of the duration.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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