Deadlocks en SQL Server después de borrar 23 millones de filas: el caso de las estadísticas fantasma

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.

El contexto: un borrado masivo necesario

En muchos sistemas de producción, las tablas de cola o de log acumulan millones de registros procesados que ya no son necesarios pero que nunca se purgan. Con el tiempo, lo que debería ser una tabla operativa de cientos de miles de filas se convierte en un monstruo de decenas de millones, degradando el rendimiento de todas las operaciones sobre ella.

Este caso documenta exactamente esa situación: una tabla de publicación de eventos que había acumulado ~29 millones de filas, la mayoría en estados terminales. El borrado se realizó correctamente, por lotes para evitar bloqueos masivos. Lo que nadie anticipó fue lo que ocurrió en las horas siguientes.

---

El borrado: correcto en la forma, con consecuencias ocultas

El script de borrado seguía las mejores prácticas habituales:

SET NOCOUNT ON;

DECLARE @BatchSize INT = 50000;
DECLARE @RowsAffected INT = 1;

WHILE (@RowsAffected > 0)
BEGIN
    BEGIN TRANSACTION;

    DELETE TOP (@BatchSize)
    FROM dbo.tabla_publicacion_eventos WITH (ROWLOCK, READPAST)
    WHERE estado_envio IN ('ENVIADO', 'ERROR')
      AND fecha_procesado < '2025-12-01';

    SET @RowsAffected = @@ROWCOUNT;

    COMMIT TRANSACTION;

    PRINT CONCAT('Filas borradas: ', @RowsAffected, ' -- ', GETDATE());

    WAITFOR DELAY '00:00:01';
END

Los elementos correctos están ahí: - TOP (@BatchSize) — lotes de 50.000 filas para no generar transacciones masivas - WITH (ROWLOCK, READPAST) — evita bloqueos de página y tabla - WAITFOR DELAY '00:00:01' — pausa entre lotes para dejar respirar al sistema - Transacción por lote, no una sola transacción para todo

Resultado: ~23 millones de filas eliminadas a lo largo del 16 y 17 de marzo. La tabla quedó con ~5,7 millones de registros activos.

---

El problema: deadlocks al día siguiente

El 17 de marzo, unas horas después de finalizar el borrado, el equipo notificó dos deadlocks sobre la misma tabla:

• Deadlock 1: a las 10:40
• Deadlock 2: a las 11:33

Los deadlocks envolvían dos tipos de operaciones: - Proceso A: DELETE de registros procesados (operación normal del sistema) - Proceso B: UPDATE de estado de registros existentes

El patrón DELETE vs UPDATE sobre la misma tabla es un clásico de deadlocks en sistemas de cola. Pero ¿por qué apareció ahora, después del borrado? La tabla llevaba años en producción sin este problema.

---

Diagnóstico: las estadísticas desactualizadas

La primera consulta ante cualquier degradación post-borrado masivo es el estado de las estadísticas:

SELECT
    s.name AS stat_name,
    s.auto_created,
    s.user_created,
    STUFF((
        SELECT ', ' + c.name
        FROM sys.stats_columns sc
        JOIN sys.columns c ON sc.object_id = c.object_id
                          AND sc.column_id = c.column_id
        WHERE sc.object_id = s.object_id
          AND sc.stats_id = s.stats_id
        ORDER BY sc.stats_column_id
        FOR XML PATH('')
    ), 1, 2, '') AS columns,
    sp.last_updated,
    sp.rows AS rows_at_update,
    sp.rows_sampled,
    CAST(sp.rows_sampled * 100.0 / NULLIF(sp.rows, 0) AS DECIMAL(5,2)) AS sample_pct,
    sp.modification_counter,
    CAST(sp.modification_counter * 100.0 / NULLIF(sp.rows, 0) AS DECIMAL(10,2)) AS modification_pct
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE s.object_id = OBJECT_ID('dbo.tabla_publicacion_eventos')
ORDER BY sp.modification_counter DESC;

Resultado — dos grupos claramente diferenciados:

Estadísticas actualizadas (post-borrado):

Estadísticas desactualizadas (pre-borrado):

El hallazgo clave: 6 estadísticas siguen creyendo que la tabla tiene 29 millones de filas cuando en realidad tiene 5,7 millones. Su contador de modificaciones es del 83% — han visto 23 millones de cambios sobre un total de 29 millones registrados.

---

¿Por qué el auto-update no funcionó?

SQL Server tiene un mecanismo automático de actualización de estadísticas. La regla tradicional (SQL Server 2016 y anteriores): si más del 20% de las filas de una tabla han cambiado, las estadísticas se actualizan automáticamente la próxima vez que se compila un plan de ejecución que las necesita.

Con 23 millones de modificaciones sobre 29 millones de filas, el umbral del 20% se superó hace mucho. ¿Por qué no se actualizaron estas 6 estadísticas?

La respuesta está en el mecanismo de disparo: el auto-update se activa cuando SQL Server compila un plan que necesita esas estadísticas. Si ninguna query activa en producción filtra por cod_categoria, num_intentos, TX_PAYLOAD o fecha_carga directamente, esas estadísticas nunca se actualizan automáticamente, independientemente de cuántas modificaciones hayan acumulado.

Las estadísticas del índice principal (PK, IDX_POLLER) sí se actualizaron porque las queries del sistema las referencian constantemente. Las 6 estadísticas auto-creadas (_WA_Sys_*) son estadísticas de columnas individuales que SQL Server crea especulativamente pero que quizás nunca se usan en queries activas.

-- Verificar si las estadísticas desactualizadas son referenciadas en planes activos
-- (proxy: ver si aparecen en planes del plan cache)
SELECT
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS avg_reads,
    qt.text AS sql_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qt.text LIKE '%tabla_publicacion_eventos%'
ORDER BY qs.execution_count DESC;

---

La conexión entre estadísticas malas y deadlocks

Las estadísticas desactualizadas afectan directamente la elección del plan de ejecución. Con estadísticas que estiman 29 millones de filas cuando hay 5,7 millones:

1. El optimizador sobreestima el número de filas que devolverán las queries
2. Para gestionar un "resultado grande", elige plans de ejecución más agresivos: parallel plans con más workers, Table Scans en lugar de Seeks, Hash Joins en lugar de Nested Loops
3. Con más workers paralelos y scans más amplios, aumenta la probabilidad de que dos transacciones accedan a las mismas páginas en orden contrario → deadlock

Específicamente en el patrón DELETE vs UPDATE: - El DELETE necesita bloquear filas en el orden en que las encuentra (determinado por el plan de ejecución) - El UPDATE hace lo mismo - Si el optimizador elige planes que acceden a páginas diferentes en ordenes opuestos (porque las estimaciones de cardinalidad son incorrectas), las dos transacciones se bloquean mutuamente esperando que la otra libere

Con estadísticas correctas, el optimizador elige un plan más eficiente que tiende a acceder a las páginas en un orden más predecible, reduciendo la ventana de colisión.

---

Solución: actualización manual de estadísticas

La solución inmediata es sencilla: actualizar las estadísticas desactualizadas.

-- Opción 1: Actualizar todas las estadísticas de la tabla
UPDATE STATISTICS dbo.tabla_publicacion_eventos WITH FULLSCAN;

-- Opción 2: Actualizar solo las estadísticas específicas desactualizadas
UPDATE STATISTICS dbo.tabla_publicacion_eventos _WA_Sys_00000005_4D94879B WITH FULLSCAN;
UPDATE STATISTICS dbo.tabla_publicacion_eventos _WA_Sys_00000007_4D94879B WITH FULLSCAN;
UPDATE STATISTICS dbo.tabla_publicacion_eventos _WA_Sys_00000003_4D94879B WITH FULLSCAN;
-- etc.

-- Opción 3: Reconstruir todos los índices de la tabla (actualiza estadísticas implícitamente)
ALTER INDEX ALL ON dbo.tabla_publicacion_eventos REBUILD WITH (ONLINE = ON);

WITH FULLSCAN es importante aquí: las estadísticas desactualizadas tenían un sample del 0,67% (194.000 filas de 29 millones). Después del borrado, hacer un full scan sobre 5,7 millones de filas es perfectamente manejable y da estadísticas mucho más precisas.

---

Cómo debería haberse gestionado el borrado masivo

Un borrado masivo no es una operación trivial. Aunque el script era correcto en su forma, faltó planificar las consecuencias.

Protocolo recomendado para borrados masivos:

1. Antes del borrado — capturar estado de estadísticas %%CODEBLOCK_4%%

2. Durante el borrado — monitorizar el progreso %%CODEBLOCK_5%%

3. Después del borrado — siempre actualizar estadísticas %%CODEBLOCK_6%%

4. Reconstruir también el índice clustered si la tabla tiene fragmentación alta

El borrado de 23 millones de filas deja páginas de índice semi-vacías y fragmentadas. Esto no causa deadlocks directamente, pero sí degrada el rendimiento de todos los rangos:

-- Verificar fragmentación post-borrado
SELECT
    i.name AS index_name,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.tu_tabla'), NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
ORDER BY ips.avg_fragmentation_in_percent DESC;

---

Por qué las estadísticas de índice sí se actualizaron (y las otras no)

Este detalle merece explicación. Las estadísticas del índice principal (PK y los índices compuestos de búsqueda) se actualizaron automáticamente en las horas post-borrado. Las 6 estadísticas de columnas individuales no.

La diferencia: uso activo en tiempo de compilación.

Las queries del sistema (el proceso que lee eventos para publicarlos) usan constantemente el índice principal para encontrar registros por estado. Cada vez que el optimizador compila un plan para esas queries, SQL Server comprueba si las estadísticas de esos índices están actualizadas. Como el modification_counter superaba el umbral, las actualizó automáticamente.

Las 6 estadísticas auto-creadas en columnas individuales (cod_categoria, TX_PAYLOAD, etc.) nunca fueron requeridas por ningún plan en compilación después del borrado. Nadie preguntó por ellas, así que nadie las actualizó.

Este comportamiento es por diseño, pero tiene implicaciones importantes: el auto-update de estadísticas es reactivo, no proactivo. Solo se dispara cuando hay una compilación que las necesita. Si tienes estadísticas sobre columnas que rara vez se usan en filtros directos, pueden quedar desactualizadas indefinidamente aunque el modification_counter sea del 99%.

La solución robusta es un job de mantenimiento de estadísticas que las actualice periódicamente, especialmente después de operaciones masivas:

-- Job de mantenimiento semanal (o post-borrado masivo)
-- Actualiza todas las estadísticas de la BD con sample inteligente
EXEC sp_updatestats;  -- actualiza solo las que tienen modificaciones > umbral

-- Versión más agresiva para post-borrado masivo:
-- (recorre todas las tablas y actualiza con FULLSCAN las que tienen >20% de modificaciones)
DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += 'UPDATE STATISTICS ' + QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(o.name) + ' WITH FULLSCAN; '
FROM sys.objects o
JOIN sys.stats s ON o.object_id = s.object_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE o.type = 'U'
  AND sp.modification_counter > sp.rows * 0.20
  AND sp.rows > 0
GROUP BY o.schema_id, o.name;
EXEC sp_executesql @sql;

---

Respuestas concretas a las preguntas del equipo de desarrollo

El equipo que realizó el borrado preguntó dos cosas específicas. Aquí las respuestas:

"¿Se realizó algún mantenimiento entre el fin del borrado y los deadlocks?"

No. El último mantenimiento programado fue el sábado anterior al borrado (3 días antes). El borrado terminó el 17 de marzo. No hubo ninguna operación DBA entre el fin del borrado y los deadlocks de las 10:40 del mismo día.

"¿Cuándo se actualizarán (o se actualizaron) las estadísticas?"

De las 11 estadísticas de la tabla: - 5 se actualizaron automáticamente entre las 4:48 y las 6:46 del 18 de marzo (porque sus índices son usados activamente por queries en compilación) - 6 no se han actualizado porque nunca fueron referenciadas por queries post-borrado — y seguirán sin actualizarse hasta que: (a) se actualicen manualmente, o (b) alguna query que necesite esas estadísticas precise compilarse

---

Lecciones y reglas prácticas

Regla 1: Todo borrado masivo debe ir seguido de actualización de estadísticas

Si eliminas más del 10% de las filas de una tabla, actualiza estadísticas inmediatamente al terminar, no esperes al mantenimiento semanal. El auto-update no es suficiente cuando la modificación es masiva y las estadísticas afectadas pueden no estar en uso activo.

Regla 2: El auto-update es reactivo, no proactivo

No confíes en que SQL Server actualizará las estadísticas porque la tabla cambió mucho. Solo las actualiza cuando compila un plan que las necesita. Si los planes ya estaban en caché (plan cache hit), la actualización puede retrasarse horas o días.

Para forzar que todos los planes en caché relacionados con la tabla sean descartados y recompilados:

-- Invalidar el plan cache de la tabla (fuerza recompilación de todas sus queries)
EXEC sp_recompile 'dbo.tabla_publicacion_eventos';

-- Después actualizar estadísticas
UPDATE STATISTICS dbo.tabla_publicacion_eventos WITH FULLSCAN;

Regla 3: Monitoriza el modification_counter antes y después

-- Script de monitorización de estadísticas (incluye en checklist post-borrado)
SELECT
    OBJECT_NAME(s.object_id) AS tabla,
    s.name AS estadistica,
    sp.last_updated,
    sp.rows AS filas_al_actualizar,
    sp.modification_counter AS modificaciones_desde_update,
    CAST(sp.modification_counter * 100.0 / NULLIF(sp.rows, 0) AS DECIMAL(10,2)) AS pct_modificacion,
    CAST(sp.rows_sampled * 100.0 / NULLIF(sp.rows, 0) AS DECIMAL(5,2)) AS pct_sample
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE s.object_id = OBJECT_ID('dbo.tabla_publicacion_eventos')
ORDER BY pct_modificacion DESC;

Un pct_modificacion > 50% en estadísticas que llevan días sin actualizarse es una señal de alerta antes de que aparezcan problemas de rendimiento o deadlocks.

Regla 4: Los borrados masivos dejan fragmentación — considera reconstruir índices

Después de borrar el 80% de las filas, los índices quedan con páginas semi-vacías. Esto no causa deadlocks, pero sí hace que las queries sean más lentas (más páginas que leer para encontrar los mismos datos). Una reconstrucción de índice después de un borrado masivo es una buena práctica:

-- Reconstruir índices online (sin bloquear el servicio)
ALTER INDEX ALL ON dbo.tabla_publicacion_eventos
REBUILD WITH (ONLINE = ON, MAXDOP = 2, FILLFACTOR = 90);

FILLFACTOR = 90 deja un 10% de espacio libre en cada página, reduciendo las divisiones de página (page splits) en inserciones posteriores.

---

Resultado

La actualización de estadísticas con FULLSCAN resolvió los deadlocks. Las queries del sistema empezaron a compilar planes correctos basados en 5,7 millones de filas reales en lugar de 29 millones fantasma.

El borrado masivo por lotes fue la decisión correcta para minimizar el impacto en producción durante la operación. La ausencia de un paso final de mantenimiento (actualizar estadísticas + opcionalmente reconstruir índices) fue lo que generó la incidencia del día siguiente.

En SQL Server, una tabla cambia no solo cuando se modifican sus datos, sino también cuando el optimizador la percibe diferente. Después de un borrado masivo, ambas cosas ocurren. El trabajo del DBA no termina cuando termina el DELETE.