moving a Partition of Partitioned Table to other File Group, SQL Server

Question

I created partitioned table on DateTime column. for last 6 month. I placed 3 oldest month on a file group named 'FG_OlderData', and 3 newest month on a file group named 'FG_NewerData'. Partition Function Definition :

CREATE PARTITION FUNCTION pf_Positions_LastSixMonth_MonthlyRange (DateTime)
AS RANGE RIGHT FOR VALUES (
 -- older_than_current_minus_5
 '20150121', -- current_minus_5
 '20150220', -- current_minus_4
 '20150321', -- current_minus_3
 '20150421', -- current_minus_2
 '20150522', -- current_minus_1
 '20150622', -- current
 '20150723' -- Future
)
GO

Partition Schema Definition :

CREATE PARTITION SCHEME ps_Positions_LastSixMonth_MontlyRange
AS PARTITION pf_Positions_LastSixMonth_MonthlyRange
TO (
 FG_OlderData,
 FG_OlderData,
 FG_OlderData,
 FG_OlderData,
 FG_NewerData, -- minus 2 month (to be moved to OlderData)
 FG_NewerData, -- minus 1 month
 FG_NewerData, -- current month
 FG_NewerData -- future month+
 );
GO

The Result :

OnlineGeoPosition 1 FG_OlderData 0 0 less than 2015年01月21日 00:00:00.000
OnlineGeoPosition 2 FG_OlderData 0 0 less than 2015年02月20日 00:00:00.000
OnlineGeoPosition 3 FG_OlderData 0 0 less than 2015年03月21日 00:00:00.000
OnlineGeoPosition 4 FG_OlderData 0 0 less than 2015年04月21日 00:00:00.000
OnlineGeoPosition 5 FG_NewerData 0 0 less than 2015年05月22日 00:00:00.000
OnlineGeoPosition 6 FG_NewerData 0 0 less than 2015年06月22日 00:00:00.000
OnlineGeoPosition 7 FG_NewerData 0 0 less than 2015年07月23日 00:00:00.000
OnlineGeoPosition 8 FG_NewerData 0 0 less than NULL

First i switched (20150522) partition's data to temp able to move to older file group :

ALTER TABLE dbo.OnlineGeoPosition
SWITCH PARTITION pf_Positions_LastSixMonth_MonthlyRange('20150522')
TO dbo.OnlineGeoPosition_NewerToOlderTemp;

Then Merge Switched partition :

ALTER PARTITION FUNCTION pf_Positions_LastSixMonth_MonthlyRange()
 MERGE RANGE ('20150522');

Result :

OnlineGeoPosition 1 FG_OlderData 0 0 less than 2015年01月21日 00:00:00.000
OnlineGeoPosition 2 FG_OlderData 0 0 less than 2015年02月20日 00:00:00.000
OnlineGeoPosition 3 FG_OlderData 0 0 less than 2015年03月21日 00:00:00.000
OnlineGeoPosition 4 FG_OlderData 0 0 less than 2015年04月21日 00:00:00.000
OnlineGeoPosition 5 FG_NewerData 0 0 less than 2015年06月22日 00:00:00.000
OnlineGeoPosition 6 FG_NewerData 0 0 less than 2015年07月23日 00:00:00.000
OnlineGeoPosition 7 FG_NewerData 0 0 less than NULL

The Problem : When i Split the '20150522' Range on OlderData, it remain in NewerData File group and next partion ('20150622') Goes to OlderData FileGroup.

ALTER PARTITION SCHEME ps_Positions_LastSixMonth_MontlyRange
 NEXT USED FG_OlderData;
ALTER PARTITION FUNCTION pf_Positions_LastSixMonth_MonthlyRange()
 SPLIT RANGE ('2015-05-22');

Result :

OnlineGeoPosition 1 FG_OlderData 0 0 less than 2015年01月21日 00:00:00.000
OnlineGeoPosition 2 FG_OlderData 0 0 less than 2015年02月20日 00:00:00.000
OnlineGeoPosition 3 FG_OlderData 0 0 less than 2015年03月21日 00:00:00.000
OnlineGeoPosition 4 FG_OlderData 0 0 less than 2015年04月21日 00:00:00.000
OnlineGeoPosition 5 **FG_NewerData** 0 0 less than **2015年05月22日** 00:00:00.000
OnlineGeoPosition 6 **FG_OlderData** 0 0 less than **2015年06月22日** 00:00:00.000
OnlineGeoPosition 7 FG_NewerData 0 0 less than 2015年07月23日 00:00:00.000
OnlineGeoPosition 8 FG_NewerData 0 0 less than NULL

After this command, i suppose to have '2015-05-22' partition on OlderData File group but it remains in FG_NewerData File Group and instead , the next partition '20150622' goes to FG_OlderData FileGroup.

What is the problem with my code? as you saw i used Range Right.

• user8408233: I am trying to solve a similar problem and your information is very helpful. I have one question that , you mentioned a step to rebuild a Cluster index on a temp table, dbo.OnlineGeoPosition_NewerToOlderTemp and I am not sure how its going to help. Do this need to be done on main partition table, OnlineGeoPosition rather Temp table?
• RDFozz: If you have a new question, please ask it by clicking the Ask Question button. Include a link to this question if it helps provide context. - From Review

Answer 1

There are a couple of issues here. First, the partition/filegroup mapping shown in your question does not match the DDL. A RANGE RIGHT function boundary is inclusive of the partition on the right so the results should be as below to match the DDL. Consider downloading the partition details custom report from codeplex that can run from an SSMS object explorer table context menu to show the boundaries and filegroups: http://ssmspartcustomrpt.codeplex.com/releases/view/30936 .

OnlineGeoPosition 1 FG_OlderData less than 2015年01月21日 00:00:00.000
OnlineGeoPosition 2 FG_OlderData greater than or equal to 2015年01月21日 00:00:00.000
OnlineGeoPosition 3 FG_OlderData greater than or equal to 2015年02月20日 00:00:00.000
OnlineGeoPosition 4 FG_OlderData greater than or equal to 2015年03月21日 00:00:00.000
OnlineGeoPosition 5 FG_NewerData greater than or equal to 2015年04月21日 00:00:00.000
OnlineGeoPosition 6 FG_NewerData greater than or equal to 2015年05月22日 00:00:00.000
OnlineGeoPosition 7 FG_NewerData greater than or equal to 2015年06月22日 00:00:00.000
OnlineGeoPosition 8 FG_NewerData greater than or equal to 2015年07月23日 00:00:00.000

When you merge a RANGE RIGHT function, the removed partition is the one on the right. Data from the removed partition are moved to the retained one on the left. So merging a RANGE RIGHT sliding window partition always moves from newer to older partitions. However it is best to MERGE and SPLIT empty partitions as you are doing to avoid expensive data movement during partition DDL, which is about 4 times that of normal DML. Conversely, a SPLIT of a RANGE RIGHT function creates a new partition to the right of the specified boundary, and on the NEXT used filegroup, which is the main problem you are having.

Below is a sample script to achieve a sliding window with the new partition/filegroup mappings below. This script uses 2 staging tables, one for the latest partition on the old data filegroup and another for the oldest partition on the new data filegroup (the one to be moved). This allows the boundary to be safely merged with both adjacent partitions empty. You'll need either check constraints on the staging tables or another partition function/scheme to constrain the staging table data to match the target partition boundaries for the switch in. Data are moved efficiently using a CREATE INDEX...DROP_EXISTING.

OnlineGeoPosition 1 FG_OlderData less than 2015年02月20日 00:00:00.000
OnlineGeoPosition 2 FG_OlderData greater than or equal to 2015年02月20日 00:00:00.000
OnlineGeoPosition 3 FG_OlderData greater than or equal to 2015年03月21日 00:00:00.000
OnlineGeoPosition 4 FG_OlderData greater than or equal to 2015年04月21日 00:00:00.000
OnlineGeoPosition 5 FG_NewerData greater than or equal to 2015年05月22日 00:00:00.000
OnlineGeoPosition 6 FG_NewerData greater than or equal to 2015年06月22日 00:00:00.000
OnlineGeoPosition 7 FG_NewerData greater than or equal to 2015年07月23日 00:00:00.000
OnlineGeoPosition 8 FG_NewerData greater than or equal to 2015年08月22日 00:00:00.000

Sample script:

--switch out newest partition on FG_OldData
ALTER TABLE dbo.OnlineGeoPosition
SWITCH PARTITION $PARTITION.pf_Positions_LastSixMonth_MonthlyRange('2015-03-21')
TO dbo.OnlineGeoPosition_OlderTemp;
--switch out oldest partition on FG_NewData
ALTER TABLE dbo.OnlineGeoPosition
SWITCH PARTITION $PARTITION.pf_Positions_LastSixMonth_MonthlyRange('2015-04-21')
TO dbo.OnlineGeoPosition_NewerToOlderTemp;
--move empty partition from new data to old data
ALTER PARTITION FUNCTION pf_Positions_LastSixMonth_MonthlyRange()
 MERGE RANGE ('2015-04-21');
--create new empty partition on old data filegroup for moved data
ALTER PARTITION SCHEME ps_Positions_LastSixMonth_MontlyRange
 NEXT USED FG_OlderData;
ALTER PARTITION FUNCTION pf_Positions_LastSixMonth_MonthlyRange()
 SPLIT RANGE ('2015-04-21');
--rebuild each index with drop existing to move data from new to old filegroup
CREATE UNIQUE CLUSTERED INDEX PK_OnlineGeoPosition_NewerToOlderTemp
ON dbo.OnlineGeoPosition_NewerToOlderTemp(PartitioningColumn)
WITH(DROP_EXISTING=ON)
ON FG_OlderData;
--switch staging tables data back in
--staging tables must have a check constraint or be partitoned similarly as the target partition
ALTER TABLE dbo.OnlineGeoPosition_OlderTemp
SWITCH TO dbo.OnlineGeoPosition PARTITION $PARTITION.pf_Positions_LastSixMonth_MonthlyRange('2015-03-21')
ALTER TABLE dbo.OnlineGeoPosition_NewerToOlderTemp
SWITCH TO dbo.OnlineGeoPosition PARTITION $PARTITION.pf_Positions_LastSixMonth_MonthlyRange('2015-04-21')
--remove oldest partition
TRUNCATE TABLE dbo.OnlineGeoPosition_OlderTemp;
ALTER TABLE dbo.OnlineGeoPosition
SWITCH PARTITION $PARTITION.pf_Positions_LastSixMonth_MonthlyRange('2015-01-21')
TO dbo.OnlineGeoPosition_OlderTemp;
TRUNCATE TABLE dbo.OnlineGeoPosition_OlderTemp;
ALTER PARTITION FUNCTION pf_Positions_LastSixMonth_MonthlyRange()
 MERGE RANGE ('2015-01-21');
--create new partition on new data filegroup for future data
ALTER PARTITION SCHEME ps_Positions_LastSixMonth_MontlyRange
 NEXT USED FG_NewerData;
ALTER PARTITION FUNCTION pf_Positions_LastSixMonth_MonthlyRange()
 SPLIT RANGE ('2015-08-22');
GO

• Javad Norouzi: Thank you dear Dan. Your answer is complete and great. it worked for me. but a question : in production server we will have about 1.5 million records in each partition, so switching out and again switching in this records is optimized in this way?
• Dan Guzman: The SWITCH out/in will be fast since no data are physically moved but the CREATE INDEX...WITH DROP EXISTING duration will be roughly proportional the amount of data moved. I'd expect 1.5M rows index to typically take seconds, not minutes unless there are resource constraints. It's best to perform partition maintenance during a maintenance window to avoid deadlocking. You can add the ONLINE=ON option to the CREATE INDEX if concurrency is a concern.