Performing fast SQL Server delete operations

There have been quite a few queries on the forums this past week with regard to performing fast delete operations on large tables and I wanted to share some of the possible solutions here with you.

Fast ordered delete using a view

“Performing Fast Operations”

To perform a fast ordered delete, take a look at the article Fast Ordered Delete provided by Kevin Stephenson of MySpace and Lubor Kollar, a member of the SQL Server Customer Advisory Team. What makes the article of particular interest is that the client is MySpace. It stands to reason then that the database in question here will be particularly large and provides a good test case for this proof of concept. The crux of the solution presented focuses on replacing a subquery within the where clause, with a view definition that identifies the records to be deleted .

Before:

delete from t1 where a in (select top (10000) a from t1 order by a);

After:

create view v1 as (select top (10000) * from t1 order by a)

delete from v1

You can see the difference and resulting simplification in the exeuction plans produced for these queries in the article Fast Ordered Delete.

This implementation lends itself well to batching delete operations, a technique that is used to limit the transaction log space used by a delete operation and to facilitate more frequent truncation of the transaction log.

Fast ordered delete using a derived table

The previous solution focused on utilising a view in order to simplify the execution plan produced for the query. This was achieved by referencing the given table once, rather than twice which in turn reduces the amount of I/O required. Interestingly, the same goal can be achieved by using a derived table as explained by Simon Sabin in the article Delete Top X rows avoiding a table scan

Working with large database tables

Perhaps one further consideration that is worthy of being raised here is database table design. For example, if during the design process for a database system it becomes apparent that a significant number of delete operations will be required for a given table, it is then likely that the table in question would be a good candidate for SQL Server Partitioning and in particular Sliding Window Partitioning. Such an implementation would allow for the Switching in and out of partitions without adverse performance implications.

About John Sansom

I’m a Microsoft Certified Master(MCM) of SQL Server. I’ve been working with database technology in a variety of flavors for over fifteen years. I absolutely love what I do and genuinely feel privileged to be a part of our tremendous technology community. Got a question about SQL Server or being a DBA? Ask me!