I have a sql query which has multiple joins and uses a view which has multiple joins.I captured the query in sql profiler and i see few information in there. I am trying to understand whether the query is a long running or long waiting query. Details:

  • query runs in parallel.
  • Has 40 logical CPU.
  • CPU column : 156595
  • Duration column : 45373

I am trying to understand if the query spend more CPU time or not.?Since the query runs parallel ,i am not sure what this high CPU time shows.

Any help.

  • The query ran for 45~ seconds and used about 156~ seconds of cpu time. A query can be long running & long waiting at the same time. If you run the query yourself on dev, does it take as long? Checking the actual execution plan XML might help to figure out if it was waiting on stuff. – Randi Vertongen Sep 27 '19 at 15:33
  • @RandiVertongen : Thanks.I used the brentozar.com/archive/2014/11/… to identify the number of CPUs being used and i see 8 scheduler_id's which means 8 CPUs. – user9516827 Sep 27 '19 at 15:45
  • So i believe a CPU average time of 19.5 sec.Hope that i am correct.This means the CPU average time is less compared to the actual duration,which means that the query is waiting for some resources. – user9516827 Sep 27 '19 at 15:47
  • It is not a given that all cores are used for the entire query execution, also not all plan operators can go parallel. Adding the actual execution plan to pastetheplan.com could be an option if you want us to take a look :) – Randi Vertongen Sep 27 '19 at 15:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.