comando - La instrucción Delete en SQL es muy lenta

delete en sql server

Question

Tengo declaraciones como esta que están expirando:

DELETE FROM [table] WHERE [COL] IN ( ''1'', ''2'', ''6'', ''12'', ''24'', ''7'', ''3'', ''5'')

Intenté hacer uno a la vez así:

DELETE FROM [table] WHERE [COL] IN ( ''1'' )

y hasta ahora es a los 22 minutos y sigue funcionando.

La tabla tiene 260,000 filas y cuatro columnas.

¿Alguien tiene alguna idea de por qué esto sería tan lento y cómo acelerarlo? Tengo un índice no único, no agrupado en el [COL] en el que estoy haciendo WHERE. Estoy usando SQL Server 2008 R2

actualización: no tengo disparadores sobre la mesa.

Answer 1

1. Desactivar CONSTRAINT

   ALTER TABLE [TableName] NOCHECK CONSTRAINT ALL;

2. Deshabilitar índice

   ALTER INDEX ALL ON [TableName] DISABLE;

3. Reconstruir el índice

   ALTER INDEX ALL ON [TableName] REBUILD;

4. Habilitar RESTRAINT

   ALTER TABLE [TableName] CHECK CONSTRAINT ALL;

5. Eliminar de nuevo

Answer 2

¿Es [COL] realmente un campo de caracteres que contiene números, o puede deshacerse de las comillas simples alrededor de los valores? @Alex tiene razón en que IN es más lento que =, así que si puedes hacer esto, estarás mejor:

DELETE FROM [table] WHERE [COL] = ''1''

Pero lo mejor es utilizar números en lugar de cadenas para encontrar las filas (los números de Me gusta de sql):

 DELETE FROM [table] WHERE [COL] = 1

Tal vez intente:

 DELETE FROM [table] WHERE CAST([COL] AS INT) = 1

En cualquier caso, asegúrese de tener un índice en la columna [COL] para acelerar el escaneo de la tabla.

Answer 3

Cosas que pueden causar que una eliminación sea lenta:

• borrando muchos registros
• muchos índices
• índices faltantes en claves externas en tablas secundarias. (gracias a @CesarAlvaradoDiaz por mencionar esto en los comentarios)
• bloqueos y bloqueo
• desencadenantes
• eliminación en cascada (esos diez registros principales que está eliminando podrían significar que millones de registros secundarios se eliminen)
• Registro de transacciones que necesita crecer
• Muchas claves extranjeras para verificar

Por lo tanto, sus opciones son averiguar qué está bloqueando y solucionarlo o ejecutar las eliminaciones fuera de horas en las que no interfieran con la carga de producción normal. Puede ejecutar la eliminación en lotes (es útil si tiene activadores, eliminaciones en cascada o una gran cantidad de registros). Puede soltar y volver a crear los índices (lo mejor es que también pueda hacerlo en horas no hábiles).

Answer 4

Eliminar muchas filas puede ser muy lento. Intente eliminar algunas a la vez, como por ejemplo:

delete top (10) YourTable where col in (''1'',''2'',''3'',''4'')
while @@rowcount > 0
    begin
    delete top (10) YourTable where col in (''1'',''2'',''3'',''4'')
    end

Answer 5

Es posible que otras tablas tengan restricciones FK para su [tabla]. Por lo tanto, el DB necesita verificar estas tablas para mantener la integridad referencial. Incluso si tiene todos los índices necesarios que corresponden a estos FK, verifique su cantidad.

Tuve la situación cuando NHibernate creó incorrectamente FK s duplicados en las mismas columnas, pero con diferentes nombres (lo cual está permitido por SQL Server). Se ha ralentizado drásticamente la ejecución de la declaración DELETE.

Answer 6

Leí este artículo, fue muy útil para solucionar cualquier tipo de inconveniente

https://support.microsoft.com/en-us/kb/224453

este es un caso de waitresource KEY: 16: 72057595075231744 (ab74b4daaf17)

-- First SQL Provider to find the SPID (Session ID)

-- Second Identify problem, check Status, Open_tran, Lastwaittype, waittype, and waittime
-- iMPORTANT Waitresource select * from sys.sysprocesses where spid = 57

select * from sys.databases where database_id=16

-- with Waitresource check this to obtain object id 
select * from sys.partitions where hobt_id=72057595075231744

select * from sys.objects where object_id=2105058535

Answer 7

Si está eliminando todos los registros en la tabla en lugar de unos pocos, puede ser mucho más rápido simplemente colocar y volver a crear la tabla.

Answer 8

Si la tabla desde la que está eliminando tiene activadores ANTES / DESPUÉS DE ELIMINAR, algo allí podría estar causando su retraso.

Además, si tiene claves externas que hacen referencia a esa tabla, pueden estar ocurriendo ACTUALIZACIONES o SUPRIMIENTOS adicionales.

Answer 9

Verifique el plan de ejecución de esta declaración de eliminación. Eche un vistazo si se usa index search. Además, ¿qué es el tipo de datos de col?

Si está utilizando un tipo de datos incorrecto, cambie la declaración de actualización (como de ''1'' a 1 o N''1 '').

Si se utiliza el análisis de índice, considere utilizar alguna sugerencia de consulta .

Answer 10

abra CMD y ejecute estos comandos

NET STOP MSSQLSERVER
NET START MSSQLSERVER

esto reiniciará la instancia de SQL Server. intente ejecutar nuevamente después de su comando de eliminación

Tengo este comando en un script por lotes y lo ejecuto de vez en cuando si encuentro problemas como este. Un reinicio normal de PC no será el mismo, por lo que reiniciar la instancia es la forma más efectiva si tiene problemas con su servidor SQL.

Answer 11

Acción preventiva

Consulte con la ayuda de SQL Profiler la causa raíz de este problema. Puede haber Triggers causen el retraso en la ejecución. Puede ser cualquier cosa. No olvide seleccionar el Database Name la Database Name y el Database Name Object Name al iniciar el Trace para excluir el escaneo de consultas innecesarias ...

Filtrado de nombre de base de datos

Tabla / Procedimiento almacenado / Filtrado de nombre de activador

Acción correctiva

Como dijo, su tabla contiene 260,000 registros ... y IN Predicate contiene seis valores. Ahora, cada registro se está buscando 260,000 veces para cada valor en IN Predicate . En su lugar, debería ser la unión interna como a continuación ...

Delete K From YourTable1 K
Inner Join YourTable2 T on T.id = K.id

Inserte los valores de IN Predicate en una Temporary Table o una Local Variable