SQL Server estimate time create(alter) index phase2

env: Windows Server 2016
        SQL Server 2016 SP2


一般在執行SQL command狀況下，我們可以透過動態視圖 "sys.dm_exec_requests"查看進度。
但是建立索引或重建所引時，在"sys.dm_exec_requests"下percent_complete卻呈現"0"。
而組織所引卻可以在"sys.dm_exec_requests"查詢到執行進度。
因此在"SQL Server estimate time create(alter) index phase1"介紹如何檢視create(alter) index進度後，新增可以執行的語句。

1.組織索引並同時查看進度
先利用指令"a"進行組織所引，接著另開新的查詢"sys.dm_exec_requests"

command:
a.ALTER INDEX [PK_test01] ON [dbo].[test01] REORGANIZE  WITH ( LOB_COMPACTION = ON )

GO

b.select percent_complete from sys.dm_exec_requests where command='dbcc'

2.改良"SQL Server estimate time create(alter) index phase1"script內容。
在執行評估中，發現落差大的原因在於'Parallelism','Online Index Insert'這兩項評估筆數造成時間的落差，因此在這次的語法中過濾掉，增加時間準確性。
command:
USE MASTER
GO

SET NOCOUNT  ON
SET LOCK_TIMEOUT 10000

DECLARE @SPID INT --= 80;
DECLARE @SQLSTR NVARCHAR(MAX)
DECLARE @Conditions NVARCHAR(MAX)

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 IN ('CREATE INDEX', 'ALTER INDEX')
ORDER BY CPU DESC

--Fetch all steps of Create or Alter Index
SELECT @Conditions =stuff((
SELECT
CAST(physical_operator_name AS NVARCHAR) + ''', N'''
FROM sys.dm_exec_query_profiles   
WHERE session_id=@SPID and physical_operator_name not in('Parallelism','Online Index Insert')
GROUP BY node_id,physical_operator_name  
FOR XML PATH('')),1,0,'')

SET @Conditions='(N''' +LEFT(@Conditions, LEN(@Conditions)-4) +')'
--PRINT @Conditions

SET @SQLSTR='SELECT 
MAX(IIF(qp.[close_time] = 0 AND qp.[first_row_time] > 0, [physical_operator_name], N''<Transition>'')) AS [CurrentStep],
SUM(qp.[estimate_row_count]) AS [TotalRows],
SUM(qp.[row_count]) AS [RowsProcessed],
(SUM(qp.[estimate_row_count]) - SUM(qp.[row_count])) AS [RowsLeft], 
CONVERT(DECIMAL(5, 2), ((SUM(qp.[row_count]) * 1.0) / SUM(qp.[estimate_row_count])) * 100) AS [CompletePercent],
CONVERT(varchar, DATEADD(ms, ((MAX(qp.last_active_time) - MIN(qp.first_active_time)) / 1000.0) * 1000, 0), 114)  AS [AlreadyRunTime_hhmiss], 
CONVERT(varchar, DATEADD(ms, ((((MAX(qp.last_active_time) - MIN(qp.first_active_time)) / 1000.0) / SUM(qp.[row_count])) * (SUM(qp.[estimate_row_count]) - SUM(qp.[row_count])))* 1000, 0), 114)  AS [RemainTimeToReady_hhmiss],
DATEADD(SECOND, ((((MAX(qp.last_active_time) - MIN(qp.first_active_time)) / 1000.0) / SUM(qp.[row_count])) * (SUM(qp.[estimate_row_count]) - SUM(qp.[row_count]))), GETDATE()) AS [EstimatedCompletionTime]
FROM sys.dm_exec_query_profiles qp 
WHERE qp.[physical_operator_name] IN ' + @Conditions +'
    AND  
qp.[session_id] = @SPID'

--PRINT @SQLSTR
EXECUTE SP_EXECUTESQL @SQLSTR, N'@SPID INT',
@SPID=@SPID

--Run this in a different session than the session in which your query is running. 
--Note that you may need to change session id 54 below with the session id you want to monitor.
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)  as estimate_percent_complete  
FROM sys.dm_exec_query_profiles   
WHERE session_id=@SPID
GROUP BY node_id,physical_operator_name  
ORDER BY node_id;