Estimate Completion Time For Long Running SQL Query

With Microsoft SQL Server, sometimes there can be long running SQL Queries which you would like to get an estimated ETA from.

Tim Ford has wrote a brilliant article which will provide an ETA for the following types of queries:

  • Database Backup
    • BACKUP
  • Database Restore
    • RESTORE
  • Index Reorganisation
    • DBREINDEX
  • DBCC Operations, such as:
    • SHRINKFILE
    • SHRINKDATABASE
    • CHECKDB
    • CHECKTABLE
  • Rollback Operations
    • KILLED/ROLLBACK

Running the following SQL query will give the following information for all transactions which are awaiting completion:

  • Session ID
  • Percentage Complete
  • Time Elapsed in Seconds
  • Wait Type
  • Wait Time
  • Last Wait Type
  • Estimated Completion Time
  • SQL Transaction Text
  • SQL Statement Currently Executing
SELECT R.session_id, 
R.percent_complete, 
R.total_elapsed_time/1000 AS elapsed_secs, 
R.wait_type,
R.wait_time,
R.last_wait_type,
DATEADD(s,100/((R.percent_complete)/ (R.total_elapsed_time/1000)), R.start_time) estim_completion_time,
ST.text, 
SUBSTRING(ST.text, R.statement_start_offset / 2, 
 (
 CASE WHEN R.statement_end_offset = -1 THEN DATALENGTH(ST.text)
 ELSE R.statement_end_offset
 END - R.statement_start_offset 
 ) / 2
) AS statement_executing
FROM sys.dm_exec_requests R
CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) ST
WHERE R.percent_complete > 0
AND R.session_id <> @@spid
OPTION(RECOMPILE);

Leave a Reply