Estar Preparados: Recolectar Datos desde sp_whoisactive a una tabla

Este artículo es una traducción de uno publicado por Kendra Little en su blog. Le agradezco su autorización y si les gusta, los agradecimientos deben ir a ella. / This post was translated from Kendra Little’s blog. I wanna thank her for letting me use this and if you like this, comments and thanks should go to her.

Soy fan del Procedimiento Almacenado sp_whoisactive de Adam Machanic.

Una de las mejores cosas del sp_whoisactive es que es muy rápido al generar una tabla para recolectar los datos necesarios, luego se ejecuta el procedimiento en un ciclo con una cláusula de espera para recolectar regularmente los datos necesarios. Este uso está documentado (ver el artículo de Adam acá), pero he habado con mucha gente q no sabe cómo hacer esto o no tiene un script guardado.

¿Qué es sp_whoisactive?

Sp_whoisactive es un procedimiento escrito por Adam Machanic. Se puede encontrar acá: sp_whoisactiveV10

Que haremos hoy

Hágase un favor: Hoy prepárese para lo siguiente:

· Crear el procedimiento almacenado sp_whoisactive en una base de datos de usuario utilizada solo para administración (Ver artículo de Adam acá)

· Jugar con el script que les entrego más abajo y examinar los parámetros del procedimiento almacenado

· Guardar el script que crea una tabla para su versión preferida de sp_whoisactive, y luego ejecútela en ciclos para almacenar los datos.

¿Por qué haría esto?

Aunque se ejecuten herramientas de monitoreo, usar sp_whoisactive para revisar datos desde las DMVs (incluyendo query plans) puede ser muy valioso.

Cuando las cosas salen mal, terriblemente mal, sería bueno tener este script en reserva para recolectar datos sobre la actividad existente en la instancia SQL mientras uno se enfoca en el problema. Esto nos ayudara a resolver el problema lo más rápido posible, si revisamos periódicamente los datos que recolecta. Como los datos están guardados, podemos usarlos más adelante para explicar las causas del problema y prevenir que suceda otra vez.

Paso 1: crear la tabla @destino

Déjenme dejar esto claro nuevamente: esto no fue mi trabajo. No quiero tomarme el crédito, pero si quiero difundirlo.

Primero, creamos la tabla destino. Sp_whoisactive permite generar una tabla destino fácilmente. Acá hay una versión básica que incluye la fecha actual en el nombre de la tabla, así como tres flags para obtener información de las transacciones, query plans y block leaders.

DECLARE @destination_table VARCHAR(4000) ;
SET @destination_table = ‘WhoIsActive_’ + CONVERT(VARCHAR, GETDATE(), 112) ;

DECLARE @schema VARCHAR(4000) ;
EXEC sp_WhoIsActive
@get_transaction_info = 1,
@get_plans = 1,
@find_block_leaders = 1,
@RETURN_SCHEMA = 1,
@SCHEMA = @schema OUTPUT ;  

SET @schema = REPLACE(@schema, ‘<table_name>’, @destination_table) ;

PRINT @schema
EXEC(@schema) ;

Paso 2: Crear el ciclo para recolectar los datos.

Es necesario asegurarse de especificar los mismos parámetros para el sp_whoisactive en este ciclo, para que los datos de salida encajen en el schema para la tabla creada.

Este ejemplo corre 10 veces con un lapso de 5 segundos entre ejecuciones. Dependiendo de lo que se encuentre, los números se cambian a discreción.

DECLARE
@destination_table VARCHAR(4000) ,
@msg NVARCHAR(1000) ;

SET @destination_table = ‘WhoIsActive_’ + CONVERT(VARCHAR, GETDATE(), 112) ;

DECLARE @numberOfRuns INT ;
SET @numberOfRuns = 10 ;

WHILE @numberOfRuns > 0
BEGIN;
EXEC dbo.sp_WhoIsActive @get_transaction_info = 1, @get_plans = 1,
@find_block_leaders = 1, @DESTINATION_TABLE = @destination_table ;

SET @numberOfRuns = @numberOfRuns – 1 ;

IF @numberOfRuns > 0
BEGIN
SET @msg = CONVERT(CHAR(19), GETDATE(), 121) + ‘: ‘ +
‘Logged info. Waiting…’
RAISERROR(@msg,0,0) WITH nowait ;

WAITFOR DELAY ’00:00:05′
END
ELSE
BEGIN
SET @msg = CONVERT(CHAR(19), GETDATE(), 121) + ‘: ‘ + ‘Done.’
RAISERROR(@msg,0,0) WITH nowait ;
END

END ;

GO

Paso 3: Generar la consulta para revisar los resultados.

Este paso es opcional, pero como la tabla contiene en el nombre un Timestamp, me gusta tener a mano una consulta para revisar los resultados sin tener que copiar y pegar el nombre de la tabla. También prefiero poner la columna collection_time primero y ordenar según ella.

DECLARE @destination_table NVARCHAR(2000), @dSQL NVARCHAR(4000) ;
SET @destination_table = ‘WhoIsActive_’ + CONVERT(VARCHAR, GETDATE(), 112) ;
SET @dSQL = N’SELECT collection_time, * FROM dbo.’ +
QUOTENAME(@destination_table) + N’ order by 1 desc’ ;
print @dSQL
EXEC sp_executesql @dSQL