Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (1)   |   Related Tips: More > Monitoring

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, March 05, 2014 - 12:09:55 AM - Glen Moffitt Back To Top

awsome script, thanks so much..  Had a sqlcmd backup job running long tonight, this allowed me to see the % complete and estimated completion time.


Learn more about SQL Server tools