17

When performance tuning, what is more important:

  • CPU time or elapsed time?
  • Are there scenarios where one of them is more important than the other?

An example: while performance tuning the CPU time will reduce by ~38% but the Elapsed Time increases by ~22%. Is this an improvement?

Community
  • 1
EnterTheCode
  • 281
  • 1
  • 2
  • 4
  • 6
    Important to who? Your requirements may differ from mine. For most of my customers, elapsed time (amount of time users spend waiting) is far more important than CPU time, so no, that would not be an improvement. Your CPUs are doing less work but, in all likelihood, more scarce resources are being used to compensate (typically I/O). You may have IT bean-counter types who value CPU time over elapsed time, because they are tuned in to alerts on CPU spikes or what have you. They have different needs than your end users, but you may need to satisfy both. I don't think there is an easy answer. – Aaron Bertrand Jan 13 '17 at 17:55

2 Answers2

19

CPU Time is the quantity of processor time taken by the process. This does not indicate duration. "Elapsed Time" represents the total duration of the task. If a given task uses a parallelism of 8 (i.e. 8 threads), and each thread is used at a rate of 100% over the entire duration of the task, CPU time could be 8000ms, while Elapsed Time would only be 1000ms.

Therefore, shorter "Elapsed Time" indicates faster response time.

You may want shorter CPU time and longer elapsed time if you are concerned about CPU pressure, and don't care about user experience.

You may accept longer CPU time if that results in a drop in elapsed time, since that might indicate the user is waiting less, at the expense of higher CPU utilization.

Arguably, the more important metric for query tuning would be wait times.

Hannah Vernon
  • 70,041
  • 22
  • 171
  • 315
6

... while performance tuning the CPU time will reduce by ~38% but the Elapsed Time increases by ~22%. Is this an improvement?

This entirely depends on what you are optimizing for, and why. In the most common sense - speed of query output - this is not an improvement.

However, if your system is CPU-bound, then this would be an improvement, as you decreased the CPU load. In fact, if this was the case, the end result could also mean a faster return of the resultset.

And if your system was disk bound or memory bound, this would be a neutral result, since there is no indication that it either improved or degraded these measures.

So, 95% of the time tuning to lower CPU utilization is not an improvement. In the remaining few cases, it may be an improvement.

Hannah Vernon
  • 70,041
  • 22
  • 171
  • 315
Laughing Vergil
  • 2,058
  • 10
  • 21