Solved

Table Partitioning with Sliding Window

Posted on 2010-08-31
1
1,523 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Generate Scripts Fails 5 34
I Need a T-SQL Query (Union or Intersect, or...?) 6 38
SELECT query on two levels (detail and summary) 13 47
SqlServer no dupes 25 34
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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

816 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

9 Experts available now in Live!

Get 1:1 Help Now