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

一尘不染

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

sql-server

SQL Server 2014，标准版

我已经读过 dm_exec_requests 中的 percent_complete 不适用于 CREATE INDEX，实际上，percent_complete 始终为 0。所以这无济于事。

我目前使用下面的方法，它至少向我展示了运动（索引创建没有被阻止）。但我对我是 %10 还是 %99 的过程一无所知。

我怎样才能得到线索？

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

阅读 129

2022-10-30

共1个答案

一尘不染

我认为以下查询至少会让您非常接近。它利用了 SQL Server 2014 中引入的 DMV：sys.dm_exec_query_profiles

请注意：

！！您将需要在正在执行的查询批处理中添加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

2022-10-30