Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1573
  • Last Modified:

Table Partitioning with Sliding Window

I am working on a Sliding Window Data Partitioning Scenario based on date column. The table now has 40M recs and expected to increase by 20M each month.

I want to have 12 Partitions for the latest 12 weeks and all the remaining in 1 historic partition.

With every new week, the oldest week should merge with the historic partition and I wish to create a new partition for the coming week.

I see an excellent example in the link:
http://blogs.msdn.com/b/menzos/archive/2008/06/30/table-partitioning-sliding-window-case.aspx

However, I want to implement the partitions to multiple file groups ( With different underlying files). In the article, it is done in Primary file group only

I have few questions:

1) When I merge the 12 week partition with historic partition, Since they are in different file groups how does it actually work. I see that merge is only a meta data operation and not a data movement operation. Can we merge partitions in different partitions.

2) Is there any clean up required since I am intending to merge the 12 week partition with historic partition. What happens to the 12 week file and filegroup.

3)  If I want to proceed with the way I explained, Looks like I need to create 1 file and 1 filegroup per every week which I am not interested in. Is there a better way to handle this. Can I place all the 2 months partitions (8 of them)in a single file group. Do you think I will be able to get perf. benifits.

4) Do we need to have each partition in a seperate file group. It may be an ideal case, but difficult to manage. Can you suggest a better way to do this.

Please suggest me any improvements to this approach or share some examples if you have.

Thanks,
0
dba2dba
Asked:
dba2dba
1 Solution
 
Racim BOUDJAKDJICommented:
Based on the volume you describe, I recommend the following:

> Create a table Table1_CURRENT_MONTH with 4 partitions (1 for each week), hosted on fast drive array (Ex: RAID 10, RAID 0+1)
> Create a table Table1_ARCHIVE with 12 or more partitions (1 for each month), hosted on a slower but roomy drive with fewer or no updates (RAID 1, RAID 5).  Ideally put the FILEGROUP in READ ONLY mode
> Create a UNION view called v_Table1_TOTAL between Table_CURRENT_MONTH and Table_ARCHIVE.  Important : Your SELECT statement should *only* point out to the UNION view (not to the underlyingtables)
> Using the ALTER SWITCH statement, set up a monthly partitionning pruning maintainance task, starting in the beginning of each month, to transfer all data from Table1_CURRENT_MONTH to Table1_ARCHIVE.  With 20 millions rows and a MAXDOP at maximum, it should take no more than 1 hour to transfer all data and leave you with  a clean Table1_CURRENT_MONTH.

For more info about how that works, please read the following article which explains the maintainance..

http://msdn.microsoft.com/en-us/library/ms191174.aspx

Hope this helps...
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now