SQL Server - Usando sp_WhoIsActive para Identificar Bloqueos, Bloqueos, Consultas Lentas, Consultas en Ejecución y Mucho Más

Descripción de columnas

Antes de mostrar qué cambia con cada parámetro utilizado, te mostraré qué devuelve cada columna de este SP:

Columna	Descripción
dd hh:mm:ss:mss	Columna que informa cuánto tiempo lleva ejecutándose la consulta (para sesiones activas) o cuánto tiempo ha pasado desde que la sesión ejecutó la última instrucción (para sesiones inactivas - durmiendo)
id_sesión	Número de sesión que ejecuta la consulta (SPID)
texto_sql	XML que contiene un extracto de la consulta que se está ejecutando (o la consulta completa, si es solo una declaración)
nombre de inicio de sesión	Nombre del DOMINIO\USUARIO que ejecuta esta consulta
esperar_info	Si la sesión tiene un evento de espera, informa cuántos milisegundos lleva ocurriendo este evento y qué tipo de evento es (Ej: LCK_M_S, CXPACKET, OLEDB, etc.)
UPC	Medición del número de ciclos de CPU utilizados por la sesión (un número muy alto significa que esta sesión ya ha utilizado una gran cantidad de CPU del servidor, pero no significa que la esté utilizando actualmente)
asignaciones_tempdb	Número de páginas TempDB (8 KB cada página) que ya han sido asignadas para esta sesión a través de tablas temporales, spools, LOB, etc.). Un número muy alto aquí significa que esta sesión tiene muchas páginas asignadas, pero no significa que sea la causa de los eventos de crecimiento automático de TempDB.
tempdb_actual	Número de páginas TempDB asignadas actualmente por esta sesión. Este recuento se resume en el número de páginas asignadas: número de páginas desasignadas de TempDB. Un número muy alto aquí significa que es una posible causa de eventos de crecimiento automático en TempDB.
bloqueo_sesión_id	Muestra el número de sesión que está bloqueando la sesión analizada (generando un evento de espera LCK en esa sesión)
lee	Número de páginas lógicas de 8 KB leídas desde la memoria del servidor (lectura rápida)
escribe	Número de páginas físicas de 8 KB escritas en el disco del servidor
lecturas_físicas	Número de páginas físicas de 8 KB leídas desde el disco del servidor (lectura lenta)
memoria_usada	Número de páginas de 8 KB utilizadas desde la memoria del servidor mediante la combinación de memoria caché de procedimientos y concesión de memoria del espacio de trabajo.
estado	Define el estado de ejecución actual de la consulta, que puede ser uno de los siguientes valores: Correr: Significa que la sesión está activa, ejecutando uno o más lotes. Este estado significa que la sesión está conectada a la base de datos, ya envió los comandos al servidor y está esperando el procesamiento por parte de SQL Server. Suspendido: Este estado significa que la sesión no está activa, ya que está esperando algún recurso del servidor (E/S, Red, etc.). Cuando se libere este recurso, la sesión volverá a estar activa y reanudará el procesamiento. Ejecutable: Este estado significa que la sesión ya ha sido asignada a un subproceso de trabajo en el procesador, pero no puede enviarla a la CPU para su ejecución. Si este evento ocurre con mucha frecuencia y durante mucho tiempo en su entorno, puede significar que necesita aumentar el procesador de su servidor o reducir el paralelismo de las consultas en ejecución (léase MAXDOP), que pueden estar ocupando todos los núcleos. Pendiente: Este estado significa que la sesión está lista y esperando que un subproceso de trabajo en el procesador la recoja para ejecutarse. Es importante tener en cuenta que esto no significa que deba aumentar el parámetro "Máx. de subprocesos de trabajo", tal vez necesite verificar qué están haciendo los otros subprocesos y por qué no se están ejecutando. Fondo: La solicitud se ejecuta en segundo plano, generalmente utilizada por Resource Monitor o Deadlock Monitor. durmiendo: La sesión está abierta y conectada a la base de datos, pero no tiene ninguna solicitud para procesar.
open_tran_count	Columna tomada de la vista de procesos del sistema descontinuados, que le permite ver cuántas transacciones abiertas activas está utilizando la sesión y qué tan profundo es el nivel de anidamiento de estas transacciones.
porcentaje_completo	Muestra cuánto % de consultas largas se completaron (ALTER INDEX REORGANIZE, opción AUTO_SHRINK con ALTER DATABASE, BACKUP DATABASE, DBCC CHECKDB, DBCC CHECKFILEGROUP, DBCC CHECKTABLE, DBCC INDEXDEFRAG, DBCC SHRINKDATABASE, DBCC SHRINKFILE, RECOVERY, RESTORE DATABASE, ROLLBACK, TDE ENCRYPTION)
nombre_host	Nombre de la máquina física de donde proviene la conexión
nombre_base_datos	Nombre de la base de datos actual de la conexión, donde se envían las consultas.
nombre_programa	Nombre del software utilizado durante la conexión (Ej: Microsoft SQL Server Management Studio - Consulta)
hora_inicio	Muestra la fecha en que comenzó a ejecutarse la consulta.
hora_iniciar sesión	Muestra la fecha en que la sesión inició sesión en la instancia.
id_solicitud	Número de solicitud de sesión actual. Esta columna no tiene una interpretación muy clara de su utilidad. Cuando el estado de la sesión es "inactivo", el valor de request_id generalmente será NULL; de lo contrario, será 0 (cero). Si encuentra un valor mayor que 0 (cero) en la columna request_id, significa que esta sesión está ejecutando más de un lote simultáneamente usando MARS (Múltiples conjuntos de resultados activos)
tiempo_colección	Muestra la fecha de recopilación de datos (fecha de ejecución de sp_WhoIsActive)

Estas columnas se pueden agrupar en 4 categorías:

Tiempo y estado

• [dd hh:mm:ss.mss]
• [hora_inicio]
• [porcentaje_completo]
• [tiempo_colección]
• [estado]

Identificadores de sesión y usuario

• [id_sesión]
• [id_solicitud]
• [nombre de inicio de sesión]
• [nombre_host]
• [nombre_base_datos]
• [nombre_programa]

Factores que pueden causar lentitud en las consultas

• [espera_info]
• [bloqueo_sesión_id]

Uso de recursos de su sesión

• [texto_sql]
• [UPC]
• [tempdb_allocations]
• [tempdb_actual]
• [lee]
• [escribe]
• [lecturas_físicas]
• [memoria_usada]
• [open_tran_count]

Consultando la ayuda de sp_WhoIsActive

Esta herramienta tiene su propia ayuda, y podemos acceder a ella usando el parámetro @help = 1:

EXEC sp_whoIsActive @help = 1

y la salida se divide en 3 conjuntos de resultados:

Información sobre el creador de SP.

Descripción de los parámetros de llamada al SP

Descripción de las columnas devueltas por SP

Permisos mínimos necesarios para ejecutar SP

Uno de los puntos importantes a comentar son los permisos mínimos necesarios para utilizar sp_WhoIsActive. He visto muchos DBA otorgar acceso de administrador de sistemas a un usuario, para que pueda usar este SP, porque no sabe qué se debe liberar para que el usuario ejecute el Procedimiento almacenado en la instancia, ya que lo más común es intentar liberar solo el permiso EJECUTAR en el Procedimiento almacenado.

Si no libera el permiso de ver el estado del servidor, verá el siguiente mensaje de error:

Para hacer esto, simplemente ejecute el siguiente comando, para que el usuario pueda usar sp_WhoIsActive normalmente:

USE [master]
GO
GRANT EXECUTE ON dbo.sp_WhoIsActive TO [dominio\usuario]
GRANT VIEW SERVER STATE TO [dominio\usuario]
GO

Si no utiliza ningún parámetro, esta es la vista que tendrá al ejecutar sp_WhoIsActive. Como hay muchas columnas, tuve que cortar el resultado en 2 imágenes.

Parte 1:

parte 2

Una característica realmente interesante de esta herramienta es la posibilidad de filtrar los resultados sin tener que exportarlos a una tabla física y luego filtrarlos. Para hacer esto, podemos usar los parámetros de filtro propios del SP.

El parámetro @filter nos permite definir lo que queremos buscar (admite el comodín % para realizar búsquedas como LIKE ‘%string%’), mientras que el parámetro @filter_type nos permite definir dónde queremos buscar esta información. Los tipos posibles para @filter_type son:

• sesión: Le permite buscar una sesión específica
• programa: Le permite buscar sesiones que utilizan un software de cliente específico para conectarse a la base de datos.
• base de datos: Este tipo de filtro se utiliza para filtrar las consultas que se ejecutan en una base de datos determinada.
• acceso: Filtro utilizado para filtrar las sesiones de un usuario específico
• anfitrión: utilice este filtro para ver solo sesiones provenientes de un nombre de host específico

Ejemplos de uso de filtros inclusivos (@filter y @filter_type):

Ejemplos de uso de filtros únicos (@not_filter y @not_filter_type):

Al detallar más parámetros de este SP, demostraré la utilidad de los parámetros @show_own_spid, @show_system_spids y @show_sleeping_spids.

El parámetro @show_own_spid (BIT) determina si la sesión que está ejecutando el procedimiento será parte del resultado final que se mostrará en pantalla. El valor predeterminado es 0 (cero), lo que significa que la sesión en sí no se muestra de forma predeterminada.

El parámetro @show_system_spids (BIT) determina si las sesiones internas del sistema SQL Server se mostrarán en el resultado final del SP. El valor predeterminado es 0 (cero), lo que hace que estas sesiones se ignoren.

El parámetro @show_sleeping_spids (TINYINT) determina si las sesiones inactivas (durmiendo) se mostrarán en el resultado final del SP. El valor predeterminado es 0 (cero), lo que hace que estas sesiones se ignoren. El valor 1 muestra todas las sesiones inactivas que tienen una transacción abierta y el valor 2 muestra todas las sesiones inactivas.

Ejemplos de uso:

El resultado final de este procedimiento almacenado es muy interesante, y con estos parámetros será aún más completo. El valor predeterminado de todos estos parámetros de tipo BIT es 0 (cero), lo que hace que ninguno de ellos se muestre (a menos que lo cambie a 1).

@get_full_inner_text

De forma predeterminada, la instrucción SQL que se devuelve en formato XML en la columna sql_text es solo el lote que se está procesando actualmente. Al utilizar este parámetro, podemos observar todo el contenido del lote que se envió a SQL Server para su procesamiento.

@get_plans

Al utilizar este parámetro con el valor 1, se generará una demostración del plan de ejecución de la consulta actual de cada sesión devuelta por este SP. Usando el valor 2 en este parámetro, se genera el plan de ejecución para toda la consulta de sesión. Al hacer clic en ResultSet XML, Management Studio ya muestra el plan de ejecución para esta consulta. ¡Fantástico!

@get_outer_command

Este parámetro es similar a @get_full_inner_text, pero en lugar de reemplazar el valor de la columna sql_text, mantiene esta columna con su valor predeterminado (solo la sección en ejecución) y agrega una nueva columna llamada sql_command, que contiene la consulta completa que está ejecutando la sesión. De esta manera, tenemos ambas opiniones.

@get_transaction_info

Usando este parámetro, podemos ver la cantidad y el volumen de datos escritos en el registro de transacciones para cada sesión.

@get_task_info

Un parámetro muy interesante para el análisis de rendimiento, @get_task_info le permite ver más información sobre las sesiones en ejecución. Al utilizar el valor 1, podemos ver los eventos de espera más grandes (aparte de CXPACKET).

Al utilizar el parámetro 2 visualizaremos el modo completo, que incluye las columnas:

• fisico_io: muestra el número de lecturas/escrituras físicas (E/S) en el disco.
• cambios de contexto: Muestra el número de cambios de contexto para la conexión activa. Un cambio de contexto ocurre cuando el kernel del sistema operativo cambia el procesador de un subproceso a otro (por ejemplo, un subproceso de mayor prioridad).

  Este indicador es muy importante para identificar si un proceso está usando la CPU más que otros procesos y evitar que lleguen al procesador. Un índice muy alto significa que hay mucha competencia en el procesador y puede estar sobrecargado. Un número bajo significa que algún proceso está asignando más CPU de la que debería, generando mucho tiempo de espera (y probablemente sesiones con estado Pendiente y Ejecutable).

  Los valores esperados deben ser algo inferiores a 2000 cambios por procesador/segundo (algunos DBA consideran aceptable un valor inferior a 5000). Los valores muy altos pueden deberse a fallas en la asignación de la memoria física (RAM). Otro posible agravante es la tecnología Intel® Hyper-Threading, que en algunos casos puede provocar muchos cambios de contexto debido a la simulación de núcleos virtuales. Si tiene este problema, una buena prueba es desactivar esta función en la placa base del servidor y realizar pruebas de rendimiento.

• tareas: Número de tareas utilizadas por la ejecución actual.

@get_locks

Parámetro muy útil para mantener e identificar bloqueos en la instancia. Al activarse muestra los objetos reservados para cada solicitud, así como el tipo de bloqueo solicitado por la sesión.

@get_avg_time

Usando este parámetro, aparece una nueva columna en el resultado final (dd hh:mm:ss.mss (avg)). Esta columna muestra el tiempo de ejecución promedio de la consulta actual que se ejecuta en cada sesión. Como puede ver en el ejemplo, mi consulta se ha estado ejecutando durante más de 2 horas, pero la sección actual está tardando un promedio de 79 ms, en un bucle de 850.000 iteraciones. Este tiempo se estima en base al plan y los historiales de ejecución.

@get_additional_info

Usando este parámetro, se creará una nueva columna en el resultado final llamada “additional_info”, que es un XML con diversa información y definiciones de comandos SET para cada sesión, como se muestra en el siguiente ejemplo:

Si se está ejecutando un trabajo del Agente SQL Server, la columna de información adicional de esa sesión que abrió el trabajo tendrá la información del trabajo:

Si usas los parámetros @get_task_info = 2 y @get_additional_info = 1 y hay un bloqueo en una sesión, el XML en la columna “additional_info” de esa sesión que está bloqueada tendrá un nodo llamado block_info con la información del bloque:

@find_block_leaders

Uno de mis parámetros favoritos, @find_block_leaders, cuando está activado, le permite analizar cada sesión y contar cuántas otras sesiones están bloqueadas esperando que esa sesión libere objetos. ¿Sabe cuándo comienzan los eventos de bloqueo en su instancia de producción y debe seguir buscando quién está causando estos bloqueos? Este parámetro es la solución para usted.

@delta_interval

Esta interesante característica permite realizar dos recopilaciones de datos en un período de tiempo determinado (este período es el valor del parámetro, en segundos) y analizar la diferencia en la asignación de tempdb, lecturas, escrituras, etc. entre las dos recopilaciones realizadas.

En el siguiente ejemplo, especifiqué un intervalo de 10 segundos entre cada colección. Al cabo de 10 segundos, se crearán columnas con el sufijo "_delta", que demuestran la diferencia entre la primera y la segunda ejecución.

Esto es muy útil para analizar el crecimiento de la asignación de tempdb o lecturas de disco en tiempo real. A menudo, analizar sólo la sesión total y la asignación actual no es suficiente para estimar el crecimiento y la asignación de recursos, lo que hace que esta característica sea muy interesante para los DBA.

Además de ser muy completo, este SP nos permite personalizar el resultado final y la salida generada de varias formas. Ahora demostraré cómo hacer esto.

@salida_columna_lista

Como ya he demostrado en algunos ejemplos anteriores, este parámetro se utiliza para definir qué columnas deben formar parte del resultado final de la ejecución del SP.

@sort_order

Como sugiere el nombre, este parámetro se utiliza para ordenar los resultados según tus necesidades, donde eliges qué columnas usar para ordenar y cuáles son los criterios (asc o desc).

@formato_salida

Este parámetro se utiliza para cambiar la forma en que se muestran algunas columnas a un modo de lectura más "humano". Con un valor de 1, el formato de salida utilizará fuentes de longitud variable. Con un valor de 2, el formato de salida utilizará fuentes de longitud fija.

Para ser honesto, veo diferencias entre el valor 0 y 1, pero no veo diferencias entre los valores 1 y 2.

@return_schema y @schema

Estos parámetros juntos sirven para generar el script de creación de resultados del SP. El parámetro @return_schema, cuando se establece en 1, en lugar de devolver el resultado de la ejecución, genera el script CREATE TABLE para el resultado. Este script debe leerse usando una variable OUTPUT en el parámetro @schema, como se muestra a continuación:

@mesa_destino

Y por último, tenemos el parámetro @destination_table. Se utiliza para insertar el resultado de la ejecución del SP en una tabla física, donde podemos almacenar el historial y consultarlo cuando queramos.

Para utilizar este parámetro se debe crear previamente la tabla, ya que este parámetro solo insertará los datos, no creará la tabla. Para obtener el comando CREATE TABLE resultante de la ejecución de este SP, basta con mirar los parámetros explicados anteriormente (@return_schema y @schema) para hacerlo de forma sencilla y en unos segundos.