what is difference between last_worker_time and last_elapsed_time in DMV sys.dm_exec_query_stats?

by Pritesh   Last Updated July 26, 2017 22:06 PM

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.

Answers 2

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

Remus Rusanu
Remus Rusanu
December 24, 2012 08:49 AM

Remus Rusanu,

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.


Monique L
Monique L
July 26, 2017 21:30 PM

Related Questions

Shrink my .mdf file in SQL Server?

Updated March 26, 2015 08:02 AM

Unable to open SQL Server database file (.mdf)

Updated April 24, 2015 21:02 PM

What transaction logs are for

Updated July 03, 2015 13:02 PM