Operaciones masivas en SQL Server: Cómo borrar millones de filas sin bloqueos

En proyectos de Business Intelligence, especialmente en sectores como el retail o la energía donde manejamos tablas de hechos con cientos de millones de registros, es habitual enfrentarse a tareas de mantenimiento o purga. Quizás necesites eliminar datos de hace cinco años para reducir el tamaño del modelo de Power BI o actualizar un flag de estado en una tabla de Ventas que ha crecido sin control. El error más común de un perfil junior es lanzar un DELETE FROM Tabla WHERE Fecha < '2020-01-01' y esperar a que termine.

Lanzar una operación masiva de este tipo en un entorno de producción es una temeridad. No solo te arriesgas a llenar el log de transacciones y dejar la base de datos en modo solo lectura, sino que provocarás lo que conocemos como escalada de bloqueos (Lock Escalation). SQL Server, para ahorrar memoria, decidirá pasar de bloquear filas individuales a bloquear la tabla completa, impidiendo que cualquier otro proceso —incluyendo la actualización programada en Power BI que depende de esa tabla— pueda ejecutarse.

Como consultores, nuestro objetivo es que los datos fluyan. Una operación de mantenimiento no puede secuestrar el sistema durante horas. En este artículo vamos a desglosar cómo ejecutar estas operaciones de forma quirúrgica, utilizando un patrón de lotes que garantiza la disponibilidad del sistema y la estabilidad del log.

El problema: Por qué el borrado masivo bloquea el sistema

Cuando ejecutas un DELETE o UPDATE, SQL Server debe garantizar las propiedades ACID (Atomicidad, Consistencia, Aislamiento y Durabilidad). Esto implica que cada fila modificada se registra en el Log de Transacciones (LDF). Si intentas borrar 10 millones de filas en una sola instrucción, SQL Server intentará mantener esos 10 millones de bloqueos de fila en memoria. Cuando el motor detecta que una sesión consume demasiados recursos de bloqueo (por defecto, el umbral está cerca de los 5.000 bloqueos por objeto), realiza la escalada de bloqueos.

Una vez que la tabla está bloqueada en modo exclusivo (X), nadie más puede leer ni escribir en ella. Si tienes usuarios consultando informes o procesos de integración de datos activos, se quedarán en espera (Suspendido) hasta que tu operación termine. Además, si el proceso falla tras 2 horas de ejecución, el rollback (la marcha atrás) tardará probablemente el mismo tiempo o más, manteniendo el bloqueo durante todo el proceso de deshacer los cambios.

Para monitorizar este comportamiento en tiempo real, solemos recurrir a las vistas de administración dinámica, aunque debemos recordar lo que se pierde al reiniciar en cuanto a métricas acumuladas en las DMVs.

El patrón de lotes: Borrado controlado con TOP y WHILE

La solución técnica estándar para evitar la escalada de bloqueos y el crecimiento descontrolado del log es trocear la operación. En lugar de una gran transacción, ejecutamos miles de pequeñas transacciones. Este enfoque permite que el motor de SQL Server libere los bloqueos de fila al finalizar cada lote y que el proceso de truncado del log (si estamos en modo Simple o tenemos backups de log frecuentes) pueda reclamar espacio.

A continuación, presento el patrón de código que utilizamos en nuestros procesos de ETL para purgas masivas en tablas de FactVentas:

-- Definición de variables para el control del proceso
DECLARE @LoteSize INT = 5000; -- Tamaño por debajo del umbral de escalada
DECLARE @FilasAfectadas INT = 1;

-- Bucle para procesar los datos por tramos
WHILE (@FilasAfectadas > 0)
BEGIN
 DELETE TOP (@LoteSize)
 FROM dbo.FactVentas
 WHERE FechaID < 20190101; -- Ejemplo: borrar datos anteriores a 2019

 SET @FilasAfectadas = @@ROWCOUNT;

 -- Opcional: Pequeña pausa para permitir que el sistema respire
 -- WAITFOR DELAY '00:00:01'; 

 -- Mensaje de progreso para seguimiento en SSMS
 -- PRINT 'Lote procesado. Filas restantes en cola...';
END

En este ejemplo, usamos TOP (@LoteSize). Es vital que la consulta de selección sea eficiente. Si la columna de la condición (FechaID) no tiene un índice, SQL Server tendrá que escanear toda la tabla en cada iteración del bucle para encontrar las siguientes 5.000 filas, lo que degradará el rendimiento exponencialmente a medida que el proceso avance. Si la lógica de selección es compleja, es preferible usar una CTE o subconsulta para identificar primero las claves primarias y luego borrar sobre ellas.

¿Por qué 5.000 filas?

No es un número mágico, pero es el valor predeterminado en el que SQL Server evalúa la escalada de bloqueos por sesión. Si mantenemos cada lote por debajo de este número, nos aseguramos de que los bloqueos se mantengan a nivel de fila o página, permitiendo que otras consultas sigan accediendo a diferentes partes de la tabla. En entornos con discos NVMe de alta velocidad, podrías subir a 10.000 o 20.000, pero el riesgo de bloqueo aumenta.

Impacto en el Log de Transacciones

Incluso si usamos lotes, si la base de datos está en modelo de recuperación FULL y no estamos realizando backups del log con la frecuencia suficiente, el archivo .LDF crecerá hasta llenar el disco. Cada DELETE es una operación costosa porque el motor debe guardar una copia de la fila borrada en el log para poder revertirla si es necesario.

Si el entorno lo permite (y tras consultar con el DBA), cambiar temporalmente a modelo SIMPLE puede mitigar este crecimiento, aunque en entornos de producción críticos esto no suele ser una opción. Una técnica profesional consiste en introducir un CHECKPOINT manual o un WAITFOR DELAY entre lotes. Este segundo permite que el proceso de Log Backup programado tenga una ventana de tiempo para ejecutarse y liberar espacio en el archivo de registro.

Además, al definir las condiciones de filtrado, debemos ser cuidadosos. Evita el uso de operadores OR en el WHERE que puedan invalidar el uso de índices y obliguen a un escaneo completo de la tabla en cada iteración del lote.

Alternativas de alto rendimiento: El particionado

Cuando hablamos de tablas que superan los cientos de millones de filas y procesos de purga recurrentes (por ejemplo, mantener solo los últimos 36 meses de datos), el borrado por lotes deja de ser la mejor opción. Aquí es donde entra en juego el Partition Switching.

Si la tabla FactVentas está particionada por mes, borrar un mes entero no requiere un DELETE. Es una operación de metadatos. Podemos mover una partición completa a una tabla auxiliar (vacía) y luego truncar esa tabla auxiliar. El tiempo de ejecución pasa de horas a milisegundos.

El particionado requiere una planificación previa del esquema de la base de datos. Si no se diseñó así desde el principio, reestructurar una tabla masiva para particionarla puede ser tan costoso como el propio borrado que intentas evitar. Por eso, en proyectos de consultoría, solemos proponer el particionado como una mejora de arquitectura a medio plazo.

Ejemplo práctico: UPDATE masivo con JOIN

A veces el reto no es borrar, sino actualizar. Imaginemos que necesitamos actualizar el PrecioUnitario de la tabla FactVentas basándonos en una tabla corregida de DimProducto. El patrón de lotes se adapta perfectamente:

DECLARE @FilasActualizadas INT = 1;

WHILE (@FilasActualizadas > 0)
BEGIN
 UPDATE TOP (5000) v
 SET v.PrecioUnitario = p.PrecioCorrecto,
 v.FechaModificacion = GETDATE()
 FROM dbo.FactVentas v
 INNER JOIN dbo.DimProducto p ON v.ProductoID = p.ProductoID
 WHERE v.PrecioUnitario <> p.PrecioCorrecto
 -- Importante: una condición que excluya lo ya procesado
 AND (v.FechaModificacion < CAST(GETDATE() AS DATE) OR v.FechaModificacion IS NULL);

 SET @FilasActualizadas = @@ROWCOUNT;
END

Fíjate en la condición adicional en el WHERE. En un UPDATE, es fundamental asegurar que cada lote procese filas nuevas. Si no incluimos un flag o una validación que excluya las filas ya actualizadas, el bucle será infinito porque TOP (5000) siempre encontrará las mismas filas iniciales.

Errores frecuentes que he visto en proyectos reales

• No usar índices: Intentar borrar por lotes usando una columna de fecha que no tiene un índice non-clustered. El consumo de CPU se dispara al 100%.
• Transacciones externas: Envolver todo el bucle WHILE en un BEGIN TRAN ... COMMIT. Esto invalida totalmente el propósito de los lotes, ya que el log no se liberará hasta el final del bucle.
• Ignorar el crecimiento del archivo de datos: Borrar millones de filas no reduce el tamaño del archivo .MDF automáticamente. Libera espacio interno (páginas), pero el archivo en disco sigue pesando lo mismo a menos que se ejecute un SHRINK, algo que generalmente desaconsejamos por la fragmentación que genera.
• Falta de logging propio: No imprimir mensajes de progreso. En procesos que duran 40 minutos, es vital saber si vamos por el lote 10 o el 500 para estimar el tiempo de finalización.

Conclusión

Ejecutar operaciones masivas en SQL Server requiere cambiar la mentalidad de «hacerlo todo de una vez» por la de «hacerlo de forma incremental». El patrón de lotes con un tamaño cercano a las 5.000 filas es el equilibrio perfecto entre seguridad, rendimiento y respeto a la concurrencia del sistema. Como consultores de datos, nuestra responsabilidad no es solo que la consulta devuelva el resultado correcto, sino que el impacto en la infraestructura sea predecible y manejable.

Preguntas frecuentes

¿Puedo usar un tamaño de lote mayor a 5.000?

Sí, puedes incrementarlo si el sistema tiene baja carga de usuarios o si has monitorizado que la escalada de bloqueos no se produce hasta umbrales superiores. No obstante, 5.000 es el estándar conservador recomendado por Microsoft para evitar el bloqueo de tablas en la mayoría de configuraciones.

¿Qué pasa si mi proceso de borrado se interrumpe a la mitad?

Gracias al patrón de lotes, los datos borrados en los ciclos completados ya están confirmados (COMMIT). Solo perderás el trabajo del lote actual que estuviera en ejecución. Esto es una ventaja competitiva frente a una transacción única, donde un fallo obligaría a un rollback total de millones de filas.

¿Es mejor TRUNCATE que DELETE por lotes?

TRUNCATE TABLE es mucho más rápido y apenas genera log, pero es una operación de «todo o nada». No puedes usar un WHERE con TRUNCATE. Si necesitas borrar solo una parte de los datos, el DELETE por lotes es tu única opción viable si no tienes la tabla particionada.

¿Cómo afecta esto a mis informes de Power BI?

Si usas lotes, los usuarios de Power BI podrán seguir consultando los informes (incluso mediante DirectQuery) sin notar bloqueos prolongados. Solo experimentarán ligeras esperas de milisegundos mientras los pequeños lotes se confirman, garantizando una experiencia de usuario fluida.

Checklist para operaciones masivas

• Verificar que la columna de filtrado (WHERE) tiene un índice adecuado.
• Asegurar que el tamaño del lote esté entre 1.000 y 5.000 filas.
• Comprobar el espacio disponible en el disco del Log de Transacciones (.LDF).
• Ejecutar el proceso fuera de las horas punta de carga de datos (ETL).
• Incluir un mecanismo de log (PRINT o tabla de auditoría) para monitorizar el progreso.