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

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

#1 楼

我认为以下查询至少会使您更加接近。它利用了SQL Server 2014中引入的DMV：sys.dm_exec_query_profiles（感谢Martin Smith通过此相关的DBA.StackExchange向我介绍了DMV。）：SELECT INTO语句的进度:-)。 br />请注意：


！！您将需要在执行SET STATISTICS PROFILE ON;的查询批处理中添加SET STATISTICS XML ON;或CREATE INDEX（如果不太明显，则放置在CREATE INDEX语句之前），否则该DMV中不会为该SPID / session_id显示任何行！ br /> 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
 

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

#3 楼

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

除了能够监视索引的创建和重建之外，可恢复索引操作还可以通过将操作分解为随操作进行而提交的小块来提供帮助。这有助于使事务日志保持较小，并且还可以帮助解决诸如可用性组之类的问题，因为该操作可以复制到任何辅助服务器。借助可恢复的索引操作，您可以在故障转移后在新的主服务器上恢复索引的创建或重建，而不会丢失进度，并且由于事务是一路提交的，因此在长索引操作期间不会出现同步备份的问题。 。