Solved

Table Partitioning with Sliding Window

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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

632 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