Pregunta MSSQL Large Delete


Tengo una tabla dentro de una base de datos MSSQL que contiene más de 100 millones de registros distribuidos en aproximadamente 100 días de datos. Necesito eliminar algunos de estos datos en función de su fecha, que es un campo indexado en la tabla. He intentado hacer una DELETE FROM en contra de una fecha individual, pero tardó mucho tiempo en ejecutarse y causó una caída en el rendimiento del servidor. ¿Hay una mejor manera de eliminar un número tan grande de registros? Algunos de estos datos aún son necesarios, por lo que, lamentablemente, no puedo usar Truncate.

Muchas gracias Mella


7
2018-05-21 11:06


origen




Respuestas:


He tenido el mejor éxito al realizar tareas similares con el siguiente flujo:

  1. Copia los datos para guardarlos en una tabla temporal.
  2. Trunca la tabla original para purgar todos los datos
  3. Mueve todo de la tabla temporal a la tabla original

Una de las principales ventajas de esto es que los índices se reconstruirán a medida que vuelva a colocar los datos en la tabla original.


2
2018-05-21 11:20



Puedo ver cómo funcionaría esto, pero suena demasiado arriesgado para mi gusto. Personalmente, no me sentiría cómodo haciendo esto en la base de datos de producción de OLTP de alto rendimiento, por ejemplo, sino solo yo. - John Sansom
No olvide volver a crear sus índices y restricciones si utiliza este método. - Nick Kavadias


Bueno, si estuviera utilizando la partición de SQL Server, por ejemplo, según la columna de fecha, posiblemente habría cambiado las particiones que ya no son necesarias. Una consideración para una futura implementación tal vez.

Creo que su única opción puede ser eliminar los datos en lotes más pequeños, en lugar de hacerlo de una vez, para evitar posibles problemas de bloqueo.


3
2018-05-21 11:13



Suena como un caso de uso perfecto para la partición de la ventana deslizante. +1 - Aaron Alton


Podría BORRAR todos los índices en la tabla, BORRAR DE la tabla, y luego volver a CREAR los índices. Esto podría acelerar las cosas, pero depende del porcentaje de registros que no se eliminan.


1
2018-05-21 11:21





Podrías eliminar trozos más pequeños. En lugar de decir, una semana vale la pena intentarlo solo por un día. Si eso es demasiado, intente ir solo por una hora a la vez.


0
2018-05-21 13:45





Otra opción sería seleccionar en otra tabla solo los datos que deseaba. Usted podría configurar la partición en la fecha de esta manera.

Si el índice de fecha era el índice agrupado, las eliminaciones deberían suceder más rápido, ya que todas estarían juntas en el disco.


0
2018-05-21 16:13





No me gusta ni defiendo la sugerencia de la tabla temporal mencionada. Si el servidor pasara entre el paso de eliminación y el paso de inserción, perdería los datos.

Tendría más probabilidades de ir con uno de los siguientes:

BCP los datos que desea conservar (BCP mediante QUERYOUT), truncar, volver a importar. El mismo efecto que la tabla temporal con menos riesgo general.

Copie los datos en otra tabla permanente, ya sea en la misma base de datos o en otra diferente, y luego retírela.

Eliminar en lotes utilizando la técnica SET ROWCOUNT. Si es cuidadoso y astuto, puede hacer un bucle para que salga fuera del alcance del bucle, por lo que las eliminaciones se realizan de forma independiente. A menos que su índice agrupado esté relacionado con la forma en que está eliminando los datos, esto causará una fragmentación extensa de la tabla.


0
2018-05-29 18:37





Probablemente, en algunas bases de datos OLTP de alto volumen es mejor no eliminar datos en absoluto. Los desarrolladores pueden crear un campo "IsDeleted" o algo así. Pero esta es una consideración para el futuro.

En cuanto a la respuesta que aceptaste. No creo que funcione más rápido que el enfoque DELETE simple, si copia 100Mb de datos. Será una carga muy pesada y un gran crecimiento del registro de transacciones. En general, depende de la cantidad de datos que desea que permanezcan intactos después de que finalice la eliminación.

Lo que recomendaría es

1) Si puede ejecutar su consulta en horas no activas, debe emitir un bloqueo de tabla exclusivo y luego eliminar registros. esto ahorrará tiempo que el servidor SQL usará para propagar bloqueos a muchas filas individuales

2) Si el primer enfoque no es posible, elimínelo por trozos, estaré de acuerdo con John Sansom. Los problemas comienzan cuando hay una transacción muy grande que bloquea muchas de las transacciones de otros usuarios activos ... Por lo tanto, tiene que eliminar en pequeñas porciones, cada una en su propia transacción ...

3) también podría apagar temporalmente (o soltar y luego recrear) los desencadenantes y restricciones Antes / Después de la eliminación (incluidas las claves foráneas); sin embargo, existe un riesgo de integridad y este enfoque requiere algunos experimentos.

AFAIK, deshabilitar / habilitar índices no mejorará la situación porque cuando elimine registros, habrá "agujeros" en los árboles de índice ... Por lo tanto, esto puede afectar el rendimiento de las próximas consultas SQL para la misma tabla, y más tarde o más tarde Es posible que desee reconstruir los índices; sin embargo, nunca veo ningún efecto en la forma en que los índices (incluso cuando usted también tiene índices) pueden disminuir la velocidad de la operación de eliminación.

En la mayoría de los casos, el mal rendimiento de DELETE es cuando la consulta DELETE no usa los índices (puede consultar el plan de consulta) o cuando tiene demasiadas claves externas o lógica de activadores pesados.


0
2017-07-20 16:16





Eliminar en trozos.

Realice una eliminación basada en una selección según sus criterios, pero la selección tiene un TOP 100000, por lo que solo se eliminan 100000 filas en cada llamada. Llama hasta que no se borre más.


0
2017-10-20 19:00