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.

Answer 1

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).

Answer 2

1. Disable CONSTRAINT

   ALTER TABLE [TableName] NOCHECK CONSTRAINT ALL;

2. Disable Index

   ALTER INDEX ALL ON [TableName] DISABLE;

3. Rebuild Index

   ALTER INDEX ALL ON [TableName] REBUILD;

4. Enable CONSTRAINT

   ALTER TABLE [TableName] CHECK CONSTRAINT ALL;

5. Delete again