DELETE performance in SQL Server on clustered index, large table

Question

I have a table with more than 20 million rows, and when i do:

DELETE [Table] WHERE ID = ?

It takes over 40 seconds. The ID column is clustered.

Is this what you could expect? or is it possible to optimize this?

Solution 1

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.

Solution 2

This is going to depend on a lot of factors that you don't tell us about...

How many rows are deleted? More rows obviously means more time.

Are there other indexes? Every index needs to get updated, not just the clustered. If you are deleting through 10 indexes it will take about 10x as long (very roughly).

Is there other activity? If there are updates or inserts happening there are very likely waits and contention.

Also very generally speaking, the number of seconds an operation takes is HIGHLY dependent on your hardware setup. If you ran this on a desktop machine vs. a server with a high-performance array and 12 cores the expectations will be very different.

Comments

[Erik Sundström]

The problem was that indexes on relating tables didn't have a index on the foreign key. That made the delete slow. Created indexes on all related tables for the foreign key column, and delete took 0 seconds.

[Yuck]

This improves concurrency by reducing lock time and contention for the table / indexes. It probably does not improve performance though.

[Mike Gledhill]

Yup, adding an index made all the difference with our issue. But, ironically, so did simply running "UPDATE STATISTICS [tableName]". Worth a try.

[Brandon]

Support for setting rowcount for DML commands is going away: Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in a future release of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. For a similar behavior, use the TOP syntax. docs.microsoft.com/en-us/sql/t-sql/statements/…

[Luke Briner]

Thanks! I didn't spot that the table scan was the foreign table, not the table I was deleting from!