Diagnóstico SQL Server: PLE de 22 segundos y discos al 9% de espacio libre

Este artículo documenta un incidente real en un entorno de producción. Los nombres de servidores, bases de datos, tablas y empresa han sido modificados por confidencialidad. Los datos de rendimiento son reales.

¿Qué es una revisión proactiva de salud y por qué importa?

La mayor parte de las incidencias graves en SQL Server tienen señales de advertencia horas, días o semanas antes de convertirse en un problema de producción. Un servidor con PLE bajo, discos casi llenos o latencias elevadas no falla de repente — se deteriora progresivamente hasta que algo rompe.

La revisión proactiva de salud es el conjunto de checks que un DBA ejecuta periódicamente para detectar esas señales antes de que llegue la llamada a las 3 de la mañana.

Este artículo documenta una revisión real realizada en un servidor SQL Server 2019 Enterprise sobre AlwaysOn (Always On Availability Groups) en un entorno de producción de una empresa con bases de datos de hasta 1,3 TB. Los números son reales. Los nombres, no.

---

El entorno

• SQL Server 2019 Enterprise con AlwaysOn Availability Groups
• 2 nodos con failover automático (mantenimiento escalonado los fines de semana)
• 11 bases de datos, la mayor de ~1,3 TB
• RAM asignada a SQL Server: 54,3 GB
• Discos: 6 volúmenes separados (datos primarios, datos secundarios, logs, TempDB, SO)
• La revisión se realizó en el nodo primario del Availability Group

---

Fase 1: Checks rápidos (menos de 5 minutos)

1.1 Bloqueos activos

SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time / 1000 AS wait_sec,
    t.text AS sql_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id > 0;

Resultado: Sin bloqueos activos. ✅

1.2 CPU reciente

SELECT
    record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_pct,
    record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS idle_pct,
    DATEADD(ms, -1 * (ts_now - [timestamp]), GETDATE()) AS event_time
FROM (
    SELECT TOP 5
        [timestamp],
        CONVERT(XML, record) AS record,
        cpu_ticks / (cpu_ticks / ms_ticks) AS ts_now
    FROM sys.dm_os_ring_buffers rb
    CROSS JOIN sys.dm_os_sys_info
    WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
    ORDER BY [timestamp] DESC
) AS ring_data
ORDER BY event_time DESC;

Resultado: SQL Server consumiendo 26-30% de CPU en los últimos 3 minutos, con un total de sistema de 34-42%. Nivel de atención — no crítico por sí solo, pero hay que correlacionarlo con los demás checks.

1.3 Estado de AlwaysOn

SELECT
    ag.name AS ag_name,
    ar.replica_server_name,
    ars.role_desc,
    ars.operational_state_desc,
    ars.connected_state_desc,
    ars.synchronization_health_desc,
    drs.synchronization_state_desc,
    drs.database_state_desc,
    DB_NAME(drs.database_id) AS db_name
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_replicas ar ON ars.replica_id = ar.replica_id
JOIN sys.availability_groups ag ON ar.group_id = ag.group_id
LEFT JOIN sys.dm_hadr_database_replica_states drs ON ars.replica_id = drs.replica_id
WHERE ars.is_local = 1;

Resultado: Nodo primario. 10 bases de datos en el AG, todas SYNCHRONIZED + HEALTHY. ✅

1.4 Espacio en disco

SELECT DISTINCT
    vs.volume_mount_point,
    vs.total_bytes / 1073741824.0 AS total_gb,
    vs.available_bytes / 1073741824.0 AS free_gb,
    CAST(vs.available_bytes * 100.0 / vs.total_bytes AS DECIMAL(5,1)) AS free_pct
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs
ORDER BY free_pct ASC;

Resultado:

Tres volúmenes en zona de atención. DATOS_SECUNDARIOS con solo 9,3% de espacio libre sobre 4,2 TB es la señal más preocupante — en términos absolutos son 396 GB, pero en un servidor con bases de datos que crecen activamente, eso puede desaparecer en semanas.

---

Fase 2: Investigación profunda

2.1 Latencias de I/O por fichero

SELECT
    DB_NAME(mf.database_id) AS db_name,
    mf.physical_name,
    mf.type_desc,
    vfs.num_of_reads,
    vfs.num_of_writes,
    CASE WHEN vfs.num_of_reads > 0
         THEN CAST(vfs.io_stall_read_ms * 1.0 / vfs.num_of_reads AS DECIMAL(10,2))
         ELSE 0 END AS avg_read_ms,
    CASE WHEN vfs.num_of_writes > 0
         THEN CAST(vfs.io_stall_write_ms * 1.0 / vfs.num_of_writes AS DECIMAL(10,2))
         ELSE 0 END AS avg_write_ms,
    vfs.io_stall_read_ms,
    vfs.io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files mf ON vfs.database_id = mf.database_id
                        AND vfs.file_id = mf.file_id
ORDER BY avg_read_ms DESC;

Hallazgo crítico — latencias en el volumen DATOS_SECUNDARIOS:

En comparación, los ficheros en el volumen DATOS_EXTRA:

Los mismos ficheros de las mismas bases de datos, pero en disco diferente. El volumen DATOS_SECUNDARIOS tiene latencias 5x más altas que DATOS_EXTRA. Esto apunta a saturación del disco físico o un problema en el array de almacenamiento.

Nota metodológica: dm_io_virtual_file_stats acumula desde el último reinicio del servicio SQL. Estas latencias reflejan el comportamiento histórico, no solo el momento actual. Si el servicio lleva semanas arrancado, los picos puntuales están promediados.

2.2 Page Life Expectancy (PLE) — El hallazgo más crítico

SELECT
    object_name AS counter_name,
    counter_name,
    instance_name,
    cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN (
    'Page life expectancy',
    'Memory Grants Pending',
    'Target Server Memory (KB)',
    'Total Server Memory (KB)'
)
ORDER BY counter_name, instance_name;

Resultado:

CRÍTICO: PLE de 22 segundos.

El Page Life Expectancy mide cuánto tiempo vive una página de datos en el buffer pool de SQL Server antes de ser expulsada para hacer sitio a otra. Valores de referencia:

Con un PLE de 22 segundos, cada página leída por SQL Server es expulsada 22 segundos después. Esto significa que prácticamente no hay caching efectivo. Cada query que necesite datos que se leyeron hace más de 22 segundos tiene que ir a disco.

Esto explica directamente las latencias de 35ms en DATOS_SECUNDARIOS: no es solo que el disco sea lento, es que SQL Server está generando muchísimas más lecturas físicas de las que debería.

2.3 Distribución del Buffer Pool por base de datos

SELECT
    DB_NAME(database_id) AS db_name,
    COUNT(*) * 8 / 1024 AS buffer_mb,
    COUNT(*) AS pages_in_buffer
FROM sys.dm_os_buffer_descriptors
WHERE database_id NOT IN (1, 2, 3, 4)  -- excluir BDs del sistema
GROUP BY database_id
ORDER BY buffer_mb DESC;

Resultado:

El hallazgo es devastador: DB_CLIENTES (1,3 TB real) tiene solo 106 MB en buffer pool. DB_PRODUCTOS (1,2 TB real) tiene 57 MB. Estas dos bases de datos juntas representan 2,5 TB de datos que prácticamente no están en memoria.

Cada vez que una query necesita datos de estas BDs, tiene que leerlos del disco físico. Multiplicado por todas las queries concurrentes, esto genera un flujo constante de lecturas físicas que satura el volumen DATOS_SECUNDARIOS y explica las latencias de 35ms.

La causa raíz: 54 GB de RAM es insuficiente para el working set de datos activo. Con bases de datos que suman varios TB, el buffer pool se llena con los datos de las BDs más pequeñas y activas, dejando fuera completamente las más grandes.

2.4 TempDB

SELECT
    name,
    size * 8 / 1024 AS size_mb,
    max_size,
    growth,
    is_percent_growth
FROM tempdb.sys.database_files
ORDER BY file_id;

Resultado: 8 ficheros de datos + 1 de log, todos de 22,5 GB fijos, sin crecimiento automático (correcto según estándar). Total pre-asignado: 180 GB sobre un disco de 200 GB → 20 GB de margen real.

No es emergencia inmediata (el tamaño está fijo, no puede crecer más de lo pre-asignado), pero hay que vigilar el disco.

2.5 Log de transacciones retenido

SELECT
    name AS db_name,
    log_reuse_wait_desc,
    log_size_mb = size * 8 / 1024.0,
    log_used_mb = FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024.0
FROM sys.databases
WHERE log_reuse_wait_desc NOT IN ('NOTHING', 'LOG_BACKUP', 'CHECKPOINT')
  AND database_id > 4;

Resultado: Una base de datos auxiliar (no en el AG, recovery model SIMPLE) tenía log_reuse_wait_desc = OLDEST_PAGE, con un log de 2,26 GB. Esto indica un checkpoint pendiente. Al ejecutar de nuevo la query 10 minutos después, ya estaba en NOTHING — el checkpoint se completó solo. Resuelto sin intervención.

2.6 Backups recientes

SELECT
    bs.database_name,
    bs.type AS backup_type,
    MAX(bs.backup_finish_date) AS last_backup,
    DATEDIFF(HOUR, MAX(bs.backup_finish_date), GETDATE()) AS hours_ago,
    AVG(bs.backup_size / 1073741824.0) AS avg_size_gb
FROM msdb.dbo.backupset bs
WHERE bs.backup_finish_date > DATEADD(DAY, -7, GETDATE())
GROUP BY bs.database_name, bs.type
ORDER BY bs.database_name, bs.type;

Resultado: Todos los backups FULL completados en las últimas 24 horas. Backups de log cada 15 minutos para las BDs en FULL recovery model. ✅

---

Resumen de hallazgos y prioridades

---

Diagnóstico de causa raíz: la espiral del PLE bajo

Todo está interconectado:

54 GB RAM insuficiente para 2,5+ TB de datos activos
         ↓
Buffer pool lleno — páginas expulsadas cada 22 segundos
         ↓
Cada query genera lecturas físicas de disco
         ↓
DATOS_SECUNDARIOS saturado → latencias 35ms
         ↓
Queries más lentas → más tiempo usando CPU
         ↓
CPU elevado → más presión sobre el scheduler
         ↓
Sistema degradado globalmente

La solución directa es ampliar la RAM asignada a SQL Server. Si el hardware no lo permite, las alternativas son:

1. Identificar qué está consumiendo el buffer pool innecesariamente:

-- Top queries por buffer pool usage
SELECT TOP 20
    DB_NAME(p.database_id) AS db_name,
    OBJECT_NAME(p.object_id, p.database_id) AS object_name,
    COUNT(*) * 8 AS buffer_kb,
    p.index_id
FROM sys.dm_os_buffer_descriptors p
WHERE p.database_id != 1
GROUP BY p.database_id, p.object_id, p.index_id
ORDER BY buffer_kb DESC;

1. Mover ficheros de las BDs más grandes al volumen DATOS_EXTRA (que tiene 34% libre y mejores latencias) para reducir contención en DATOS_SECUNDARIOS.

1. Revisar índices con alta user_lookups — los key lookups obligan a leer páginas adicionales que contaminan el buffer pool.

---

Checklist de salud DBA — Resumen ejecutable

Este es el checklist completo que cubre en menos de 15 minutos los puntos críticos de un servidor SQL Server:

-- 1. Bloqueos activos
SELECT session_id, blocking_session_id, wait_type, wait_time/1000 AS wait_sec
FROM sys.dm_exec_requests WHERE blocking_session_id > 0;

-- 2. PLE y memoria
SELECT counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Page life expectancy', 'Memory Grants Pending');

-- 3. Espacio en disco
SELECT DISTINCT vs.volume_mount_point,
    vs.available_bytes/1073741824.0 AS free_gb,
    CAST(vs.available_bytes*100.0/vs.total_bytes AS DECIMAL(5,1)) AS free_pct
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs
ORDER BY free_pct ASC;

-- 4. Top latencias I/O
SELECT DB_NAME(mf.database_id) AS db, mf.physical_name,
    CASE WHEN vfs.num_of_reads>0
         THEN CAST(vfs.io_stall_read_ms*1.0/vfs.num_of_reads AS DECIMAL(10,1)) END AS read_ms,
    CASE WHEN vfs.num_of_writes>0
         THEN CAST(vfs.io_stall_write_ms*1.0/vfs.num_of_writes AS DECIMAL(10,1)) END AS write_ms
FROM sys.dm_io_virtual_file_stats(NULL,NULL) vfs
JOIN sys.master_files mf ON vfs.database_id=mf.database_id AND vfs.file_id=mf.file_id
ORDER BY read_ms DESC;

-- 5. Logs retenidos
SELECT name, log_reuse_wait_desc, size*8/1024 AS log_mb
FROM sys.databases
WHERE log_reuse_wait_desc NOT IN ('NOTHING','LOG_BACKUP','CHECKPOINT')
  AND database_id > 4;

-- 6. Backups vencidos (>25h sin FULL backup)
SELECT database_name, MAX(backup_finish_date) AS last_full,
    DATEDIFF(HOUR, MAX(backup_finish_date), GETDATE()) AS hours_ago
FROM msdb.dbo.backupset
WHERE type = 'D'
GROUP BY database_name
HAVING DATEDIFF(HOUR, MAX(backup_finish_date), GETDATE()) > 25;

-- 7. Planes forzados en Query Store (si QS habilitado)
SELECT OBJECT_NAME(q.object_id) AS proc_name, q.query_id, p.plan_id,
    FORMAT(p.initial_compile_start_time, 'dd/MM HH:mm') AS forced_since
FROM sys.query_store_plan p
JOIN sys.query_store_query q ON p.query_id = q.query_id
WHERE p.is_forced_plan = 1;

Con estos 7 checks tienes una foto completa del estado del servidor. El orden importa: los bloqueos activos son siempre lo primero (pueden estar causando el resto), y el PLE es el indicador más rápido de presión de memoria.