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_typelast_wait_type FROM sys.dm_exec_requests
order by ‘Minutes to Completion’ desc

  1. Alexander Suprun says:

    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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s