SQL Server：如何跟踪 CREATE INDEX 命令的進度？

SQL Server 2014，標準版

我已經讀過 dm_exec_requests 中的 percent_complete 不適用於 CREATE INDEX，實際上，percent_complete 始終為 0。所以這無濟於事。

我目前使用下面的方法，它至少向我展示了運動（索引創建沒有被阻止）。但我對我是 %10 還是 %99 的過程一無所知。

我嘗試了這裡描述的方法： https ://dba.stackexchange.com/a/102545/6229 但它顯示了一個明顯錯誤的 est 完成時間（它基本上顯示了 60 多分鐘的過程中的“現在”，我 10 分鐘進入)

我怎樣才能得到線索？

SELECT percent_complete, estimated_completion_time, reads, writes, logical_reads, text_size, *
FROM
sys.dm_exec_requests AS r
WHERE
r.session_id <> @@SPID
AND r.session_id = 58

我認為以下查詢至少會讓您非常接近。它利用了 SQL Server 2014 中引入的 DMV：sys.dm_exec_query_profiles（感謝 Martin Smith 通過這個相關的 DBA.StackExchange Answer: Progress of SELECT INTO 語句向我介紹它：-）。

請注意：

• **！！**您將需要在正在執行的查詢批處理中添加SET STATISTICS PROFILE ON;或（如果這不明顯，則放置在語句之前），否則此 DMV 中不會顯示該SPID / !!SET STATISTICS XML ON;``CREATE INDEX``CREATE INDEX``session_id
• 該IN運算符用於過濾掉Index Insert如果包含，將增加TotalRows值的行，這將扭曲計算，因為該行從不顯示任何已處理的行。
• 此處顯示的行數（即TotalRows）是表行數的兩倍，因為該操作採取了兩個步驟，每個步驟都對所有行進行操作：第一個是“表掃描”或“聚集索引掃描”，第二個是“排序”。創建聚集索引或在堆上創建非聚集索引時，您將看到“表掃描”。在聚集索引上創建非聚集索引時，您將看到“聚集索引掃描”。
• 創建過濾索引時，此查詢似乎不起作用。由於某種原因，過濾索引 a) 沒有“排序”步驟，並且 b) 該row_count欄位永遠不會從 0 增加。

不確定我之前測試的是什麼，但我的測試現在表明此查詢擷取了過濾索引。甜的。雖然只是要注意行數可能會關閉（我會看看有一天我能不能解決這個問題）。

• 在已經有非聚集索引的堆上創建聚集索引時，需要重建非聚集索引（以換出聚集索引鍵的 RID – RowID），並且每個非聚集索引重建將是一個單獨的操作，因此不會反映在創建聚集索引期間此查詢返回的統計資訊中。

• 此查詢已針對：

  • 創建：

    • 堆上的非聚集索引
    • 聚集索引（不存在非聚集索引）
    • 聚集索引/表上的非聚集索引
    • 非聚集索引已存在時的聚集索引
    • 聚集索引/表上的唯一非聚集索引

  • 通過以下方式重建（具有聚集索引和一個非聚集索引的表；在 SQL Server 2014、2016、2017 和 2019 上測試）：

    • ALTER TABLE [schema_name].[table_name] REBUILD;（使用此方法時僅顯示聚集索引）
    • ALTER INDEX ALL ON [schema_name].[table_name] REBUILD;
    • ALTER INDEX [index_name] ON [schema_name].[table_name] REBUILD;

DECLARE @SPID INT = 51;

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

樣本輸出：

                       Rows                 Percent   Elapsed  Estimated    Estimated
CurrentStep  TotalRows  Processed  RowsLeft  Complete  Seconds  SecondsLeft  CompletionTime
-----------  ---------  ---------  --------  --------  -------  -----------  --------------
Clustered    11248640   4786937    6461703   42.56     4.89400  6.606223     2016-05-23
Index Scan                                                                   14:32:40.547

引用自：https://dba.stackexchange.com/questions/139191