3
  1. Parallel plans mean that cpu_time can be greater than total_elapsed time.
  2. Therefore, per-query wait time is not possible to determine accurately from DMVs on servers with more than one CPU thread.
  3. When total_elapsed_time > cpu_time, we can conclude from DMVs that a query waited for at least (total_elapsed_time - cpu_time).
  4. When cpu_time >= total_elapsed_time, we can't conclude from DMVs whether a particular query waited, nor for how long.

Not homework questions; just putting together some documentation for DMV queries that I'm writing.

Improve this question
  • 2
    1. is true. For the rest, you know that sys.dm_exec_requests is not the only DMV on the server, right? There is also sys.dm_os_wait_stats, for example (though it shows aggregate waits, not per-query waits). And in SQL Sentry Plan Explorer PRO, when you generate an actual plan, we'll actually fire up an XEvents session and tell you exactly what wait stats were associated with your batch. You may also want to try sp_whoisactive. – Aaron Bertrand Jan 15 '13 at 18:12
  • Disclaimer: I work for SQL Sentry. – Aaron Bertrand Jan 15 '13 at 18:12
  • For parallel plans you can look at sys.dm_os_waiting_tasks IIRC sys.dm_exec_requests just contains details of thread 0. – Martin Smith Jan 15 '13 at 18:15
  • 1
    Hey thanks, the DMV query I'm working on is specifically about checking current activity, so sys.dm_os_wait_stats is probably not appropriate in this case. However, the tip about Plan Explorer Pro is useful, and I'll have a closer look at sys.dm_os_waiting_tasks. I use and like sp_whoisactive, but it doesn't always report everything I want to know, and sometimes I don't want to create a stored procedure due to intense change control rules. – James L Jan 15 '13 at 19:03

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.