SQL Server: ¿Cómo realizar un seguimiento del progreso del comando CREATE INDEX?

Al fin después de tanto luchar hemos hallado el resultado de esta pregunta que muchos usuarios de este espacio presentan. Si tienes alguna información que compartir no dudes en aportar tu conocimiento.

Solución:

Creo que la siguiente consulta al menos lo acercará bastante. Hace uso de un DMV que se introdujo en SQL Server 2014: sys.dm_exec_query_profiles (y gracias a Martin Smith por presentármelo a través de este DBA relacionado. Respuesta de StackExchange: Progreso de la instrucción SELECT INTO :-).

Tenga en cuenta:

• !! Necesitarás agregar SET STATISTICS PROFILE ON; o SET STATISTICS XML ON; en el lote de consultas que está haciendo el CREATE INDEX (y colocado antes de los CREATE INDEX declaración, si eso no fue obvio), de lo contrario, no aparecerán filas en este DMV para ese SPID / session_id!!

• los IN El operador se utiliza para filtrar el Index Insert fila que, si se incluye, aumentará la TotalRows valores, lo que sesgará los cálculos ya que esa fila nunca muestra ninguna fila procesada.

• El recuento de filas que se muestra aquí (es decir, TotalRows) es el doble del recuento de filas de la tabla debido a que la operación toma dos pasos, cada uno operando en todas las filas: el primero es un “Escaneo de tabla” o “Escaneo de índice agrupado”, y el segundo es el “Ordenar”. Verá “Escaneo de tabla” cuando cree un índice agrupado o un índice no agrupado en un montón. Verá “Escaneo de índice agrupado” cuando cree un índice no agrupado en un índice agrupado.

• Esta consulta no parece funcionar al crear índices filtrados. Por alguna razón, los índices filtrados a) no tienen el paso “Ordenar” yb) el row_count el campo nunca aumenta de 0.

  No estoy seguro de lo que estaba probando antes, pero mis pruebas ahora indican que los índices filtrados están capturado por esta consulta. Dulce. Aunque tenga en cuenta que el recuento de filas puede estar apagado (veré si puedo solucionarlo algún día).

• Al crear un índice agrupado en un montón que ya tiene índices no agrupados, es necesario reconstruir los índices no agrupados (para intercambiar el RID – RowID – por las claves de índice agrupadas), y cada reconstrucción de índice no agrupado ser una operación separada y, por lo tanto, no se refleja en las estadísticas devueltas por esta consulta durante la creación del índice agrupado.

• Esta consulta ha sido probada contra:

  • Creando:
    • Índices no agrupados en un montón
    • un índice agrupado (no existen índices no agrupados)
    • Índices no agrupados en el índice / tabla agrupados
    • un índice agrupado cuando ya existen índices no agrupados
    • Índices no agrupados únicos en el índice / tabla agrupados
  • Reconstrucción (tabla con índice agrupado y un índice no agrupado; probado en SQL Server 2014, 2016, 2017 y 2019) a través de:
    • ALTER TABLE [schema_name].[table_name] REBUILD; (solo se muestra el índice agrupado cuando se usa 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'')) 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;

Salida de muestra:

                        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

Creo que podemos eliminar la variable @SPID con una referencia 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'')) 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;

Dado que este tema parece estar todavía activo, pensé que valdría la pena señalar que el uso de las nuevas operaciones de índice reanudables en SQL Server 2019 y Azure SQL DB (en modo de compatibilidad 150) proporciona esta funcionalidad. La vista de catálogo sys.index_resumable_operations tiene una columna percent_complete que indica el progreso.

Además de poder monitorear la creación y reconstrucción de índices, las operaciones de índice reanudables también ayudan al dividir la operación en pequeños fragmentos que se confirman a medida que avanza la operación. Esto ayuda a que el registro de transacciones sea pequeño y también puede ayudar con cosas como los grupos de disponibilidad, ya que la operación se puede replicar en cualquier servidor secundario. Con las operaciones de índice reanudables, puede reanudar la creación o reconstrucción del índice en el nuevo servidor principal después de una conmutación por error sin perder el progreso, y dado que las transacciones se confirman en el camino, no tendrá el problema de realizar copias de seguridad de la sincronización durante las operaciones de índice largas. .

Si eres capaz, tienes el poder dejar una noticia acerca de qué te ha gustado de este post.

---

---

Utiliza Nuestro Buscador