SQL Server lento: cómo encontrar la causa raíz paso a paso

Cuando una aplicación empieza a responder lentamente, uno de los errores más frecuentes es comenzar a cambiar parámetros de SQL Server sin haber demostrado todavía cuál es el problema: subir memoria, cambiar MAXDOP, crear los índices sugeridos por las DMVs, hacer rebuild de todos los índices, aumentar recursos de la máquina o culpar al storage. Cualquiera de esas acciones podría mejorar un caso concreto. También podría no resolver nada o incluso introducir un problema nuevo.

La pregunta correcta no es «¿qué configuración puedo cambiar para que SQL Server vaya más rápido?». La pregunta es:

Microsoft plantea una separación especialmente útil para troubleshooting: una consulta lenta puede estar consumiendo activamente CPU o puede estar pasando una parte importante de su tiempo esperando un recurso. Una investigación de performance seria sigue aproximadamente esta cadena:

El objetivo de esta guía es hacer precisamente eso: recorrer cada eslabón con las consultas T-SQL necesarias.

Identifica primero qué está lento ahora mismo

Antes de mirar configuración global, identifica las solicitudes que están activas en este momento.

SELECT
    r.session_id,
    DB_NAME(r.database_id) AS database_name,
    r.status,
    r.command,
    r.cpu_time AS cpu_time_ms,
    r.total_elapsed_time AS elapsed_time_ms,
    r.logical_reads,
    r.reads AS physical_reads,
    r.writes,
    r.wait_type,
    r.wait_time AS wait_time_ms,
    r.wait_resource,
    r.blocking_session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    SUBSTRING
    (
        t.text,
        (r.statement_start_offset / 2) + 1,
        (
            (
                CASE r.statement_end_offset
                    WHEN -1 THEN DATALENGTH(t.text)
                    ELSE r.statement_end_offset
                END
                - r.statement_start_offset
            ) / 2
        ) + 1
    ) AS statement_text
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
    ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id <> @@SPID
  AND s.is_user_process = 1
ORDER BY r.total_elapsed_time DESC;

No mires solamente elapsed_time_ms. Correlaciona varias columnas a la vez:

Una query que tarda 20 segundos no necesariamente tiene el mismo problema que otra que tarda 20 segundos: la primera podría consumir CPU durante casi todo ese tiempo, mientras la segunda podría consumir 500 ms de CPU y esperar 19,5 segundos por locks, I/O, memoria u otro recurso. Ese diagnóstico cambia completamente la solución.

¿SQL Server está ejecutando o esperando? CPU vs waiting

Un cálculo sencillo ayuda mucho: elapsed time − CPU time ≈ waiting time. No debe tratarse como una fórmula perfecta para todos los casos —especialmente con consultas paralelas—, pero es una excelente señal inicial. Por ejemplo:

Elapsed Time     12,000 ms
CPU Time          1,400 ms
                 ----------
Approx. Waiting  10,600 ms

Aquí no conviene empezar optimizando CPU. La pregunta correcta es:

Wait Statistics: una señal, no la causa raíz

Consulta inicial para ver las esperas dominantes del servidor:

SELECT TOP (20)
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms,
    CAST(
        wait_time_ms * 100.0 /
        NULLIF(SUM(wait_time_ms) OVER (), 0)
        AS DECIMAL(10,2)
    ) AS wait_pct
FROM sys.dm_os_wait_stats
WHERE wait_time_ms > 0
ORDER BY wait_time_ms DESC;

Aquí puedes encontrar waits como PAGEIOLATCH_*, WRITELOG, LCK_M_*, RESOURCE_SEMAPHORE, CXPACKET, CXCONSUMER, SOS_SCHEDULER_YIELD, THREADPOOL o ASYNC_NETWORK_IO. Pero hay una regla importante:

Por ejemplo, PAGEIOLATCH_SH alto no demuestra automáticamente que el storage sea defectuoso. También puede existir esta cadena:

Poor execution plan
       ↓
Table / Index Scan
       ↓
Logical reads ↑
       ↓
Physical reads ↑
       ↓
Storage workload ↑
       ↓
PAGEIOLATCH ↑

Una query puede saturar el subsistema de I/O debido a una cantidad excesiva de lecturas incluso cuando el storage no sea el origen inicial del problema.

Wait deltas vs waits acumulados desde el arranque

sys.dm_os_wait_stats acumula información. Si SQL Server lleva semanas encendido, estás viendo la historia acumulada de semanas, no necesariamente lo que ocurrió durante el incidente actual. Una técnica mucho más útil es tomar dos muestras y calcular el delta:

IF OBJECT_ID('tempdb..#waits_before') IS NOT NULL
    DROP TABLE #waits_before;

SELECT
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms
INTO #waits_before
FROM sys.dm_os_wait_stats;

WAITFOR DELAY '00:00:10';

SELECT TOP (20)
    a.wait_type,
    a.waiting_tasks_count - b.waiting_tasks_count AS waiting_tasks_delta,
    a.wait_time_ms - b.wait_time_ms AS wait_time_delta_ms,
    a.signal_wait_time_ms - b.signal_wait_time_ms AS signal_wait_delta_ms
FROM sys.dm_os_wait_stats AS a
INNER JOIN #waits_before AS b
    ON a.wait_type = b.wait_type
WHERE a.wait_time_ms > b.wait_time_ms
ORDER BY wait_time_delta_ms DESC;

En producción puedes usar una ventana mayor. La idea es analizar el wait delta y no solamente el wait total desde el arranque — esto mejora significativamente la relevancia del diagnóstico.

Blocking: investiga antes de matar sesiones

Una query excelente puede tardar minutos si otra sesión mantiene el recurso bloqueado.

SELECT
    r.session_id,
    r.blocking_session_id,
    DB_NAME(r.database_id) AS database_name,
    r.status,
    r.wait_type,
    r.wait_time AS wait_time_ms,
    r.wait_resource,
    r.open_transaction_count,
    s.login_name,
    s.host_name,
    s.program_name,
    t.text AS sql_text
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
    ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

Una sesión bloqueadora puede ser el síntoma visible de una transacción excesivamente larga o de código de aplicación que no hace COMMIT oportunamente.

Las queries que realmente están consumiendo recursos

Para información disponible en plan cache:

SELECT TOP (20)
    qs.execution_count,
    CAST(qs.total_worker_time / 1000.0 AS DECIMAL(18,2))
        AS total_cpu_ms,
    CAST(
        (qs.total_worker_time / NULLIF(qs.execution_count,0)) / 1000.0
        AS DECIMAL(18,2)
    ) AS avg_cpu_ms,
    CAST(qs.total_elapsed_time / 1000.0 AS DECIMAL(18,2))
        AS total_elapsed_ms,
    CAST(
        (qs.total_elapsed_time / NULLIF(qs.execution_count,0)) / 1000.0
        AS DECIMAL(18,2)
    ) AS avg_elapsed_ms,
    qs.total_logical_reads,
    qs.total_logical_reads / NULLIF(qs.execution_count,0)
        AS avg_logical_reads,
    qs.total_physical_reads,
    DB_NAME(st.dbid) AS database_name,
    SUBSTRING
    (
        st.text,
        (qs.statement_start_offset / 2) + 1,
        (
            (
                CASE qs.statement_end_offset
                    WHEN -1 THEN DATALENGTH(st.text)
                    ELSE qs.statement_end_offset
                END
                - qs.statement_start_offset
            ) / 2
        ) + 1
    ) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_logical_reads DESC;

Puedes cambiar el ORDER BY por total_worker_time, total_elapsed_time o total_physical_reads, dependiendo de qué estés investigando. Pero recuerda: plan cache no es historia permanente. Puede desaparecer por restart, recompilaciones, memory pressure y otros eventos. Aquí entra Query Store.

Query Store: compara el presente contra el pasado

Primero valida su estado en la base afectada:

SELECT
    actual_state_desc,
    desired_state_desc,
    current_storage_size_mb,
    max_storage_size_mb,
    query_capture_mode_desc
FROM sys.database_query_store_options;

Query Store conserva información especialmente útil para troubleshooting: query text, execution plans, runtime statistics y wait statistics por query. Esto permite responder preguntas que una DMV puntual no puede contestar bien:

• ¿Esta query siempre tardó 4 segundos, o antes tardaba 180 ms?
• ¿Cambió el plan, y cuándo cambió?
• ¿Aumentó CPU o aumentaron los logical reads?
• ¿Qué waits aparecieron?
• ¿Existe un plan anterior mejor?

Query Store es especialmente útil para analizar Top Resource Consuming Queries, Regressed Queries, Queries With High Variation y Query Wait Statistics. Ese contexto histórico es fundamental.

Un plan diferente no siempre es una regresión

Un DBA debe correlacionar plan changed + duration changed + CPU changed + reads changed + wait profile changed antes de concluir «plan regression». El objetivo no es reaccionar ante cada cambio de plan, sino detectar cuando el nuevo comportamiento es objetivamente peor. Ejemplo con evidencia fuerte de degradación:

Un cambio de plan_id sin cambios negativos de performance no demuestra un problema por sí solo.

Estadísticas y cardinalidad: cuando el optimizador se equivoca

El optimizador necesita estadísticas para estimar cardinalidad y seleccionar estrategias de acceso. Puedes revisar las estadísticas con mayor cantidad de modificaciones:

SELECT
    OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
    OBJECT_NAME(s.object_id) AS table_name,
    s.name AS statistics_name,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter,
    CAST(
        sp.modification_counter * 100.0 /
        NULLIF(sp.rows, 0)
        AS DECIMAL(10,2)
    ) AS modification_pct
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties
(
    s.object_id,
    s.stats_id
) AS sp
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
ORDER BY sp.modification_counter DESC;

No utilices un porcentaje fijo como verdad universal. Correlaciona modification counter, last updated, table size, estimated rows vs actual rows, plan y query regression. Esto es mucho más útil que ejecutar UPDATE STATISTICS indiscriminadamente y asumir que el problema quedó resuelto.

I/O por archivo: no culpes al storage sin evidencia

Puedes usar esta consulta para medir latencia por archivo de base de datos:

SELECT
    DB_NAME(vfs.database_id) AS database_name,
    mf.name AS logical_file_name,
    mf.type_desc,
    mf.physical_name,

    vfs.num_of_reads,
    vfs.num_of_bytes_read,

    CAST(
        vfs.io_stall_read_ms * 1.0 /
        NULLIF(vfs.num_of_reads, 0)
        AS DECIMAL(18,2)
    ) AS avg_read_latency_ms,

    vfs.num_of_writes,
    vfs.num_of_bytes_written,

    CAST(
        vfs.io_stall_write_ms * 1.0 /
        NULLIF(vfs.num_of_writes, 0)
        AS DECIMAL(18,2)
    ) AS avg_write_latency_ms

FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
INNER JOIN sys.master_files AS mf
    ON vfs.database_id = mf.database_id
   AND vfs.file_id = mf.file_id
ORDER BY avg_read_latency_ms DESC;

No existe un único número mágico que permita concluir «X ms = storage malo» sin conocer el workload. Compara latencia, volumen de reads/writes, baseline histórico, queries afectadas y waits. La latencia puede deberse a infraestructura, pero también puede aparecer porque SQL Server está enviando un volumen excesivo de I/O debido a queries ineficientes.

Missing Index no significa «CREATE INDEX inmediatamente»

Las DMVs de missing indexes son útiles, pero son una señal del optimizador, no una orden administrativa. Antes de crear un índice analiza:

• ¿Ya existe uno parecido, o se puede ampliar un índice existente?
• ¿Cuánto espacio ocupará?
• ¿Cuántos INSERT/UPDATE/DELETE recibe la tabla?
• ¿Cuánto write overhead generará?
• ¿Cuántos seeks reales justificarían mantenerlo?
• ¿La query problemática tiene una causa diferente?

Una base llena de índices «recomendados» puede terminar con excelentes SELECT aislados y un DML significativamente más caro. La decisión debe ser contextual.

MAXDOP y Cost Threshold for Parallelism

SELECT
    name,
    value,
    value_in_use
FROM sys.configurations
WHERE name IN
(
    'max degree of parallelism',
    'cost threshold for parallelism'
);

Encontrar muchos waits CXPACKET/CXCONSUMER no implica automáticamente «baja MAXDOP». Correlaciona parallel plans, workers, scheduler pressure, runnable tasks, CPU, Cost Threshold, query cost, tipo de workload y topología NUMA/CPU. El mismo MAXDOP puede funcionar correctamente en una instancia y ser inadecuado en otra — la configuración de paralelismo debe evaluarse contra el workload real.

La causa raíz suele ser una cadena, no una métrica

Esta es probablemente la idea más importante de todo el proceso. Supongamos que encuentras PAGEIOLATCH alto. Podrías concluir «storage». Pero después encuentras en Query Store que el plan cambió hace 20 minutos, los logical reads aumentaron 40 veces y las estadísticas fueron modificadas de forma intensiva. La hipótesis cambia:

PAGEIOLATCH era real. Pero era síntoma, no necesariamente causa raíz. Ese es el motivo por el que troubleshooting serio requiere correlación.

Before / After: no cambies cinco cosas a la vez

Cuando creas tener una causa probable: captura métricas before, realiza una remediación acotada, espera una ventana representativa, vuelve a capturar y compara.

Con esta comparación tienes evidencia. En cambio, aplicar UPDATE STATISTICS, ALTER INDEX, MAXDOP, Cost Threshold y memoria todo al mismo tiempo destruye tu capacidad de saber qué cambio solucionó qué problema.

Validar la remediación: sin error no es lo mismo que exitosa

Esto merece especial énfasis: que SQL Server responda Command completed successfully solo significa que aceptó el comando, no que el problema de performance quedó solucionado. La validación correcta sigue esta secuencia:

DIAGNOSIS → ACTION → BEFORE → REMEDIATION → AFTER → MEASURABLE OUTCOME

El resultado incluso puede ser SUCCESS, PARTIAL SUCCESS, NO MEASURABLE CHANGE, REGRESSION o INCONCLUSIVE. Aceptar un resultado inconcluso es mejor que atribuir falsamente una mejora.

Checklist final cuando SQL Server está lento

Si necesitas investigar un incidente en producción, utiliza este orden:

• 1. Identifica sesiones y queries afectadas
• 2. Determina CPU vs waiting
• 3. Identifica waits actuales (delta, no solo acumulado)
• 4. Busca blocking y transacciones largas
• 5. Obtén las queries que consumen más CPU/reads/duration
• 6. Compara histórico mediante Query Store
• 7. Revisa cambios de plan
• 8. Comprueba estadísticas y cardinalidad
• 9. Analiza índices y access paths
• 10. Mide I/O por archivo
• 11. Revisa TempDB, memoria y transaction log si la evidencia apunta allí
• 12. Evalúa parallelism solo con contexto
• 13. Construye una hipótesis de causa raíz
• 14. Aplica una acción acotada
• 15. Valida before vs after

El orden no es rígido. Lo importante es evitar el atajo métrica → conclusión automática → cambio y reemplazarlo por evidencia → correlación → hipótesis → validación.

¿Y si tienes que hacer este análisis todos los días?

Los scripts anteriores sirven para investigar señales individuales. El problema aparece cuando una instancia tiene simultáneamente decenas de waits, cientos de queries relevantes, varios execution plans, cientos de índices, estadísticas, blocking, I/O, TempDB, parallelism, transaction log y configuración a la vez. La dificultad ya no es obtener métricas: es correlacionarlas. ConsultorDBA AI Health Agent está diseñado precisamente para esa capa.

Las remediaciones permanecen bajo aprobación humana y el modelo generativo no recibe control irrestricto para ejecutar SQL arbitrario.