Solved

Table Partitioning with Sliding Window

Posted on 2010-08-31
1
1,510 Views
Last Modified: 2012-05-10
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
Comment
Question by:dba2dba
1 Comment
 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 500 total points
ID: 33567673
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql query 7 36
C# Application Local DB Connection String 23 61
Database connection opened on a machine 8 36
Update in Sql 7 0
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now