Dan Guzman Blog

Move a Partition to a Different File Group Efficiently

by Dan Guzman

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.

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.

The Simple Method

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:  

Simple maintenance script example:

-- Monthly Partition Move Scipt

-- merge month to be moved into prior month partition

ALTER PARTITION FUNCTION PF_Last12Months()

MERGE RANGE ('20110201');

 

-- set partition scheme next used to the OlderData filegroup

ALTER PARTITION SCHEME PS_Last12Months

NEXT USED OlderData;

 

-- move data from NewData to OlderData filegroup

ALTER PARTITION FUNCTION PF_Last12Months()

SPLIT RANGE ('20110201');

 

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. 

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.

Before maintenance:

Rows

Partition Number

Filegroup

Lower Boundary

Upper Boundary

0

1

PartitioningDemo_OlderData

 

4/1/2010 12:00:00 AM

791,549

2

PartitioningDemo_OlderData

4/1/2010 12:00:00 AM

5/1/2010 12:00:00 AM

817,935

3

PartitioningDemo_OlderData

5/1/2010 12:00:00 AM

6/1/2010 12:00:00 AM

791,550

4

PartitioningDemo_OlderData

6/1/2010 12:00:00 AM

7/1/2010 12:00:00 AM

817,935

5

PartitioningDemo_OlderData

7/1/2010 12:00:00 AM

8/1/2010 12:00:00 AM

817,935

6

PartitioningDemo_OlderData

8/1/2010 12:00:00 AM

9/1/2010 12:00:00 AM

791,550

7

PartitioningDemo_OlderData

9/1/2010 12:00:00 AM

10/1/2010 12:00:00 AM

817,935

8

PartitioningDemo_OlderData

10/1/2010 12:00:00 AM

11/1/2010 12:00:00 AM

791,550

9

PartitioningDemo_OlderData

11/1/2010 12:00:00 AM

12/1/2010 12:00:00 AM

817,935

10

PartitioningDemo_OlderData

12/1/2010 12:00:00 AM

1/1/2011 12:00:00 AM

817,935

11

PartitioningDemo_OlderData

1/1/2011 12:00:00 AM

2/1/2011 12:00:00 AM

738,780

12

PartitioningDemo_NewerData

2/1/2011 12:00:00 AM

3/1/2011 12:00:00 AM

817,935

13

PartitioningDemo_NewerData

3/1/2011 12:00:00 AM

4/1/2011 12:00:00 AM

369,476

14

PartitioningDemo_NewerData

4/1/2011 12:00:00 AM

5/1/2011 12:00:00 AM

0

15

PartitioningDemo_NewerData

5/1/2011 12:00:00 AM

 

 

After maintenance:

Rows

Partition Number

Filegroup

Lower Boundary

Upper Boundary

0

1

PartitioningDemo_OlderData

 

4/1/2010 12:00:00 AM

791,549

2

PartitioningDemo_OlderData

4/1/2010 12:00:00 AM

5/1/2010 12:00:00 AM

817,935

3

PartitioningDemo_OlderData

5/1/2010 12:00:00 AM

6/1/2010 12:00:00 AM

791,550

4

PartitioningDemo_OlderData

6/1/2010 12:00:00 AM

7/1/2010 12:00:00 AM

817,935

5

PartitioningDemo_OlderData

7/1/2010 12:00:00 AM

8/1/2010 12:00:00 AM

817,935

6

PartitioningDemo_OlderData

8/1/2010 12:00:00 AM

9/1/2010 12:00:00 AM

791,550

7

PartitioningDemo_OlderData

9/1/2010 12:00:00 AM

10/1/2010 12:00:00 AM

817,935

8

PartitioningDemo_OlderData

10/1/2010 12:00:00 AM

11/1/2010 12:00:00 AM

791,550

9

PartitioningDemo_OlderData

11/1/2010 12:00:00 AM

12/1/2010 12:00:00 AM

817,935

10

PartitioningDemo_OlderData

12/1/2010 12:00:00 AM

1/1/2011 12:00:00 AM

817,935

11

PartitioningDemo_OlderData

1/1/2011 12:00:00 AM

2/1/2011 12:00:00 AM

738,780

12

PartitioningDemo_OlderData

2/1/2011 12:00:00 AM

3/1/2011 12:00:00 AM

817,935

13

PartitioningDemo_NewerData

3/1/2011 12:00:00 AM

4/1/2011 12:00:00 AM

369,476

14

PartitioningDemo_NewerData

4/1/2011 12:00:00 AM

5/1/2011 12:00:00 AM

0

15

PartitioningDemo_NewerData

5/1/2011 12:00:00 AM

 

 

SWITCH and DROP_EXISTING Method

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.  

Demo Maintenance Script

-- Monthly Partition Move Scipt

DECLARE @MonthToMove datetime = '20110201';

 

-- create staging table on NewerData filegroup with aligned indexes

IF OBJECT_ID(N'dbo.PartitionMoveDemoStaging') IS NOT NULL

      DROP TABLE dbo.PartitionMoveDemoStaging;

CREATE TABLE dbo.PartitionMoveDemoStaging(

      PartitioningDateTimeColumn datetime NOT NULL

      ,Column1 bigint NOT NULL

) ON PartitioningDemo_NewerData;

 

CREATE CLUSTERED INDEX cdx_PartitionMoveDemoStaging_PartitioningColumn

      ON dbo.PartitionMoveDemoStaging(PartitioningDateTimeColumn)

      ON PartitioningDemo_NewerData;     

 

CREATE NONCLUSTERED INDEX idx_PartitionMoveDemoStaging_Column1

      ON dbo.PartitionMoveDemoStaging(Column1)

      ON PartitioningDemo_NewerData;     

 

-- switch partition into staging table

ALTER TABLE dbo.PartitionMoveDemo

      SWITCH PARTITION $PARTITION.PF_Last12Months(@MonthToMove)

      TO dbo.PartitionMoveDemoStaging;

 

-- remove partition

ALTER PARTITION FUNCTION PF_Last12Months()

      MERGE RANGE (@MonthToMove);

     

-- set next used to OlderData filegroup

ALTER PARTITION SCHEME PS_Last12Months

      NEXT USED PartitioningDemo_OlderData;

 

-- recreate partition on OlderData filegroup

ALTER PARTITION FUNCTION PF_Last12Months()

      SPLIT RANGE (@MonthToMove);

     

-- recreate staging table indexes using the partition scheme

-- this will move the staging table to OlderData filegroup with aligned indexes

CREATE CLUSTERED INDEX cdx_PartitionMoveDemoStaging_PartitioningColumn

      ON dbo.PartitionMoveDemoStaging(PartitioningDateTimeColumn)

      WITH (DROP_EXISTING = ON)

      ON PS_Last12Months(PartitioningDateTimeColumn);

     

CREATE NONCLUSTERED INDEX idx_PartitionMoveDemoStaging_Column1

      ON dbo.PartitionMoveDemoStaging(Column1)

      WITH (DROP_EXISTING = ON)

      ON PS_Last12Months(PartitioningDateTimeColumn);

 

-- switch staging table back into primary table partition

ALTER TABLE dbo.PartitionMoveDemoStaging

      SWITCH PARTITION $PARTITION.PF_Last12Months(@MonthToMove)

      TO dbo.PartitionMoveDemo PARTITION $PARTITION.PF_Last12Months(@MonthToMove);

 

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.

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

Demo Setup Script

--create database with monthly filegroups

CREATE DATABASE PartitioningDemo

ON(

      NAME='Primary',

      FILENAME='S:\SolidState\PartitioningDemo.mdf',

      SIZE=10MB),

FILEGROUP NewerData (

      NAME='PartitioningDemo_NewerData',

      FILENAME='S:\SolidState\PartitioningDemo_NewerData.ndf',

      SIZE=400MB,

      FILEGROWTH=10MB),

FILEGROUP OlderData (

      NAME='PartitioningDemo_OlderData',

      FILENAME='D:\SpinningDisks\PartitioningDemo_OlderData.ndf',

      SIZE=600MB,

      FILEGROWTH=10MB)

LOG ON(

      NAME='PartitioningDemo_Log',

      FILENAME='L:\LogFiles\PartitioningDemo_Log.ldf',

      SIZE=10MB,

      FILEGROWTH=10MB);

     

ALTER DATABASE PartitioningDemo

      SET RECOVERY SIMPLE;

GO

 

USE PartitioningDemo;

 

CREATE PARTITION FUNCTION PF_Last12Months( datetime )

AS RANGE RIGHT

FOR VALUES

(               -- older_than_current_minus_12

      '20100401'  -- current_minus_12

      ,'20100501' -- current_minus_11

      ,'20100601' -- current_minus_10

      ,'20100701' -- current_minus_9

      ,'20100801' -- current_minus_8

      ,'20100901' -- current_minus_7

      ,'20101001' -- current_minus_6

      ,'20101101' -- current_minus_5

      ,'20101201' -- current_minus_4

      ,'20110101' -- current_minus_3

      ,'20110201' -- current_minus_2

      ,'20110301' -- current_minus_1

      ,'20110401' -- current

      ,'20110501' -- future

);

 

CREATE PARTITION SCHEME PS_Last12Months

AS PARTITION PF_Last12Months

TO

      (

      OlderData,

      OlderData,

      OlderData,

      OlderData,

      OlderData,

      OlderData,

      OlderData,

      OlderData,

      OlderData,

      OlderData,

      OlderData,

      NewerData, -- minus 2 month (to be moved to OlderData)

      NewerData, -- minus 1 month

      NewerData, -- current month

      NewerData  -- future month+

      );

 

-- create table with 10,000,000 rows

ALTER DATABASE PartitioningDemo

      MODIFY FILEGROUP NewerData DEFAULT;

 

WITH

      t1 AS (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2

                            UNION ALL SELECT 3 UNION ALL SELECT 4

                            UNION ALL SELECT 5 UNION ALL SELECT 6

                              UNION ALL SELECT 7 UNION ALL SELECT 8

                              UNION ALL SELECT 9),

      t2 AS (SELECT a.n

                    FROM t1 a, t1 b, t1 c, t1 d, t1 e, t1 f, t1 g)

SELECT

      ISNULL(

            DATEADD(

                  day

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

                  , '20100401') AS PartitioningDateTimeColumn

      ,ISNULL((ROW_NUMBER() OVER(ORDER BY t2.n)), 0) AS Column1

INTO dbo.PartitionMoveDemo

FROM t2;

 

-- create indexes partitioned indexes on table

CREATE CLUSTERED INDEX cdx_PartitionMoveDemo_PartitioningColumn

      ON dbo.PartitionMoveDemo(PartitioningDateTimeColumn)

      ON PS_Last12Months(PartitioningDateTimeColumn);

     

CREATE NONCLUSTERED INDEX idx_PartitionMoveDemo_Column1

      ON dbo.PartitionMoveDemo(Column1)

      ON PS_Last12Months(PartitioningDateTimeColumn);

GO

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