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

Advertisements
Comments
  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.

    Like

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s