Query Progress

Putting together various scripts developed by people in the community to check the progress of currently running queries. Some of them I have tweaked to make them more readable.

Script 1:  Time remaining for DB Backup & Restore

 

--https://www.sqlservercentral.com/blogs/how-to-get-estimated-completion-time-of-sql-server-database-backup-or-restore
SELECT dmr.session_id
	,dmr.command
	,CONVERT(NUMERIC(6, 2), dmr.percent_complete) AS [Percent Complete]
	,CONVERT(VARCHAR(20), DATEADD(ms, dmr.estimated_completion_time, GetDate()), 20) AS [ETA Completion Time]
	,CONVERT(NUMERIC(10, 2), dmr.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min]
	,CONVERT(NUMERIC(10, 2), dmr.estimated_completion_time / 1000.0 / 60.0) AS [ETA Min]
	,CONVERT(NUMERIC(10, 2), dmr.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [ETA Hours]
	,CONVERT(VARCHAR(1000), (
			SELECT SUBSTRING(TEXT, dmr.statement_start_offset / 2, CASE 
						WHEN dmr.statement_end_offset = - 1
							THEN 1000
						ELSE (dmr.statement_end_offset - dmr.statement_start_offset) / 2
						END)
			FROM sys.dm_exec_sql_text(sql_handle)
			)) [sqltxt]
FROM sys.dm_exec_requests dmr
WHERE command IN (
		'RESTORE DATABASE'
		,'BACKUP DATABASE'
		,'BACKUP LOG'
		,'RESTORE LOG'
		)

Script 2:  Time remaining for below commands :

ALTER INDEX REORGANIZE
AUTO_SHRINK option with ALTER DATABASE
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
RECOVERY
ROLLBACK
TDE ENCRYPTION

SELECT session_id
	,percent_complete
	,start_time
	,STATUS
	,DATEADD(MILLISECOND, estimated_completion_time, CURRENT_TIMESTAMP) Estimated_finish_time
	,(total_elapsed_time / 1000) / 60 Total_Elapsed_Time_MINS
	,DB_NAME(Database_id) Database_Name
	,command
	,last_wait_type
FROM sys.dm_exec_requests
WHERE session_id > 50
	AND session_id <> @@spid
	--and DB_NAME(Database_id)='AWSDB'
	--and session_id=652

Script 3:  Progress of CREATE INDEX

--https://dba.stackexchange.com/questions/139191/sql-server-how-to-track-progress-of-create-index-command
DECLARE @SPID INT = 51;;

WITH agg
AS (
	SELECT SUM(qp.[row_count]) AS [RowsProcessed]
		,SUM(qp.[estimate_row_count]) AS [TotalRows]
		,MAX(qp.last_active_time) - MIN(qp.first_active_time) AS [ElapsedMS]
		,MAX(IIF(qp.[close_time] = 0
				AND qp.[first_row_time] > 0, [physical_operator_name], N'<Transition>')) AS [CurrentStep]
	FROM sys.dm_exec_query_profiles qp
	WHERE qp.[physical_operator_name] IN (
			N'Table Scan'
			,N'Clustered Index Scan'
			,N'Index Scan'
			,N'Sort'
			)
		AND qp.[session_id] = @SPID
	)
	,comp
AS (
	SELECT *
		,([TotalRows] - [RowsProcessed]) AS [RowsLeft]
		,([ElapsedMS] / 1000.0) AS [ElapsedSeconds]
	FROM agg
	)
SELECT [CurrentStep]
	,[TotalRows]
	,[RowsProcessed]
	,[RowsLeft]
	,CONVERT(DECIMAL(5, 2), (([RowsProcessed] * 1.0) / [TotalRows]) * 100) AS [PercentComplete]
	,[ElapsedSeconds]
	,(([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]) AS [EstimatedSecondsLeft]
	,DATEADD(SECOND, (([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]), GETDATE()) AS [EstimatedCompletionTime]
FROM comp;

Script 4:  Progress of SELECT INTO

 

--https://dba.stackexchange.com/questions/129090/progress-of-select-into-statement/129152#129152
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT --OBJECT_NAME(sp.[object_id]) AS [TableName], sdobd.*, '---', sp.*, '---', sau.*
	SUM(sdobd.[row_count]) AS [BufferPoolRows]
	,SUM(sp.[rows]) AS [AllocatedRows]
	,COUNT(*) AS [DataPages]
FROM sys.dm_os_buffer_descriptors sdobd
INNER JOIN sys.allocation_units sau ON sau.[allocation_unit_id] = sdobd.[allocation_unit_id]
INNER JOIN sys.partitions sp ON (
		sau.[type] = 1
		AND sau.[container_id] = sp.[partition_id]
		) -- IN_ROW_DATA
	OR (
		sau.[type] = 2
		AND sau.[container_id] = sp.[hobt_id]
		) -- LOB_DATA
	OR (
		sau.[type] = 3
		AND sau.[container_id] = sp.[partition_id]
		) -- ROW_OVERFLOW_DATA
WHERE sdobd.[database_id] = DB_ID()
	AND sdobd.[page_type] = N'DATA_PAGE'
	AND sp.[object_id] = (
		SELECT so.[object_id]
		FROM sys.objects so
		WHERE so.[name] = 'TestDump'

Done!! I hope this helps 😊