December 23, 2008 at 5:49 am
I'm designing a partition function with 12 monthly boundaries and one further boundary that is for everything older that 12 months. Here's the SQL to give you an idea of what it looks like:
create partition scheme ps_fact
as partition pf_fact
to
(
FG_Fact_Old--data older than 12 months
, FG_Fact_Jan--data for jan
, ...
, FG_Fact_Dec--data for dec
);
go
So, in January 09 I'll be aging the data in partition 2 by switching data out of partition 2 (data for Jan 08) and moving it to partition 1. This isn't a big problem and I can manage the switch such that I end up with partition 2 in the FG_Fact_Old filegroup.
So now I have partition 1 and 2 in the same filegroup and I want to merge them.
Problem is that using the merge option within the alter partition function takes ages and I was under the impression that if performed between two partitions in the same filegroup it was a fast operation.
I won't bother outlining the steps I take to switch and move the data but you can read up on it in http://www.microsoft.com/downloads/details.aspx?FamilyID=A4139D84-AD2D-4CD5-A463-239C6B7D88C9&displaylang=en. I'm using Method 1 in the Aging data section, around page 18. The only difference been that the method stops at step 12, leaving 2 partitions in the same filegroup, whereas I want to merge those two partitions so that I can re-use it (in a sliding window fashion).
Has anyone had to deal with a similar scenario and am I going about it in the right way?
Thanks,
December 23, 2008 at 8:39 am
I've figured out a quicker of doing what I wanted. So for those that are interested:
I switch partition 1 into a table called [table_old_data]. I then switch partition 2 into a table called [table_12_months_old_data]. Both these tables were created in the same filegroup.
With partitions 1 and 2 now empty I was able to perform the merge, which happened quickly, as you'd expect.
I then performed an "insert into [table_old_data] select ... from [table_12_months_old_data]".
I then switched [table_old_data] into partition 1, which happened very quickly.
Regards,
December 23, 2008 at 8:52 am
That's nice...a good way of working around the slowness.
Just curious, were the filegroups on different drives? I'm not exactly sure how the partitioning works, but if you have the two filegroups on differing physical drives would it attempt to move data over to the first drive, or try to even out the files in the filegroup so that they were using the same physical space?
December 23, 2008 at 9:53 am
Nicholas Cain (12/23/2008)
That's nice...a good way of working around the slowness.Just curious, were the filegroups on different drives? I'm not exactly sure how the partitioning works, but if you have the two filegroups on differing physical drives would it attempt to move data over to the first drive, or try to even out the files in the filegroup so that they were using the same physical space?
In this case I was testing on a test server so the filegroups were all on the same drive but when I move it to production the filegroups will be on different drives. But the basic principle is that it would move the data across drives. Which is why, when you plan to switch partitions you have to create the "switch" table on the same filegroup as the partition you're trying to switch out. And then of course it's up to you to move that data into another filegroup.
December 23, 2008 at 11:33 am
When I've done this I have always had all the partitions in the same filegroup, so it was a simple case to switch out the data, however I always found the merge to take forever. I like your solution.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply