March 15, 2012 at 2:54 pm
I have a table with millions of rows in it that a previous DBA partitioned about 3 years ago. This table has 3 partitions in it,
1. 2007 500000 rows
2. 2008 1.5M rows
3 2009-Current 300M rows
I've been tasked with updating the partitioning on this table and I'd like to keep it current from here going forward.
I'd like to leave the existing partitions in place and start allocating new partitions on the end of the table.
In the example above I'd like to take whatever the current ID is in that table and move out about 10000 and put a new partition in front of the table so that when the 10001th row gets inserted it gets inserted into the new partition and then keep doing this every 6 months. This way I don't have to split up the existing table since it's not important but get the benefits of the new smaller partitions from an index rebuild and query standpoint.
I have about 20 tables that need this done to them and I only have about a 2 hour window per table so i'm struggling with the Split function because it appears to be taking all the data in the 3rd partition and moving it into the TempDB and then moving thre results back into the new partitions. Is there a better answer to this? Ideally I just want to move the new partition in there and since it's left boundary is greater than the max ID it shouldn't be moving any data but that's not what i'm seeing.
Any ideas?
March 15, 2012 at 3:12 pm
My first concern is that you are talking about partitioning on ID, but I assume from your previous partition naming that it is actually partitioned on date. So which one is it?
Jared
CE - Microsoft
March 16, 2012 at 10:06 am
We are psedo partitioning by date by getting the maximium ID for a period in time and then partitioning based on that. This is due to 80% of the queries on the table are by ID so we're aligning the partitioning to the most common way the tables are queried. (Hopefully for performance)
Does that make sense?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply