Explorar guías rápidas · SQL Server

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

Flujo de diagnóstico de rendimiento de SQL Server

Usa un flujo de diagnóstico de SQL Server repetible para distinguir bloqueos, CPU, E/S, esperas, regresiones de consultas y presión de carga antes de cambiar nada.

En esta página
  1. Problema
  2. Respuesta rápida
  3. Código / comandos
  4. 1. Confirmar qué se está ejecutando ahora mismo
  5. 2. Revisar los bloqueos de inmediato
  6. 3. Revisar las esperas actuales
  7. 4. Revisar las principales esperas acumuladas
  8. 5. Revisar las solicitudes activas con mucho consumo de CPU
  9. 6. Revisar las solicitudes activas con muchas lecturas
  10. 7. Revisar Query Store para detectar regresiones históricas
  11. 8. Revisar las transacciones abiertas cuando hay bloqueos o crecimiento del log
  12. 9. Revisar la hora de inicio de SQL Server antes de interpretar DMV acumulativas
  13. 10. Revisar el uso de índices solo después de identificar la carga
  14. 11. Revisar con cuidado las recomendaciones de índices faltantes
  15. Qué significa el resultado
  16. Qué revisar
  17. Los bloqueos explican la lentitud
  18. El CPU está alto y las solicitudes activas consumen CPU
  19. El tiempo transcurrido es alto pero el CPU es bajo
  20. Query Store muestra un cambio de plan en el mismo momento en que se degradó el rendimiento
  21. El servidor parece sano pero una aplicación va lenta
  22. Ahora no se ve nada raro
  23. Notas de producción
  24. Diagnostica antes de cambiar la configuración
  25. Las DMV en vivo son transitorias
  26. Query Store suele ser el puente entre el diagnóstico en vivo y el histórico
  27. La infraestructura sigue importando
  28. Compara antes y después de un solo cambio
  29. Los permisos varían según la versión de SQL Server
  30. Errores comunes
  31. 1. Empezar por los índices
  32. 2. Terminar sesiones demasiado pronto
  33. 3. Mirar una sola métrica
  34. 4. Usar las esperas acumuladas como evidencia actual
  35. 5. Tomar una recomendación de índice faltante como la respuesta
  36. 6. Ignorar las capas de aplicación e infraestructura
  37. 7. Hacer varios cambios a la vez
  38. Siguiente paso
  39. Para profundizar

Problema

SQL Server va lento, los usuarios se quejan, los dashboards agotan el tiempo de espera o una aplicación de repente tarda más de lo normal.

Lo más difícil no es ejecutar una consulta sobre una DMV.

Lo más difícil es decidir qué revisar primero y evitar la optimización al azar.

Un flujo de diagnóstico útil debe responder rápidamente:

  • ¿El problema está ocurriendo ahora mismo?
  • ¿Se trata de bloqueos?
  • ¿Se trata de presión de CPU?
  • ¿La carga está esperando al almacenamiento, a locks, a la memoria, a workers o a otra cosa?
  • ¿Una sola consulta es la responsable, o toda la carga está bajo presión?
  • ¿Cambió algún plan?
  • ¿Aumentó el tráfico o la concurrencia?
  • ¿El problema es puntual o histórico?

Respuesta rápida

Sigue esta secuencia:

1. Confirma el síntoma y la ventana de tiempo
2. Revisa las solicitudes activas
3. Revisa los bloqueos
4. Revisa las esperas actuales
5. Revisa el trabajo intensivo en CPU / en lecturas
6. Revisa Query Store en busca de regresiones
7. Revisa las transacciones abiertas si hay bloqueos / crecimiento del log
8. Revisa los índices solo después de tener evidencia de la carga de trabajo
9. Revisa la infraestructura si la evidencia de SQL apunta fuera del motor
10. Vuelve a probar tras un único cambio controlado

La primera consulta SQL normalmente debe ser una instantánea de las solicitudes activas:

SELECT
    r.session_id,
    r.status,
    r.command,
    DB_NAME(r.database_id) AS database_name,
    CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
    r.cpu_time AS cpu_ms,
    r.logical_reads,
    r.reads,
    r.writes,
    r.wait_type,
    r.wait_time AS wait_ms,
    r.blocking_session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    txt.text AS batch_text
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    r.session_id <> @@SPID
    AND s.is_user_process = 1
ORDER BY
    r.total_elapsed_time DESC;

A partir de ahí, sigue el síntoma en lugar de saltar directamente a los índices o a cambios de configuración.

Código / comandos

1. Confirmar qué se está ejecutando ahora mismo

SELECT
    r.session_id,
    r.status,
    r.command,
    DB_NAME(r.database_id) AS database_name,
    r.start_time,
    CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
    r.cpu_time AS cpu_ms,
    r.logical_reads,
    r.reads,
    r.writes,
    r.wait_type,
    r.wait_time AS wait_ms,
    r.wait_resource,
    r.blocking_session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    txt.text AS batch_text
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    r.session_id <> @@SPID
    AND s.is_user_process = 1
ORDER BY
    r.total_elapsed_time DESC;

Usa esta consulta cuando el problema está activo.

Si no aparece nada anormal, puede que el incidente ya haya pasado. Pasa a Query Store o al monitoreo.


2. Revisar los bloqueos de inmediato

SELECT
    r.session_id AS blocked_session_id,
    r.blocking_session_id,
    DB_NAME(r.database_id) AS database_name,
    r.status,
    r.wait_type,
    r.wait_time AS wait_ms,
    r.wait_resource,
    txt.text AS blocked_batch_text
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    r.blocking_session_id > 0
ORDER BY
    r.wait_time DESC;

Si devuelve muchas filas, detén la optimización general y encuentra primero el bloqueador principal.

Los bloqueos pueden hacer que una consulta sana parezca lenta.


3. Revisar las esperas actuales

SELECT
    wt.session_id,
    wt.wait_duration_ms,
    wt.wait_type,
    wt.blocking_session_id,
    wt.resource_description
FROM sys.dm_os_waiting_tasks AS wt
ORDER BY
    wt.wait_duration_ms DESC;

Esto ayuda a responder qué están esperando las sesiones ahora mismo.

Usa la categoría de espera para decidir la siguiente rama.


4. Revisar las principales esperas acumuladas

SELECT TOP (30)
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE
    wait_time_ms > 0
ORDER BY
    wait_time_ms DESC;

Usa las esperas acumuladas para obtener patrones y contexto, no como prueba del incidente actual.

Ten siempre claro el intervalo de observación.


5. Revisar las solicitudes activas con mucho consumo de CPU

SELECT TOP (20)
    r.session_id,
    DB_NAME(r.database_id) AS database_name,
    r.cpu_time AS cpu_ms,
    CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
    r.logical_reads,
    r.wait_type,
    r.blocking_session_id,
    txt.text AS batch_text
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    r.session_id <> @@SPID
ORDER BY
    r.cpu_time DESC;

Mucho CPU con poca espera suele apuntar a trabajo activo costoso.

Mucho tiempo transcurrido con poco CPU suele apuntar a esperas.


6. Revisar las solicitudes activas con muchas lecturas

SELECT TOP (20)
    r.session_id,
    DB_NAME(r.database_id) AS database_name,
    r.logical_reads,
    r.reads,
    r.cpu_time AS cpu_ms,
    CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
    r.wait_type,
    txt.text AS batch_text
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    r.session_id <> @@SPID
ORDER BY
    r.logical_reads DESC;

Un volumen alto de lecturas puede apuntar a:

  • scans
  • baja selectividad
  • rutas de acceso deficientes
  • consultas analíticas grandes
  • comportamiento sensible a parámetros

No saltes directamente a «agregar un índice».


7. Revisar Query Store para detectar regresiones históricas

Si el problema es intermitente o ya pasó:

SELECT TOP (20)
    q.query_id,
    qt.query_sql_text,
    p.plan_id,
    SUM(rs.count_executions) AS executions,
    CAST(
        SUM(rs.avg_duration * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0)
        / 1000.0
        AS decimal(18,2)
    ) AS weighted_avg_duration_ms,
    CAST(
        SUM(rs.avg_cpu_time * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0)
        / 1000.0
        AS decimal(18,2)
    ) AS weighted_avg_cpu_ms
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
GROUP BY
    q.query_id,
    qt.query_sql_text,
    p.plan_id
ORDER BY
    weighted_avg_duration_ms DESC;

Usa esta consulta para encontrar cambios de plan y regresiones de carga que las DMV en vivo ya no pueden mostrar.


8. Revisar las transacciones abiertas cuando hay bloqueos o crecimiento del log

SELECT
    s.session_id,
    s.status AS session_status,
    s.login_name,
    s.host_name,
    s.program_name,
    s.open_transaction_count,
    at.transaction_begin_time,
    DATEDIFF(second, at.transaction_begin_time, SYSDATETIME()) AS transaction_age_seconds
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_tran_session_transactions AS st
    ON st.session_id = s.session_id
INNER JOIN sys.dm_tran_active_transactions AS at
    ON at.transaction_id = st.transaction_id
WHERE
    s.is_user_process = 1
ORDER BY
    at.transaction_begin_time;

Las sesiones inactivas (sleeping) con transacciones antiguas merecen especial atención.


9. Revisar la hora de inicio de SQL Server antes de interpretar DMV acumulativas

SELECT
    sqlserver_start_time
FROM sys.dm_os_sys_info;

Esto importa para:

  • estadísticas de esperas
  • uso de índices
  • estadísticas de procedimientos
  • otra evidencia basada en caché o DMV

Un reinicio reciente puede dejar sin sentido conclusiones como «sin uso» o «poca actividad».


10. Revisar el uso de índices solo después de identificar la carga

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.name AS index_name,
    COALESCE(us.user_seeks, 0) AS user_seeks,
    COALESCE(us.user_scans, 0) AS user_scans,
    COALESCE(us.user_lookups, 0) AS user_lookups,
    COALESCE(us.user_updates, 0) AS user_updates
FROM sys.indexes AS i
INNER JOIN sys.tables AS t
    ON t.object_id = i.object_id
INNER JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
LEFT JOIN sys.dm_db_index_usage_stats AS us
    ON us.database_id = DB_ID()
   AND us.object_id = i.object_id
   AND us.index_id = i.index_id
WHERE
    t.is_ms_shipped = 0
    AND i.index_id > 0
ORDER BY
    user_seeks + user_scans + user_lookups DESC;

Usa esta consulta solo después de identificar una consulta o tabla concreta.

Optimizar índices sin evidencia de la carga es adivinar.


11. Revisar con cuidado las recomendaciones de índices faltantes

SELECT TOP (20)
    OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id) AS schema_name,
    OBJECT_NAME(mid.object_id, mid.database_id) AS table_name,
    migs.user_seeks,
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
    ON mig.index_group_handle = migs.group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON mid.index_handle = mig.index_handle
WHERE
    mid.database_id = DB_ID()
ORDER BY
    migs.user_seeks
      * migs.avg_total_user_cost
      * (migs.avg_user_impact / 100.0) DESC;

Trata este resultado como una lista de candidatos.

No crees índices automáticamente a partir de la DMV.

Qué significa el resultado

El flujo de diagnóstico es más importante que cualquier métrica aislada.

Señal Dirección probable
blocking_session_id > 0 Investigación de locks y bloqueos
LCK_M_* Esperas de lock
cpu_time alto Consulta o carga con mucho consumo de CPU
Lecturas lógicas altas Consulta o ruta de acceso con muchas lecturas
Esperas de señal (signal waits) altas Presión de planificación de CPU
PAGEIOLATCH_* Ruta de esperas de E/S en páginas de datos
PAGELATCH_* Contención de latches en memoria
WRITELOG Presión en el vaciado del log
RESOURCE_SEMAPHORE Presión de concesiones de memoria (memory grants)
THREADPOOL Agotamiento de workers o concurrencia
Cambio de plan en Query Store Posible regresión
Sesión inactiva + transacción abierta Problema de gestión de transacciones
Sin evidencia en vivo Ruta histórica / de monitoreo

Qué revisar

Los bloqueos explican la lentitud

Si muchas sesiones esperan detrás de un mismo bloqueador, prioriza el bloqueador principal.

No pierdas tiempo optimizando primero a las víctimas.

El CPU está alto y las solicitudes activas consumen CPU

Investiga:

  • planes de ejecución
  • carga de alta frecuencia
  • scans y joins
  • comportamiento de los parámetros
  • concurrencia

El tiempo transcurrido es alto pero el CPU es bajo

Probablemente la consulta está esperando.

Revisa:

  • esperas actuales
  • bloqueos
  • E/S
  • esperas de memoria
  • consumo externo de los resultados

Query Store muestra un cambio de plan en el mismo momento en que se degradó el rendimiento

Es una evidencia sólida de regresión.

Valida:

  • número de ejecuciones
  • sensibilidad a parámetros
  • cambios de esquema, índices o estadísticas
  • comparabilidad de la carga

El servidor parece sano pero una aplicación va lenta

Usa:

  • login
  • host
  • nombre del programa
  • base de datos
  • correlación entre sesiones y solicitudes

para aislar la carga de la aplicación.

Ahora no se ve nada raro

No concluyas que el problema no fue real.

Usa:

  • Query Store
  • monitoreo persistente
  • Extended Events
  • telemetría de la aplicación
  • métricas de infraestructura

para obtener evidencia histórica.

Notas de producción

Diagnostica antes de cambiar la configuración

No hagas cambios a nivel de servidor porque una métrica parezca sospechosa.

Evita cambiar:

  • MAXDOP
  • cost threshold
  • configuración de memoria
  • worker threads
  • distribución de tempdb
  • índices

hasta que la evidencia apunte ahí.

Las DMV en vivo son transitorias

Los datos de solicitudes activas y tareas en espera desaparecen cuando termina la carga.

Si el problema es intermitente, captura evidencia o usa telemetría persistente.

Query Store suele ser el puente entre el diagnóstico en vivo y el histórico

Usa las DMV en vivo para «qué está pasando ahora».

Usa Query Store para «qué cambió con el tiempo».

La infraestructura sigue importando

Los síntomas de SQL Server pueden originarse fuera del motor.

Ejemplos:

  • latencia del almacenamiento
  • presión de CPU en la VM
  • limitaciones de memoria
  • retrasos de red
  • lentitud del lado del cliente
  • saturación de IIS o del servidor de aplicaciones

No te detengas en la base de datos si la evidencia de SQL apunta hacia fuera.

Compara antes y después de un solo cambio

Evita cambiar varias cosas a la vez.

Un ciclo de diagnóstico controlado es:

Observar
→ Formular una hipótesis
→ Hacer un solo cambio
→ Medir
→ Mantener o revertir

Los permisos varían según la versión de SQL Server

Para un acceso amplio a las DMV de rendimiento:

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

Las plataformas de Azure SQL tienen reglas de alcance y permisos diferentes.

Errores comunes

1. Empezar por los índices

Los índices son importantes, pero no toda lentitud es un problema de indexación.

Los bloqueos, la presión de CPU, la E/S, las concesiones de memoria y las regresiones de plan pueden parecer «SQL lento».

2. Terminar sesiones demasiado pronto

Terminar un bloqueador puede provocar un rollback e impactar a la aplicación.

Entiende:

  • la antigüedad de la transacción
  • la operación de negocio
  • el costo del rollback

antes de actuar.

3. Mirar una sola métrica

El CPU sin las esperas, las esperas sin las consultas o las lecturas sin los planes pueden llevar a conclusiones erróneas.

Usa varias señales.

4. Usar las esperas acumuladas como evidencia actual

Las estadísticas de esperas de la instancia pueden reflejar días o semanas de carga.

Usa las tareas en espera actuales y deltas por intervalo de tiempo para analizar un incidente.

5. Tomar una recomendación de índice faltante como la respuesta

Las DMV de índices faltantes son orientativas.

Valídala frente a:

  • índices existentes
  • plan de consulta
  • costo de escritura
  • importancia de la carga

6. Ignorar las capas de aplicación e infraestructura

Una consulta SQL puede parecer lenta porque:

  • el cliente lee las filas despacio
  • la API está sobrecargada
  • IIS o el servidor de aplicaciones está saturado
  • el almacenamiento tiene latencia
  • la VM se queda sin CPU

El contexto de extremo a extremo importa.

7. Hacer varios cambios a la vez

Si el rendimiento mejora, no sabrás qué lo arregló.

Si el rendimiento empeora, revertir se vuelve más difícil.

Siguiente paso

Usa este flujo:

  • Hay bloqueos → encuentra el bloqueador principal e inspecciona las transacciones abiertas.
  • CPU alto → identifica las consultas con mucho CPU, activas o en el histórico, e inspecciona sus planes.
  • Lecturas altas → inspecciona predicados, scans, joins e índices.
  • Predominan las esperas → sigue la categoría de espera.
  • El plan cambió → compara los planes y considera forzar temporalmente un plan si está justificado.
  • Sin evidencia en vivo → pasa a Query Store y al monitoreo persistente.
  • SQL parece sano pero la aplicación va lenta → inspecciona el servidor de aplicaciones, la red y el comportamiento del cliente.
  • Un cambio lo soluciona → valídalo con la carga normal antes de dar el problema por resuelto.

← Todas las guías rápidas