SQL Server Profiler

Question

When you use SQL Server profiler What's difference between Duration and CPU? I know by default both were shown as in MilliSeconds and CPU is amount of CPU time it took to run the query while duration is more like amount of time it took query to return any data.

Based on my understanding if there is any blocking etc. your CPU time could be less but duration could be higher.

Can anyone proivde more insight? Scramble through the BOL but hard to find subtle differences.

Answer 1

The CPU time represents time consumed actively (not suspended) by the request/statement (depending on what event you're monitoring) on all CPUs. For single threaded queries this will always be less or equal than the elapsed time (duration = wall clock time). But for parallel queries the CPU time can exceed the elapsed time because it adds up the CPU time from several CPUs.

In general this distinction between elapsed and CPU time applies everywhere in SQL Server, from SET STATISTICS TIME ON output, to DMVs like sys.dm_exec_query_stats (where CPU time is called worker_time) or sys.dm_exec_requests (again named cpu_time in this DMV).

Answer 2

Duration can include wait time, I/O, data transfer time, etc. CPU is just the time the CPU spent. If your query is CPU-bound, it will be a lot closer than if it is I/O- or memory-bound.