locked
Sliding Windows Partition implementation on 2 File Groups. One file group is on Fast Disk & another is on Slow Disk RRS feed

  • Question

  • Hi All,

    Sliding Windows Partition implementation on 2 File Groups. One file group is on Fast Disk & another is on Slow Disk. Here What I am trying to achieve is suppose I have 10 Partitions & out of 10, Old 1-5 I want to keep on Slow Disk File group & 6-10 I want to keep on fast disk File Group always.

    To Bring next day data, I will split Latest Partition 10 & create another boundary 11 & this will be on fast disk file group. The oldest data that is in partition 1 need to switch out & merge partition 1 to partition 2. But with this, slowly all data will be moving to fast disk. In above case next day: 1-4 will be on Slow disk & 5-10 will be on fast disk. next day again I will split & merge and 1-3 will be on slow disk & 4-6 will be on fast disk. A day will come when all the data will be on fast disk but requirement is to keep old 1-5 always on slow disk & 6-10 always on fast disk.

    How to do this?

    Note: I have created 365*2 no of partitions to hold daily data in each partition & want to keep old one year data on slow disk & latest one year data on fast disk.


    Thanks Shiven:) If Answer is Helpful, Please Vote


    Wednesday, July 31, 2013 7:42 AM

Answers

  • Hi All,

    While implementing above, I observed one thing. When you are partitioning your table & indexes then those indexes will be aligned (Means by default It will include Partition Key Column in all the Indexes).

    When you try to switch out the data from Partition table to non partition table (Same Structure as partition table & on same file group) then you might get error that Index is not having same structure "ALTER TABLE SWITCH statement failed. There is no identical index in source table '' for the index '' in target table ''".. Reason your aligned index on partition table will have one extra column included but that column will not appear when you generate Create Index script from your partition table. 

    Solution is include the partition column at the end in index when you are creating index on non partition table. You can check the same by below query:

    Enter you partition index name in below query & get the object_ID & index_ID and pass to 2nd query.. See how many columns are included in this index. Now Do same thing for Non partition table index. Enter you Non partition table index name in below query & get the object_ID & index_ID and pass to 2nd query, you will see that partition column is not available & due to this when you try to switch out you will get error.

    SELECT * FROM sys.indexes WHERE NAME='NC_SON_TIMESTAMP'


    SELECT B.name,A.* FROM sys.index_columns A 
    INNER JOIN sys.columns B ON A.column_id=b.column_id AND A.object_id=B.object_id
    WHERE A.object_id IN (959342482) AND index_id=7

    Example: When I generated Create Index Script on partition table (Partition key [TXN_TIMESTAMP]) for aligned index then got below script 

    CREATE NONCLUSTERED INDEX [IX__05] ON [ODS].[Table_PARTITION]
    (
    [EMP_ID] ASC 
    )
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 

    and same I created for my Temp table to switch out the data but It was throwing error: "ALTER TABLE SWITCH statement failed. There is no identical index in source table '' for the index '' in target table ''"

    Then I checked with above query & got the difference & created below index on my temp table & then Switch OUT is working fine.

    CREATE NONCLUSTERED INDEX [IX__05] ON [ODS].[Table_TEMP]
    (
    [EMP_ID] ASC 
    )
    INCLUDE ([TXN_TIMESTAMP]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [USER_FG]


    Thanks Shiven:) If Answer is Helpful, Please Vote


    • Edited by Shivendoo Kumar Dubey Thursday, August 1, 2013 8:14 AM Modified
    • Proposed as answer by Sofiya Li Thursday, August 1, 2013 1:58 PM
    • Marked as answer by Sofiya Li Friday, August 9, 2013 2:33 PM
    Thursday, August 1, 2013 8:04 AM

All replies

  • Hi All,

    I got below post: http://weblogs.sqlteam.com/dang/archive/2011/04/17/move-a-partition-to-a-different-file-group-efficiently.aspx

    Let me know if there is any other way to do this.

    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.

    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.


    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 INDEXcdx_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 INDEXcdx_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


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Wednesday, July 31, 2013 8:13 AM
  • Hi All,

    While implementing above, I observed one thing. When you are partitioning your table & indexes then those indexes will be aligned (Means by default It will include Partition Key Column in all the Indexes).

    When you try to switch out the data from Partition table to non partition table (Same Structure as partition table & on same file group) then you might get error that Index is not having same structure "ALTER TABLE SWITCH statement failed. There is no identical index in source table '' for the index '' in target table ''".. Reason your aligned index on partition table will have one extra column included but that column will not appear when you generate Create Index script from your partition table. 

    Solution is include the partition column at the end in index when you are creating index on non partition table. You can check the same by below query:

    Enter you partition index name in below query & get the object_ID & index_ID and pass to 2nd query.. See how many columns are included in this index. Now Do same thing for Non partition table index. Enter you Non partition table index name in below query & get the object_ID & index_ID and pass to 2nd query, you will see that partition column is not available & due to this when you try to switch out you will get error.

    SELECT * FROM sys.indexes WHERE NAME='NC_SON_TIMESTAMP'


    SELECT B.name,A.* FROM sys.index_columns A 
    INNER JOIN sys.columns B ON A.column_id=b.column_id AND A.object_id=B.object_id
    WHERE A.object_id IN (959342482) AND index_id=7

    Example: When I generated Create Index Script on partition table (Partition key [TXN_TIMESTAMP]) for aligned index then got below script 

    CREATE NONCLUSTERED INDEX [IX__05] ON [ODS].[Table_PARTITION]
    (
    [EMP_ID] ASC 
    )
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 

    and same I created for my Temp table to switch out the data but It was throwing error: "ALTER TABLE SWITCH statement failed. There is no identical index in source table '' for the index '' in target table ''"

    Then I checked with above query & got the difference & created below index on my temp table & then Switch OUT is working fine.

    CREATE NONCLUSTERED INDEX [IX__05] ON [ODS].[Table_TEMP]
    (
    [EMP_ID] ASC 
    )
    INCLUDE ([TXN_TIMESTAMP]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [USER_FG]


    Thanks Shiven:) If Answer is Helpful, Please Vote


    • Edited by Shivendoo Kumar Dubey Thursday, August 1, 2013 8:14 AM Modified
    • Proposed as answer by Sofiya Li Thursday, August 1, 2013 1:58 PM
    • Marked as answer by Sofiya Li Friday, August 9, 2013 2:33 PM
    Thursday, August 1, 2013 8:04 AM