| Author |
Topic |
|
sql777
Constraint Violating Yak Guru
314 Posts |
 Posted - 2007-05-08 : 15:07:07
|
| When I turn statistics on when in QA, I am a little confused as the what the 'cpu time' and 'elapsed time' values mean.for example, I run my query and I get the following statistics info:"sql server execution times:cpu time = 1030ms, elapsed time = 327 ms"So do I add both times to get the total time to run the query??? |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-08 : 15:10:34
|
I reckon that's 3 CPUs at 95% utilisation ...  I reckon that's 4 CPUs at 78% utilisation ... 3 CPUs would be 105% utilisation, in which case I'll buy them off you! |
 |
|
|
pootle_flump
1064 Posts |
Posted - 2007-05-09 : 03:48:22
|
| The total running time is the elapsed time. The cpu time is of more interest for performance tuning. The main point of interest though are the logical reads. Check out sql-server-performance for useful articles about this stuff e.g.http://www.sql-server-performance.com/statistics_io_time.asp |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-05-09 : 04:01:13
|
| Ah - beg your pardon - I see your point - didn't note the values |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-09 : 08:50:46
|
| You are still right about checking Logical Reads when doing performance tuning ... |
|
|
|
sql777
Constraint Violating Yak Guru
314 Posts |
Posted - 2007-05-09 : 09:54:05
|
| Kristen,I didn't get your point! My queries are running too long i'm guessing? hehe |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-05-09 : 10:25:33
|
| Actually I go for physical reads. If the data is cached then you can get a lot of reads in very little elapsed time.The idea is to get the data into memory then do as much processing as possible on it before it gets purged.This of course means tuning the process to the amount of memory available - this used to mean knowing knowing what else is running. As systems tend to have a lot more memory now it's more making sure that no process tries to access vast amounts of data unless it's the only thing running as that would purge everything else from memory.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-05-09 : 11:52:22
|
| How do you control the physical reads? Granted IO is the greatest bottleneck but my understanding is you can't really dp much about it. You can't even pin tables anymore (not that I ever did as it happens). If the data is not cached then it needs to be read from disk. It will not be cached if the cost of keeping it cached is too high in relation to the benefit. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-05-09 : 12:02:11
|
| You can control how much data you read. Do things in batches rather than processing vast amounts of data. It's often faster to read a few thousand rows and process those before going on to the next rather than trying to process everything at once - especially if there are other processes running.It was a necessary skill with v6.5 and before - sql server has got better at dealing with large volumes but it still helps to get round a lot of performance issues.Also if something fails you can carry on from where you left off rather than starting again.Also gives the log a chance to clear and for data to start relplicating.The actual tuning is done by trial and error. If you keep increasing the batch size you should see a linear incrase in time taken until you reach the memory limit at which point there will be a slow down. ==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-05-09 : 12:07:57
|
| Thanks nr - that's what I typically do. But I would equate that with managing logical reads rather than physical reads - or maybe just managing reads full stop. I think my point was that with statistics IO the logical reads are the same for the same query & data irrespective of everything else. The physical reads though can be affected by the contents of the cache and there is little you can, or would want, to do about that. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-05-09 : 12:14:17
|
| The point is that if you read a lot of data frmo a table that will give a high physical disk IO.Say you have to process a large table twice. If you process the whole table each time then you will have to read all data from disk twice.If you read it in batches tailored to the size of memory and do the two processes in the batch then you only have to physically read the table from disk once but you will logically read it twice. Most processes are more complicated and you can get more benefit from batching.Try performing an aggregate on some data then doing it again to see the sort of performance benefit you can get if it fits in memory.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-05-09 : 13:36:01
|
| Right - I think I have you. You are talking about volumes of data that would get flushed from the cache immediately as they are too large to be held in memory?So we aren't directly managing the physical reads as such but by working with smaller datasets we allow the engine to cache these sets in the data cache while work with them, resulting in less IO.Thanks for the clarification. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-09 : 14:57:22
|
| "You are talking about volumes of data that would get flushed from the cache immediately as they are too large to be held in memory?"I think it is more that the Elephantine approach is squashing everything that is in the cache already ..."we allow the engine to cache these sets in the data cache while work with them"And I think that that is more to do with the smaller records sets not squashing the other data in the cache, so that data is still there when the processes that want it call for it. So the "core data" which is frequently used gets retained in memory, rather than getting expelled by some blunderbuss "SELECT * FROM MyBiggerThanRAMTable" !Kristen |
|
|
|
sql777
Constraint Violating Yak Guru
314 Posts |
Posted - 2007-05-09 : 16:13:40
|
| Kristen,what did you mean when you said "I reckon that's 4 CPUs at 78% utilisation ... 3 CPUs would be 105% utilisation, in which case I'll buy them off you!" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-09 : 16:17:56
|
| cpu time = 1030ms, if that is 4 CPUs then it is 257ms each. elapsed time = 327 ms, that's 78%Whereas for 3 CPU that would be 343 ms each - which for an elapsed time of 327 ms each is defying the conservation-of-process-power law!But I'm guessing that's how the figure is arrived at. If you have any number of CPUs >= 4 it holds good. If you have < 4 CPUs I'm hosed!Kristen |
|
|
|
sql777
Constraint Violating Yak Guru
314 Posts |
Posted - 2007-05-09 : 16:27:32
|
| oh so I never realized the cpu time and elapsed time are related like that.So on a server with just 1 CPU both cpu-time and elapsed-time will be the same?(BTW the server is Hyperthreaded so not really 2 CPUS) |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-05-09 : 17:50:21
|
quote:
Originally posted by sql777
So on a server with just 1 CPU both cpu-time and elapsed-time will be the same?
No - IO takes time to complete but the CPU sits around thumb twiddling whilst it takes place. The CPUs typically are only active for a fraction (often a significant fraction) of actual execution time. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-10 : 04:18:17
|
| ... and the CPU can be busy with someone else's query whilst its waiting for the data for your query to arrive from diskKristen |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-05-10 : 07:33:27
|
quote:
Originally posted by Kristen
... and the CPU can be busy with someone else's query whilst its waiting for the data for your query to arrive from disk
Beg your pardon yes - I meant that there are various processes that take place that will affect the elapsed time but won't require any processing by the processor(s). The CPU activity for other queriers won't be reported in STATISTICS TIME for your own query though. |
|
|
|
|
CPU Time versus Elapsed Time