Identify Completion Time for Long Running SQL Server Processes using Dynamic Management Objects
By: Tim Ford | Updated: 2014-03-03 | Comments (1) | Related: More > Monitoring
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.
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 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.
- Not familiar with CROSS APPLY? Here is a tip on its syntax.
- Review more of Tim's MSSQLTips.
- Want a Primer on the full suite of SQL Server Dynamic Management Objects? Check out The Periodic Table of Dynamic Management Objects. Also, here is a book on the topic of SQL Server Dynamic Management Objects authored by Louis Davidson and Tim Ford.
Last Updated: 2014-03-03
About the author
View all my tips