Dan Guzman Blog

Automating Sliding Window Maintenance

I mentioned in my last post that partitioning provides a great way to manage a time-based sliding window.  Unfortunately, there are no out-of-the-box tools to facilitate adjusting the sliding window so I decided to share scripts I’ve developed to automate the task.

Details of a time-based sliding window strategy vary slightly depending on whether the partition function is RANGE RIGHT or RANGE LEFT.   The default RANGE LEFT is pervasive so I’ll post a RANGE LEFT example here.  I’ll follow up with a RANGE RIGHT version in my next post.

The stored procedure below shows how you can automate a RANGE LEFT daily sliding window for a datetime data type partition function.  This sample procedure can be adjusted for other time-including data types simply by changing the data type declarations and adjusting the boundary calculation to return the latest possible time.  See my Sliding Window Table Partitioning post for a list of the appropriate time value boundary values by data type.

The @RetentionDays parameter specifies the desired data retention in days.  This value is used to purge old data using SWITCH and also adjust the partition function so that historical, current and future data are in separate partitions.  See my Sliding Window Table Partitioning post for more information on how this approach avoids costly data movement during SPLIT and MERGE.

This sample stored proc can be tweaked in order to address environment-specific requirements (object names, filegroups, multiple partition schemes) other time intervals (e.g. month, year).  I’ve found the verbose messages (SELECT and RAISERROR) are useful for troubleshooting but these can be removed if desired.

Note that this sample uses a staging table as a SWITCH target that is partitioned identically to the primary table.  I’ve found that this technique makes it easy to ensure the staging table target partition is properly aligned for SWITCH.

Example of RANGE LEFT Sliding Window Automation

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

–no boundaries initially - proc will create as needed/o:p

CREATE PARTITION FUNCTION PF_MyPartitionFunction(datetime)/o:p

AS RANGE LEFT FOR VALUES();/o:p

GO/o:p

 /o:p

CREATE PARTITION SCHEME PS_MyPartitionScheme/o:p

AS PARTITION PF_MyPartitionFunction ALL TO ([PRIMARY]);/o:p

GO/o:p

 /o:p

CREATE TABLE dbo.MyPartitionedTable/o:p

(/o:p

      PartitionColumnDateTime datetime/o:p

) ON PS_MyPartitionScheme(PartitionColumnDateTime);/o:p

GO/o:p

 /o:p

–note staging table uses same partition scheme as primary table/o:p

CREATE TABLE dbo.MyPartitionedTable_Staging/o:p

(/o:p

      PartitionColumnDateTime datetime/o:p

) ON PS_MyPartitionScheme(PartitionColumnDateTime);/o:p

GO

 /o:p

The sliding window proc:

CREATE PROC dbo.SlideRangeLeftWindow_datetime/o:p

      @RetentionDays int,/o:p

      @RunDate datetime = NULL/o:p

//o:p

      This proc maintains a RANGE LEFT daily sliding window/o:p

      based on the specified @RetentionDays.  In addition to/o:p

      purging old data, the partition function is adjusted to/o:p

      account for scheduling issues or changes in @RetentionDays./o:p

 /o:p

      Partitions are split and merged so that the first partition/o:p

      contains the oldest data and the last two partitions (future/o:p

      data) are empty.  The other petitions will contain historical/o:p

      and current data for the specified number of @RetentionDays.  /o:p

 /o:p

      After successful execution, (at least) the following/o:p

      partitions will exist: /o:p

      - partition 1 = oldest data period/o:p

      - third from last partition = current data (@RunDate)/o:p

      - second from last partition = @RunDate + 1 (normally empty)/o:p

      - last partition = @RunDate + 2  (normally empty)/o:p

//o:p

 /o:p

AS/o:p

 /o:p

SET NOCOUNT, XACT_ABORT ON;/o:p

 /o:p

DECLARE/o:p

        @Error int,/o:p

        @RowCount bigint,/o:p

        @ErrorLine int,/o:p

        @Message varchar(255),/o:p

            @ExpirationDate datetime,/o:p

        @PartitionBoundaryDate datetime;/o:p

 /o:p

SET @Error = 0;/o:p

 /o:p

BEGIN TRY/o:p

 /o:p

      IF @RunDate IS NULL/o:p

      BEGIN/o:p

            –use current date (midnight) if no date specified/o:p

            SET @RunDate = DATEADD(day, 0, DATEDIFF(day, '', GETDATE()));/o:p

      END/o:p

      ELSE/o:p

      BEGIN/o:p

            –set time to midnight of specified date/o:p

            SET @RunDate = DATEADD(day, 0, DATEDIFF(day, '', @RunDate));/o:p

      END/o:p

 /o:p

      SET @Message = /o:p

            'Run date = ' +/o:p

            CONVERT(varchar(23), @RunDate, 121) +/o:p

            ', Retention days = ' +/o:p

            CAST(@RetentionDays AS varchar(10));/o:p

 /o:p

      RAISERROR (@Message, 0, 1) WITH NOWAIT;/o:p

 /o:p

      –set datetime boundary to latest posible datetime of previous day/o:p

      SET @RunDate = DATEADD(millisecond, -3, @RunDate);/o:p

 /o:p

      –calculate expiration date based on @RetentionDays and @RunDate/o:p

      SET @ExpirationDate = DATEADD(day, @RetentionDays * -1, @RunDate);/o:p

 /o:p

      RAISERROR ('Partition boundaries before maintenance:', 0, 1) WITH NOWAIT;/o:p

      SELECT CAST(prv.value AS datetime) AS PartitionBoundary/o:p

            FROM sys.partition_functions AS pf/o:p

            JOIN sys.partition_range_values AS prv ON/o:p

                  prv.function_id = pf.function_id/o:p

            WHERE pf.name = 'PF_MyPartitionFunction';/o:p

 /o:p

      –get oldest existing boundary/o:p

      SELECT/o:p

            @PartitionBoundaryDate = /o:p

                  CAST(MIN(prv.value) AS datetime)/o:p

      FROM sys.partition_functions AS pf/o:p

      JOIN sys.partition_range_values AS prv ON/o:p

            prv.function_id = pf.function_id/o:p

      WHERE pf.name = 'PF_MyPartitionFunction';/o:p

 /o:p

      BEGIN TRAN;/o:p

 /o:p

      –acquire exclusive table lock to prevent deadlocking/o:p

      –with concurrent activity./o:p

      SELECT TOP 1 @error = 0/o:p

      FROM dbo.MyPartitionedTable WITH (TABLOCKX, HOLDLOCK);/o:p

 /o:p

      –If first partition boundary is later than expiration date,/o:p

      –split first partition so that it contains only expired data./o:p

      IF @PartitionBoundaryDate > @ExpirationDate/o:p

            OR @PartitionBoundaryDate IS NULL/o:p

      BEGIN/o:p

            SET @PartitionBoundaryDate = @ExpirationDate;/o:p

            ALTER PARTITION SCHEME PS_MyPartitionScheme /o:p

                  NEXT USED [PRIMARY];/o:p

            ALTER PARTITION FUNCTION PF_MyPartitionFunction()/o:p

                  SPLIT RANGE(@PartitionBoundaryDate);/o:p

            SET @Message = /o:p

                  'Created boundary ' + /o:p

                  CONVERT(varchar(30), @PartitionBoundaryDate, 121);/o:p

            RAISERROR(@Message, 0, 1) WITH NOWAIT;/o:p

      END;/o:p

 /o:p

      –Switch and merge partitions older that retention period/o:p

      WHILE @PartitionBoundaryDate <= @ExpirationDate/o:p

      BEGIN/o:p

 /o:p

            –Ensure target staging partition is empty/o:p

            TRUNCATE TABLE dbo.MyPartitionedTable_Staging;/o:p

            ALTER TABLE dbo.MyPartitionedTable SWITCH PARTITION 1 /o:p

                  TO dbo.MyPartitionedTable_Staging PARTITION 1;/o:p

            –get count of purged rows for message/o:p

            SELECT @RowCount = rows/o:p

            FROM sys.partitions/o:p

            WHERE/o:p

                  object_id = OBJECT_ID(N'MyPartitionedTable_Staging') /o:p

                  AND partition_number = 1;/o:p

            –purge data permanently/o:p

            TRUNCATE TABLE dbo.MyPartitionedTable_Staging;/o:p

            SET @Message = /o:p

                  'Moved data older than ' + /o:p

                  CONVERT(varchar(23), @PartitionBoundaryDate, 121) + /o:p

                  ' (' + CAST(@RowCount as varchar(20)) + ' rows) to staging table';/o:p

            RAISERROR(@Message, 0, 1) WITH NOWAIT;/o:p

 /o:p

            –merge first and second partitions/o:p

            ALTER PARTITION FUNCTION PF_MyPartitionFunction() /o:p

                  MERGE RANGE(@PartitionBoundaryDate);/o:p

            SET @Message = /o:p

                  'Removed boundary ' + /o:p

                  CONVERT(varchar(30), @PartitionBoundaryDate, 121);/o:p

 /o:p

            RAISERROR(@Message, 0, 1) WITH NOWAIT;/o:p

 /o:p

            –get oldest partition boundary for next iteration/o:p

            SET @PartitionBoundaryDate = NULL;/o:p

            SELECT/o:p

                  @PartitionBoundaryDate = /o:p

                        CAST(MIN(prv.value) AS datetime)/o:p

            FROM sys.partition_functions AS pf/o:p

            JOIN sys.partition_range_values AS prv ON/o:p

                  prv.function_id = pf.function_id/o:p

            WHERE pf.name = 'PF_MyPartitionFunction';/o:p

      END;/o:p

 /o:p

      –Make sure we have individual partitons for tomorrow/o:p

      –and future days./o:p

      SET @PartitionBoundaryDate = DATEADD(day, 1, @RunDate);/o:p

      WHILE @PartitionBoundaryDate <= DATEADD(day, 2, @RunDate)/o:p

      BEGIN/o:p

            IF NOT EXISTS/o:p

                  (/o:p

                  SELECT prv.value/o:p

                  FROM sys.partition_functions AS pf/o:p

                  JOIN sys.partition_range_values AS prv ON/o:p

                        prv.function_id = pf.function_id/o:p

                  WHERE/o:p

                        pf.name = 'PF_MyPartitionFunction'/o:p

                        AND CAST(prv.value AS datetime) = @PartitionBoundaryDate/o:p

                  )/o:p

            BEGIN/o:p

                  ALTER PARTITION SCHEME PS_MyPartitionScheme/o:p

                        NEXT USED [PRIMARY];/o:p

                  ALTER PARTITION FUNCTION PF_MyPartitionFunction()/o:p

                        SPLIT RANGE(@PartitionBoundaryDate);/o:p

                  SET @Message = /o:p

                        'Created boundary ' +/o:p

                        CONVERT(varchar(30), @PartitionBoundaryDate, 121);/o:p

 /o:p

                  RAISERROR(@Message, 0, 1) WITH NOWAIT;/o:p

            END;/o:p

            ELSE/o:p

            BEGIN/o:p

                  SET @Message =/o:p

                        'Partition already exists for boundary ' +/o:p

                        CONVERT(varchar(30), @PartitionBoundaryDate, 121);/o:p

                  RAISERROR(@Message, 0, 1) WITH NOWAIT;/o:p

            END;/o:p

 /o:p

            –calc boundary date for next iteration/o:p

            SET @PartitionBoundaryDate = /o:p

                  DATEADD(day, 1, @PartitionBoundaryDate);/o:p

      END/o:p

 /o:p

      COMMIT;/o:p

 /o:p

END TRY/o:p

BEGIN CATCH/o:p

 /o:p

      SELECT/o:p

            @Error = ERROR_NUMBER(),/o:p

            @Message = ERROR_MESSAGE(),/o:p

            @ErrorLine = ERROR_LINE();/o:p

 /o:p

      SET @Message = /o:p

            'Partition maintenenace failed with error %d at line %d: ' +/o:p

            @Message;/o:p

      RAISERROR(@Message, 16, 1, @Error, @ErrorLine) WITH NOWAIT;/o:p

 /o:p

      IF @@TRANCOUNT > 0/o:p

      BEGIN/o:p

            ROLLBACK;/o:p

      END;/o:p

 /o:p

END CATCH;/o:p

 /o:p

RAISERROR ('Partition boundaries after maintenance', 0, 1) WITH NOWAIT;/o:p

SELECT CAST(prv.value AS datetime) AS PartitionBoundary/o:p

      FROM sys.partition_functions AS pf/o:p

      JOIN sys.partition_range_values AS prv ON/o:p

            prv.function_id = pf.function_id/o:p

      WHERE pf.name = 'PF_MyPartitionFunction';/o:p

 /o:p

Done:/o:p

 /o:p

RETURN @error;/o:p

GO

 /o:p

Demo scripts:

–day 1: create initial 3 partitions/o:p

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

EXEC dbo.SlideRangeLeftWindow_datetime @RetentionDays = 1, @RunDate = '2008-09-01T00:00:00';/o:p

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

 /o:p

–day 2: create partition for future data (4 partitions)/o:p

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

EXEC dbo.SlideRangeLeftWindow_datetime @RetentionDays = 1, @RunDate = '2008-09-02T00:00:00';/o:p

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

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

 /o:p

–day 3: purge data and create partition for future data (rolling 4 partitions)/o:p

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

EXEC dbo.SlideRangeLeftWindow_datetime @RetentionDays = 1, @RunDate = '2008-09-03T00:00:00';/o:p

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

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

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

 /o:p

–day 5: catch-up after missed day 4/o:p

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

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

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

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

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

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

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

 /o:p

Below is the output from the demo script:

Run date = 2008-09-01 00:00:00.000, Retention days = 1/o:p

Partition boundaries before maintenance:/o:p

PartitionBoundary/o:p

———————–/o:p

 /o:p

Created boundary 2008-08-30 23:59:59.997/o:p

Moved data older than 2008-08-30 23:59:59.997 (0 rows) to staging table/o:p

Removed boundary 2008-08-30 23:59:59.997/o:p

Created boundary 2008-09-01 23:59:59.997/o:p

Created boundary 2008-09-02 23:59:59.997/o:p

Partition boundaries after maintenance/o:p

PartitionBoundary/o:p

———————–/o:p

2008-09-01 23:59:59.997/o:p

2008-09-02 23:59:59.997/o:p

 /o:p

Run date = 2008-09-02 00:00:00.000, Retention days = 1/o:p

Partition boundaries before maintenance:/o:p

PartitionBoundary/o:p

———————–/o:p

2008-09-01 23:59:59.997/o:p

2008-09-02 23:59:59.997/o:p

 /o:p

Created boundary 2008-08-31 23:59:59.997/o:p

Moved data older than 2008-08-31 23:59:59.997 (0 rows) to staging table/o:p

Removed boundary 2008-08-31 23:59:59.997/o:p

Partition already exists for boundary 2008-09-02 23:59:59.997/o:p

Created boundary 2008-09-03 23:59:59.997/o:p

Partition boundaries after maintenance/o:p

PartitionBoundary/o:p

———————–/o:p

2008-09-01 23:59:59.997/o:p

2008-09-02 23:59:59.997/o:p

2008-09-03 23:59:59.997

Run date = 2008-09-03 00:00:00.000, Retention days = 1/o:p

Partition boundaries before maintenance:/o:p

PartitionBoundary/o:p

———————–/o:p

2008-09-01 23:59:59.997/o:p

2008-09-02 23:59:59.997/o:p

2008-09-03 23:59:59.997/o:p

 /o:p

Moved data older than 2008-09-01 23:59:59.997 (2 rows) to staging table/o:p

Removed boundary 2008-09-01 23:59:59.997/o:p

Partition already exists for boundary 2008-09-03 23:59:59.997/o:p

Created boundary 2008-09-04 23:59:59.997/o:p

Partition boundaries after maintenance/o:p

PartitionBoundary/o:p

———————–/o:p

2008-09-02 23:59:59.997/o:p

2008-09-03 23:59:59.997/o:p

2008-09-04 23:59:59.997/o:p

 /o:p

Run date = 2008-09-05 00:00:00.000, Retention days = 1/o:p

Partition boundaries before maintenance:/o:p

PartitionBoundary/o:p

———————–/o:p

2008-09-02 23:59:59.997/o:p

2008-09-03 23:59:59.997/o:p

2008-09-04 23:59:59.997/o:p

 /o:p

Moved data older than 2008-09-02 23:59:59.997 (3 rows) to staging table/o:p

Removed boundary 2008-09-02 23:59:59.997/o:p

Moved data older than 2008-09-03 23:59:59.997 (4 rows) to staging table/o:p

Removed boundary 2008-09-03 23:59:59.997/o:p

Created boundary 2008-09-05 23:59:59.997/o:p

Created boundary 2008-09-06 23:59:59.997/o:p

Partition boundaries after maintenance/o:p

PartitionBoundary/o:p

———————–/o:p

2008-09-04 23:59:59.997/o:p

2008-09-05 23:59:59.997/o:p

2008-09-06 23:59:59.997

Legacy Comments


peleg
2008-12-24
re: Automating Sliding Window Maintenance
i have an un paratiotioned index, and when i try to make a switch i get : "Partition maintenenace failed with error 7733 at line 106: 'ALTER TABLE SWITCH' statement failed. The table 'NReports5.dbo.TargetTable2' is partitioned while index 'IX_TargetTable1_RecipientSender' is not partitioned."
what can i do?do i need to fix it on both original table and staging table?

thnaks in advance
peleg

Dan Guzman
2009-01-03
re: Automating Sliding Window Maintenance
This error means that the table is partitioned but the index listed in the error message is not. One of the requirements in order to use SWITCH
is that a table and it's indexes must be aligned (partitioned similarly). This implies that the tables cannot contain a mix of partitioned and non-partitioned indexes. See the Books Online for details.

The script below (based on DDL from your separate email) shows how you can change the existing non-partitioned index to a partitioned one so that up can use SWITCH:

CREATE TABLE [dbo].[TargetTable2_Staging](
[StartDate] [varchar](15) NOT NULL,
[Column1] [varchar](25) NOT NULL,
[Column2] [varchar](25) NOT NULL,
[id] [bigint] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_TargetTable2_Staging] PRIMARY KEY CLUSTERED
(
[StartDate] ASC,
[id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_TargetTable2_Staging] ON
[dbo].[TargetTable2_Staging]
(
Column1,
Column2
) ON [PRIMARY]
GO

CREATE PARTITION FUNCTION [PF_TargetTable2](varchar(15))
AS RANGE LEFT FOR VALUES (N'20081210000000', N'20081211000000',
N'20081212000000', N'20081213000000', N'20081214000000', N'20081215000000',
N'20081216000000', N'20081217000000', N'20081218000000', N'20081230000000')
GO

CREATE PARTITION SCHEME [PS_TargetTable2] AS PARTITION
[PF_TargetTable2] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY],
[PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
GO

CREATE TABLE [dbo].[TargetTable2](
[StartDate] [varchar](15) NOT NULL,
[Column1] [varchar](25) NOT NULL,
[Column2] [varchar](25) NOT NULL,
[id] [bigint] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_TargetTable2] PRIMARY KEY CLUSTERED
(
[StartDate] ASC,
[id] ASC
) ON [PS_TargetTable2]([StartDate])
) ON [PS_TargetTable2]([StartDate])
GO

CREATE NONCLUSTERED INDEX [IX_TargetTable2] ON [dbo].[TargetTable2]
(
Column1,
Column2
) ON [PRIMARY]
GO

--this will fail
ALTER TABLE dbo.TargetTable2
SWITCH PARTITION 1 TO TargetTable2_Staging
/*
Msg 7733, Level 16, State 4, Line 1
'ALTER TABLE SWITCH' statement failed. The table 'TechnologyStack.dbo.TargetTable2' is partitioned while index 'IX_TargetTable2' is not partitioned.
*/

--recreate source table index as partitioned
CREATE NONCLUSTERED INDEX [IX_TargetTable2] ON
[dbo].[TargetTable2]
(
Column1,
Column2
)
WITH (DROP_EXISTING = ON)
ON [PS_TargetTable2]([StartDate])

--this will succeed
ALTER TABLE dbo.TargetTable2
SWITCH PARTITION 1 TO TargetTable2_Staging