对 set statistics time on的两个执行时间权威解释

今天在sqlservercentral上看到一个帖子，关于对set statistics time on输出两个cpu执行时间的解释（大牛的解释）：

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

For example, if it takes 1 millisecond for the CPU to tell the drive array that it needs data from a set of addresses, and then it takes 5 minutes for the drives to dump that data into RAM, and then it takes the CPU 10 milliseconds to process the data, and 4 to format it and send it to the video card, you'll end up with 15 milliseconds of CPU time, and a total time over 5 minutes. (That's a bit of a simplification, but it's essentially what'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 have the opposite, where CPU time is greater than total time. This comes from multiple CPUs. If each one spends 30 milliseconds on something, on a 4-CPU server, you can end up with 120 milliseconds of CPU time, and a total time of something like 31 milliseconds.

 

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

n general, the time difference between CPU time and elapsed time is the time that the worker executing that query spent waiting.

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

Often, when doing time tests on a prod server (eg with profiler) a large difference between CPU time and elapsed time indicated 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 is the time that the worker executing that query spent waiting.

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

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

 

也就是说总共的时间有很多种情况会大于真正的执行时间， 甚至于绘制表格的时间，That's probably mostly time spent rendering results.
这也算是对我这篇文章的一个解释

http://www.cnblogs.com/perfectdesign/archive/2009/10/25/1589661.html