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 😊

Query Cached Plan Statistics

We can use the below script to find the Query Cached Plan statistics for all DB's

SELECT UseCount = p.usecounts
	,PlanSize_KB = p.size_in_bytes / 1024
	,CPU_ms = qs.total_worker_time / 1000
	,Duration_ms = qs.total_elapsed_time / 1000
	,ObjectType = p.cacheobjtype + ' (' + p.objtype + ')'
	,DatabaseName = db_name(convert(INT, pa.value))
	,txt.ObjectID
	,qs.total_physical_reads
	,qs.total_logical_writes
	,qs.total_logical_reads
	,qs.last_execution_time
	,StatementText = SUBSTRING(txt.[text], qs.statement_start_offset / 2 + 1, CASE 
			WHEN qs.statement_end_offset = - 1
				THEN LEN(CONVERT(NVARCHAR(max), txt.[text]))
			ELSE qs.statement_end_offset / 2 - qs.statement_start_offset / 2 + 1
			END)
	,QueryPlan = qp.query_plan
FROM sys.dm_exec_query_stats AS qs
INNER JOIN sys.dm_exec_cached_plans p ON p.plan_handle = qs.plan_handle
OUTER APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS pa
OUTER APPLY sys.dm_exec_sql_text(p.plan_handle) AS txt
OUTER APPLY sys.dm_exec_query_plan(p.plan_handle) AS qp
WHERE pa.attribute = 'dbid' --retrieve only the database id from sys.dm_exec_plan_attributes
ORDER BY qs.total_worker_time + qs.total_elapsed_time DESC;

Done!! I hope this helps 😊

Get SQL statement using the sql_handle , start offset and end offset

We can use the below script to find the exact SQL statement using the sql_handle , start offset and end offset. 

 

DECLARE @sql_handle VARBINARY(64) = 0x0300050097E18713F1A9570113A8000001000000000000000000000000000000000000000000000000000000 --Pass sql handle here
DECLARE @offsetStart INT = 150 --Pass start offset here
DECLARE @offsetEnd INT = 326 --Pass End offset here

SELECT SUBSTRING(TEXT, (@offsetStart / 2) + 1, (
			(
				CASE @offsetEnd
					WHEN - 1
						THEN DATALENGTH(TEXT)
					ELSE @offsetEnd
					END - @offsetStart
				) / 2
			) + 1) AS statement_text
FROM sys.dm_exec_sql_text(@sql_handle)
/*
-- Get SQL TEXT and QUERY PLAN
select * from sys.[dm_exec_sql_text](0x02000000ACAC3005372F5458DAAFAAB4D6A72F99D43FBC080000000000000000000000000000000000000000) -- sql_handle
select * from sys.[dm_exec_query_plan](0x06000700ACAC3005C0DDD2E50100000001000000000000000000000000000000000000000000000000000000) -- plan_handle
go

*/

Done!! I hope this helps 😊