Explorar guías rápidas · SQL Server

Tecnología: SQL ServerCategoría: Solución de problemasNivel: IntermedioActualizado

Actividad por login, host y aplicación en SQL Server

Agrupa la actividad de SQL Server por login, host, aplicación y base de datos para identificar qué cliente o carga de trabajo está generando conexiones, solicitudes, consumo de CPU, lecturas o bloqueos.

En esta página
  1. Problema
  2. Respuesta rápida
  3. Código / comandos
  4. 1. Agrupar las solicitudes activas por login
  5. 2. Agrupar las solicitudes activas por aplicación
  6. 3. Agrupar las solicitudes activas por host
  7. 4. Agrupar la actividad por login, host, aplicación y base de datos
  8. 5. Contar las sesiones, incluidas las conexiones inactivas
  9. 6. Encontrar sesiones con transacciones abiertas por aplicación
  10. 7. Encontrar las solicitudes bloqueadas por aplicación
  11. 8. Encontrar los bloqueadores y la aplicación a la que pertenecen
  12. 9. Mostrar el detalle de las sesiones de una aplicación
  13. 10. Inspeccionar el origen de la conexión y los detalles de autenticación
  14. Qué significa el resultado
  15. Qué revisar
  16. Una aplicación domina las solicitudes activas
  17. Un login domina el CPU o las lecturas
  18. Muchas sesiones, pero pocas solicitudes activas
  19. Muchas solicitudes bloqueadas de una misma aplicación
  20. El bloqueador está inactivo con una transacción abierta
  21. Aplicación o host inesperados
  22. Notas de producción
  23. host_name y program_name los proporciona el cliente
  24. Las solicitudes activas y las sesiones conectadas responden a preguntas distintas
  25. Los resultados agregados de las DMV son instantáneas puntuales
  26. Los logins compartidos reducen la observabilidad
  27. El pool de conexiones afecta al número de sesiones
  28. Los permisos varían según la versión
  29. Errores comunes
  30. 1. Tratar host_name como una fuente fiable
  31. 2. Suponer que muchas sesiones inactivas son un problema
  32. 3. Culpar a la aplicación bloqueada
  33. 4. Agrupar solo por login
  34. 5. Deshabilitar un login durante un incidente sin confirmar a quién pertenece
  35. 6. Tratar una sola instantánea como un historial de tráfico
  36. 7. Ignorar las transacciones abiertas
  37. Siguiente paso
  38. Para profundizar

Problema

Una instancia de SQL Server está muy ocupada, el volumen de solicitudes ha aumentado o una aplicación parece estar generando presión.

Necesitas responder preguntas como estas:

  • ¿Qué login genera más solicitudes activas?
  • ¿Qué host abre más sesiones?
  • ¿Qué aplicación está consumiendo CPU o lecturas?
  • ¿Qué base de datos está recibiendo la actividad?
  • ¿Hay un login responsable de los bloqueos?
  • ¿Hay clientes inesperados conectados a la instancia?

Esto es especialmente útil durante incidentes en los que varias aplicaciones comparten el mismo SQL Server.

Respuesta rápida

Empieza agrupando las solicitudes activas por login, host, aplicación y base de datos.

SELECT
    s.login_name,
    s.host_name,
    s.program_name,
    DB_NAME(r.database_id) AS database_name,

    COUNT(*) AS active_request_count,
    SUM(r.cpu_time) AS total_cpu_ms,
    SUM(r.logical_reads) AS total_logical_reads,
    SUM(r.reads) AS total_physical_reads,
    SUM(r.writes) AS total_writes,

    SUM(
        CASE
            WHEN r.blocking_session_id > 0 THEN 1
            ELSE 0
        END
    ) AS blocked_request_count,

    MAX(r.total_elapsed_time) AS max_elapsed_ms
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
WHERE
    r.session_id <> @@SPID
    AND s.is_user_process = 1
GROUP BY
    s.login_name,
    s.host_name,
    s.program_name,
    DB_NAME(r.database_id)
ORDER BY
    active_request_count DESC,
    total_cpu_ms DESC;

Usa esta consulta primero cuando necesites identificar qué carga de trabajo de cliente está activa en este momento.

Código / comandos

1. Agrupar las solicitudes activas por login

SELECT
    s.login_name,
    COUNT(*) AS active_request_count,
    SUM(r.cpu_time) AS total_cpu_ms,
    SUM(r.logical_reads) AS total_logical_reads,
    SUM(r.reads) AS total_physical_reads,
    SUM(r.writes) AS total_writes
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
WHERE
    r.session_id <> @@SPID
    AND s.is_user_process = 1
GROUP BY
    s.login_name
ORDER BY
    active_request_count DESC,
    total_cpu_ms DESC;

Usa esta consulta cuando el mismo login de SQL o el mismo usuario contenido pueda estar compartido por muchas sesiones de aplicación.


2. Agrupar las solicitudes activas por aplicación

SELECT
    s.program_name,
    COUNT(*) AS active_request_count,
    SUM(r.cpu_time) AS total_cpu_ms,
    SUM(r.logical_reads) AS total_logical_reads,
    SUM(r.writes) AS total_writes,
    MAX(r.total_elapsed_time) AS max_elapsed_ms
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
WHERE
    r.session_id <> @@SPID
    AND s.is_user_process = 1
GROUP BY
    s.program_name
ORDER BY
    total_cpu_ms DESC,
    active_request_count DESC;

Es útil cuando varios servicios usan el mismo login, pero se identifican mediante el nombre de aplicación de la conexión.


3. Agrupar las solicitudes activas por host

SELECT
    s.host_name,
    COUNT(*) AS active_request_count,
    SUM(r.cpu_time) AS total_cpu_ms,
    SUM(r.logical_reads) AS total_logical_reads,
    SUM(r.writes) AS total_writes
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
WHERE
    r.session_id <> @@SPID
    AND s.is_user_process = 1
GROUP BY
    s.host_name
ORDER BY
    active_request_count DESC,
    total_cpu_ms DESC;

Úsala para identificar qué servidor de aplicaciones, estación de trabajo o host de procesos por lotes está generando la carga de trabajo.


4. Agrupar la actividad por login, host, aplicación y base de datos

SELECT
    s.login_name,
    s.host_name,
    s.program_name,
    DB_NAME(r.database_id) AS database_name,

    COUNT(*) AS active_request_count,
    SUM(r.cpu_time) AS total_cpu_ms,
    SUM(r.logical_reads) AS total_logical_reads,
    SUM(r.reads) AS total_physical_reads,
    SUM(r.writes) AS total_writes,

    SUM(
        CASE
            WHEN r.blocking_session_id > 0 THEN 1
            ELSE 0
        END
    ) AS blocked_request_count
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
WHERE
    r.session_id <> @@SPID
    AND s.is_user_process = 1
GROUP BY
    s.login_name,
    s.host_name,
    s.program_name,
    DB_NAME(r.database_id)
ORDER BY
    active_request_count DESC,
    total_cpu_ms DESC;

Suele ser la mejor vista a nivel de incidente, porque muestra a la vez a quién pertenece la carga de trabajo y qué recursos consume.


5. Contar las sesiones, incluidas las conexiones inactivas

Las solicitudes activas solo muestran el trabajo que se está ejecutando en este momento.

Para examinar la huella de conexiones:

SELECT
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    COUNT(*) AS session_count
FROM sys.dm_exec_sessions AS s
WHERE
    s.is_user_process = 1
GROUP BY
    s.login_name,
    s.host_name,
    s.program_name,
    s.status
ORDER BY
    session_count DESC;

Esto ayuda a responder:

  • qué aplicaciones mantienen más conexiones
  • si los pools de conexiones son muy grandes
  • si hay muchas sesiones inactivas

Un número elevado de sesiones inactivas no es automáticamente un problema.


6. Encontrar sesiones con transacciones abiertas por aplicación

SELECT
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    COUNT(*) AS sessions_with_open_transactions,
    SUM(s.open_transaction_count) AS total_open_transactions
FROM sys.dm_exec_sessions AS s
WHERE
    s.is_user_process = 1
    AND s.open_transaction_count > 0
GROUP BY
    s.login_name,
    s.host_name,
    s.program_name,
    s.status
ORDER BY
    total_open_transactions DESC;

Usa esta consulta cuando sospeches que una aplicación deja transacciones abiertas.

Continúa con un análisis de las transacciones a nivel de sesión antes de tomar cualquier medida.


7. Encontrar las solicitudes bloqueadas por aplicación

SELECT
    s.login_name,
    s.host_name,
    s.program_name,
    COUNT(*) AS blocked_request_count,
    MAX(r.wait_time) AS max_wait_ms
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
WHERE
    r.blocking_session_id > 0
    AND s.is_user_process = 1
GROUP BY
    s.login_name,
    s.host_name,
    s.program_name
ORDER BY
    blocked_request_count DESC,
    max_wait_ms DESC;

Esto muestra qué aplicaciones están sufriendo bloqueos.

No te dice qué aplicación está causando el bloqueo.


8. Encontrar los bloqueadores y la aplicación a la que pertenecen

WITH blockers AS
(
    SELECT DISTINCT
        blocking_session_id AS session_id
    FROM sys.dm_exec_requests
    WHERE
        blocking_session_id > 0
)
SELECT
    b.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    s.open_transaction_count
FROM blockers AS b
INNER JOIN sys.dm_exec_sessions AS s
    ON s.session_id = b.session_id
ORDER BY
    b.session_id;

Úsala para identificar la aplicación o el login asociados a la sesión que causa el bloqueo.


9. Mostrar el detalle de las sesiones de una aplicación

DECLARE @program_name nvarchar(128) = N'Your Application Name';

SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status AS session_status,
    s.login_time,
    s.last_request_start_time,
    s.last_request_end_time,
    s.open_transaction_count,

    r.status AS request_status,
    DB_NAME(r.database_id) AS database_name,
    r.command,
    r.cpu_time AS cpu_ms,
    r.logical_reads,
    r.wait_type,
    r.blocking_session_id,
    txt.text AS batch_text
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    s.is_user_process = 1
    AND s.program_name = @program_name
ORDER BY
    s.session_id;

Úsala después de que la vista agregada identifique una aplicación sospechosa.


10. Inspeccionar el origen de la conexión y los detalles de autenticación

SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    c.client_net_address,
    c.net_transport,
    c.protocol_type,
    c.auth_scheme,
    c.connect_time
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_connections AS c
    ON c.session_id = s.session_id
WHERE
    s.is_user_process = 1
ORDER BY
    c.connect_time DESC;

Es útil cuando la identidad de la aplicación no está clara o cuando varios sistemas informan el mismo program_name.

Ten cuidado al compartir direcciones IP de clientes o nombres de host fuera de los equipos de operaciones.

Qué significa el resultado

Campo Por qué importa
login_name Login de SQL Server o identidad de autenticación asociada a la sesión.
host_name Nombre del host del cliente informado por la conexión. Útil para rastrear a quién pertenece la carga de trabajo.
program_name Nombre de la aplicación informado por el controlador del cliente. Suele ser la forma más sencilla de distinguir los servicios.
active_request_count Número de solicitudes que se están ejecutando en el grupo.
session_count Número de sesiones conectadas, incluidas las inactivas.
total_cpu_ms CPU acumulado por las solicitudes actuales del grupo.
total_logical_reads Actividad de páginas generada por las solicitudes actuales.
blocked_request_count Número de solicitudes activas que están bloqueadas en este momento.
open_transaction_count Señal, a nivel de sesión, de que hay transacciones que siguen abiertas.
client_net_address Dirección de red del lado del cliente tal como la ve SQL Server.
auth_scheme Mecanismo de autenticación que usa la conexión.

Qué revisar

Una aplicación domina las solicitudes activas

Esto puede indicar:

  • un pico de tráfico
  • una tormenta de reintentos (retry storm)
  • una carga de trabajo ineficiente con demasiadas llamadas pequeñas
  • un job por lotes
  • una carga de trabajo de reporting
  • un aumento repentino de conexiones en el pool

Compara el número de solicitudes con el CPU, las lecturas y el tiempo transcurrido.

Un login domina el CPU o las lecturas

El login puede representar:

  • una sola aplicación
  • varias aplicaciones que comparten credenciales
  • un job programado
  • un usuario de reporting

Profundiza en host_name y program_name antes de atribuir la responsabilidad.

Muchas sesiones, pero pocas solicitudes activas

Suele ser el comportamiento normal del pool de conexiones.

No des por hecho que el número de sesiones por sí solo indique presión.

Revisa:

  • las solicitudes activas
  • el uso de workers
  • las transacciones abiertas
  • la rotación de conexiones
  • el comportamiento de la aplicación

Muchas solicitudes bloqueadas de una misma aplicación

Esto identifica la carga de trabajo víctima.

A continuación, encuentra el bloqueador real y la aplicación a la que pertenece.

El bloqueador está inactivo con una transacción abierta

Es una señal clara de problemas en la gestión de transacciones.

Investiga:

  • el manejo de commit/rollback
  • los errores de la aplicación
  • las rutas de tiempo de espera agotado (timeout)
  • el pool de conexiones

Aplicación o host inesperados

Esto puede revelar:

  • actividad ad hoc desde SSMS
  • jobs olvidados
  • versiones antiguas de aplicaciones
  • herramientas de prueba que acceden a producción
  • tráfico de reporting inesperado

Valida a quién pertenece antes de bloquear o deshabilitar el acceso.

Notas de producción

host_name y program_name los proporciona el cliente

Estos campos son útiles a nivel operativo, pero no son identidades de seguridad fiables.

Un cliente puede establecerlos u omitirlos.

Úsalos para el diagnóstico, no para la autorización.

Las solicitudes activas y las sesiones conectadas responden a preguntas distintas

sys.dm_exec_requests muestra el trabajo que se está ejecutando.

sys.dm_exec_sessions muestra las sesiones conectadas.

Una carga de trabajo puede tener:

  • muchas sesiones inactivas
  • pocas solicitudes activas

o bien:

  • pocas sesiones
  • muchas solicitudes costosas a lo largo del tiempo

Interpreta ambas cosas.

Los resultados agregados de las DMV son instantáneas puntuales

Las consultas anteriores te dicen lo que existe ahora.

Para picos de tráfico o patrones recurrentes, guarda instantáneas o usa:

  • Extended Events
  • Query Store
  • la telemetría de la aplicación
  • herramientas de monitoreo

Los logins compartidos reducen la observabilidad

Si varias aplicaciones usan el mismo login de SQL, la agregación por login puede ser demasiado general.

Unos buenos valores de Application Name en las cadenas de conexión facilitan mucho el diagnóstico en producción.

El pool de conexiones afecta al número de sesiones

Los pools de conexiones mantienen conexiones abiertas de forma intencionada.

Un número elevado de sesiones inactivas puede ser normal.

Céntrate en:

  • la rotación de conexiones
  • la presión de las solicitudes activas
  • las transacciones que quedan abiertas
  • los síntomas de agotamiento del pool

Los permisos varían según la versión

Para tener acceso general a estas DMV de rendimiento:

  • SQL Server 2019 y versiones anteriores suelen requerir VIEW SERVER STATE
  • SQL Server 2022 y versiones posteriores suelen requerir VIEW SERVER PERFORMANCE STATE

Azure SQL Database usa reglas de ámbito y de permisos diferentes.

Errores comunes

1. Tratar host_name como una fuente fiable

Son metadatos proporcionados por el cliente.

Úsalos como una pista, no como un hecho de seguridad.

2. Suponer que muchas sesiones inactivas son un problema

Los pools de conexiones pueden mantener muchas sesiones inactivas de forma intencionada.

Revisa la carga de trabajo y la presión sobre los recursos antes de actuar.

3. Culpar a la aplicación bloqueada

Una aplicación con muchas solicitudes bloqueadas puede ser la víctima.

Encuentra la sesión que causa el bloqueo antes de atribuir la causa raíz.

4. Agrupar solo por login

Los logins compartidos pueden ocultar la aplicación real.

Combina login, host, programa y base de datos.

5. Deshabilitar un login durante un incidente sin confirmar a quién pertenece

Un login puede dar servicio a varias aplicaciones.

Antes de deshabilitar el acceso, confirma:

  • el responsable de negocio
  • las aplicaciones dependientes
  • el plan de reversión
  • el procedimiento para volver a habilitarlo
  • la ventana del incidente

6. Tratar una sola instantánea como un historial de tráfico

Una única consulta a las DMV no puede decirte cómo ha cambiado la actividad durante la última hora.

Guarda instantáneas si necesitas analizar tendencias.

7. Ignorar las transacciones abiertas

Una aplicación aparentemente inactiva puede seguir manteniendo bloqueos mediante una transacción abierta.

Comprueba el estado de las transacciones antes de concluir que las sesiones inactivas son inofensivas.

Siguiente paso

Usa el patrón de pertenencia para elegir la siguiente línea de investigación.

  • Una aplicación domina el CPU → inspecciona sus consultas activas o con alto consumo de CPU y sus planes.
  • Una aplicación domina las lecturas → inspecciona sus consultas con muchas lecturas y sus rutas de acceso.
  • Una aplicación tiene muchas solicitudes bloqueadas → encuentra el bloqueador y la aplicación a la que pertenece.
  • Una aplicación tiene sesiones inactivas con transacciones abiertas → inspecciona el manejo de transacciones.
  • Muchas sesiones, pero poco trabajo activo → valida el pool de conexiones antes de cambiar los límites.
  • Aparece un host o una aplicación inesperados → confirma a quién pertenece y si el acceso es legítimo.
  • Necesitas patrones de tráfico por hora o por día → guarda instantáneas de las DMV o usa monitoreo o Extended Events.
  • Nada anormal según la identidad del cliente → continúa con Query Store, las esperas y el diagnóstico a nivel de sistema.

← Todas las guías rápidas