Automating RANGE RIGHT Sliding Window Maintenance

I posted example scripts to automate RANGE LEFT sliding window maintenance in my last post.  As promised, I am sharing a RANGE RIGHT version in this post.

I personally prefer a RANGE RIGHT partition function when partitioning on a data type that includes time.  RANGE RIGHT allows specification of an exact date boundary instead of the maximum date/time value needed for RANGE LEFT to keep all data for a given date in the same partition.  Another nicety with RANGE RIGHT is that same boundaries can be used in a RANGE RIGHT partition function of any date/time data type.  In contrast, the time component of RANGE LEFT boundary values must be customized for the specific data type as I described in Sliding Window Table Partitioning.

The downside with RANGE RIGHT is that maintaining the sliding window isn’t quite as intuitive as with RANGE LEFT.  Instead of switching out and merging the first partition during purge/archive, one needs to switch out and merge the second partition.  This practice avoids the costly movement of retained data from the removed second partition into the retained first partition.  Both the first and second partitions are empty during the merge so no data need to be move moved.  The first partition is normally empty at all times.

The stored procedure below shows how you can automate a RANGE RIGHT daily sliding window.  The main differences between this version and the RANGE LEFT version I posted in Automating Sliding Window Maintenance are

1)      Removed boundary calculation of maximum time (DATEADD(millisecond, -3, @RunDate)).

2)      Added a conditional create of upper boundary of second partition (oldest period retained in third partition)

3)      Added $PARTITION for SWITCH instead of hard-coding the partition number

Example of RANGE RIGHT Sliding Window Automation

Below are the demo objects used by the range RIGHT sliding window procedure.

--no boundaries initially - proc will create as needed

CREATE PARTITION FUNCTION PF_MyPartitionFunction(datetime)

AS RANGE RIGHT FOR VALUES();

GO

 

CREATE PARTITION SCHEME PS_MyPartitionScheme

AS PARTITION PF_MyPartitionFunction ALL TO ([PRIMARY]);

GO

 

CREATE TABLE dbo.MyPartitionedTable

(

      PartitionColumnDateTime datetime

) ON PS_MyPartitionScheme(PartitionColumnDateTime);

GO

 

--note staging table uses same partition scheme as primary table

CREATE TABLE dbo.MyPartitionedTable_Staging

(

      PartitionColumnDateTime datetime

) ON PS_MyPartitionScheme(PartitionColumnDateTime);

GO

 

The sliding window proc:

CREATE PROC dbo.SlideRangeRightWindow_datetime

        @RetentionDays int,

        @RunDate datetime = NULL

/*

      This proc maintains a RANGE RIGHT daily sliding window

      based on the specified @RetentionDays.  It is intended to

      be scheduled daily shortly after midnight. In addition to

      purging old data, the partition function is adjusted to

      account for scheduling issues or changes in @RetentionDays.

 

      Partitions are split and merged so that the first partition

      boundary is the oldest retained data date and the last

      boundary is the next day.  Other partitions contain current

      and historical data for the specifiednumber of @RetentionDays. 

 

      After successful execution, (at least) the following

      partitions will exist:

      - partition 1 = data older than retained date (empty)

      - other partitions = hitorical data (@RunDate - 1 and earlier)

      - second from last partition = current data (@RunDate)

      - last partition = future data (@RunDate + 1) (empty)     

 

*/

 

AS

 

SET NOCOUNT, XACT_ABORT ON;

 

DECLARE

        @Error int,

        @RowCount bigint,

        @ErrorLine int,

        @Message varchar(255),

        @OldestRetainedDate datetime,

        @PartitionBoundaryDate datetime;

 

SET @Error = 0;

 

BEGIN TRY

 

      IF @RunDate IS NULL

      BEGIN

            --use current date (midnight) if no date specified

            SET @RunDate = DATEADD(day, 0, DATEDIFF(day, '', GETDATE()));

      END

      ELSE

      BEGIN

            --set time to midnight of specified date

            SET @RunDate = DATEADD(day, 0, DATEDIFF(day, '', @RunDate));

      END

     

      --calculate oldest retention date based on @RetentionDays and @RunDate

      SET @OldestRetainedDate = DATEADD(day, @RetentionDays * -1, @RunDate);

 

      SET @Message =

            'Run date = ' +

            + CONVERT(varchar(23), @RunDate, 121)

            + ', Retention days = '

            + CAST(@RetentionDays AS varchar(10))

            + ', Oldest retained data date = '

            + CONVERT(varchar(23), @OldestRetainedDate, 121);

 

      RAISERROR (@Message, 0, 1) WITH NOWAIT;

 

      BEGIN TRAN;

 

      --acquire exclusive table lock to prevent deadlocking

      --with concurrent activity.

      SELECT TOP 1 @error = 0

      FROM dbo.MyPartitionedTable WITH (TABLOCKX, HOLDLOCK);

 

      --make sure we have a boundary for oldest retained period

      IF NOT EXISTS(

            SELECT prv.value

            FROM sys.partition_functions AS pf

            JOIN sys.partition_range_values AS prv ON

                  prv.function_id = pf.function_id

            WHERE

                  pf.name = 'PF_MyPartitionFunction'

                  AND CAST(prv.value AS datetime) = @OldestRetainedDate

            )

      BEGIN

            ALTER PARTITION SCHEME PS_MyPartitionScheme

                    NEXT USED [PRIMARY];

            ALTER PARTITION FUNCTION PF_MyPartitionFunction()

                    SPLIT RANGE(@OldestRetainedDate);

            SET @Message =

                    'Created boundary for oldest retained data ('

                    + CONVERT(varchar(30), @OldestRetainedDate, 121) + ')';

 

            RAISERROR(@Message, 0, 1) WITH NOWAIT;

      END

      ELSE

      BEGIN

            SET @Message =

                    'Oldest retained data boundary already exists ('

                    + CONVERT(varchar(30), @OldestRetainedDate, 121) + ')';

 

            RAISERROR(@Message, 0, 1) WITH NOWAIT;

      END

       

      --get earliest expired boundary

      SET @PartitionBoundaryDate = NULL;

      SELECT

            @PartitionBoundaryDate =

                    MIN(CAST(prv.value AS datetime))

      FROM sys.partition_functions AS pf

      JOIN sys.partition_range_values AS prv ON

            prv.function_id = pf.function_id

      WHERE

            pf.name = 'PF_MyPartitionFunction'

            AND CAST(prv.value AS datetime) < @OldestRetainedDate;

 

      --get rowcount of first partition

      SELECT @RowCount = rows

      FROM sys.partitions

      WHERE

            object_id = OBJECT_ID(N'dbo.MyPartitionedTable')

            AND partition_number = 1

            AND index_id IN(0, 1);

 

      --purge data from first partition if not empty

      IF @RowCount > 0

      BEGIN

            TRUNCATE TABLE dbo.MyPartitionedTable_Staging;

            ALTER TABLE dbo.MyPartitionedTable SWITCH PARTITION 1

                    TO dbo.MyPartitionedTable_Staging PARTITION 1;

            TRUNCATE TABLE dbo.MyPartitionedTable_Staging;

            SET @Message =

                    'Purged data older than '

                    + CONVERT(varchar(23), @PartitionBoundaryDate, 121)

                    + ' (' + CAST(@RowCount as varchar(20)) + ' rows)';

            RAISERROR(@Message, 0, 1) WITH NOWAIT;

      END

      ELSE

      BEGIN

            SET @Message =

                    'First partition is empty.  No data older than '

                    + CONVERT(varchar(23), @OldestRetainedDate, 121);

            RAISERROR(@Message, 0, 1) WITH NOWAIT;   

      END

 

      --Switch and merge expired data partitions, starting with the earliest

      WHILE @PartitionBoundaryDate < @OldestRetainedDate

      BEGIN

     

            --get count of rows to be purged

            SELECT @RowCount = rows

            FROM sys.partitions

            WHERE

                    object_id = OBJECT_ID(N'MyPartitionedTable')

                    AND partition_number = $PARTITION.PF_MyPartitionFunction(@PartitionBoundaryDate)

                    AND index_id IN(0, 1);     

 

            --purge data, if needed

            IF @RowCount > 0

            BEGIN

                  --move data to staging table

                  TRUNCATE TABLE dbo.MyPartitionedTable_Staging;

                  ALTER TABLE dbo.MyPartitionedTable SWITCH PARTITION $PARTITION.PF_MyPartitionFunction(@PartitionBoundaryDate)

                          TO dbo.MyPartitionedTable_Staging PARTITION $PARTITION.PF_MyPartitionFunction(@PartitionBoundaryDate);

                         

                   

                  --purge data from staging table

                  TRUNCATE TABLE dbo.MyPartitionedTable_Staging;

                  SET @Message =

                          'Purged data for boundary '

                          + CONVERT(varchar(23), @PartitionBoundaryDate, 121)

                          + ' (' + CAST(@RowCount as varchar(20)) + ' rows)';

                  RAISERROR(@Message, 0, 1) WITH NOWAIT;

            END

            ELSE

            BEGIN

                  SET @Message =

                          'Partition for boundary '

                          + CONVERT(varchar(23), @PartitionBoundaryDate, 121)

                          + ' is empty';

                  RAISERROR(@Message, 0, 1) WITH NOWAIT;         

            END

         

            --remove purged partition

            ALTER PARTITION FUNCTION PF_MyPartitionFunction()

                  MERGE RANGE(@PartitionBoundaryDate);

            SET @Message =

                  'Removed boundary '

                  + CONVERT(varchar(30), @PartitionBoundaryDate, 121);

 

            RAISERROR(@Message, 0, 1) WITH NOWAIT;

 

            --get earliest boundary before retention date for next iteration

            SET @PartitionBoundaryDate = NULL;

            SELECT

                  @PartitionBoundaryDate =

                          MIN(CAST(prv.value AS datetime))

            FROM sys.partition_functions AS pf

            JOIN sys.partition_range_values AS prv ON

                  prv.function_id = pf.function_id

            WHERE

                  pf.name = 'PF_MyPartitionFunction'

                  AND CAST(prv.value AS datetime) < @OldestRetainedDate;

      END;

 

      --Make sure we have an empty partition for tomorrow

      SET @PartitionBoundaryDate = DATEADD(day, 1, @RunDate);

      IF NOT EXISTS

              (

              SELECT prv.value

              FROM sys.partition_functions AS pf

              JOIN sys.partition_range_values AS prv ON

                        prv.function_id = pf.function_id

              WHERE

                        pf.name = 'PF_MyPartitionFunction'

                        AND CAST(prv.value AS datetime) = @PartitionBoundaryDate

              )

      BEGIN

              ALTER PARTITION SCHEME PS_MyPartitionScheme

                        NEXT USED [PRIMARY];

              ALTER PARTITION FUNCTION PF_MyPartitionFunction()

                        SPLIT RANGE(@PartitionBoundaryDate);

              SET @Message =

                        'Created boundary future data '

                        + CONVERT(varchar(30), @PartitionBoundaryDate, 121);

 

              RAISERROR(@Message, 0, 1) WITH NOWAIT;

      END

      ELSE

      BEGIN

              SET @Message =

                        'Partition already exists for future boundary '

                        + CONVERT(varchar(30), @PartitionBoundaryDate, 121);

              RAISERROR(@Message, 0, 1) WITH NOWAIT;

      END;

 

      COMMIT;

 

END TRY

BEGIN CATCH

 

      SELECT

            @Error = ERROR_NUMBER(),

            @Message = ERROR_MESSAGE(),

            @ErrorLine = ERROR_LINE();

 

      RAISERROR('Partition maintenenace failed with error %d at line %d: %s', 16, 1, @Error, @ErrorLine, @Message) WITH NOWAIT;

 

      IF @@TRANCOUNT > 0

      BEGIN

            ROLLBACK;

      END;

 

END CATCH;

 

RETURN @Error;

GO

 

Demo scripts:

--day 1: create initial 4 partitions

INSERT INTO dbo.MyPartitionedTable VALUES('2008-09-01T01:00:00');

EXEC dbo.SlideRangeRightWindow_datetime @RetentionDays = 1, @RunDate = '2008-09-01T00:00:00';

INSERT INTO dbo.MyPartitionedTable VALUES('2008-09-01T02:00:00');

 

--day 2: purge data and create partition for future data (rolling 4 partitions)

INSERT INTO dbo.MyPartitionedTable VALUES('2008-09-02T01:00:00');

EXEC dbo.SlideRangeRightWindow_datetime @RetentionDays = 1, @RunDate = '2008-09-02T00:00:00';

INSERT INTO dbo.MyPartitionedTable VALUES('2008-09-02T02:00:00');

INSERT INTO dbo.MyPartitionedTable VALUES('2008-09-02T03:00:00');

 

--day 3: purge data and create partition for future data (rolling 4 partitions)

INSERT INTO dbo.MyPartitionedTable VALUES('2008-09-03T01:00:00');

EXEC dbo.SlideRangeRightWindow_datetime @RetentionDays = 1, @RunDate = '2008-09-03T00:00:00';

INSERT INTO dbo.MyPartitionedTable VALUES('2008-09-03T02:00:00');

INSERT INTO dbo.MyPartitionedTable VALUES('2008-09-03T03:00:00');

INSERT INTO dbo.MyPartitionedTable VALUES('2008-09-03T04:00:00');

 

--day 5: catch-up after missed day 4

INSERT INTO dbo.MyPartitionedTable VALUES('2008-09-04T01:00:00');

INSERT INTO dbo.MyPartitionedTable VALUES('2008-09-04T02:00:00');

INSERT INTO dbo.MyPartitionedTable VALUES('2008-09-04T03:00:00');

INSERT INTO dbo.MyPartitionedTable VALUES('2008-09-04T04:00:00');

INSERT INTO dbo.MyPartitionedTable VALUES('2008-09-04T05:00:00');

INSERT INTO dbo.MyPartitionedTable VALUES('2008-09-05T01:00:00');

EXEC dbo.SlideRangeRightWindow_datetime @RetentionDays = 1, @RunDate = '2008-09-05T00:00:00';

 

Below is the output from the demo script:

Run date = 2008-09-01 00:00:00.000, Retention days = 1, Oldest retained data date = 2008-08-31 00:00:00.000
Created boundary for oldest retained data (2008-08-31 00:00:00.000)

Created boundary future data 2008-09-02 00:00:00.000
Run date = 2008-09-02 00:00:00.000, Retention days = 1, Oldest retained data date = 2008-09-01 00:00:00.000
Created boundary for oldest retained data (2008-09-01 00:00:00.000)
First partition is empty.  No data older than 2008-08-31 00:00:00.000
Partition for boundary 2008-08-31 00:00:00.000 is empty
Removed boundary 2008-08-31 00:00:00.000
Created boundary future data 2008-09-03 00:00:00.000
Run date = 2008-09-03 00:00:00.000, Retention days = 1, Oldest retained data date = 2008-09-02 00:00:00.000
Oldest retained data boundary already exists (2008-09-02 00:00:00.000)
First partition is empty.  No data older than 2008-09-01 00:00:00.000
Purged data for boundary 2008-09-01 00:00:00.000 (2 rows)
Removed boundary 2008-09-01 00:00:00.000
Created boundary future data 2008-09-04 00:00:00.000
Run date = 2008-09-05 00:00:00.000, Retention days = 1, Oldest retained data date = 2008-09-04 00:00:00.000
Oldest retained data boundary already exists (2008-09-04 00:00:00.000)
First partition is empty.  No data older than 2008-09-02 00:00:00.000
Purged data for boundary 2008-09-02 00:00:00.000 (3 rows)
Removed boundary 2008-09-02 00:00:00.000
Purged data for boundary 2008-09-03 00:00:00.000 (4 rows)
Removed boundary 2008-09-03 00:00:00.000
Created boundary future data 2008-09-06 00:00:00.000