Delete millions of records using partition tables?

Question

Every day we write about 1 million records to the sql server table. Records have insertdate and status fields, among others, of course. I need to delete records from time to time in order to free up space on the volume, but leaving records there for the last 4 days. The problem is that the removal takes several hours and a lot of resources.

I am thinking of partition tables setting a section field in insertdate, but I have never used such tables.

How can I achieve the goal using fewer cpu / disk resources, and having a solution, possibly less disadvantages? (I assume that any solution has its drawbacks, but please explain them if you know).

Answer 1

There are two approaches you can take to expedite deletion. One of them is to delete 10,000 rows at a time so that the transaction log does not grow to a huge size. Based on some logic, you continue to delete 10,000 lines, until all lines fulfill the condition, they are deleted. This may, depending on your system, speed up the removal by 100 times.

Another approach is to create a partition in a table. You have to create a section scheme and function, and if all the lines that you delete are in the same section, say, the cost of sales per day, and then deleting the section will delete all the lines in the meta-operation, and this will only take a few seconds. Separation is not difficult, but you need to spend some time to properly configure the rolling window. This is more than an hour, but less than a week later.