Sql Server Delete Is Slower with Indexes

SQL Server DELETE is slower with indexes

Indexes make lookups faster - like the index at the back of a book.

Operations that change the data (like a DELETE) are slower, as they involve manipulating the indexes. Consider the same index at the back of the book. You have more work to do if you add, remove or change pages because you have to also update the index.

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

DELETE performance in SQL Server on clustered index, large table

In addition to the fine points JNK included in their answer, one particular killer I've seen is when you're deleting rows from the referenced table for one or more foreign key constraints, and the referencing column(s) in the referencing table(s) aren't indexed - you're forcing a table scan on each of those tables to occur before the delete can be accepted.

SQL Delete a single row by PK is very slow

I dropped and re-created all Foreign Keys with NO cascade delete.
Now the execution plan is using efficient Index Seeks to check RI on all FKs.

Not sure why having cascade delete enabled caused these Index Scans though...

Delete operation is slow and rebuilding index doesn't seems to solve the issue

Right. So your problem is not the deletion of records, which is instantaneous. (84 rows). The problem is the scan on the WholesalerSale table afterwards.

My guess would be, that ImportSale.Id is a foreign key in WholesalerSale, and that SqlServer simply validates that you haven't deleted a referenced key.

Solution is to index your foreign key column in WholesalerSale to speed up this check.

CREATE INDEX IX_WholesalerSale_ImportSaleId ON WholesalerSales (ImportSaleId);

---

Related Topics