Identify Completion Time for Long Running SQL Server Processes using Dynamic Management Objects

By: Tim Ford   |   Comments (1)   |   Related: > Monitoring

Problem

If you've been a DBA for any length of time you've found yourself wondering just how long that backup / restore / index reorganization operation is going to take to complete.  In this tip we will take a look at how to find the percentage complete along with the estimated completion time for long running operations that are currently executing.

Solution

Those, and some other operations will actually provide a percentage of completion metric that can then be extrapolated based upon the performance to-date to determine a completion time. Depending on your version of SQL you'll be able to see results for the following operations (thank you Aaron Bertrand for previously itemizing these):

• Database Backup and Restore
• Index Reorganizations
• Various DBCC operations (SHRINKFILE, SHRINKDATABASE, CHECKDB, CHECKTABLE...)
• Rollback Operations

For the sake of identifying this information we only need to look as far as two Dynamic Management Objects: sys.dm_exec_sql_text, which is a Dynamic Management Function (which expects a parameter of sql_handle to be passed to it) and sys.dm_exec_requests, which provides information about requests submitted to the query engine for processing from active sessions on the SQL instance.

The dm_exec_requests DMV provides the sql_handle information (a unique identifier for a batch SQL command on a SQL instance) that dm_exec_sql_text needs to return the actual batch command for the request. The dm_exec_requests DMV also provides information via two columns: statement_start_offset and statement_end_offset that will identify the current statement in the batch that is executing at the time.

Therefore the query below returns not only information about the batch command and when it's going to complete, but it dials into the specific statement being executed and that is the statement for which the percent_complete information corresponds - not the completion metrics for the batch as a whole.

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);

Let's say you were executing index rebuilds and reorganizations that looked like this:

--=======================
-- Reorgin' Some Indexes
--=======================
ALTER INDEX [PK_PAT_ENC_RSN_VISIT] 
ON [EpicClarity_DBCC].[dbo].[PAT_ENC_RSN_VISIT] REORGANIZE 
WITH (LOB_COMPACTION = ON);

ALTER INDEX [PK_FAILED_LOGINS] 
ON [EpicClarity_DBCC].[dbo].[FAILED_LOGINS] REORGANIZE 
WITH (LOB_COMPACTION = ON);

ALTER INDEX [PK_PAT_ENC_WT_TM_LOG] 
ON [EpicClarity_DBCC].[dbo].[PAT_ENC_WT_TM_LOG] REORGANIZE 
WITH (LOB_COMPACTION = ON);

ALTER INDEX [PK_ORDER_SIGNED_PROC] 
ON [EpicClarity_DBCC].[dbo].[ORDER_SIGNED_PROC] REORGANIZE 
WITH (LOB_COMPACTION = ON);
GO

If you run the query above, that gets information from the Dynamic Management Objects, and the index rebuild and reorganizations are running you will see information like this for the statement that is currently executing.  As you can see the percent_complete tells us that this process is 22% complete along with the estimated completion time.  This can be very helpful to know how much longer an operation will take to complete, especially for very long running operations.

Next Steps

• Next time you have a long running process and want to know the percent complete, utlize this handy T-SQL script.
• Be sure to check out the other MSSQLTips related to the SQL Server Dynamic Management Objects.
  • Finding a SQL Server process percentage complete with DMVs
• Not familiar with CROSS APPLY? Here is a tip on its syntax.

About the author

Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips