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


By:   |   Updated: 2014-03-03   |   Comments (1)   |   Related: More > Monitoring


Is the Database the Culprit of Your Application Issues?

Free MSSQLTips Webinar: Is the Database the Culprit of Your Application Issues?

When you're troubleshooting application performance issues, have you ever found the problem residing a few layers deep in the database or not at all? How long did it take you to find and fix the issue? Don't worry if you said "a while". Learn how to solve performance problems fast.


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 Updated: 2014-03-03


get scripts

next tip button



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

View all my tips
Related Resources





Comments For This Article




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

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



download





Recommended Reading

How to Read Log File in SQL Server using TSQL

How to setup SQL Server alerts and email operator notifications

SQL Server Wait Stats Monitoring with PowerShell

Posting SQL Server Notifications to Slack

SQL Server High CPU Query Use Monitoring with PowerShell








get free sql tips
agree to terms


Learn more about SQL Server tools