SQL Server: Como acompanhar o progresso do comando CREATE INDEX?

Eu acho que a consulta a seguir, pelo menos, chegará bem perto. Ele faz uso de um DMV que foi introduzido no SQL Server 2014: sys.dm_exec_query_profiles (e obrigado a Martin Smith por me apresentar por meio deste DBA.StackExchange relacionado Resposta: Progresso da instrução SELECT INTO :-).

Observe:

• !! Você precisará adicionar SET STATISTICS PROFILE ON;ou SET STATISTICS XML ON;no lote de consulta que está fazendo o CREATE INDEX(e colocado antes da CREATE INDEXinstrução, se isso não for óbvio), senão nenhuma linha aparecerá neste DMV para esse SPID / session_id !!

• O INoperador é usado para filtrar a Index Insertlinha que, se incluída, aumentará os TotalRowsvalores, o que distorcerá os cálculos, pois essa linha nunca mostra nenhuma linha processada.

• A contagem de linhas exibida aqui (ou seja, TotalRows) é o dobro da contagem de linhas da tabela devido à operação ter duas etapas, cada uma operando em todas as linhas: a primeira é uma "Varredura de tabela" ou "Varredura de índice agrupado" e a segunda é o tipo". Você verá "Table Scan" ao criar um índice clusterizado ou criar um índice não clusterizado em um heap. Você verá "Verificação de índice clusterizado" ao criar um índice não clusterizado em um índice clusterizado.

• Esta consulta parece não funcionar ao criar índices filtrados. Por algum motivo, os Índices Filtrados a) não possuem a etapa "Classificar" e b) o row_countcampo nunca aumenta de 0.
  Não tenho certeza do que eu estava testando antes, mas meus testes agora indicam que os índices filtrados são capturados por essa consulta. Doce. Embora apenas tome cuidado com a contagem de linhas pode estar desativada (vou ver se consigo consertar isso algum dia).

• Ao criar um índice clusterizado em um heap que já possui índices não clusterizados, os índices não clusterizados precisam ser reconstruídos (para trocar o RID -- RowID -- pela(s) chave(s) de índice clusterizado), e cada reconstrução de índice não clusterizado será ser uma operação separada e, portanto, não refletida nas estatísticas retornadas por essa consulta durante a criação do Índice Agrupado.

• Esta consulta foi testada em relação a:

  • Criando:
    • Índices não clusterizados em um heap
    • um índice agrupado (não existem índices não agrupados)
    • Índices não agrupados no índice/tabela agrupados
    • um índice clusterizado quando já existem índices não clusterizados
    • Índices não clusterizados exclusivos no índice/tabela clusterizados
  • Reconstruindo (tabela com índice clusterizado e um índice não clusterizado; testado no SQL Server 2014, 2016, 2017 e 2019) por meio de:
    • ALTER TABLE [schema_name].[table_name] REBUILD;( somente o Clustered Index aparece ao usar este método )
    • 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;

Saída de amostra:

                        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

Acho que podemos remover a variável @SPID com uma referência a sys.dm_exec_requests:

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

Como este tópico parece ainda estar ativo, achei que valeria a pena observar que o uso das novas operações de índice retomáveis ​​no SQL Server 2019 e no Azure SQL DB (no modo de compatibilidade 150) fornece essa funcionalidade. A exibição de catálogo sys.index_resumable_operations tem uma coluna percent_complete que indica o andamento.

Além de poder monitorar a criação e a reconstrução do índice, as operações de índice retomáveis ​​também ajudam dividindo a operação em pequenos pedaços que são confirmados à medida que a operação avança. Isso ajuda a manter o log de transações pequeno e também pode ajudar com coisas como Grupos de Disponibilidade, pois a operação pode ser replicada para qualquer servidor secundário. Com as operações de índice retomáveis, você pode retomar a criação ou reconstrução do índice no novo servidor primário após um failover sem perder o progresso e, como as transações são confirmadas ao longo do caminho, você não terá o problema de fazer backup de sincronização durante operações de índice longas .

Para expandir Bruce Pratt: Vamos agrupar por session_id para que possamos ver o progresso de vários comandos Create Index executados em paralelo.

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