different CPU Times for same query

Question

Post reply

different CPU Times for same query

mohsinabdul

SSC Enthusiast

Points: 172
More actions
March 2, 2011 at 12:52 am

#234590

Hi,
I am executing a complex query directly on a test DB machine and I am the ONLY user on that machine.
I have SET Statistics Time ON.
i have noticed that CPU time is not showing consistent figures, intially it showed 28 ms, second time 14 ms, third time 8 ms, then 8, then 14 again.
i haved tested other quries also, CPU time varies.
i am using SQL server 2005 enterprise edition
what can be the reason?
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Pouliot Philippe SSCommitted Points: 1912 More actions · Answer 1

Pouliot Philippe

SSCommitted

Points: 1912

March 2, 2011 at 12:04 pm

#1293676

Maybe the cache

mister.magoo SSC-Forever Points: 47174 More actions · Answer 2

It could be that cpu time measurements on something that quick are not reliable...

I have also noticed the same behaviour.

Also try putting two queries in one batch, run them both together and note the cpu time for each - then swap them round so the first one is second, and the second one is first, then run them again (together as one batch)....it makes you stop worrying about milliseconds!

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

mohsinabdul SSC Enthusiast Points: 172 More actions · Answer 3

while searching i found an article in which someone complaint that the CPU time is more than elapsed time on which someone responded that the reason may be "processor sometime works on different frequnecy due to power saving options and SP3 has fixed this issue" (as it is not using CPU timestamp for calculating time). i have not checked it yet as here team is not confident to install SP on production server. will post the results.