Thanks to “Solomon Rutzky”, for sharing the script online. I have modified it slightly to work in
my scenarios as well.
This will help you to find the estimate each operator index creation will take
You have to
just mention the SPID which is running rebuild index
/**************************************/
DECLARE @SPID INT = 56;
;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'' )) AS [CurrentStep]
FROM
sys.dm_exec_query_profiles qp
WHERE
qp.[physical_operator_name] IN (N'Table Scan', N'Clustered Index Scan', N'Sort', 'Index Insert')
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;
/**************************************/
Output Format:
super, thanks
ReplyDelete