Recommendations about deleting large set of rows in MSSQL 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.
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):

Create a new PARTITION FUNCTION if you do not have an existing one you can use.
Create a new PARTITION SCHEME if you do not have an existing one you can use.
Create a new PARTITIONED TABLE that will contain all new data.
Add a CHECK CONSTRAINT to the new table to ensure that no data with a date older than X is added.
Add a CHECK CONSTRAINT to the old table to ensure that no data with a date newer than X is added.
RENAME THE EXISTING TABLE to something to identify that it is old/archived data.
CREATE A PARTITIONED VIEW that combines the old and new tables with the correct name.
Allow the existing process of adding &amp; 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.