sql-server - temporales - tuning base de datos sql server

¿Cómo puedo acelerar las eliminaciones de una tabla de base de datos grande?

Question

Este es el problema que trato de resolver: recientemente completé un nuevo diseño de la capa de datos que me permite equilibrar la carga de mi base de datos en múltiples fragmentos. Para mantener los fragmentos equilibrados, necesito poder migrar datos de un fragmento a otro, lo que implica copiar desde el fragmento A al fragmento B, y luego eliminar los registros del fragmento A. Pero tengo varias tablas que son muy grandes. y tiene muchas claves externas apuntadas a ellos, por lo que eliminar un solo registro de la tabla puede llevar más de un segundo.

En algunos casos, necesito eliminar millones de registros de las tablas, y me lleva demasiado tiempo ser práctico.

Desactivar claves foráneas no es una opción. Eliminar grandes lotes de filas tampoco es una opción porque se trata de una aplicación de producción y los grandes borrados bloquean demasiados recursos, lo que provoca fallos. Estoy usando Sql Server y sé sobre tablas particionadas, pero las restricciones de partición (y las tarifas de licencia para la edición empresarial) son tan poco realistas que no son posibles.

Cuando comencé a trabajar en este problema, pensé que la parte difícil sería escribir el algoritmo que determina cómo eliminar filas desde el nivel de hoja hasta la parte superior del modelo de datos, de modo que no se violen las restricciones de clave externa en el camino. Pero resolver ese problema no me sirvió, ya que lleva semanas eliminar registros que deben desaparecer de la noche a la mañana.

Ya incorporé una forma de marcar los datos como virtualmente eliminados, por lo que en lo que respecta a la aplicación, los datos se han ido, pero todavía estoy tratando con archivos de datos grandes, copias de seguridad grandes y consultas más lentas debido al gran tamaño de las mesas.

¿Algunas ideas? Ya he leído publicaciones antiguas relacionadas aquí y no he encontrado nada que ayude.

Answer 1

Aquí está la solución a su problema.

DECLARE @RC AS INT
SET @RC = -1
WHILE @RC <> 0
BEGIN
DELETE TOP(1000000) FROM [Archive_CBO_ODS].[CBO].[AckItem] WHERE [AckItemId] >= 300
SET @RC = @@ROWCOUNT
--SET @RC = 0
END

Answer 2

Otra sugerencia es cambiar el nombre de la tabla y agregar una columna de estado. Cuando status = 1 (eliminado), entonces no querrá que se muestre. Entonces creas una vista con el mismo nombre que la tabla original que selecciona de la tabla cuando el estado es nulo o = 0 (dependiendo de cómo lo implementes). La eliminación parece inmediata para el usuario y un trabajo en segundo plano puede ejecutarse cada quince minutos, eliminando los registros que se ejecutan sin que nadie que no sea la base de datos sea consciente de ello.

Answer 3

Podría crear nuevos archivos, copiar todas las filas excepto las "eliminadas" y luego intercambiar los nombres en las tablas. Por último, suelta las mesas antiguas. Si está eliminando un gran porcentaje de los registros, esto puede ser más rápido.

Answer 4

Por favor vea: Optimizando Eliminar en SQL Server

Este artículo de soporte de MS podría ser interesante: cómo resolver los problemas de bloqueo causados ​​por la escalada de bloqueo en SQL Server :

Divida las operaciones de lotes grandes en varias operaciones más pequeñas . Por ejemplo, suponga que ejecutó la siguiente consulta para eliminar varios cientos de miles de registros antiguos de una tabla de auditoría y luego descubrió que causó una escalada de bloqueo que bloqueó a otros usuarios:

DELETE FROM LogMessages WHERE LogDate < ''2/1/2002'' 

Al eliminar estos registros unos pocos cientos a la vez, puede reducir drásticamente el número de bloqueos que se acumulan por transacción y evitar la escalada de bloqueos. Por ejemplo:

SET ROWCOUNT 500
delete_more:
DELETE FROM LogMessages WHERE LogDate < ''2/1/2002''
IF @@ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0

Reduzca la huella de bloqueo de la consulta haciendo que la consulta sea lo más eficiente posible. Las exploraciones grandes o un gran número de búsquedas de marcadores pueden aumentar la posibilidad de una escalada de bloqueo; Además, aumenta la posibilidad de puntos muertos y, en general, afecta negativamente la concurrencia y el rendimiento.

Answer 5

Puedes eliminar pequeños lotes usando un bucle while, algo como esto:

DELETE TOP (10000) FROM LogMessages WHERE LogDate < ''2/1/2002''
WHILE @@ROWCOUNT > 0
BEGIN
DELETE TOP (10000) FROM LogMessages WHERE LogDate < ''2/1/2002''
END

Answer 6

Si está utilizando SQL 2005 o 2008, tal vez el uso de "aislamiento de instantáneas" lo ayudaría. Permite que los datos permanezcan visibles para los usuarios mientras hay un procesamiento de operación de actualización de datos subyacente y luego los revela tan pronto como se confirman. Incluso si la eliminación tarda 30 minutos en ejecutarse, sus aplicaciones permanecerán en línea durante este tiempo.

Aquí hay una introducción rápida del bloqueo de instantáneas:

http://www.mssqltips.com/tip.asp?tip=1081

Aunque todavía debe intentar acelerar su eliminación para que sea lo más rápido posible, esto puede aliviar algo de la carga.

Answer 7

delete_more:
DELETE TOP(500) FROM LogMessages WHERE LogDate < ''2/1/2002''
IF @@ROWCOUNT > 0 GOTO delete_more

Puede obtener el mismo resultado utilizando SET ROWCOUNT como lo sugiere Mitch, pero de acuerdo con MSDN no será compatible con DELETE y algunas otras operaciones en versiones futuras de SQL Server:

El uso de SET ROWCOUNT no afectará las declaraciones DELETE, INSERT y UPDATE en una versión futura de SQL Server. Evite usar SET ROWCOUNT con las declaraciones DELETE, INSERT y UPDATE en el nuevo trabajo de desarrollo, y planee modificar las aplicaciones que actualmente lo usan. Para un comportamiento similar, use la sintaxis TOP Para obtener más información, vea TOP (Transact-SQL).