Caso real ocurrido en septiembre de 2025 y repetido en marzo de 2026 sobre un servidor de staging que recibe replicación continua desde un mainframe. Nombres de servidores y bases de datos enmascarados; los comportamientos descritos y los tiempos son los reales.

El indicio: una latencia que crecía sin razón aparente

A media mañana, el cuadro de mando de Elastic empezó a mostrar latencia creciente en la cola de aplicación de la replicación CDC. Habitualmente la latencia se mantenía por debajo de 30 segundos. Esa mañana subió a 5 minutos, luego 15, luego 45.

La replicación CDC es del tipo que aplica cambios constantemente desde un origen externo (un mainframe en este caso) a tablas de staging en SQL Server. No tiene ventanas de inactividad. Si la latencia crece, los datos en BI/reporting empiezan a estar desfasados y los dashboards de las áreas de negocio dan números antiguos.

El primer reflejo fue mirar la red, el agente de replicación, el espacio de tempdb. Todo correcto. La causa estaba en el lugar más inesperado: un job de mantenimiento de índices que llevaba ejecutándose 17 horas seguidas.

El job inocente que no dejaba respirar

El job era estándar: IndexOptimize de Ola Hallengren, programado todos los miércoles a las 22:00. La configuración:

EXEC dbo.IndexOptimize
    @Databases = 'BD_STAGING_LOG',
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30,
    @UpdateStatistics = 'ALL',
    @LogToTable = 'Y';

La tabla principal de la BD (fact_personas_log, ~180 millones de filas, 31 índices) estaba siendo reorganizada. La fragmentación estaba en el rango medio (entre 5% y 30%), por lo que el motor optaba por INDEX REORGANIZE (operación online, fila a fila).

Lo que en una tabla sin tráfico tarda 30-60 minutos, en una tabla con CDC continuo aplicando inserts a alta velocidad se prolongaba indefinidamente. Cada vez que CDC iba a insertar una página que el reorganize estaba moviendo, ambos competían por los mismos bloqueos. Y SQL Server resolvía el conflicto eligiendo víctima.

El patrón de deadlock entre REORGANIZE y CDC

El log de errores del SQL Server estaba lleno de líneas como esta:

Transaction (Process ID 142) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.

Para ver el detalle real del deadlock, una query a sys.event_log o al fichero por defecto de Extended Events:

-- Si tienes los XE por defecto
SELECT TOP 50
    DATEADD(MINUTE,
        DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()),
        xes.event_data.value('(event/@timestamp)[1]', 'datetime2')) AS hora,
    xes.event_data.value('(event/data[@name="object_name"]/value)[1]',
                         'nvarchar(100)') AS objeto,
    CAST(xes.event_data.value('(event/data[@name="xml_report"]/value)[1]',
                              'nvarchar(max)') AS XML) AS deadlock_xml
FROM (
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file(
        'system_health*.xel', NULL, NULL, NULL)
    WHERE object_name = 'xml_deadlock_report'
) xes
ORDER BY hora DESC;

El XML del deadlock siempre mostraba el mismo patrón:

Una sesión del agente CDC ejecutando INSERT INTO fact_personas_log con bloqueos IX (intent exclusive) sobre páginas del índice clusterizado.
Una sesión del job de mantenimiento ejecutando ALTER INDEX ... REORGANIZE con bloqueos S (shared) y U (update) sobre las mismas páginas mientras las recoloca.
El motor mataba habitualmente al CDC (por menor tiempo de transacción, no por menor prioridad — REORGANIZE no es más prioritario).

El CDC reintentaba automáticamente, pero el ciclo deadlock-retry-deadlock-retry se repitió cientos de veces, acumulando segundos de latencia cada minuto. Como el job no terminaba (las sesiones de CDC abrían más páginas que el motor de reorganize aún no había tocado), el ciclo se prolongaba.

La solución inmediata y la solución estructural

Solución inmediata — parar el job:

EXEC msdb.dbo.sp_stop_job @job_name = 'DBA - IndexOptimize Weekly';

En cuanto el REORGANIZE soltó los bloqueos, los reintentos del CDC empezaron a tener éxito y la cola de replicación se vació en ~10 minutos. La latencia volvió a sub-minuto.

Solución estructural — añadir @TimeLimit al job:

EXEC dbo.IndexOptimize
    @Databases = 'BD_STAGING_LOG',
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30,
    @TimeLimit = 14400,   -- 4 horas máximo
    @UpdateStatistics = 'ALL',
    @LogToTable = 'Y';

Cuatro horas es más que suficiente para los índices más fragmentados. Lo que no llegue, se procesa la siguiente semana — la fragmentación no es lineal, los índices más malos se reorganizan los primeros.

¿REORGANIZE u ONLINE REBUILD? Cuenta intuitiva vs práctica

La intuición típica dice: REORGANIZE es online y rápido, REBUILD es pesado y largo. Pero en escenarios con carga continua, esa intuición es engañosa.

INDEX REORGANIZE: - Compacta páginas página a página, manteniendo bloqueos de corta duración. - Tiene un patrón de bloqueos prolongado (puede tardar horas) que choca con cualquier UPDATE/INSERT continuo. - No actualiza estadísticas (hay que hacerlo aparte). - Es preferible cuando la fragmentación está entre 5% y 30%.

INDEX REBUILD WITH (ONLINE = ON) (Enterprise Edition): - Construye un índice nuevo en background, manteniendo el viejo activo. - Solo bloquea brevemente al final, durante el switch. - Actualiza estadísticas con FULLSCAN automáticamente. - Es preferible para tablas con muy alta carga continua, incluso con fragmentación baja.

En el caso real, después del incidente cambiamos el threshold de @FragmentationLevel2 de 30 a 15. El resultado: la mayor parte de los índices con fragmentación >15% pasan a REBUILD ONLINE en lugar de REORGANIZE. El total del job pasa de 17h a ~3h, sin deadlocks contra CDC.

La alerta que ahora siempre tenemos

Una alerta SQL Agent que se dispara si cualquier job de mantenimiento lleva más de 4 horas activo:

SELECT
    j.name AS job_name,
    ja.start_execution_date,
    DATEDIFF(MINUTE, ja.start_execution_date, GETDATE()) AS minutos_en_ejecucion
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobactivity ja ON j.job_id = ja.job_id
WHERE ja.start_execution_date IS NOT NULL
  AND ja.stop_execution_date IS NULL
  AND DATEDIFF(MINUTE, ja.start_execution_date, GETDATE()) > 240;

Si devuelve filas y el servidor tiene replicación CDC activa, alguien recibe email y revisa. La mayoría de las veces el job ha terminado solo. Pero cuando no termina, ahora se descubre antes de que la latencia llegue al cuadro de mando.

La lección práctica

En servidores con replicación continua, mantenimiento sin límite de tiempo es una bomba de relojería. La regla práctica que adopté tras los dos incidentes:

Todo job de mantenimiento de índices tiene @TimeLimit. 4 horas es un buen valor por defecto.
Si la tabla recibe carga continua (CDC, IIDR, replicación, IoT, eventos), prefiere REBUILD ONLINE a REORGANIZE aunque la fragmentación sea baja. La operación es más cara individualmente pero menos invasiva en bloqueos.
Alerta proactiva sobre jobs de mantenimiento con duración anormal. La métrica no es CPU ni memoria — es duración.

El mantenimiento de índices es necesario, pero no debe convertirse en un servicio peor que la fragmentación que pretende evitar.

Mantenimiento de índices vs replicación 24/7: deadlocks que paralizan el CDC