Two execution time authoritative explanations for SET statistics times on

I saw a post on Sqlservercentral today explaining the two CPU execution time on the SET statistics-on output (Daniel's explanation):

CPU time is what much time was spent by the CPU (or CPUs). Total time is a long it took from start to finish.

For example, if it takes 1 millisecond for the CPU to the ' drive ' array, the IT needs data from a set of addres SES, and then it takes 5 minutes for the drives to dump that data into RAM, and then it takes the CPU ten milliseconds to P Rocess the data, and 4 to format it and send it to the video card, you'll end up with the milliseconds of CPU time, and a T Otal time over 5 minutes. (That's a bit of a simplification, but it's essentially ' s going on.)

The time to render the data for display can be much longer than the CPU time, as can the drive access time.

You can also has the opposite, where CPU time is greater than total time. This comes from multiple CPUs. If each one spends the milliseconds on something, the on a 4-CPU server, you can end up with the milliseconds of CPU time, and A total time of something like milliseconds.

-------------------

n General, the time difference between CPU time and elapsed time was the time that the worker executing that query spent WA Iting.

It may has been waiting for IO to be completed (data off disk). It may has been waiting for a lock or latch to be released. It may has been waiting for a memory grant. It may even has been waiting for some time on the processor.

Often, when doing time tests to a PROD server (eg with profiler) a large difference between CPU time and elapsed time Indi cated a possible blocking scenario, especially if it happens regularly.

----------------------------

So to summarize it maybe 4 things:

1. IO (data off disk)
2. Lock or latch to be released
3. May wait for a memory grant
4. May wait for some time on the processor

--------------------------

In general, the time difference between CPU time and elapsed time was the time that the worker executing that query spent W Aiting.

It may has been waiting for IO to be completed (data off disk). It may has been waiting for a lock or latch to be released. It may has been waiting for a memory grant. It may even has been waiting for some time on the processor.

Often, when doing time tests to a PROD server (eg with profiler) a large difference between CPU time and elapsed time Indi cated a possible blocking scenario, especially if it happens regularly.

That means that there are a number of times in total that will be greater than the actual execution time, even when the table is drawn, that'sprobably mostly time spent rendering results.
That's one of the explanations for my essay.

Two execution time authoritative explanations for SET statistics times on