Deleting rows in database is very costly procedure, if you want to delete lots of them.
I've tried running batch deleting using CHECKPOINT per 50000 rows (takes 3-10s) on a table with 20mln rows, deleting roughly half of it, while the table is in use. While it done the job, it slowed down access to database to a crawl, the whole system stopped responding in a timely manner.
What can I do delete old cruft periodically without slowing down the system too much?
Table in mention has very long rows (hundreds of columns), ordered by time (newest are updated/inserted), we often select newest, but deleted oldest.
Copying filtered rows to temp tables, truncating the table and inserting to freshly-truncated table works much better.