--main table partition function (before start of new month)
CREATE PARTITION FUNCTION PF_12MonthSlidingWindow (datetime2(0))
AS RANGE RIGHT FOR VALUES (
'2014-11-01T00:00:00' --current month -12 on OlderData filegroup
, '2014-12-01T00:00:00' --current month -11 on OlderData filegroup
, '2015-01-01T00:00:00' --current month -10 on OlderData filegroup
, '2015-02-01T00:00:00' --current month -9 on OlderData filegroup
, '2015-03-01T00:00:00' --current month -8 on OlderData filegroup
, '2015-04-01T00:00:00' --current month -7 on OlderData filegroup
, '2015-05-01T00:00:00' --current month -6 on OlderData filegroup
, '2015-06-01T00:00:00' --current month -5 on OlderData filegroup
, '2015-07-01T00:00:00' --current month -4 on OlderData filegroup
, '2015-08-01T00:00:00' --current month -3 on NewerData filegroup
, '2015-09-01T00:00:00' --current month -2 on NewerData filegroup
, '2015-10-01T00:00:00' --current month -1 on NewerData filegroup
, '2015-11-01T00:00:00' --current month on NewerData filegroup
, '2015-12-01T00:00:00' --next month on NewerData filegroup
, '2016-01-01T00:00:00' --future month
)
GO
--main table partition scheme
CREATE PARTITION SCHEME PS_12MonthSlidingWindow
AS PARTITION PF_12MonthSlidingWindow
TO (
FG_OlderData --older than 12 montths
, FG_OlderData --current month -12 on OlderData
, FG_OlderData --current month -11 on OlderData
, FG_OlderData --current month -10 on OlderData
, FG_OlderData --current month -9 on OlderData
, FG_OlderData --current month -8 on OlderData
, FG_OlderData --current month -7 on OlderData
, FG_OlderData --current month -6 on OlderData
, FG_OlderData --current month -5 on OlderData
, FG_OlderData --current month -4 on OlderData
, FG_OlderData --current month -3 on NewerData
, FG_NewerData --current month -2 on NewerData
, FG_NewerData --current month -1 on NewerData
, FG_NewerData --current month on NewerData
, FG_NewerData --next month on NewerData
, FG_NewerData --future month on NewerData
);
GO
--secondary partition function identical to main function
CREATE PARTITION FUNCTION PF_12MonthSlidingWindow_Secondary (datetime2(0))
AS RANGE RIGHT FOR VALUES (
'2014-11-01T00:00:00' --current month -12 on OlderData filegroup
, '2014-12-01T00:00:00' --current month -11 on OlderData filegroup
, '2015-01-01T00:00:00' --current month -10 on OlderData filegroup
, '2015-02-01T00:00:00' --current month -9 on OlderData filegroup
, '2015-03-01T00:00:00' --current month -8 on OlderData filegroup
, '2015-04-01T00:00:00' --current month -7 on OlderData filegroup
, '2015-05-01T00:00:00' --current month -6 on OlderData filegroup
, '2015-06-01T00:00:00' --current month -5 on OlderData filegroup
, '2015-07-01T00:00:00' --current month -4 on OlderData filegroup
, '2015-08-01T00:00:00' --current month -3 on NewerData filegroup
, '2015-09-01T00:00:00' --current month -2 on NewerData filegroup
, '2015-10-01T00:00:00' --current month -1 on NewerData filegroup
, '2015-11-01T00:00:00' --current month on NewerData filegroup
, '2015-12-01T00:00:00' --next month on NewerData filegroup
, '2016-01-01T00:00:00' --future month
)
GO
--secondary partition scheme identical to main scheme
CREATE PARTITION SCHEME PS_12MonthSlidingWindow_Secondary
AS PARTITION PF_12MonthSlidingWindow_Secondary
TO (
FG_OlderData --older than 12 montths
, FG_OlderData --current month -12 on OlderData
, FG_OlderData --current month -11 on OlderData
, FG_OlderData --current month -10 on OlderData
, FG_OlderData --current month -9 on OlderData
, FG_OlderData --current month -8 on OlderData
, FG_OlderData --current month -7 on OlderData
, FG_OlderData --current month -6 on OlderData
, FG_OlderData --current month -5 on OlderData
, FG_OlderData --current month -4 on OlderData
, FG_OlderData --current month -3 on NewerData
, FG_NewerData --current month -2 on NewerData
, FG_NewerData --current month -1 on NewerData
, FG_NewerData --current month on NewerData
, FG_NewerData --next month on NewerData
, FG_NewerData --future month on NewerData
);
GO
--main partitioned table
CREATE TABLE dbo.SlidingWindowTable(
PartitioningColumn datetime2(0)
, OtherKeyColumn int NOT NULL
, OtherData int NULL
, CONSTRAINT PK_SlidingWindowTable PRIMARY KEY
CLUSTERED (PartitioningColumn, OtherKeyColumn)
ON PS_12MonthSlidingWindow(PartitioningColumn)
) ON PS_12MonthSlidingWindow(PartitioningColumn);
GO
--The staging table initially uses the same scheme as the main table
--except with maintenance is in progress.
CREATE TABLE dbo.SlidingWindowTable_Staging(
PartitioningColumn datetime2(0)
, OtherKeyColumn int NOT NULL
, OtherData int NULL
, CONSTRAINT PK_SlidingWindowTable_Staging PRIMARY KEY
CLUSTERED (PartitioningColumn, OtherKeyColumn)
ON PS_12MonthSlidingWindow(PartitioningColumn)
) ON PS_12MonthSlidingWindow(PartitioningColumn);
GO
--This second staging table uses the same scheme as the main table and
--is used to switch data out of the main table after a partition is copied.
CREATE TABLE dbo.SlidingWindowTable_Staging2(
PartitioningColumn datetime2(0)
, OtherKeyColumn int NOT NULL
, OtherData int NULL
, CONSTRAINT PK_SlidingWindowTable_Staging2 PRIMARY KEY
CLUSTERED (PartitioningColumn, OtherKeyColumn)
ON PS_12MonthSlidingWindow(PartitioningColumn)
) ON PS_12MonthSlidingWindow(PartitioningColumn);
GO
WITH
t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))
,t256 AS (SELECT 0 AS n FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d)
,t16M AS (SELECT ROW_NUMBER() OVER (ORDER BY (a.n)) - 1 AS num FROM t256 AS a CROSS JOIN t256 AS b CROSS JOIN t256 AS c)
INSERT INTO dbo.SlidingWindowTable WITH (TABLOCKX) (PartitioningColumn, OtherKeyColumn, OtherData)
SELECT DATEADD(month, num/1000000, '20141101'), num, num
FROM t16M
WHERE num <= 13000000;
GO
CREATE PROCEDURE dbo.PerformMonthlySlidingWindowMaintenace
@ExpiredMonthStartDate datetime2(0) --purge data for this month
, @MonthStartDateToMoveFromCurrentToOld datetime2(0) --move data from this month from current data filegroup to old data filegroup
, @FutureMonthStartDate datetime2(0) --create new partition for this date on current data filegroup
/*
This proc performs partition maintenance needed to maintain a monthly sliding
window with filegroups on tiered storage. The objective is to keep recent frequently
used data on filegroup FG_NewData and less often accessed data on PS_12MonthSlidingWindow_Secondary.
*/
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @result int;
BEGIN TRY
BEGIN TRAN;
--acquire exclusive lock on main table to prevent deadlocking during partition maintenance
SET @result = (SELECT TOP (0) 1 FROM dbo.SlidingWindowTable WITH (TABLOCKX));
--**************************
--*** Purge expired data ***
--**************************
--purge oldest partition from main table (including partiton for data older than first boundary)
TRUNCATE TABLE dbo.SlidingWindowTable_Staging;
ALTER TABLE dbo.SlidingWindowTable
SWITCH PARTITION $PARTITION.PF_12MonthSlidingWindow(DATEADD(month, -1, @ExpiredMonthStartDate))
TO dbo.SlidingWindowTable_Staging PARTITION $PARTITION.PF_12MonthSlidingWindow_Secondary(DATEADD(month, -1, @ExpiredMonthStartDate));
ALTER TABLE dbo.SlidingWindowTable
SWITCH PARTITION $PARTITION.PF_12MonthSlidingWindow(@ExpiredMonthStartDate)
TO dbo.SlidingWindowTable_Staging PARTITION $PARTITION.PF_12MonthSlidingWindow_Secondary(@ExpiredMonthStartDate);
TRUNCATE TABLE dbo.SlidingWindowTable_Staging;
--remove expired partition boundary from both functions after purge
ALTER PARTITION FUNCTION PF_12MonthSlidingWindow()
MERGE RANGE (@ExpiredMonthStartDate);
ALTER PARTITION FUNCTION PF_12MonthSlidingWindow_Secondary()
MERGE RANGE (@ExpiredMonthStartDate);
--*******************************
--*** Prepare for future data ***
--*******************************
--add new partition on new data filegroup for future data to both functions/schemes
ALTER PARTITION SCHEME PS_12MonthSlidingWindow
NEXT USED FG_NewerData;
ALTER PARTITION FUNCTION PF_12MonthSlidingWindow()
SPLIT RANGE (@FutureMonthStartDate);
ALTER PARTITION SCHEME PS_12MonthSlidingWindow_Secondary
NEXT USED FG_NewerData;
ALTER PARTITION FUNCTION PF_12MonthSlidingWindow_Secondary()
SPLIT RANGE (@FutureMonthStartDate);
--this will release the exclusve table lock
COMMIT;
--********************************************************
--*** move partition from FG_NewerData to FG_OlderData ***
--********************************************************
--alter secondary partition function and scheme to move partition from FG_NewerData to FG_OlderData
ALTER PARTITION FUNCTION PF_12MonthSlidingWindow_Secondary()
MERGE RANGE (@MonthStartDateToMoveFromCurrentToOld);
ALTER PARTITION SCHEME PS_12MonthSlidingWindow_Secondary
NEXT USED FG_OlderData;
ALTER PARTITION FUNCTION PF_12MonthSlidingWindow_Secondary()
SPLIT RANGE (@MonthStartDateToMoveFromCurrentToOld);
--rebuild staging table using new partition scheme to move partition to filegroup FG_OlderData
CREATE UNIQUE CLUSTERED INDEX PK_SlidingWindowTable_Staging
ON dbo.SlidingWindowTable_Staging(PartitioningColumn, OtherKeyColumn)
WITH(DROP_EXISTING=ON)
ON PS_12MonthSlidingWindow_Secondary(PartitioningColumn);
--copy data from main table into corresponding staging table partition, now on FG_OlderData
INSERT INTO dbo.SlidingWindowTable_Staging WITH(TABLOCKX) (PartitioningColumn, OtherKeyColumn, OtherData)
SELECT
PartitioningColumn
, OtherKeyColumn
, OtherData
FROM dbo.SlidingWindowTable
WHERE
PartitioningColumn >= @MonthStartDateToMoveFromCurrentToOld
AND PartitioningColumn < DATEADD(month, 1, @MonthStartDateToMoveFromCurrentToOld);
BEGIN TRAN;
--acquire exclusive lock on main table to prevent deadlocking during partition maintenance
SET @result = (SELECT TOP (0) 1 FROM dbo.SlidingWindowTable WITH (TABLOCKX));
--make sure the second staging table is empty
TRUNCATE TABLE dbo.SlidingWindowTable_Staging2;
--switch out partition to be moved on FG_NewData into second staging table
ALTER TABLE dbo.SlidingWindowTable
SWITCH PARTITION $PARTITION.PF_12MonthSlidingWindow(@MonthStartDateToMoveFromCurrentToOld)
TO dbo.SlidingWindowTable_Staging2 PARTITION $PARTITION.PF_12MonthSlidingWindow(@MonthStartDateToMoveFromCurrentToOld);
--remove redundant copy of data
TRUNCATE TABLE dbo.SlidingWindowTable_Staging2;
--switch the latest partition of main table on the FG_OlderData filegroup to the staging table
--so that adjacent partitions of the main table are empty for the MERGE and SPLIT needed to
--physically move the main table partition to FG_OlderData
ALTER TABLE dbo.SlidingWindowTable
SWITCH PARTITION $PARTITION.PF_12MonthSlidingWindow(DATEADD(month, -1, @MonthStartDateToMoveFromCurrentToOld))
TO dbo.SlidingWindowTable_Staging PARTITION $PARTITION.PF_12MonthSlidingWindow_Secondary(DATEADD(month, -1, @MonthStartDateToMoveFromCurrentToOld));
--remove empty partition
ALTER PARTITION FUNCTION PF_12MonthSlidingWindow()
MERGE RANGE (@MonthStartDateToMoveFromCurrentToOld);
--create new empty partition on old data filegroup for moved data
ALTER PARTITION SCHEME PS_12MonthSlidingWindow
NEXT USED FG_OlderData;
ALTER PARTITION FUNCTION PF_12MonthSlidingWindow()
SPLIT RANGE (@MonthStartDateToMoveFromCurrentToOld);
--now that both partition functions/schemes are identical, switch partitions from staging table back into main table
ALTER TABLE dbo.SlidingWindowTable_Staging
SWITCH PARTITION $PARTITION.PF_12MonthSlidingWindow_Secondary(DATEADD(month, -1, @MonthStartDateToMoveFromCurrentToOld))
TO dbo.SlidingWindowTable PARTITION $PARTITION.PF_12MonthSlidingWindow(DATEADD(month, -1, @MonthStartDateToMoveFromCurrentToOld));
ALTER TABLE dbo.SlidingWindowTable_Staging
SWITCH PARTITION $PARTITION.PF_12MonthSlidingWindow_Secondary(@MonthStartDateToMoveFromCurrentToOld)
TO dbo.SlidingWindowTable PARTITION $PARTITION.PF_12MonthSlidingWindow(@MonthStartDateToMoveFromCurrentToOld);
COMMIT;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
THROW;
END CATCH;
GO
CHECKPOINT
DBCC DROPCLEANBUFFERS
--when scheduling after the start of a new month, set @RunMonth to the first day of the current month
DECLARE
--@RunMonth datetime2(0) = DATEADD(day, 1, DATEADD(month, -1, EOMONTH(GETDATE())));
@RunMonth datetime2(0) = '20151201';
--calculate boundary dates based on @RunMonth
DECLARE
@ExpiredMonthStartDate datetime2(0) = DATEADD(month, -13, @RunMonth)
, @MonthStartDateToMoveFromCurrentToOld datetime2(0) = DATEADD(month, -4, @RunMonth)
, @FutureMonthStartDate datetime2(0) = DATEADD(month, 2, @RunMonth);
SELECT
@ExpiredMonthStartDate AS ExpiredMonthStartDate
, @MonthStartDateToMoveFromCurrentToOld AS MonthStartDateToMoveFromCurrentToOld
, @FutureMonthStartDate AS FutureMonthStartDate;
EXEC dbo.PerformMonthlySlidingWindowMaintenace
@ExpiredMonthStartDate = @ExpiredMonthStartDate
, @MonthStartDateToMoveFromCurrentToOld = @MonthStartDateToMoveFromCurrentToOld
, @FutureMonthStartDate = @FutureMonthStartDate;
GO