I have around 10 billion system (infrastructure) data in SQL Server of last 5 years, and I want to purge that data incrementally on every weekend, same time other jobs also running which doing maintenance and inserting data of that particular day into warehouse.
The purging process will delete about 600,000 records every weekend but it will scan entire data warehouse for it to take out these 600,000 records.
Please suggest an optimal way of doing this.
Partition switching in SQL Server enables you to swap chunks of data in/out of your table pretty much instantaneously. There are numerous good tutorials, here is one: https://www.brentozar.com/archive/2013/01/sql-server-table-partitioning-tutorial-videos-and-scripts/
A partition in SQL Server physically separates a table based on the partition scheme, most commonly using a date range. So swapping partitions in/out of your table is more of a logical switch than a physical switch, and therefore incredibly fast.
If you don't have SQL Server Enterprise Edition, you should make sure the field you're using to identify records to delete is indexed. With large deletes, breaking the delete up can improve performance, so perhaps you create a loop that deletes 1 day at a time, 7 times, instead of the entire week at once.
I would take an incremental approach as mentioned in the article:
http://sqlperformance.com/2013/03/io-subsystem/chunk-deletes
The number of records to delete per batch is configurable. You'll want to test different sizes in a test environment to get the optimal size for your data set.
