For DBA day-to-day activities, Sometimes it happened, anyone executed queries on production server and after long waiting, he/she come to DBA and ask a very funny question like I have executed this/that queries and its running since long time, now tell me how
much time it will take to complete
Sometime its very difficult to explain but from using sys.dm_exec_requests DMV, somehow we can find out the answer like
SELECT percent_complete,start_time,status,command,estimated_completion_time/1000/60 As ‘Minutes to Completion’,
total_elapsed_time/1000/60 As ‘Minutes Elapsed’, wait_type, last_wait_type FROM sys.dm_exec_requests
order by ‘Minutes to Completion’ desc
You forgot to mention that only very few types of queries will report estimated_completion_time as well as percent_complete. And all these queries are administrative ones like DBCC or DATABASE BACKUP.
So there is basically no way to find out how much time it will take for any type of query to complete.
LikeLike
Hi Alexander Suprun,
Thanks a lot for your valuable feedback. Yes its TRUE.
LikeLike