locked
Add new Partition to Table Without Split RRS feed

  • Question

  • I have an extremely large table with over 2 Billion records and 1.4TB of data.  It is currently split into 5 partitions.  I now need to add a new partition, because the last partition is getting too big - it is much larger than the others.  The ranges go by date values, with a partition for every year.  The last partition contains 2 and a half years of data.  I would like to continue the partitioning scheme by adding new files, and new ranges to the function.

    My concern is the amount of data involved, and if i use the SPLIT function, than i will run into log growth and space issues.  I've also considered creating a new function and then a new table on that function and spilling all the data into that new table.  Again, we are talking a lot of data.

    Any better ideas?

    Thanks

    Friday, June 20, 2014 8:50 PM

Answers

  • You are correct that splitting a non-empty partition can take considerable time and logging if a lot of data needs to be moved.  I suggest the technique below, which assumes a clustered index and aligned non-clustered indexes.  The key here is moving data to the new filegroups using CREATE INDEX...WITH DROP_EXISTING (which avoids a sort) and SWITCH to move data back into the main table.

    1. create a staging table exactly like the source table using the same partition scheme
    2. switch the last partition to the staging table
    3. create a temporary partition function with same boundaries as your existing function plus boundaries for the additional years
    4. create a temporary partition scheme with same filegroups as your existing scheme plus filegroups for the new partitions
    5. for each index, execute a CREATE INDEX...WITH DROP_EXISTING
    6. split the original partition function to create the new boundaries
    7. switch the non-empty staging table partitions back into the main table
    8. update statistics

    Below is a sample script.

    --create staging table using existing partition scheme CREATE TABLE dbo.MyTable_Staging( DateTimeColumn datetime2(0) NOT NULL , IntColumn int NOT NULL ) ON PS_MyTable_Year(DateTimeColumn); ALTER TABLE dbo.MyTable_Staging ADD CONSTRAINT PK_MyTable_Staging PRIMARY KEY( DateTimeColumn , IntColumn ) ON PS_MyTable_Year(DateTimeColumn); GO --switch last partition with multiple years to staging table ALTER TABLE dbo.MyTable SWITCH PARTITION $PARTITION.PF_MyTable_Year('2012-01-01T00:00:00') TO dbo.MyTable_Staging PARTITION $PARTITION.PF_MyTable_Year('2012-01-01T00:00:00'); --create temporary partition function with new boundaries CREATE PARTITION FUNCTION PF_MyTable_Year_Temp(datetime2(0)) AS RANGE RIGHT FOR VALUES( '2009-01-01T00:00:00' ,'2010-01-01T00:00:00' ,'2011-01-01T00:00:00' ,'2012-01-01T00:00:00' ,'2013-01-01T00:00:00' --new boundary ,'2014-01-01T00:00:00' --new boundary ,'2015-01-01T00:00:00'); --new boundary --create temporary partition scheme with new filegroups CREATE PARTITION SCHEME PS_MyTable_Year_Temp AS PARTITION PF_MyTable_Year_Temp TO ( MyDatabase_FG_Archive ,MyDatabase_FG_2009 ,MyDatabase_FG_2010 ,MyDatabase_FG_2011 ,MyDatabase_FG_2012 ,MyDatabase_FG_2013 --new filegroup ,MyDatabase_FG_2014 --new filegroup ,MyDatabase_FG_2015 --new filegroup ); GO --rebuild clustered index on staging table with new scheme and with drop_existing option CREATE UNIQUE CLUSTERED INDEX PK_MyTable_Staging ON dbo.MyTable_Staging ( DateTimeColumn , IntColumn ) WITH (DROP_EXISTING = ON) ON PS_MyTable_Year_Temp(DateTimeColumn); --create new partitions (split partition is empty) ALTER PARTITION SCHEME PS_MyTable_Year NEXT USED MyDatabase_FG_2013; --new year filegroup ALTER PARTITION FUNCTION PF_MyTable_Year() SPLIT RANGE('2013-01-01T00:00:00'); --new year boundary ALTER PARTITION SCHEME PS_MyTable_Year NEXT USED MyDatabase_FG_2014; --new year filegroup ALTER PARTITION FUNCTION PF_MyTable_Year() SPLIT RANGE('2014-01-01T00:00:00'); --new year boundary ALTER PARTITION SCHEME PS_MyTable_Year NEXT USED MyDatabase_FG_2015; --new year filegroup ALTER PARTITION FUNCTION PF_MyTable_Year() SPLIT RANGE('2015-01-01T00:00:00'); --new year boundary GO --switch partitions from staging table back to main table ALTER TABLE dbo.MyTable_Staging SWITCH PARTITION $PARTITION.PF_MyTable_Year('2012-01-01T00:00:00') TO dbo.MyTable PARTITION $PARTITION.PF_MyTable_Year('2012-01-01T00:00:00'); ALTER TABLE dbo.MyTable_Staging SWITCH PARTITION $PARTITION.PF_MyTable_Year('2013-01-01T00:00:00') TO dbo.MyTable PARTITION $PARTITION.PF_MyTable_Year('2013-01-01T00:00:00'); ALTER TABLE dbo.MyTable_Staging SWITCH PARTITION $PARTITION.PF_MyTable_Year('2014-01-01T00:00:00') TO dbo.MyTable PARTITION $PARTITION.PF_MyTable_Year('2014-01-01T00:00:00'); ALTER TABLE dbo.MyTable_Staging SWITCH PARTITION $PARTITION.PF_MyTable_Year('2015-01-01T00:00:00') TO dbo.MyTable PARTITION $PARTITION.PF_MyTable_Year('2015-01-01T00:00:00'); GO DROP TABLE dbo.MyTable_Staging; DROP PARTITION SCHEME PS_MyTable_Year_Temp; DROP PARTITION FUNCTION PF_MyTable_Year_Temp; GO

    UPDATE STATISTICS dbo.MyTable;

    GO



    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Sunday, June 22, 2014 3:43 AM spelling
    • Proposed as answer by Visakh16MVP Sunday, June 22, 2014 7:44 AM
    • Marked as answer by Sofiya Li Wednesday, July 2, 2014 9:26 AM
    Sunday, June 22, 2014 3:42 AM