CROSS APPLY TOP 1 en SQL Server: cuando un índice incompleto multiplica el tiempo por 90
Nota: Este artículo está basado en un incidente real de producción. Los datos de rendimiento son reales, pero los nombres de servidores, bases de datos, tablas y procedimientos han sido modificados para proteger la confidencialidad.
El incidente
Un procedimiento ETL que cargaba datos de trazabilidad pasó de ejecutarse en 16 minutos a no terminar en 24 horas. Lo más desconcertante: el plan bueno ya estaba forzado en Query Store, pero SQL Server lo ignoraba silenciosamente.
El patrón problemático
La query contenía un CROSS APPLY que se ejecutaba por cada una de las 76,5 millones de filas:
CROSS APPLY (
SELECT TOP 1 menu_id
FROM dw_operaciones.log_interacciones lm
WHERE lm.registro_id = c.registro_id
ORDER BY fecha DESC
) cmPara cada registro, buscaba la última interacción (por fecha) en una tabla de 1.067 millones de filas (67 GB).
La regresión catastrófica
| Métrica | Plan bueno | Plan malo | Factor |
|---|---|---|---|
| Duración | 16-50 min | 20-24 horas | 30-90x |
| Lecturas | 388M | 34.598-242.472M | 89-625x |
| CPU | 42-50 min | 53-380 horas | 60-450x |
| Resultado | ~2,64M filas | 0 (no termina) | — |
El índice incompleto
El índice existente cubría solo registro_id, pero NO incluía fecha:
IX_LOG_REGISTRO (registro_id) -- Solo la columna de búsquedaPara resolver el ORDER BY fecha DESC, SQL Server necesitaba: 1. Index Seek por registro_id → N filas 2. Key Lookup al clustered por cada fila para leer fecha 3. Sort de las N filas por fecha DESC 4. Top 1
Multiplicado por 76,5 millones de registros = más de mil millones de Key Lookups + Sorts.
El plan forzado ignorado (Silent Fallback)
El plan bueno estaba forzado en Query Store:
plan_id 92615 → is_forced_plan = true
force_failure_count = 0Pero SQL Server no lo usaba ni reportaba fallos. Este "silent fallback" ocurre cuando el plan forzado no es reproducible por cambios internos. SQL Server cae a otro plan sin avisar.
La solución: índice de cobertura
CREATE NONCLUSTERED INDEX [IX_LOG_REGISTRO_FECHA]
ON dw_operaciones.log_interacciones (registro_id, fecha DESC)
INCLUDE (menu_id)
WITH (ONLINE = ON, MAXDOP = 4);| Columna | Posición | Por qué |
|---|---|---|
registro_id | Key 1 | Seek por igualdad |
fecha | Key 2 DESC | TOP 1 se resuelve con el primer registro |
menu_id | INCLUDE | Evita Key Lookup |
Con este índice: 1 fila leída por registro en vez de N filas + sort + lookup.
Mejora esperada
| Aspecto | Plan malo | Con índice |
|---|---|---|
| Lecturas | 34.598-242.472M | < 100M |
| Duración | 20-24 horas | < 15 minutos |
| Estabilidad | 1.319 compilaciones | Estable |
Lecciones aprendidas
- CROSS APPLY TOP 1 necesita un índice que cubra WHERE + ORDER BY. Si solo cubre el WHERE, SQL Server hace Key Lookup + Sort por cada iteración.
- El forzado de planes no es infalible. El silent fallback puede ignorar un plan forzado sin avisar.
- SELECT TOP 1 * es un anti-patrón: especifica solo las columnas necesarias para que un índice más pequeño sea suficiente.
- Las tablas de mil millones de filas magnifican cualquier ineficiencia: 0,1ms por fila × 1.000M filas = 27 horas.