主页 / dba / 问题 / 139191
Accepted
Jonesome Reinstate Monica
Asked: 2016-05-22 23:25:52 +0800 CST

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

  • 772

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 index

4 个回答

  1. Best Answer

    我认为以下查询至少会让您非常接近。它利用了 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 INDEXCREATE INDEXsession_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
    • 95

  2. 我认为我们可以通过引用 sys.dm_exec_requests 来删除 @SPID 变量：

    ;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] IN (SELECT session_id from sys.dm_exec_requests where command IN ( 'CREATE INDEX','ALTER INDEX','ALTER TABLE') )
), 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;
    • 12

  3. 由于这个话题似乎仍然活跃，我认为值得注意的是，使用SQL Server 2019 和 Azure SQL DB（在 150 兼容模式下）中新的可恢复索引操作提供了此功能。目录视图sys.index_resumable_operations有一个表示进度的 percent_complete 列。

    除了能够监控索引创建和重建之外，可恢复索引操作还有助于将操作分解为随着操作进行而提交的小块。这有助于使事务日志保持较小，并且还可以帮助处理可用性组之类的事情，因为可以将操作复制到任何辅助服务器。使用可恢复索引操作，您可以在故障转移后在新的主服务器上恢复索引创建或重建，而不会丢失进度，并且由于事务是沿途提交的，因此在长时间索引操作期间您不会遇到同步备份的问题.

    • 8

  4. 扩展 Bruce Pratt：让我们按 session_id 分组，这样我们就可以看到并行运行的多个 Create Index 命令的进度。

    ;WITH agg AS
(
     SELECT qp.session_id, 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] IN (SELECT session_id from sys.dm_exec_requests where command IN ( 'CREATE INDEX','ALTER INDEX','ALTER TABLE') )
     group by qp.session_id
), comp AS
(
     SELECT *,
            ([TotalRows] - [RowsProcessed]) AS [RowsLeft],
            ([ElapsedMS] / 1000.0) AS [ElapsedSeconds]
     FROM   agg
)
SELECT session_id,
        [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;
    • 0

相关问题