DELETE performance in SQL Server on clustered index, large table

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 i开发者_Go百科t possible to optimize this?

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.

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.

Also try deleting data in a batch. Example

set rowcount 10000
delete [table] where id = ? 
while @@rowcount >0
begin
delete [table] where id = ? 
end