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.
For further reading, consult the Microsoft Whitepaper: How to Implement an Automatic Sliding Window in a Partitioned Table on SQL Server 2005
Can’t Find the Answer Your Looking for?
Do you have a SQL Server question that you can’t find the answer to? Sounds like a great problem to have , let me know about it!
You may also be interested in…………..
- How much memory is each SQL Server database using?
- How to identify the most costly SQL Server queries using DMV’s
- The SQL Server Buffer Pool and Buffer Cache Hit Ratio
- How to become a SQL Server DBA
Did you find this post helpful? Subscribe to the Blog feed and show your support by clicking the Google+ button at the top of the page.
It spreads the word helping our SQL community grow and shows you care :-)
