uso - Optimizar Delete en SQL Server

sql instrucción delete

Question

Delete en el servidor SQL a veces son lentas y con frecuencia he necesitado optimizarlas para disminuir el tiempo necesario. He estado buscando un poco de google en busca de consejos sobre cómo hacer eso, y he encontrado diversas sugerencias. Me gustaría conocer sus técnicas favoritas y más efectivas para domesticar a la bestia eliminada, y cómo y por qué funcionan.

hasta ahora:

• asegúrese de que las claves externas tengan índices

• asegúrese de que las condiciones estén indexadas

• uso de WITH ROWLOCK

• destruir los índices no utilizados, eliminar, reconstruir los índices

ahora te toca.

Answer 1

¡Simplifica cualquier uso de funciones en tu cláusula WHERE! Ejemplo:

DELETE FROM Claims
WHERE dbo.YearMonthGet(DataFileYearMonth) = dbo.YearMonthGet(@DataFileYearMonth)

Esta forma de la cláusula WHERE requirió 8 minutos para borrar 125,837 registros.

La función YearMonthGet compuso una fecha con el año y el mes a partir de la fecha de entrada y establece el day = 1 . Esto fue para asegurarnos de eliminar los registros en función del año y el mes, pero no del día del mes.

Reescribí la cláusula WHERE para:

WHERE YEAR(DataFileYearMonth) = YEAR(@DataFileYearMonth)
AND MONTH(DataFileYearMonth) = MONTH(@DataFileYearMonth)

El resultado: ¡la eliminación requirió alrededor de 38-44 segundos para eliminar esos 125,837 registros!

Answer 2

¿Tiene claves externas con integridad referencial activada? ¿Tiene activadores activados?

Answer 3

(Si los índices están "sin usar", ¿por qué están allí?)

Una opción que he usado en el pasado es hacer el trabajo en lotes. La forma más SET ROWCOUNT 20000 sería usar SET ROWCOUNT 20000 (o lo que sea) y loop (quizás con un WAITFOR DELAY ) hasta que se deshaga de todo (@@ ROWCOUNT = 0).

Esto podría ayudar a reducir el impacto en otros sistemas.

Answer 4

Añadiré otro a esto:

Asegúrese de que el nivel de aislamiento de la transacción y las opciones de la base de datos estén configurados de forma adecuada. Si su servidor SQL está configurado para no usar el control de versiones de filas, o está usando un nivel de aislamiento en otras consultas donde esperará a que se eliminen las filas, podría estarse configurando para un rendimiento muy bajo mientras la operación está ocurriendo. .

Answer 5

Creo que la gran trampa con delete que mata el rendimiento es que sql después de eliminar cada fila, actualiza todos los índices relacionados para cualquier columna en esta fila. ¿Qué hay de delting todos los índices antes de la eliminación masiva?

Answer 6

El problema es que no has definido tus condiciones lo suficiente. Es decir, ¿qué estás optimizando exactamente?

Por ejemplo, ¿está el sistema inactivo para el mantenimiento nocturno y no hay usuarios en el sistema? ¿Y borras un gran% de la base de datos?

Si está fuera de línea y elimina un gran%, puede tener sentido crear una nueva tabla con datos que conservar, descartar la tabla anterior y renombrarla. Si elimina un% pequeño, es probable que desee agrupar las cosas en lotes tan grandes como lo permita su espacio de registro. Depende completamente de su base de datos, pero la caída de índices durante la reconstrucción puede dañar o ayudar, incluso si es posible debido a estar "fuera de línea".

Si está en línea, ¿cuál es la probabilidad de que sus eliminaciones entren en conflicto con la actividad del usuario (y la actividad del usuario es principalmente leer, actualizar o qué)? ¿O está tratando de optimizar la experiencia del usuario o la velocidad de hacer su consulta? Si está eliminando de una tabla que otros usuarios actualizan con frecuencia, debe hacerlo por lotes pero con tamaños de lote más pequeños. Incluso si hace algo así como un bloqueo de tabla para forzar el aislamiento, eso no sirve de mucho si su enunciado de eliminación demora una hora.

Cuando define mejor sus condiciones, puede elegir una de las otras respuestas aquí. Me gusta el enlace en la publicación de Rob Sanders para dosificar cosas.

Answer 7

El siguiente artículo, Operaciones de eliminación ordenadas rápidamente puede ser de su interés.

Realizando operaciones rápidas de eliminación de SQL Server

La solución se centra en utilizar una vista para simplificar el plan de ejecución producido para una operación de eliminación por lotes. Esto se logra al hacer referencia a la tabla dada una vez, en lugar de dos veces, lo que a su vez reduce la cantidad de E / S requerida.

Answer 8

En tablas muy grandes donde tiene un conjunto muy específico de criterios para eliminar, también puede dividir la tabla, cambiar la partición y luego procesar las eliminaciones.

El equipo de SQLCAT ha estado utilizando esta técnica en volúmenes realmente grandes de datos. Encontré algunas referencias here pero intentaré encontrar algo más definitivo.

Answer 9

Hay eliminaciones y luego hay eliminaciones. Si está agotando los datos como parte de un trabajo de recorte, con suerte podrá eliminar bloques contiguos de filas mediante la clave agrupada. Si tiene que superar los datos de una tabla de alto volumen que no es contigua, es muy doloroso.

Answer 10

Me pregunto si es hora de recoger bases de datos basura. Marca una fila para eliminarla y el servidor la elimina más adelante durante un barrido. No querría esto para cada eliminación, porque a veces una fila debe irse ahora, pero sería útil en ocasiones.

Answer 11

Para ser sincero, eliminar un millón de filas de una tabla aumenta tan mal como insertar o actualizar un millón de filas. El problema es el tamaño del conjunto de filas, y no hay mucho que puedas hacer al respecto.

Mis sugerencias:

• Asegúrese de que la tabla tenga una clave principal e índice agrupado (esto es vital para todas las operaciones).
• Asegúrese de que el índice agrupado sea tal que se produzca una reorganización mínima de la página si se eliminara un bloque grande de filas.
• Asegúrese de que sus criterios de selección sean SARGables.
• Asegúrese de que todas las restricciones de clave externa sean actualmente confiables.

Answer 12

Si es verdad que las ACTUALIZACIONES son más rápidas que las DELETES, puede agregar una columna de estado llamada DELETED y filtrarla en sus selecciones. A continuación, ejecute un proceso por la noche que elimina las eliminaciones reales.

Answer 13

Si tiene muchas tablas de claves externas, comience en la parte inferior de la cadena y avance. La eliminación final irá más rápido y bloqueará menos cosas si no hay registros secundarios para eliminar en cascada (que NO activaría si tuviera un número grande de tablas secundarias, ya que eso mataría el rendimiento).

Eliminar en lotes.

Si tiene tablas de claves externas que ya no se usan (le sorprenderá la frecuencia con la que los databs de producción terminan en tablas antiguas de las que nadie se va a deshacer), deshágase de ellas o al menos rompa la conexión FK / PK. No tiene sentido marcar una tabla para los registros si no se está utilizando.

No eliminar: marque registros como delted y luego excluya los registros marcados de todas las consultas. Esta es la mejor configuración en el momento del diseño de la base de datos. Mucha gente usa esto porque también es la mejor forma de recuperar registros borrados accidentalmente. Pero es mucho trabajo para configurar en un sistema ya existente.

Answer 14

Tengo mucha más experiencia con Oracle, pero es muy probable que también se aplique a SQL Server:

• al eliminar una gran cantidad de filas, ejecute un bloqueo de tabla, por lo que la base de datos no tiene que hacer muchos bloqueos de fila
• si otras tablas hacen referencia a la tabla que elimina, asegúrese de que esas otras tablas tengan índices en la (s) columna (s) de clave externa (de lo contrario, la base de datos realizará una exploración de tabla completa para cada fila eliminada en la otra tabla para asegurarse de fila no infringe la restricción de clave externa)

Answer 15

Resumen de respuestas hasta el 2014-11-05

Esta respuesta está marcada como wiki de la comunidad, ya que este es un tema en constante evolución con muchos matices, pero muy pocas respuestas posibles en general.

El primer problema es que debes preguntarte por qué escenario estás optimizando. En general, esto es rendimiento con un solo usuario en la base de datos o escala con muchos usuarios en la base de datos. Algunas veces las respuestas son exactamente lo opuesto.

Para la optimización de usuario único

• TABLELOCK un TABLELOCK
• Elimine los índices no utilizados en la eliminación y luego recompáguelos después
• Lote usando algo como SET ROWCOUNT 20000 (o lo que sea, dependiendo del espacio de registro) y bucle (quizás con un WAITFOR DELAY ) hasta que se deshaga de todo ( @@ROWCOUNT = 0 )
• Si elimina un gran% de tabla, solo haga una nueva y elimine la tabla anterior
• Particione las filas para eliminar, luego suelte la parición. [Lee mas...]

Para la optimización multiusuario

• Sugerencia de bloqueos de fila
• Use el índice agrupado
• Diseñe el índice agrupado para minimizar la reorganización de la página si se eliminan los bloques grandes
• Actualice la columna "is_deleted", luego realice la eliminación real más tarde durante una ventana de mantenimiento

Para la optimización general

• Asegúrese de que los FK tengan índices en sus tablas fuente
• Asegúrese de que la cláusula WHERE tenga índices
• Identifique las filas para eliminar en la cláusula WHERE con una vista o tabla derivada en lugar de hacer referencia directamente a la tabla. [Lee mas...]