[ACCEPTED]-DELETE performance in SQL Server on clustered index, large table-clustered-index

Accepted answer
Score: 24

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

Score: 10

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

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

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

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

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

Score: 1

Also try deleting data in a batch. Example

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


More Related questions