CPU v duration

Question

CPU v duration

spencepa

Ten Centuries

Points: 1042
More actions
August 15, 2005 at 4:37 am

#187035

Hi, been looking at profiler over the last few weeks and want to know the difference between CPU and duration and what causes large discrepancies
I had some queries that were very simple and quick that were taking ages eg "select region from applicants where ID = 133000" The estimated plan uses the ID index and has a subtree cost of .0064 so no probs there... But thru profiler i saw a 0 CPU v 17000 duration... 17 seconds!
Looking now, most of the queries running are much better (eg 2300 v 4010), it looks as though duration has something to do with the no. of rows returned or read - but that doesnt add up to the query above. Is there anything i have missed like locks?
Thanks in advance

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

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

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

Duration simply shows the time it took from when a query started to when the results where returned. CPU shows the time the processor was busy executing the query. If there are for instance locks that block the query from executing the duration will be higher than CPU. A query could execute in 1 sec in a single-user environment but need 10 sec in a multi-user environment where other queries might block it.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

spencepa Ten Centuries Points: 1042 More actions · Answer 2

thanks Chris

Whats the best way to locate these long running queries that lock multiple rows or even whole tables, - i think they are impacting the whole db. We keep getting random calls where the web page times out for users.

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 3

Take a look at this KB article that provides information on how to create and use the stored procedure sp_blocker_pss80. This will give you valuable information to find which processes are blocking others.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Ian Yates SSCoach Points: 19738 More actions · Answer 4

And also bear in mind that the slowest thing in your DB server will be your disk(s). So the duration includes the time spent waiting on data from the disk - time that the CPU will spend doing other things.

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 5

Yes, good call. In this case with such large difference I assume there is more in play, but in general it is as simple as CPU shows how many milliseconds the processor worked to resolve the query, duration shows how long time it took from start to finish and can include loads of different wait times. For instance, if the query issues a WAITFOR statement that time will be included in the duration.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

spencepa Ten Centuries Points: 1042 More actions · Answer 6

ok update for you

been off reading lots of stuff after seeing the sp_blocker output - eventually came across a MS ppt which details info dbcc sqlperf(waitstats) and a couple more scripts that scan the sqlperf over 10 minutes... The outcome was "cxpacket" was taking up 90% of the wait_types - it suggested setting degrees of parrallelism for multi processor systems from 0 to 1, this switch parrallelism off (2xeon processor system). So did this today.....

Result was an immediate drop of our longest running web queries from up to 10 secs to ALL below 2 seconds 😮 Now i would call that a result!

Thanks for the help - also found lots of interesting stuff like DBCC DROPCLEANBUFFERS, DBCC FREEPROCCACHE, SET STATISTICS IO ON, SET STATISTICS TIME ON!!!

Rao.V Hall of Fame Points: 3452 More actions · Answer 7

Hi all....

The above methods wats mentioned are good in finding out the CPU utilization.Can anyone advice me how i can monitor all the processes consuming CPU usage continuously (by the use of some job scheduled to execute a procedure which determines the CPU usage)and can keep some threshold like 70% and if CPU usage crosses that threshold I should be able to get an alert.

Thanks in Advance...

Vinay