11

what is meaning of last_worker_time and last_elapsed_time in DMV sys.dm_exec_query_stats and what is differnce between them?

when I fire below query

    SELECT TOP 20 
qs.last_worker_time, qs.last_worker_time/1000000 last_worker_time_in_S,
qs.last_elapsed_time, qs.last_elapsed_time/1000000 last_elapsed_time_in_S
FROM sys.dm_exec_query_stats qs
order by qs.last_worker_time desc

I get reult like below.

enter image description here

On thing i notice was either both are equal or elapsed time is more than worker time.I would like to unsderstand significany of both so it also might help me in performance tunning.

17

Worker time is the time the task(s) was effectively active, occupying a scheduler and running code (ie. not suspended). Elapsed time is clock time. On a DOP 1 query the worker time will be at most the elapsed time, less if the task was suspended at any moment during execution (thus the clock time would advance, but the worker time not). For DOP > 1 the worker times aggregate so they can exceed elapsed time, while still subject to suspension.

A significant difference between worker time and elapsed time is indicative of blocking, consider the 682616 worker vs. 11509766 elapsed: this a request that blocked for 11 seconds waiting on something (probably a lock).

| improve this answer | |
  • Note: DOP stands for "degree of parallelism"; 1 means the entire query is processed by a single CPU, more than one means the job is divided into threads, each of which can be run by a different CPU. – RDFozz Jul 26 '17 at 22:12
  • @RDFozz Sorry, I know this is an old thread. Just wanted to make a slight correction to what you said. The DOP applies to each operator in the execution plan of the query.So you can still have multiple threads running per query, just not per operator. sqlmag.com/blog/common-misconception-about-maxdop – SpaceGhost440 Sep 6 '17 at 13:39

Your Answer

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