Delete statement in SQL is very slow

Question

I have statements like this that are timing out   DELETE FROM  table  WHERE  COL  IN    1    2    6    12    24    7    3    5     I tried doing one at a time like this   DELETE FROM  table  WHERE  COL  IN    1      and so far it s at 22 minutes and still going    The table has 260 000 rows in it and is four columns   Does anyone have any ideas why this would be so slow and how to speed it up  I do have a non-unique  non-clustered index on the  COL  that i m doing the WHERE on  I m using SQL Server 2008 R2  update  I have no triggers on the table

User · Accepted Answer

Things that can cause a delete to be slow:

deleting a lot of records
many indexes
missing indexes on foreign keys in child tables. (thank you to @CesarAlvaradoDiaz for mentioning this in the comments)
deadlocks and blocking
triggers
cascade delete (those ten parent records you are deleting could mean millions of child records getting deleted)
Transaction log needing to grow
Many Foreign keys to check

So your choices are to find out what is blocking and fix it or run the deletes in off hours when they won't be interfering with the normal production load. You can run the delete in batches (useful if you have triggers, cascade delete, or a large number of records). You can drop and recreate the indexes (best if you can do that in off hours too).

User · Answer

If you re deleting all the records in the table rather than a select few it may be much faster to just drop and recreate the table

User · Answer

As an extension to Andomar s answer  above  I had a scenario where the first 700 000 000 records  of  1 2 billion  processed very quickly  with chunks of 25 000 records processing per second  roughly    But  then it starting taking 15 minutes to do a batch of 25 000   I reduced the chunk size down to 5 000 records and it went back to its previous speed   I m not certain what internal threshold I hit  but the fix was to reduce the number of records  further  to regain the speed

User · Answer

In my case the database statistics had become corrupt   The statement  delete from tablename where col1    v1     was taking 30 seconds even though there were no matching records but  delete from tablename where col1    rubbish    ran instantly  running  update statistics tablename   fixed the issue

User · Answer

Disable CONSTRAINT   ALTER TABLE  TableName  NOCHECK CONSTRAINT ALL  Disable Index  ALTER INDEX ALL ON  TableName  DISABLE  Rebuild Index  ALTER INDEX ALL ON  TableName  REBUILD  Enable CONSTRAINT   ALTER TABLE  TableName  CHECK CONSTRAINT ALL  Delete again

User · Answer

I read this article it was really helpful for troubleshooting any kind of inconveniences  https   support microsoft com en-us kb 224453  this is a case of 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

User · Answer

Is  COL  really a character field that s holding numbers  or can you get rid of the single-quotes around the values   Alex is right that IN is slower than    so if you can do this  you ll be better off   DELETE FROM  table  WHERE  COL     1    But better still is using numbers rather than strings to find the rows  sql likes numbers     DELETE FROM  table  WHERE  COL    1   Maybe try    DELETE FROM  table  WHERE CAST  COL  AS INT    1   In either event  make sure you have an index on column  COL  to speed up the table scan

User · Answer

It s possible that other tables have FK constraint to your  table   So the DB needs to check these tables to maintain the referential integrity  Even if you have all needed indexes corresponding these FKs  check their amount   I had the situation when NHibernate incorrectly created duplicated FKs on the same columns  but with different names  which is allowed by SQL Server   It has drastically slowed down running of the DELETE statement

User · Answer

Check execution plan of this delete statement  Have a look if index seek is used  Also what is data type of col   If you are using wrong data type  change update statement  like from  1  to 1 or N 1     If index scan is used consider using some query hint

User · Answer

open CMD and run this commands  NET STOP MSSQLSERVER NET START MSSQLSERVER   this will restart the SQL Server instance  try to run again after your delete command  I have this command in a batch script and run it from time to time if I m encountering problems like this  A normal PC restart will not be the same so restarting the instance is the most effective way if you are encountering some issues with your sql server

User · Answer

If the table you are deleting from has BEFORE AFTER DELETE triggers  something in there could be causing your delay   Additionally  if you have foreign keys referencing that table  additional UPDATEs or DELETEs may be occurring

User · Answer

Deleting a lot of rows can be very slow   Try to delete a few at a time  like   delete top  10  YourTable where col in   1   2   3   4   while   rowcount  gt  0     begin     delete top  10  YourTable where col in   1   2   3   4       end

User · Answer

Older topic but one still relevant  Another issue occurs when an index has become fragmented to the extent of becoming more of a problem than a help  In such a case  the answer would be to rebuild or drop and recreate the index and issuing the delete statement again

User · Answer

Preventive Action  Check with the help of SQL Profiler for the root cause of this issue  There may be Triggers causing the delay in Execution  It can be anything  Don t forget to Select the Database Name and Object Name while Starting the Trace to exclude scanning unnecessary queries     Database Name Filtering  Table Stored Procedure Trigger Name Filtering  Corrective Action   As you said your table contains 260 000 records   and IN Predicate contains six values  Now  each record is being search 260 000 times for each value in IN Predicate  Instead it should be the Inner Join like below     Delete K From YourTable1 K Inner Join YourTable2 T on T id   K id   Insert the IN Predicate values into a Temporary Table or Local Variable

User · Answer

After inspecting an SSIS Package due to a SQL Server executing commands really slow   that was set up in a client of ours about 5-4 years before the time of me writing this  I found out that there were the below tasks  1  insert data from an XML file into a table called  Importbarcdes    2  merge command on an another target table  using as source the above mentioned table   3   delete from  Importbarcodes    to clear the table of the row that was inserted after the XML file was read by the task of the SSIS Package   After a quick inspection all statements SELECT  UPDATE  DELETE etc   on the table ImportBarcodes that had only 1 row  took about 2 minutes to execute   Extended Events showed a whole lot PAGEIOLATCH EX wait notifications   No indexes were present of the table and no triggers were registered   Upon close inspection of the properties of the table  in the Storage Tab and under general section  the Data Space field showed more than 6 GIGABYTES of space allocated in pages   What happened    The query ran for a good portion of time each day for the last 4 years  inserting and deleting data in the table  leaving unused pagefiles behind with out freeing them up   So  that was the main reason of the wait events that were captured by the Extended Events Session and the slowly executed commands upon the table   Running ALTER TABLE ImportBarcodes REBUILD fixed the issue freeing up all the unused space  TRUNCATE TABLE ImportBarcodes did a similar thing  with the only difference of deleting all pagefiles and data

[sql] Delete statement in SQL is very slow

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to database-optimization