How to check estimated finish time for some queries

As I am waiting for a backup to complete I decided to do something useful. Here’s a short query that shows running queries and displays estimated finish time:

set nocount on
select er.session_id, er.start_time, st.text, er.percent_complete, dateadd(ms, datediff(ms, start_time, getdate()) * 100 / percent_complete, start_time) ETA
from sys.dm_exec_requests er
cross apply sys.dm_exec_sql_text(sql_handle) st
where er.percent_complete > 0

I saw it in once in Jack Vamvas’s blog, but couldn’t find it, so I decided to write my own version.
Right now I am running it in a loop (using WAITFOR DELAY and GO 1000) to pass the time quicker while backup is finishing. One word of notice – it does not apply to all queries, only to those that report progress in percent_complete column of sys.dm_exec_requests – so there’s no way for any of DML queries to show there. I use it for rough progress estimate of backups/restores/DBCC.

Advertisements

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