Delete statement in SQL is very slow

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

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

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 > 0
    begin
    delete top (10) YourTable where col in ('1','2','3','4')
    end

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