I need to delete about 75+ million rows from a table everyday that contains around 3.5 billions of record.

Database recovery mode is simple, I have writen a code that deletes 15.000 rows in a while condition until all 75M records is deleted. (i use batch delete due to log file grow) However, with current deletion speed it looks like it will take at least 5 days, which means that amount of data required to be deleted is multiply faster than my deletion speed.

Basically what i'm trying to do is summarizing (in another table) and deleting data older than 2 months. There is no update operation in that table, only insert and delete.

I have an enterprise edition of MSSQL 2017

Any suggestions will be welcome.

Table definition: (for giving you clear view)

Table has 3 indexes, clustered bigint Id index, non-clustered (Datetime + bit + int) index and non clustered columnstore index that contains every column(this required by ui application), Straight forward way looks like creating partition scheme over CreateDate column that creates partitions for every day. Then drop that partition when its data marked to be deleted. However with these approach i have encountered major performance impact, almost entire system crashed.

  • 3
    Have you looked into partitioning? You could drop the older data in seconds. – LowlyDBA - John McCall Jun 24 at 20:15
  • I have tried, however application encountered numerous errors i suspected it is because of columnstore index that table has. – helloEveryone Jun 24 at 20:20
  • I may have convince customer to summerize all existing data then truncate table, then start partitioning, however i tried that 2 months ago, but encountered lots of timeouts even with few data. I may be messed up indexes, but If you suggest that there is no other feasible way, where i could get help about partitioning? Can you recommend me good sources? – helloEveryone Jun 24 at 20:25
  • This and this are good starting points. – Aaron Bertrand Jun 24 at 20:40

I've had to do this a couple of times and this is my preferred method for transforming a very large table into a partitioned one with minimal downtime. It does however, rely on the fact that you are fine with waiting a few days/weeks for it to complete the process (don't panic - you will understand why it takes this long later on):

  1. Create a new PARTITION FUNCTION if you do not have an existing one you can use.
  2. Create a new PARTITION SCHEME if you do not have an existing one you can use.
  3. Create a new PARTITIONED TABLE that will contain all new data.
  4. Add a CHECK CONSTRAINT to the new table to ensure that no data with a date older than X is added.
  5. Add a CHECK CONSTRAINT to the old table to ensure that no data with a date newer than X is added.
  6. RENAME THE EXISTING TABLE to something to identify that it is old/archived data.
  7. CREATE A PARTITIONED VIEW that combines the old and new tables with the correct name.
  8. Allow the existing process of adding & removing data to continue.

All newly inserted data will be added to your newly partitioned table while the deletes will act upon the older table. Over time as the data is cleared out of the older table the deletes will become faster and have less of an impact on your system. Based on your estimate of 75 million rows per day, it will take about 6 weeks to get to the point where all new actions are performed against the partitioned table.

During this time you can develop a PARTITION SWITCHING strategy to use when deleting data from the newly partitioned table.

Once all of the data has been removed from the old table, you can DROP THE VIEW, DROP THE OLD TABLE and then rename your partitioned table correctly.

I have found this approach to be the least disruptive to a production environment because you never actually go back and partition the existing data - if you are happy and are permitted to wait the required amount of time. If it is a matter of urgency, then other approaches will require downtime while you partition the existing table.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.