CODEWITHSUNDEEP
sql-serversql-server-2008
urlreader
urlreader

Reputation: 6615

How to delete data in large size SQL Server faster?

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

Upvotes: 1

Views: 205

Answers (1)

jods
jods

Reputation: 4591

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:

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

  2. TRUNCATE TABLE otherTableToDelete.

Upvotes: 2

Related Questions