Table partitioning for archiving data

Question

Scenario:

• two databases: DB_A and DB_Archive with one very big table called tableA.
• every day, records older than 60 days are deleted from DB_A and moved to DB_Archive mainly to leave thing "separated" because tableA is heavily queried on DB_A for records of the past 2 months.

I want to get rid of this process because it is slow and consumes a lot of resources. I'm thinking of implementing table partitioning on DB_A with a partition function on a date column and storing all records < 2 month on one partition and all records > 2 months on another partition. My questions:

• is this scenario going to behave like if I had 2 different databases? If I query my tableA for records > getdate() - 30, is it going to read the archiving partition?
• I supposed I have to partition the indexes as well, right?
• How do I deal with the fact that tomorrow my partition function will "change", I mean, if I create the function today (2nd of July, its range will be 2nd of May, but tomorrow would be the 3rd of May). Can I create a dynamic partition function?

Answer 1

With partitioning you would have to do a partition per day, which puts the Pre-SQL 2012 limit of 1000 paritions in a new perspective as it would only allow for 3 years archive. With SQL Server 2012 you get 15000 partitions which is plenty for 1 partition per day.

Every day you would add a new partition. If you want to move the 61st past day partition you can do it efficiently, but is still an offline operation. See Move a Partition to a Different File Group Efficiently.

All your indexes would have to be aligned, see Special Guidelines for Partitioned Indexes.

Buying into partitioning is not an easy decission and it may be quite a big bite to chew... see How To Decide if You Should Use Table Partitioning. Specifically you should not expect performance improvements from partitioning. You should approach performance problems on time seriest by clustering by datetime.

Answer 2

I don't know if the partition function can be dynamic but I doubt it. Some options for you without going that route:

1 - Partition on calendar DATE and move off the oldest partition each day

2 - Create a view that filters on date, and point all your existing queries there (this can be easily managed by renaming the underlying table to something else and naming the view what the current table's name is). This can be optimized as well with index changes.

Bear in mind that the first option above will work a LOT better if you use the date field in your queries. If you don't it'll still be quicker than the current process but queries won't have a huge improvement. Partitioning in general works best if you can filter on your partition field and the optimizer knows which partition to look at.

Answer 3

Here's what should work for you: DB_A - tableA with a different partition for each of the last 60 days - stagingTable to move data from oldest partition

DB_Archive tableA - stores all data older than 60 days. (not partitioned)

Process: 1. before end of day: alter partition function - split range to add a new partition for the new day. (NB: instead of creating partitions for "today's date + 1 day" you may want to be a few steps ahead. eg: "today's date + 5 day"

1. After end of each day, you first switch oldest partition in DB_A.tableA to DB_A.stagingTable; Merge the oldest partitions.

2. Import data from DB_A.stagingTable to DB_Archive.tableA. Finally trunacte DB_A.stagingTable

The above is called Rolling Window and is a pretty common scenario for VLDB's. See this white paper by microsoft on partitioning: Partition table and index strategies or try this specifically on Sliding Window scenario

Answer 4

You can use dynamic approach of archiving and purging data in SQL Server. Please follow below link for that.

http://www.sqlscientist.com/2012/09/auto-maintain-archival-process.html