Sql-server – moving a Partition of Partitioned Table to other File Group, SQL Server

mergepartitioningsql server

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.

Best Answer

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

Related Solutions

Sql-server – Best way to increase no of partition in SQL Server Table

It sounds like what you want to do is to create a new boundary for your existing partitioning implementation, and this can be one through ALTER PARTITION FUNCTION ... SPLIT RANGE ....

You will also need to run prior to this an ALTER PARTITION SCHEME ... NEXT USED ... to indicate the next filegroup that will hold the new partition (there are a few caveats to this, as quoted below). Here is a quote from BOL regarding this operation:

A filegroup must exist online and be marked by the partition scheme that uses the partition function as NEXT USED to hold the new partition. Filegroups are allocated to partitions in a CREATE PARTITION SCHEME statement. If a CREATE PARTITION SCHEME statement allocates more filegroups than necessary (fewer partitions are created in the CREATE PARTITION FUNCTION statement than filegroups to hold them), then there are unassigned filegroups, and one of them is marked NEXT USED by the partition scheme. This filegroup will hold the new partition. If there are no filegroups marked NEXT USED by the partition scheme, you must use ALTER PARTITION SCHEME to either add a filegroup, or designate an existing one, to hold the new partition.

Sql-server – Expanding a partitioned table

Here are two best practices for partitioning that pertain to the question:

Keep an empty staging partition at the leftmost and rightmost ends of the partition range to ensure that the partitions split when loading in new data, and merge, after unloading old data, do not cause data movement.
Do not split or merge a partition already populated with data because this can cause severe locking and explosive log growth.

http://www.informit.com/articles/article.aspx?p=1946159&seqNum=5

If the leftmost end of your partition is empty, use ALTER PARTITION FUNCTION SPLIT RANGE to add new ranges to the partition function.

To check if the leftmost partition is empty, use a query like the following:

DECLARE @PartitionFunctionName sysname = 'YourPartitionFunctionNameHere';

SELECT 
p.partition_number, SUM(pst.row_count) RowCountInPartition, pf.name PartitionFunction, ps.name PartitionScheme
FROM sys.dm_db_partition_stats pst
INNER JOIN sys.partitions p ON pst.partition_id = p.partition_id
INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
INNER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
WHERE pf.name = @PartitionFunctionName
GROUP BY p.partition_number, pf.name, ps.name;

If the first partition is not empty, the best practices recommend that you create a new function with all values, create a new table on that function, then insert the data to the new table.

Also, if the left partition just has a few records, a split may be fine. Not sure on that as I've never tried it.

Whatever you do, make sure to leave some empty partitions at the leftmost and rightmost partition when you're finished. I might even go so far as to creating partition ranges for 0 and 1, then add a check constraint to prevent the first partition from getting data in it. Do the same thing for the end.

Best Answer

Related Solutions

Sql-server – Best way to increase no of partition in SQL Server Table

Sql-server – Expanding a partitioned table

Related Question