Kendra Little explains some of the intricacies behind query durations:
I typically look at the ‘CPU time’ metric when tuning instead of ‘elapsed time’ (duration). This can work well for tuning because you’re measuring how much more efficient you made the query in terms of CPU cycles.
But ‘CPU time’ isn’t perfect, and it can get a little weird for reporting results to users, because:
-
If the query uses parallelism, CPU time can be higher than the duration — which may make the query seem “slower” than it actually is to anyone reading a report
-
‘elapsed time’ includes all the time that it takes to display the results in Management Studio, which is probably a different duration than it would take to return the results to an application server. If you’re just returning a few rows, this may be negligible– but once it gets into the thousands of rows, it can be very noticeable.
Moral of the story: also use SQL Sentry Plan Explorer…