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.