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.
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.
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).
Thanks fo ryour reply on this. I just have a question about what DOP stands for. I have a case where worker time is greater than the elapsed time and it's not clear to me why this would happen.