Improving delete operations with SQL Server

The SQLCAT team blog has a great tip on deleting rows efficiently to help preventing blocking and minimize log file growth http://sqlcat.com/msdnmirror/archive/2009/05/20/fast-ordered-delete.aspx.

What they recommended is to create a view that selects the top x rows from the table with an order by, then just simply run delete from vw_viewname. As you can see from the post, the IO used is drastically reduced.

For those who don't know how to minimize blocking and log file growth when deleting large number of rows, you delete in small increments in an ordered way. For example, if you have 1 Billion row table and need to delete 5 million rows, you would create that view (you can delete once finished with operation) with the order ASC or DESC depending on order of records your table uses, then delete say 100K records and monitor how your server performs and if any blocking occurs. Also, if you are replicating the table, watch for latency (with replication monitor) and put a time delay in your batch process so that you don't overwhelm your subscribers. In the batch process use a while loop to delete the batch of rows, delay, delete next batch of rows, then put this all in a proc and schedule it with SQL Agent to run when you need it to.

-Chuck Lathrope