Optimizing Delete on SQL Server
sql, sql-server
Solution
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.
Problem
`Delete`s on sql server are sometimes slow and I've been often in need to optimize them in order to diminish the needed time. I've been googleing a bit looking for tips on how to do that, and I've found diverse suggestions. I'd like to know your favorite and most effective techinques to tame the delete beast, and how and why they work. until now: be sure foreign keys have indexes be sure the where conditions are indexed use of `WITH ROWLOCK` destroy unused indexes, delete, rebuild the indexes now, your turn.