December 28, 2012

Using the DM tables for estimations.


Using the following query we can obtain estimations based on the statuses stored on the DM tables (Backups, restores, DBCC*, etc...)

SELECT      command, percent_complete,
            'elapsed' = total_elapsed_time / 60000.0,
            'remaining' = estimated_completion_time / 60000.0,
            'SSID' = session_id
FROM        sys.dm_exec_requests

And if you want to filter the selection for a specific action, you can use the following filters.

WHERE command like 'BACKUP%'
WHERE command like 'RESTORE%'
WHERE command like 'DBCC%'

Or with an estimated date:

SELECT   r.percent_complete, estimated_finish_time = DATEADD(MILLISECOND, estimated_completion_time, CURRENT_TIMESTAMP), 
t.[text]FROM   sys.dm_exec_requests AS r CROSS APPLY 
sys.dm_exec_sql_text(r.[sql_handle]) AS t WHERE   r.session_id = 293;



And this is the same query but now including everything and also the SQL handle:

SELECT          'SSID' = session_id,
                        'PID number'=hostprocess,
                        'User'=nt_username,
                        command,
                        'Percent Complete'=CONVERT(VARCHAR(50),percent_complete)+'%',
                        'elapsed' = total_elapsed_time / 60000.0,
                        'remaining' = estimated_completion_time / 60000.0,
                        'Estimated finish time'=DATEADD(MILLISECOND, estimated_completion_time, CURRENT_TIMESTAMP),
                        'CPU Time'=cpu/3600.0,
                        'Number of open transactions'=open_tran,
                        'Status'=queries.status,
                        'Statement'=s.text
FROM             sys.dm_exec_requests requests, sys.sysprocesses queries
Cross apply sys.dm_exec_sql_text (queries.sql_handle) s
WHERE           session_id = spid