How to delete data in large size SQL Server faster?

Question

I have a SQL Server database. The size is about 150GB which saves some data for analysis. Each day, new data comes in and we need to delete old data (based on date). Recently, the daily data size increase a lot, it will be about 8-9GB per day soon.

Currently, we delete in small batch, which takes a very long time to finish. Is there a general guide to make it faster? Tried to drop/disable index before delete, after delete finished, then rebuild index. It does not help much.

Or, this will totally depend on the actual date?

Thanks

This is not your answer just for more info: TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause:both remove all rows in the table.But TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log msdn.microsoft.com/en-us/library/aa260621(SQL.80).aspx — Reza
– Reza, Commented Mar 31, 2014 at 22:47
We could help you a lot more if you could provide more concrete information. Specifically, the command that you are using to do the delete it's Query Plan (Actual) . — RBarryYoung
– RBarryYoung, Commented Apr 1, 2014 at 0:20

jods · Accepted Answer · 2014-03-31 22:43:34Z

2

Given the amount of data I would use a partitioned table, one for each day.

Swapping partitions in and out is going to be the fastest way to delete all data for one day.

EDIT: since truncating a partition is not as trivial as it should be in SQL Server, I figured I'd provide more details, in case you're not familiar with partitions.

In the next release of SQL Server, you should be able to just TRUNCATE PARTITION or something like that. In the meantime you have to proceed as follows:

The quickest way to delete a day of data in your database is to have the table partitioned by day and then:

Swap out the partition that you want to delete to another table: ALTER TABLE partitioned SWAP PARTITION n TO otherTableToDelete.
TRUNCATE TABLE otherTableToDelete.

edited Mar 31, 2014 at 22:43

answered Mar 31, 2014 at 22:26

jods

4,59118 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to delete data in large size SQL Server faster?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related