Optimizing Delete on SQL Server

Solution 1:

The following article, Fast Ordered Delete Operations may be of interest to you.

Performing fast SQL Server delete operations

The solution focuses on utilising a view in order to simplify the execution plan produced for a batched delete operation. This is achieved by referencing the given table once, rather than twice which in turn reduces the amount of I/O required.

Solution 2:

I have much more experience with Oracle, but very likely the same applies to SQL Server as well:

• when deleting a large number of rows, issue a table lock, so the database doesn't have to do lots of row locks
• if the table you delete from is referenced by other tables, make sure those other tables have indexes on the foreign key column(s) (otherwise the database will do a full table scan for each deleted row on the other table to ensure that deleting the row doesn't violate the foreign key constraint)

Solution 3:

I wonder if it's time for garbage-collecting databases? You mark a row for deletion and the server deletes it later during a sweep. You wouldn't want this for every delete - because sometimes a row must go now - but it would be handy on occasion.

Solution 4:

Summary of Answers through 2014-11-05

This answer is flagged as community wiki since this is an ever-evolving topic with a lot of nuances, but very few possible answers overall.

The first issue is you must ask yourself what scenario you're optimizing for? This is generally either performance with a single user on the db, or scale with many users on the db. Sometimes the answers are the exact opposite.

For single user optimization

• Hint a TABLELOCK
• Remove indexes not used in the delete then rebuild them afterward
• Batch using something like SET ROWCOUNT 20000 (or whatever, depending on log space) and loop (perhaps with a WAITFOR DELAY) until you get rid of it all (@@ROWCOUNT = 0)
• If deleting a large % of table, just make a new one and delete the old table
• Partition the rows to delete, then drop the parition. [Read more...]

For multi user optimization

• Hint row locks
• Use the clustered index
• Design clustered index to minimize page re-organization if large blocks are deleted
• Update "is_deleted" column, then do actual deletion later during a maintenance window

For general optimization

• Be sure FKs have indexes on their source tables
• Be sure WHERE clause has indexes
• Identify the rows to delete in the WHERE clause with a view or derived table instead of referencing the table directly. [Read more...]