SQL Server estimate time create(alter) index phase1

env: Windows Server 2016
        SQL Server 2016 SP2

SQL 2014有推出DMV"sys.dm_exec_query_profiles"查詢執行的即時進度

有一篇問答有明確的script可以參考。
https://dba.stackexchange.com/questions/139191/sql-server-how-to-track-progress-of-create-index-command

用來查詢CREATE(ALTER) INDEX的進度，這對於維護上有一個可預估的時間外，也在施作維護上有一個明確客觀的依據


1.開始使用DMV: sys.dm_exec_query_profiles

在官方文章"sys.dm_exec_query_profiles"有明確說明，執行前必須啟用STATISTICS PROFILE

在單一session啟用STATISTICS PROFILE
例如:

SET STATISTICS PROFILE ON;
ALTER INDEX [IX_TESTIDX01] ON [dbo].[TESTTB01] REBUILD PARTITION = ALL 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = ON, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

另外一個方式是啟用TRACE FLAG 7412
DBCC TRACEON - Trace Flags (Transact-SQL):
https://docs.microsoft.com/zh-tw/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-2017

這個使用要注意，因為使用以下command開啟TRACE FLAG 7412，是全域開啟，留意系統資源追蹤即時進度的耗損。
例如:

DBCC TRACEON (7412, -1);  
GO 
ALTER INDEX [IX_TESTIDX01] ON [dbo].[TESTTB01] REBUILD PARTITION = ALL 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = ON, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

2.在第一步啟用STATISTICS PROFILE或TRACE FLAG 7412後，開始利用DMV: sys.dm_exec_query_profiles查詢及實執行進度

下面這一句就是官方文件提供的參考，我有加了IIF避免除以0的錯誤。

SELECT node_id,physical_operator_name, SUM(row_count) row_count,
  SUM(estimate_row_count) AS estimate_row_count,
  CAST(SUM(row_count)*100 AS float)/IIF(SUM(estimate_row_count) >0,  SUM(estimate_row_count) , 1)
FROM sys.dm_exec_query_profiles  
WHERE session_id=54
GROUP BY node_id,physical_operator_name
ORDER BY node_id;

下面是我透過STACKEXCHANGE調整的SCRIPT:
自動抓取"ALTER INDEX"的spid查詢
移除條件:qp.[physical_operator_name] IN (N'Table Scan', N'Clustered Index Scan', N'Sort')
這樣會讓時間比較接近實際完成時間。

USE MASTER
GO

DECLARE @SPID INT --= 80;
SELECT
       @SPID=SPID
FROM SYSPROCESSES SP
INNER JOIN sys.dm_exec_requests ER
ON sp.spid = ER.session_id
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(er.sql_handle) EST
WHERE ER.command ='ALTER INDEX'
ORDER BY CPU DESC

;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'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

實際執行結果: