Solved

Table Partitioning with Sliding Window

Posted on 2010-08-31
1
1,536 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

751 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