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.
Best Answer
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.