June 6, 2008 at 1:36 pm
When I use
SET STATISTICS TIME ON
I get these results:
(2679 row(s) affected)
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 6053 ms.
Question:
What is CPU time?
And as from my sample CPU time is only 15 ms
but elapsed time is 6063 ms
what does it mean?
Does it mean CPU processing it fast
but it takes too long for the query to
run?
June 6, 2008 at 2:43 pm
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.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 6, 2008 at 2:47 pm
It helped me even if it doesn't help the OP.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 6, 2008 at 2:50 pm
I have exactly the same two databses.
PROD database is on SQL Server 2000
DEV database is on SQl Server 2005
I run the same query with SET STATISTICS TIME ON.
PROD results
------------
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 60 ms.
DEV results
-----------
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 12422 ms.
Why the elapsed time is so huge on DEV?
It's Windows 2003 server with 3 GB RAM
When the query is running CPU on this box is less than 2%.
Nothing else is running on this server.
Where the bottleneck is?
June 6, 2008 at 3:02 pm
I did a small test.
I wrote a small loop:
declare @number float
declare @Result float
set @number = 0
while @number <= 100000
begin
set @Result = @number * 1.003
set @number = @number + 1
print @number
end
and it took
29 sec on PROD
and
31 sec on DEV
So it's not that much of a difference from the hardware point of view?
Am I right?
Then what seems to be the problem?
Why is it slow on SQL 2005 box?
June 6, 2008 at 3:03 pm
If you restored the database on the DEV server from a backup on the PROD server you probably want to update the statistics for one. As you can see the CPU time actually decreases on the SQL 2005 box so the CPU is not the problem. What is the disk system on each machine? Is it that "slow" on each run or just the first one?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 9, 2008 at 7:28 am
I Remote Desktoped to this DEV box,
connected to the LOCAL server and ran exactly the same query.
Elapsed Time is 193 ms.
From my PC Elapsed Time is always more than 7000 ms.
So nothing is wrong with the box I guess?
It's just slow from my PC to that server.
It looks like I'm entering Network problems area and I'm not too strong here.
Maybe I'm on a different Domain or something?
From my past experience I know Network guys always say "everything is OK with your network connection".
Now I have to sit down and think how I prove them that
my connection to the DEV server is slow...
"ping" won't help. It shows "time<1ms".
TRACERT doesn't indicate any problems as well:
DEV
1 <1 ms <1 ms <1 ms 172.16.41.3
2 <1 ms <1 ms <1 ms toracceval00.accounts.xxxxxxcorp.com [172.16.38.129]
PROD
1 <1 ms <1 ms <1 ms 172.16.41.3
2 <1 ms <1 ms <1 ms 172.16.1.85
I'm frustrated.
June 9, 2008 at 8:57 am
Maybe it's just a slow connection somewhere along the way.
My boss had a 1-Gig card in his computer, and a 1-Gig connection at the switches, but was getting horrible download speed from the servers. Ping was plenty fast, but download was terrible. Turned out his computer was connected via his phone (cable from computer to phone, then phone to switch), and the phone had a 10-Meg switch in it. IT set up a separate line for the comp and the phone, and he ended up quite fast on moving files around.
Since the total time includes the time spent returning results to the client computer, that might do it if the result-set is particularly large. (Drive time can do it too, in some cases.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 9, 2008 at 11:50 am
I actually selected "Include Client Statistics"
and it looks like the problem is always with "Client processing time".
On PRD it's 200-300. On DEV box it's thousands.
That's what's taking so long to run the query.
Trial 3Trial 2Trial 1 Average
-----------------------------------------------------------------------------------------------
Client processing time4000378163754718.667
Total execution time 4015 379664534754.667
Wait time on server replies 15 15 78 36
The question now is what does it mean
"client processing time"? Is it the time my client
takes to check the syntax,connect to the server
or to actually render the results in QA?
June 9, 2008 at 1:36 pm
That's probably mostly time spent rendering results.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 9, 2008 at 2:24 pm
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.
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
June 9, 2008 at 3:02 pm
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
But don't I eliminate 1 and 4
if running locally shows perfect results:
CPU time = 47 ms, elapsed time = 243 ms.
CPU time = 47 ms, elapsed time = 222 ms.
CPU time = 78 ms, elapsed time = 228 ms.
Does it means i can focus on 2 & 3 only?
June 9, 2008 at 3:10 pm
I won't call that perfect. Your elapsed time is still way higher than your CPU time.
Run the query in 1 window of management studio, in the other query sys.dm_exec_requests and look at what the wait type is.
Memory grant is not common unless the server is very short of memory. It's not consistent either.
If it's waitjng for IO, you'll often see the first execution has a long diuration and subsequent queries are faster (because the data's in memory, doesn't have to be fetched from disk)
Waiting for CPU, easy to check. What % CPU is SQL using?
There are a number of other things it could be waiting for as well. SQL 2005 has a couple hundred different wait types.
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
June 9, 2008 at 8:44 pm
So if it's IO what WAIT_TYPE I would see?
June 10, 2008 at 8:59 am
Probably IO_Completion or Async_IO_Completion
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
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply