Porcentaje de Creación de un Índice en SQL Server en Vivo

En nuestro día a día como DBA debemos crear índices para mejorar el rendimiento de las consultas lanzadas contra las tablas de producción. Una herramienta necesaria sería saber qué porcentaje de creación tiene nuestro índice.

En muchas ocasiones, no disponemos de una versión SQL Server Enterprise y, por lo tanto, no podemos realizar la creación «online» de estos índices. ¿Cuál es el problema? Ni más ni menos que el bloqueo de la tabla sobre la que se está realizando la creación del índice.

¿Cuántas veces se ha dado el caso de estar creando un índice en SQL Server, estar bloqueando una tabla importante y tener la duda de cuánto va a tardar la creación? o bien… ¿Valdrá la pena cancelarlo? ¿Y si está casi terminado?

SQL Server no nos proporciona directamente información sobre el porcentaje de creación de los índices, pero tenemos la solución.

La primera acción a llevar a cabo será abrir una nueva consulta donde activaremos el profile usando el siguiente código:

SET STATISTICS PROFILE ON

A continuación de éste, averiguaremos cuál es el id de la sesión de nuestra consulta para utilizarlo posteriormente.

SELECT @@SPID as Session_id

Una vez realizados los pasos anteriores, podemos proceder a la creación del índice, el código debería ser parecido al siguiente:

SET STATISTICS PROFILE ON
GO
SELECT @@SPID as Session_id
GO
USE [database]
GO
CREATE NONCLUSTERED INDEX [index_name] ON [table_name]
(
	[field1] ASC,
	[field2] ASC,
	[fieldN] ASC
)
GO

Ahora vamos con la parte importante de la entrada, descubrir el porcentaje de creación y el tiempo estimado faltante para que esta se complete, solamente debemos ejecutarlo en una nueva consulta (ventana) mientras el índice se está creando.

IMPORTANTE: Modificar la variable @session_id por el valor obtenido anteriormente.

-- Percentage index creation by M.Angel Motos @aleson-itc   m.motos@aleson-itc.com

-- Declare @session_id to set session_id of index creation

DECLARE @session_id AS int
SET @session_id = 72

-- cte to simplify the code

;WITH tempdb_cte (node_id, Time_Taken)
	AS
		(

		SELECT 
			node_id,
			(DATEDIFF(SECOND, DATEADD(SECOND, - 3610, DATEADD(MILLISECOND, eqp.last_active_time % 1000, DATEADD(SECOND, eqp.last_active_time / 1000, (
			SELECT create_date
			FROM sys.databases
			WHERE NAME = 'tempdb'
			)))), DATEADD(SECOND, - 3610, DATEADD(MILLISECOND, eqp.first_active_time % 1000, DATEADD(SECOND, first_active_time / 1000, (
			SELECT create_date
			FROM sys.databases
			WHERE NAME = 'tempdb'
			))))) * - 1
		    ) AS Time_Taken
		FROM sys.dm_exec_query_profiles AS eqp
		)

-- Begin the Query

SELECT 
	   eqp.Node_Id,
	   eqp.Physical_Operator_Name,   
       SUM(eqp.row_count) Row_Count, 
       SUM(eqp.estimate_row_count) AS Estimate_Row_Count,
       CAST(SUM(eqp.row_count)*100 AS float)/SUM(eqp.estimate_row_count)  AS Estimate_Percent_Complete,
	CASE	
		WHEN eqp.node_id = 2 THEN (SELECT CAST(SUM(eqp.row_count)*50 AS float)/SUM(eqp.estimate_row_count))
		ELSE 
			CASE
				WHEN eqp.row_count = 0 THEN  0
				ELSE (SELECT 50+CAST(SUM(eqp.row_count)*50 AS float)/SUM(eqp.estimate_row_count))
			END  
	END AS 'Total_Percent_Complete',
	CASE
		WHEN eqp.row_count != 0 and node_id = 2 THEN (
			SELECT
				Time_Taken
			FROM tempdb_cte
			WHERE node_id = 2)
		WHEN eqp.row_count != 0 AND node_id = 1 THEN (
			SELECT 
				Time_Taken
			FROM tempdb_cte
			WHERE node_id = 1) - (
			SELECT
				Time_Taken
				FROM tempdb_cte
			WHERE node_id = 2)
		ELSE NULL
	END as Time_Taken,
	CASE
		WHEN eqp.row_count != 0 AND node_id = 1 THEN 
		ROUND((((
			SELECT
				Time_Taken
			FROM tempdb_cte
			WHERE node_id = 1)
			 -
		(
		SELECT
			Time_Taken
		FROM tempdb_cte
		WHERE node_id = 2) 
		)* 100)
		/ ((SELECT CAST(SUM(eqp.row_count)*100 AS float)/SUM(eqp.estimate_row_count) 
			FROM sys.dm_exec_query_profiles AS eqp WHERE node_id = 1)),2,1)
			
		WHEN eqp.row_count != 0 AND node_id = 2 THEN 
			ROUND(((SELECT
						Time_Taken
					FROM tempdb_cte
					WHERE node_id = 2) 
						*100) / (CAST(SUM(eqp.row_count)*100 AS float)
						/SUM(eqp.estimate_row_count)),2,1)
		ELSE NULL
	END AS Estimate_Time_Taken
FROM 
	sys.dm_exec_query_profiles AS eqp
WHERE 
	session_id = @session_id
GROUP BY 
	node_id,physical_operator_name,row_count,estimate_row_count,last_active_time,first_active_time
ORDER BY 
	node_id DESC;

La ejecución de este código nos mostrará como resultado algo parecido al siguiente:

Donde podemos ver la operación que se lleva a cabo, por orden de aparición: las filas que lleva analizadas, el total de filas de la tabla, el porcentaje de creación del paso en cuestión, el porcentaje total de creación, el tiempo que llevamos transcurrido y el estimado del tiempo faltante.

Cuando volvamos a lanzar la consulta, los tiempos se actualizarán.

Una vez acabe el primer paso, el tiempo transcurrido volverá a empezar de cero en el siguiente y el porcentaje de creación se actualizará junto al tiempo estimado para que este acabe. Podemos verlo en el siguiente ejemplo de la creación del mismo índice al haber completado el primer paso en 57 segundos y llevando 13 segundos del segundo paso:

Cuando el porcentaje de creación del segundo paso llegue al 100%, el índice se habrá creado exitosamente.

Con los datos obtenidos, al fin podemos dar información a nuestros clientes a las preguntas como: «¿Cuánto le queda al índice para crearse? ¿El bloqueo será muy largo? Si queremos implementar este índice, ¿cuánto tiempo nos llevará?

Si quieres que ayudemos a tu negocio o empresa contacta con nosotros en info@aleson-itc.com o llámanos al +34 962 681 242