
Script de Monitoreo Avanzado de Sesiones en SQL Server
Monitoreo Avanzado de Sesiones en SQL Server, en la administración de bases de datos SQL Server, es crucial monitorear las sesiones activas para identificar problemas de rendimiento, bloquear procesos, y optimizar las operaciones. Una manera efectiva de hacerlo es utilizando consultas avanzadas que nos proporcionen una visión detallada de lo que está sucediendo en el servidor. En este blog, desglosaremos una consulta compleja que te permite obtener información detallada sobre las sesiones activas, el progreso de sus operaciones, y otros indicadores clave.
La Consulta en Detalle
La consulta que vamos a analizar es la siguiente:
SELECT
ss.session_id SPID,
original_login_name,
sp.status,
blocking_session_id [Blk by],
command,
db_name(sp.database_id) [Database],
[objectid],
percent_complete [% Done],
CASE
WHEN DATEDIFF(mi, start_time, GETDATE()) > 60 THEN
CONVERT(varchar(4), (DATEDIFF(mi, start_time, GETDATE()) / 60)) + ' hr '
ELSE ''
END +
CASE
WHEN DATEDIFF(ss, start_time, GETDATE()) > 60 THEN
CONVERT(varchar(4), (DATEDIFF(mi, start_time, GETDATE()) % 60)) + ' min '
ELSE ''
END +
CONVERT(varchar(4), (DATEDIFF(ss, start_time, GETDATE()) % 60)) + ' sec' [Duration],
estimated_completion_time / 60000 [ETA (Min)],
[text] [input stream/text],
(SUBSTRING([text], statement_start_offset / 2 + 1,
((CASE
WHEN statement_end_offset < 0 THEN (LEN(CONVERT(nvarchar(max), [text])) * 2)
ELSE statement_end_offset
END) - statement_start_offset) / 2 + 1)) AS [Executing_sql_statement],
wait_resource,
wait_time / 1000 [wait_time (sec)],
last_wait_type,
login_time,
last_request_start_time,
last_request_end_time,
host_name,
CASE
WHEN program_name LIKE 'SQLAgent%Job%' THEN
(SELECT TOP 1 '(SQLAgent Job - ' + name + ' - ' + RIGHT(program_name, LEN(program_name) - CHARINDEX(':', program_name))
FROM msdb.dbo.sysjobs SJ
WHERE UPPER(master.dbo.fn_varbintohexstr(SJ.job_id)) = UPPER(SUBSTRING([program_name], 30, 34)))
ELSE program_name
END [program_name],
sp.open_transaction_count,
CASE
WHEN sp.transaction_isolation_level = 0 THEN 'Unspecified'
WHEN sp.transaction_isolation_level = 1 THEN 'ReadUncommitted'
WHEN sp.transaction_isolation_level = 2 THEN 'ReadCommitted'
WHEN sp.transaction_isolation_level = 3 THEN 'Repeatable'
WHEN sp.transaction_isolation_level = 4 THEN 'Serializable'
WHEN sp.transaction_isolation_level = 5 THEN 'Snapshot'
END [transaction_isolation_level],
sp.cpu_time,
sp.reads,
sp.writes,
sp.logical_reads,
sp.lock_timeout,
sp.row_count
FROM sys.dm_exec_requests AS sp
OUTER APPLY sys.dm_exec_sql_text(sp.sql_handle) AS esql
RIGHT OUTER JOIN sys.dm_exec_sessions ss ON ss.session_id = sp.session_id
WHERE ss.status <> 'sleeping';

¿Qué hace esta consulta sobre Monitoreo Avanzado de Sesiones en SQL Server?
Esta consulta está diseñada para obtener información detallada sobre las sesiones activas en SQL Server, excluyendo aquellas en estado sleeping
. Vamos a desglosar los principales elementos de la consulta para entender cómo funciona y qué información proporciona.
1. Información Básica de la Sesión
- SPID (session_id): Identificador único de la sesión en el servidor.
- original_login_name: El nombre de usuario que inició la sesión.
- status: El estado actual de la sesión, por ejemplo,
running
,suspended
, etc. - Blk by (blocking_session_id): Indica el
SPID
del proceso que está bloqueando esta sesión, si existe un bloqueo.
2. Detalles del Comando y Progreso
- command: El comando SQL que se está ejecutando en la sesión.
- Database: La base de datos donde se está ejecutando el comando.
- percent_complete: Porcentaje de progreso de la operación en ejecución, útil para operaciones de larga duración como indexaciones o backups.
- Duration: Muestra cuánto tiempo ha estado en ejecución la operación actual.
3. Estimaciones y Texto de la Consulta
- ETA (Min): Estimación del tiempo restante para completar la operación en minutos.
- input stream/text: El texto completo del comando SQL que se está ejecutando.
- Executing_sql_statement: El texto exacto de la declaración SQL que se está ejecutando en ese momento.
4. Información de Espera y Bloqueos
- wait_resource: El recurso específico en el que la sesión está esperando.
- wait_time (sec): Tiempo que la sesión ha estado esperando en segundos.
- last_wait_type: El tipo de espera más reciente para esta sesión, que puede ayudar a diagnosticar problemas de rendimiento.
5. Detalles del Sistema y Programa
- host_name: Nombre del host desde el cual se ha iniciado la sesión.
- program_name: Nombre del programa que inició la sesión. Si es un trabajo de SQL Agent, también se muestra el nombre del trabajo.
6. Transacciones y Aislamiento
- open_transaction_count: Número de transacciones abiertas en la sesión.
- transaction_isolation_level: El nivel de aislamiento de transacción actual, que puede variar desde
ReadUncommitted
hastaSerializable
.
7. Estadísticas de Uso de Recursos
- cpu_time, reads, writes, logical_reads: Estadísticas sobre el uso de CPU, operaciones de lectura y escritura, y lecturas lógicas realizadas por la sesión.
- lock_timeout: El número de veces que una operación ha excedido el tiempo de espera para obtener un bloqueo.
- row_count: El número de filas procesadas por la sesión.
Aplicaciones Prácticas
Monitoreo de Sesiones Activas
Esta consulta es invaluable para administradores de bases de datos que necesitan una visión detallada de las sesiones activas en su servidor. Permite identificar rápidamente sesiones problemáticas, como aquellas que están bloqueando otras, o que están consumiendo una cantidad excesiva de recursos.
Diagnóstico de Problemas de Rendimiento
Al revisar los tiempos de espera, los tipos de bloqueo, y las estadísticas de uso de recursos, puedes diagnosticar cuellos de botella en el rendimiento y tomar medidas para optimizar las operaciones. Por ejemplo, si un proceso está tomando demasiado tiempo debido a una espera en un recurso, puedes investigar más a fondo o ajustar la configuración de SQL Server para mejorar la eficiencia.
Gestión de Transacciones
Con el conteo de transacciones abiertas y el nivel de aislamiento de transacciones, esta consulta también ayuda a garantizar que las transacciones se manejen de manera eficiente y que no existan conflictos que puedan causar bloqueos o ralentizaciones.
¿Cuándo usar este script: Monitoreo Avanzado de Sesiones en SQL Server?
Esta consulta es una herramienta poderosa para monitorear y diagnosticar sesiones activas en SQL Server. Aquí te explico algunas situaciones específicas en las que puede ser especialmente útil:
1. Identificación de Procesos Lentos o Colgados
Si experimentas que ciertas operaciones en SQL Server están tomando mucho más tiempo de lo habitual, puedes ejecutar esta consulta para identificar el proceso problemático. Al revisar columnas como Duration
, percent_complete
, y ETA (Min)
, puedes determinar cuánto tiempo ha estado ejecutándose la operación y si está cerca de completarse.
Situación: Supongamos que un trabajo de mantenimiento, como la reconstrucción de un índice, está tomando demasiado tiempo. Esta consulta te permite ver el porcentaje completado (percent_complete
), el tiempo estimado de finalización (ETA (Min)
), y el tiempo total que ha estado en ejecución (Duration
). Si los tiempos son excesivos, puedes tomar decisiones como cancelar el trabajo o investigar más a fondo qué lo está ralentizando.
2. Diagnóstico de Bloqueos
Los bloqueos son un problema común en bases de datos concurridas, donde un proceso retiene un bloqueo sobre un recurso y otros procesos deben esperar. Esta consulta te ayuda a identificar procesos que están siendo bloqueados y el proceso que los está bloqueando (Blk by
).
Situación: Si los usuarios informan que una aplicación se está quedando colgada o que las transacciones están tardando más de lo normal, puedes ejecutar esta consulta para ver si hay bloqueos. La columna Blk by
te muestra el SPID
del proceso que está causando el bloqueo, permitiéndote investigar más a fondo o tomar acciones, como matar el proceso bloqueador.
3. Optimización de Consultas
La consulta muestra el texto completo del SQL que se está ejecutando (input stream/text
) y la parte específica de la declaración que se está ejecutando actualmente (Executing_sql_statement
). Esto es extremadamente útil para optimizar consultas, ya que te permite identificar qué parte exacta de una consulta compleja está consumiendo más recursos o está tomando más tiempo.
Situación: Si una consulta está utilizando demasiado CPU o está provocando muchas lecturas/escrituras, puedes usar esta información para revisar y optimizar la consulta SQL en cuestión. Podrías agregar índices, reescribir la consulta, o ajustar los planes de ejecución para mejorar el rendimiento.
4. Monitoreo de Trabajos Programados
SQL Server Agent es responsable de ejecutar trabajos programados, como copias de seguridad o tareas de mantenimiento. A veces, estos trabajos pueden fallar o tomar más tiempo del esperado. La columna program_name
en esta consulta identifica específicamente si una sesión está ejecutando un trabajo de SQL Agent, incluyendo el nombre del trabajo.
Situación: Si sospechas que un trabajo de SQL Agent no se está ejecutando correctamente, esta consulta te permitirá identificarlo y ver en qué etapa está. Esto es útil para resolver problemas rápidamente sin tener que buscar manualmente en los registros de SQL Agent.
5. Análisis de Esperas y Bloqueos
Las columnas wait_time (sec)
y last_wait_type
te permiten ver cuánto tiempo ha estado esperando un proceso y cuál fue el último tipo de espera experimentado. Esto es crucial para diagnosticar problemas de rendimiento relacionados con la espera de recursos como discos, CPU, o bloqueos de otros procesos.
Situación: Si notas que ciertas transacciones están tardando más de lo normal, puedes usar estas columnas para ver si están esperando en recursos específicos, como una página de datos bloqueada por otro proceso o esperando en disco debido a una operación de entrada/salida lenta.
6. Gestión de Transacciones
Las columnas open_transaction_count
y transaction_isolation_level
son útiles para entender cómo las transacciones están siendo manejadas en el servidor. Esto puede ayudarte a diagnosticar problemas de concurrencia o ver si una transacción está manteniendo recursos bloqueados durante mucho tiempo.
Situación: Si un proceso tiene una transacción abierta durante un período prolongado, podría estar reteniendo bloqueos que afectan a otros procesos. Identificar el nivel de aislamiento de la transacción también te permite ajustar configuraciones o recomendaciones para evitar problemas de concurrencia.
7. Control del Uso de Recursos
Las columnas que muestran cpu_time
, reads
, writes
, y logical_reads
te permiten evaluar el uso de recursos de una sesión específica. Esta información es crucial para identificar procesos que están consumiendo más recursos de los necesarios.
Situación: Si el rendimiento del servidor está degradado, puedes usar esta consulta para identificar qué procesos están utilizando mucho CPU o realizando muchas operaciones de lectura/escritura. Esto te ayuda a tomar decisiones sobre si es necesario optimizar la consulta, moverla a un horario de menor carga, o revisar el plan de ejecución.
Conclusión
Esta consulta avanzada es una herramienta poderosa para el monitoreo y diagnóstico de sesiones activas en SQL Server. Proporciona una visión profunda del comportamiento de las sesiones y permite a los administradores identificar problemas de rendimiento, gestionar transacciones, y asegurar que el servidor funcione de manera óptima. Si estás buscando una manera de mejorar el rendimiento y la estabilidad de tu servidor SQL, incorporar esta consulta en tus rutinas de monitoreo es un excelente primer paso.
UPDATE JOIN en SQL para Actualizar Tablas Relacionadas
dm_exec_procedure_stats en SQL Server
Información del Servidor SQL Server con T-SQL
Script para saber el histórico de queries ejecutados SQL
Guía Completa para Implementar FULLTEXT en SQL
Cuentos de miedo para Informáticos: Historias de terror para informáticos