DECLARE @Processid INT = 70 ---here mention SPid (session id)
;WITH Summary 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] = @Processid
), Result AS
(
SELECT *,
([TotalRows] - [RowsProcessed]) AS [RowsLeft],
([ElapsedMS] / 1000.0) AS [ElapsedSeconds]
FROM Summary
)
SELECT [CurrentStep],
[TotalRows],
[RowsProcessed],
[RowsLeft],
GETDATE() AS [CurrentTime],
CONVERT(DECIMAL(5, 2),
(([RowsProcessed] * 1.0) / [TotalRows]) * 100) AS [PercentComplete]
FROM Result;
output
CurrentStep TotalRows RowsProcessed RowsLeft CurrentTime PercentComplete
Comments
Post a Comment
If you have any doubt then please let me know in comment section.