SET STATISTICS TIME - What "exactly" is CPU time a measure of versus elapsed time, and how are they related?

Question

SET STATISTICS TIME - What "exactly" is CPU time a measure of versus elapsed time, and how are they related?

Jared

SSC Guru

Points: 61793
More actions
March 5, 2012 at 12:33 pm

#391726

So I ran a query with the time statistics on and got a result that puzzled my intuitive (not taught) understanding of these statistics. The CPU Time was 16ms and elapsed time was 9ms. I had always assumed that CPU time was included in elapsed time. So, not being able to find a definition of what is actually being measured, I have come to the experts. Anyone?
Jared
CE - Microsoft

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

CPU time = total time spent by CPU processing the query. Elapsed time = total time between query starting and query completing.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

okbangas SSChampion Points: 11773 More actions · Answer 2

Gail is correct in his definition. To elaborate a bit: If you use 4 cores for 2 seconds, you'll see a CPU time of 8s whereas the elapsed time is 2s. What I've found, is that in general the CPU time increases as the level of parallelism (number of cores used) increases. So, a task that uses 8 seconds of CPU time on a single core, may use four cores for 2.5 seconds, a total CPU time of 10 seconds. So, the CPU load of the server increases, but locks are held for a shorter period of time.

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

Jared SSC Guru Points: 61793 More actions · Answer 3

Thanks for the quick responses. So basically, the maximum CPU time is a function of #cores/cpu * #cpus * elapsed time. Of course it can be lower than this, but should never be greater?

Jared
CE - Microsoft

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Why complicate matters? It's the number of cores available to SQL Server, SQL doesn't care about the number of sockets.

Also consider measuring inaccuracies.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jared SSC Guru Points: 61793 More actions · Answer 5

GilaMonster (3/5/2012)
Why complicate matters? It's the number of cores available to SQL Server, SQL doesn't care about the number of sockets.
Also consider measuring inaccuracies.

Good point 🙂 Thanks!

Jared
CE - Microsoft

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

okbangas (3/5/2012)
Gail is correct in his definition

Gail is a HER , not a HIS 😀

Gail Shaw is one of the MVPs of SQL Server and the best I have seen knowing about indexes, data architecture, storage engine and in all most of the internals of SQL Server. Read more about her here[/url]

okbangas SSChampion Points: 11773 More actions · Answer 7

Sorry, no offensive intended. I'll try to remember 🙂

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

Nadrek SSC-Insane Points: 20039 More actions · Answer 8

I'd also recommend using SQL Profiler (SQL:BatchCompleted event) instead of SET STATISTICS * for measuring reads, writes, duration, and CPU time; Gail's Functions, IO statistics and the Execution plan[/url] article shows one solid reason for using Profiler rather than SET STATISTICS IO, at least.

Also consider your system; on at least some systems, IO is more important than CPU time (spindle contention).