Dan Guzman Blog

Move a Partition to a Different File Group Efficiently

SQL Server table partitioning can reduce storage costs associated with large tables while maintaining performance SLAs.  Table partitioning, available in Enterprise and above SKUs, allows you to keep frequently used current data on fast storage while storing infrequently accessed older data on slower, less expensive storage.  But moving vast amounts of data efficiently as data ages can be a challenge.  This post will discuss alternate techniques to accomplish this task./o:p

Consider the scenario of a table partitioned on a datetime column by month.  Your objective is to keep recent (current and prior month) data on a solid state disk and older data on traditional spinning media.  2 filegroups are used for this table, one with files on a solid state device and the other with files on spinning disks.  The table is partitioned with a RANGE RIGHT partition function (inclusive date boundary) and monthly sliding window maintenance is scheduled to create a partition for the new month and perhaps remove the oldest month.  Every month after the slide, you want to move an older partition (prior month minus 1) from fast to slow storage to make room for new data on the fast file group./o:p

The Simple Method/o:p

The easiest way to move a partition from the NewerData file group to the OlderData filegroup is with MERGE and SPLIT.  The example below will move the February partition from the NewerData to the OlderData filegroup:  /o:p

Simple maintenance script example:/o:p

– Monthly Partition Move Scipt/o:p

– merge month to be moved into prior month partition/o:p

ALTER PARTITION FUNCTION PF_Last12Months()/o:p

MERGE RANGE ('20110201');/o:p

 /o:p

– set partition scheme next used to the OlderData filegroup/o:p

ALTER PARTITION SCHEME PS_Last12Months/o:p

NEXT USED OlderData;/o:p

 /o:p

– move data from NewData to OlderData filegroup/o:p

ALTER PARTITION FUNCTION PF_Last12Months()/o:p

SPLIT RANGE ('20110201');/o:p

 /o:p

The figures below show the partitions before and after this script was run against a 10M row test table (setup script with complete DDL and sample data at the end of this post).  Although this method is quite easy, it can take quite a bit of time with large partitions.  This MERGE command will merge February data into the January partition on the OlderData filegroup, requiring all of February’s data to be moved in the process, and then remove the February partition.  The SPLIT will then create a new February partition on the OlderData filegroup, move February data to the new partition and finally remove the February data from the source partition.  So February data is actually moved twice, once by the MERGE and again by the SPLIT.  /o:p

This MERGE/SPLIT process took 52 seconds on my test system with a cold buffer cache but I was only moving 738,780 rows.  Think about the performance impact of this method against a much larger production table partition.  The atomic MERGE and SPLIT are offline operations so the entire table is unavailable while those statements are running.  Also, these operations are resource intensive when a lot of data needs to be moved and/or you have many indexes./o:p

Before maintenance:/o:p

Rows/o:p

Partition Number/o:p

Filegroup/o:p

Lower Boundary/o:p

Upper Boundary/o:p

0/o:p

1/o:p

PartitioningDemo_OlderData/o:p

 /o:p

4/1/2010 12:00:00 AM/o:p

791,549/o:p

2/o:p

PartitioningDemo_OlderData/o:p

4/1/2010 12:00:00 AM/o:p

5/1/2010 12:00:00 AM/o:p

817,935/o:p

3/o:p

PartitioningDemo_OlderData/o:p

5/1/2010 12:00:00 AM/o:p

6/1/2010 12:00:00 AM/o:p

791,550/o:p

4/o:p

PartitioningDemo_OlderData/o:p

6/1/2010 12:00:00 AM/o:p

7/1/2010 12:00:00 AM/o:p

817,935/o:p

5/o:p

PartitioningDemo_OlderData/o:p

7/1/2010 12:00:00 AM/o:p

8/1/2010 12:00:00 AM/o:p

817,935/o:p

6/o:p

PartitioningDemo_OlderData/o:p

8/1/2010 12:00:00 AM/o:p

9/1/2010 12:00:00 AM/o:p

791,550/o:p

7/o:p

PartitioningDemo_OlderData/o:p

9/1/2010 12:00:00 AM/o:p

10/1/2010 12:00:00 AM/o:p

817,935/o:p

8/o:p

PartitioningDemo_OlderData/o:p

10/1/2010 12:00:00 AM/o:p

11/1/2010 12:00:00 AM/o:p

791,550/o:p

9/o:p

PartitioningDemo_OlderData/o:p

11/1/2010 12:00:00 AM/o:p

12/1/2010 12:00:00 AM/o:p

817,935/o:p

10/o:p

PartitioningDemo_OlderData/o:p

12/1/2010 12:00:00 AM/o:p

1/1/2011 12:00:00 AM/o:p

817,935/o:p

11/o:p

PartitioningDemo_OlderData/o:p

1/1/2011 12:00:00 AM/o:p

2/1/2011 12:00:00 AM/o:p

738,780/o:p

12/o:p

PartitioningDemo_NewerData/o:p

2/1/2011 12:00:00 AM/o:p

3/1/2011 12:00:00 AM/o:p

817,935/o:p

13/o:p

PartitioningDemo_NewerData/o:p

3/1/2011 12:00:00 AM/o:p

4/1/2011 12:00:00 AM/o:p

369,476/o:p

14/o:p

PartitioningDemo_NewerData/o:p

4/1/2011 12:00:00 AM/o:p

5/1/2011 12:00:00 AM/o:p

0/o:p

15/o:p

PartitioningDemo_NewerData/o:p

5/1/2011 12:00:00 AM/o:p

 /o:p

 /o:p

After maintenance:/o:p

Rows/o:p

Partition Number/o:p

Filegroup/o:p

Lower Boundary/o:p

Upper Boundary/o:p

0/o:p

1/o:p

PartitioningDemo_OlderData/o:p

 /o:p

4/1/2010 12:00:00 AM/o:p

791,549/o:p

2/o:p

PartitioningDemo_OlderData/o:p

4/1/2010 12:00:00 AM/o:p

5/1/2010 12:00:00 AM/o:p

817,935/o:p

3/o:p

PartitioningDemo_OlderData/o:p

5/1/2010 12:00:00 AM/o:p

6/1/2010 12:00:00 AM/o:p

791,550/o:p

4/o:p

PartitioningDemo_OlderData/o:p

6/1/2010 12:00:00 AM/o:p

7/1/2010 12:00:00 AM/o:p

817,935/o:p

5/o:p

PartitioningDemo_OlderData/o:p

7/1/2010 12:00:00 AM/o:p

8/1/2010 12:00:00 AM/o:p

817,935/o:p

6/o:p

PartitioningDemo_OlderData/o:p

8/1/2010 12:00:00 AM/o:p

9/1/2010 12:00:00 AM/o:p

791,550/o:p

7/o:p

PartitioningDemo_OlderData/o:p

9/1/2010 12:00:00 AM/o:p

10/1/2010 12:00:00 AM/o:p

817,935/o:p

8/o:p

PartitioningDemo_OlderData/o:p

10/1/2010 12:00:00 AM/o:p

11/1/2010 12:00:00 AM/o:p

791,550/o:p

9/o:p

PartitioningDemo_OlderData/o:p

11/1/2010 12:00:00 AM/o:p

12/1/2010 12:00:00 AM/o:p

817,935/o:p

10/o:p

PartitioningDemo_OlderData/o:p

12/1/2010 12:00:00 AM/o:p

1/1/2011 12:00:00 AM/o:p

817,935/o:p

11/o:p

PartitioningDemo_OlderData/o:p

1/1/2011 12:00:00 AM/o:p

2/1/2011 12:00:00 AM/o:p

738,780/o:p

12/o:p

PartitioningDemo_OlderData/o:p

2/1/2011 12:00:00 AM/o:p

3/1/2011 12:00:00 AM/o:p

817,935/o:p

13/o:p

PartitioningDemo_NewerData/o:p

3/1/2011 12:00:00 AM/o:p

4/1/2011 12:00:00 AM/o:p

369,476/o:p

14/o:p

PartitioningDemo_NewerData/o:p

4/1/2011 12:00:00 AM/o:p

5/1/2011 12:00:00 AM/o:p

0/o:p

15/o:p

PartitioningDemo_NewerData/o:p

5/1/2011 12:00:00 AM/o:p

 /o:p

 /o:p

SWITCH and DROP_EXISTING Method/o:p

An alternative to the method above is to employ SWITCH along with the DROP EXISTING option of CREATE INDEX.  As you may know, SWITCH of an aligned partition is a metadata-only operation and is very fast because no physical data movement is required.  Furthermore, CREATE INDEX…WITH DROP_EXISTING = ON avoids sorting when the existing table index is already suitably sorted and is especially appropriate for improving performance of large index rebuilds.  Using these commands, instead of relying on SPLIT and MERGE to move data, will greatly reduce the time needed to move a partition from one filegroup to another.  The maintenance script below reduced the time of the partition move from 52 seconds down to 7 seconds, reducing maintenance time by over 85% compared to the MERGE/SPLIT script above.  /o:p

Demo Maintenance Script/o:p

– Monthly Partition Move Scipt/o:p

DECLARE @MonthToMove datetime = '20110201';/o:p

 /o:p

– create staging table on NewerData filegroup with aligned indexes/o:p

IF OBJECT_ID(N'dbo.PartitionMoveDemoStaging') IS NOT NULL /o:p

      DROP TABLE dbo.PartitionMoveDemoStaging;/o:p

CREATE TABLE dbo.PartitionMoveDemoStaging(/o:p

      PartitioningDateTimeColumn datetime NOT NULL/o:p

      ,Column1 bigint NOT NULL/o:p

) ON PartitioningDemo_NewerData;/o:p

 /o:p

CREATE CLUSTERED INDEX cdx_PartitionMoveDemoStaging_PartitioningColumn /o:p

      ON dbo.PartitionMoveDemoStaging(PartitioningDateTimeColumn) /o:p

      ON PartitioningDemo_NewerData;      /o:p

 /o:p

CREATE NONCLUSTERED INDEX idx_PartitionMoveDemoStaging_Column1 /o:p

      ON dbo.PartitionMoveDemoStaging(Column1) /o:p

      ON PartitioningDemo_NewerData;      /o:p

 /o:p

– switch partition into staging table/o:p

ALTER TABLE dbo.PartitionMoveDemo/o:p

      SWITCH PARTITION $PARTITION.PF_Last12Months(@MonthToMove)/o:p

      TO dbo.PartitionMoveDemoStaging;/o:p

 /o:p

– remove partition/o:p

ALTER PARTITION FUNCTION PF_Last12Months()/o:p

      MERGE RANGE (@MonthToMove);/o:p

      /o:p

– set next used to OlderData filegroup/o:p

ALTER PARTITION SCHEME PS_Last12Months/o:p

      NEXT USED PartitioningDemo_OlderData;/o:p

 /o:p

– recreate partition on OlderData filegroup/o:p

ALTER PARTITION FUNCTION PF_Last12Months()/o:p

      SPLIT RANGE (@MonthToMove);/o:p

      /o:p

– recreate staging table indexes using the partition scheme/o:p

– this will move the staging table to OlderData filegroup with aligned indexes/o:p

CREATE CLUSTERED INDEX cdx_PartitionMoveDemoStaging_PartitioningColumn/o:p

      ON dbo.PartitionMoveDemoStaging(PartitioningDateTimeColumn) /o:p

      WITH (DROP_EXISTING = ON)/o:p

      ON PS_Last12Months(PartitioningDateTimeColumn);/o:p

      /o:p

CREATE NONCLUSTERED INDEX idx_PartitionMoveDemoStaging_Column1 /o:p

      ON dbo.PartitionMoveDemoStaging(Column1) /o:p

      WITH (DROP_EXISTING = ON)/o:p

      ON PS_Last12Months(PartitioningDateTimeColumn);/o:p

 /o:p

– switch staging table back into primary table partition/o:p

ALTER TABLE dbo.PartitionMoveDemoStaging/o:p

      SWITCH PARTITION $PARTITION.PF_Last12Months(@MonthToMove)/o:p

      TO dbo.PartitionMoveDemo PARTITION $PARTITION.PF_Last12Months(@MonthToMove);/o:p

 /o:p

The maintenance steps here are similar to the first method except that the partition is SWITCHed into a staging table before the MERGE and SPLIT.  This way, no data movement is needed during the MERGE or SPLIT.  After the MERGE and SPLIT, staging table indexes are recreated using the same partition scheme as the primary table.  This will move the staging table from the NewerData to the OlderData filegroup and ensure staging table indexes are aligned for the SWITCH.  The DROP_EXISTING = ON option allows the CREATE INDEX to leverage the existing staging table index sequence, thus eliminating the need to sort the index keys.  Finally, the staging table is SWITCHed back into the moved partition./o:p

I hope you find this method useful.  Below is the script I used to create the demo database and objects.  /o:p

Demo Setup Script/o:p

–create database with monthly filegroups/o:p

CREATE DATABASE PartitioningDemo/o:p

ON(/o:p

      NAME='Primary', /o:p

      FILENAME='S:\SolidState\PartitioningDemo.mdf', /o:p

      SIZE=10MB),/o:p

FILEGROUP NewerData (/o:p

      NAME='PartitioningDemo_NewerData', /o:p

      FILENAME='S:\SolidState\PartitioningDemo_NewerData.ndf', /o:p

      SIZE=400MB,/o:p

      FILEGROWTH=10MB), /o:p

FILEGROUP OlderData (/o:p

      NAME='PartitioningDemo_OlderData', /o:p

      FILENAME='D:\SpinningDisks\PartitioningDemo_OlderData.ndf', /o:p

      SIZE=600MB,/o:p

      FILEGROWTH=10MB)/o:p

LOG ON(/o:p

      NAME='PartitioningDemo_Log', /o:p

      FILENAME='L:\LogFiles\PartitioningDemo_Log.ldf', /o:p

      SIZE=10MB,/o:p

      FILEGROWTH=10MB);/o:p

      /o:p

ALTER DATABASE PartitioningDemo/o:p

      SET RECOVERY SIMPLE;/o:p

GO/o:p

 /o:p

USE PartitioningDemo;/o:p

 /o:p

CREATE PARTITION FUNCTION PF_Last12Months( datetime )/o:p

AS RANGE RIGHT/o:p

FOR VALUES/o:p

(               – older_than_current_minus_12/o:p

      '20100401'  – current_minus_12/o:p

      ,'20100501' – current_minus_11/o:p

      ,'20100601' – current_minus_10/o:p

      ,'20100701' – current_minus_9/o:p

      ,'20100801' – current_minus_8/o:p

      ,'20100901' – current_minus_7/o:p

      ,'20101001' – current_minus_6/o:p

      ,'20101101' – current_minus_5/o:p

      ,'20101201' – current_minus_4/o:p

      ,'20110101' – current_minus_3/o:p

      ,'20110201' – current_minus_2/o:p

      ,'20110301' – current_minus_1/o:p

      ,'20110401' – current/o:p

      ,'20110501' – future/o:p

);/o:p

 /o:p

CREATE PARTITION SCHEME PS_Last12Months/o:p

AS PARTITION PF_Last12Months/o:p

TO/o:p

      (/o:p

      OlderData,/o:p

      OlderData,/o:p

      OlderData,/o:p

      OlderData,/o:p

      OlderData,/o:p

      OlderData,/o:p

      OlderData,/o:p

      OlderData,/o:p

      OlderData,/o:p

      OlderData,/o:p

      OlderData,/o:p

      NewerData, – minus 2 month (to be moved to OlderData) /o:p

      NewerData, – minus 1 month/o:p

      NewerData, – current month /o:p

      NewerData  – future month+/o:p

      );/o:p

 /o:p

– create table with 10,000,000 rows/o:p

ALTER DATABASE PartitioningDemo/o:p

      MODIFY FILEGROUP NewerData DEFAULT;/o:p

 /o:p

WITH/o:p

      t1 AS (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2/o:p

                            UNION ALL SELECT 3 UNION ALL SELECT 4/o:p

                            UNION ALL SELECT 5 UNION ALL SELECT 6/o:p

                              UNION ALL SELECT 7 UNION ALL SELECT 8/o:p

                              UNION ALL SELECT 9),/o:p

      t2 AS (SELECT a.n /o:p

                    FROM t1 a, t1 b, t1 c, t1 d, t1 e, t1 f, t1 g)/o:p

SELECT/o:p

      ISNULL(/o:p

            DATEADD(/o:p

                  day/o:p

                  , (ROW_NUMBER() OVER(ORDER BY t2.n))/26385, '20100401')/o:p

                  , '20100401') AS PartitioningDateTimeColumn/o:p

      ,ISNULL((ROW_NUMBER() OVER(ORDER BY t2.n)), 0) AS Column1/o:p

INTO dbo.PartitionMoveDemo/o:p

FROM t2;/o:p

 /o:p

– create indexes partitioned indexes on table/o:p

CREATE CLUSTERED INDEX cdx_PartitionMoveDemo_PartitioningColumn/o:p

      ON dbo.PartitionMoveDemo(PartitioningDateTimeColumn) /o:p

      ON PS_Last12Months(PartitioningDateTimeColumn);/o:p

      /o:p

CREATE NONCLUSTERED INDEX idx_PartitionMoveDemo_Column1 /o:p

      ON dbo.PartitionMoveDemo(Column1) /o:p

      ON PS_Last12Months(PartitioningDateTimeColumn);/o:p

GO/o:p

Legacy Comments


POTUS
2011-06-10
re: Move a Partition to a Different File Group Efficiently
Why in this example did you create the nonclustered indexes on the staging table? I understand the requirement for the clustered index, but why the NC?

selvakumar
2012-07-03
re: Move a Partition to a Different File Group Efficiently
Excellent and very descriptive article.I had a similar use case and I could implement it successfully after going through the article.

Anil
2012-07-10
re: Move a Partition to a Different File Group Efficiently
I have tried this approach but unfortunately its not working for me, I am getting error saying “My staging table is having different file group and partition is associated with different file group” if both staging and partition is based on same file group then its working….but my requirement is to change the file group of particular partition. Pls help