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.

  • 4
    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
  • 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 '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
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.

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.