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

實際執行結果: