progress.sql

/* This query provides a comprehensive view of actively running commands, with a particular focus on
   operations that report progress (e.g., backups, restores, index operations, DBCC commands). */
select x.session_id as SPID, x.command, x.[status],
       FORMAT(x.percent_complete, 'p0') as percent_complete,
       FORMAT(x.start_time, 'MM/dd/yyyy hh:mm:ss tt') as start_time,
       FORMAT(x.total_elapsed_time / 3600000 % 60, '00') + ':' + FORMAT(x.total_elapsed_time / 60000 % 60, '00') + ':' + FORMAT(x.total_elapsed_time / 1000 % 60, '00') as elapsed_time,
       FORMAT(DATEADD(MILLISECOND, x.estimated_completion_time, GETDATE()), 'MM/dd/yyyy hh:mm:ss tt') as estimated_completion_time,
       FORMAT(x.estimated_completion_time / 3600000 % 60, '00') + ':' + FORMAT(x.estimated_completion_time / 60000 % 60, '00') + ':' + FORMAT(x.estimated_completion_time / 1000 % 60, '00') as estimated_time_remaining,
       FORMAT((x.total_elapsed_time + x.estimated_completion_time) / 3600000 % 60, '00') + ':' + FORMAT((x.total_elapsed_time + x.estimated_completion_time) / 60000 % 60, '00') + ':' + FORMAT((x.total_elapsed_time + x.estimated_completion_time) / 1000 % 60, '00') as estimated_total_time,
       x.physical_operator_name,
       x.query_text
  from (select r.session_id, r.command, r.[status],
               case when index_status.session_id is null then r.percent_complete / 100 else index_status.percent_complete end as percent_complete,
               r.start_time, r.total_elapsed_time,
               case when index_status.session_id is null then r.estimated_completion_time else index_status.ms_remaining end as estimated_completion_time,
               index_status.physical_operator_name,
               t.text as query_text
          from sys.dm_exec_requests r
         cross apply sys.dm_exec_sql_text(r.sql_handle) t
          -- Subquery to calculate progress for index-related operations.
          left join (select eqp.session_id, eqp.node_id, eqp.physical_operator_name,
                            SUM(eqp.row_count) * 1.0 / SUM(eqp.estimate_row_count) as percent_complete,
                            r.total_elapsed_time * (SUM(eqp.estimate_row_count) - SUM(eqp.row_count)) * 1.0 / SUM(eqp.row_count) as ms_remaining
                       from sys.dm_exec_requests r
                      inner join sys.dm_exec_query_profiles eqp on r.session_id = eqp.session_id
                      where r.command like '%INDEX%'
                        and eqp.row_count > 0
                      group by r.total_elapsed_time, eqp.session_id, eqp.node_id, eqp.physical_operator_name) as index_status on r.session_id = index_status.session_id) x
 where x.percent_complete > 0