CPU v duration

  • 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

  • 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.

  • 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.

  • 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.

  • 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.

  • 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.

  • 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!!!

  • 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

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

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