SQL Server 2016 SP1 Standard Edition Enhancements

I seldom get excited about service packs but the changes released with SQL Server 2016 SP1 are the most significant I’ve seen in a SQL Server service pack in 20+ years. Microsoft announced this week at the Microsoft Connect(); developer’s conference that SQL Server 2016 SP1, which is available for download immediately, allows features previously available only in Enterprise/Developer Editions to be used in lessor Standard, Web, Express, and LocalDB Editions too. Features like table partitioning, In-Memory OLTP, and columnstore are now options for developers and DBAs using SQL Server Standard Edition and even the free Express Edition in production. See SQL Server 2016 Service Pack 1 (SP1) released !!! for the complete matrix of programmability features by edition along with other cool SP1 information.

The implications are huge now that SQL Server has the same programmability surface area among editions. The choice of the production edition can be made independently based on operational needs rather than programmability features. Developers can use a free edition (i.e. LocalDB, Express or Developer) without fear a feature won’t be available in production as long as prod is running SQL Server 2016 SP1 or greater. DBAs can now choose the appropriate edition for production based on other considerations like advanced high availability, TDE, Auditing as well as performance features like higher supported memory, more number of cores, and advanced scanning. This separation of concerns avoids the need to lock in the production edition early in the application lifecycle, making development easier and production implementation more flexible.

Real World Use Case Scenario
I work with an ISV with hundreds of customers running a mix of Standard and Enterprise Edition. Their needs vary widely and SQL Server Enterprise Edition is not an option for some due to budget constraints. Some tables are often quite large so partitioning is required for manageability and, for their reporting workload, partitioning also improves performance of large scans due to partition elimination. The ugliness though, is that table partitioning (and/or columnstore) is the right tool for the job but was not an option for customers on Standard Edition.

The ISV initially compromised and used view partitioning instead of table partitioning so that the same code would run regardless of edition. Although that provided the expected manageability benefits, there were some downsides. Compilation time increased significantly as the number of partitioned view member tables increased as did the query plan complexity. This sometimes resulted in poor query plans against very large tables and especially impacted larger and most valued customers, most of which were running Enterprise Edition.

To address the problem before SQL Server 2016 SP1, the ISV added conditional code to the application so that either view or table partitioning could be used depending on the SQL Server edition. This wasn’t ideal as it added code complexity and doubled the number of QA test cases for application features that performed partition maintenance. However, since the resultant benefits for their larger customers on Enterprise Edition were quite significant; the additional costs of development and testing were well-justified.

Now that table partitioning is available in SQL Server 2016 SP1 Standard Edition, they plan to require SQL Server 2016 SP1 (or later) going forward, use table partitioning unconditionally, and perhaps introduce usage of other features like columnstore that were previously Enterprise only. Not only will this simplify the code base and test cases, customers on Standard Edition will be happier with their experience and can upgrade to Enterprise if they so choose without reinstalling or reconfiguring the application. It will of course take some time before all their customers upgrade to the latest product version and SQL 2016 SP1+ but the future is much brighter now.

Perform Due Diligence
If you are new to features previously available only in Enterprise Edition, I suggest you perform due diligence before using these features. Memory-optimized features like columnstore and In-Memory OLTP require additional physical memory and insufficient memory with memory-optimized features will be a production show-stopper. Make sure your hardware is sized appropriately regardless of edition and, in the case of editions other than Enterprise or Developer, memory requirements don’t exceed the maximum capacity limits for that edition. Although very powerful, In-Memory OLTP is a fundamentally different paradigm that you might be accustomed to regarding transactional behavior and isolation levels. Be sure you fully understand these features before using it in development or production.

Summary
I hope these changes are enough motivation for you to consider upgrading to SQL Server 2016 SP1, especially if you are running Standard Edition or are currently on an older SQL Server version. Together with the fact that SQL Server 2016 just runs faster, the time and effort spend in upgrading is a solid investment that will pay dividends regardless of edition.

Add or Remove IDENTITY Property From an Existing Column Efficiently

Introduction
Refactoring is often needed to improve schema design or address changes in requirements. Occasionally, these schema modifications require removing the IDENTITY property from an existing non-empty table while retaining the existing values. Unfortunately, there is no DDL command to add or remove IDENTITY from an existing column. Traditionally, one must go through the pain of either recreating the table or jumping through hoops by adding a new column, updating the new column value with the old column value, and dropping the original column. This is especially problematic with large tables and short maintenance windows.

This article details how to use SWITCH as an alternative technique to add or remove IDENTITY from an existing column. I have been using SWITCH for many years but learned only recently that the identical schema restriction for SWITCH does not include IDENTITY column property; one can SWITCH data from one table to another even though the IDENTITY column property differs. This behavior allows the IDENTITY property to be added or removed.

Overview
All tables are partitioned from a database storage engine perspective since SQL Server 2005, although multiple partitions require Enterprise Edition. The implication of this storage architecture is that one can use ALTER TABLE…SWITCH regardless of SQL Server edition to move the entire contents of one table to another without physically moving rows as long as the source and target tables have an identical schema and are physically aligned (indexes and tables on same filegroup). SWITCH performs fast storage meta-data changes so the operation typically takes less than a second regardless of table size. The target table must be empty before SWITCH. After SWITCH, the target table will contain the data and the source table will be empty (similar to TRUNCATE).

Since the identical schema restriction does not include the IDENTITY column property, SWITCH is a handy technique to add or remove IDENTITY from an existing column of a non-empty table. For an empty table, a simple drop and create is easier and more efficient.

Remove IDENTITY from an Existing Column
The script below uses a staging table without the IDENTITY column property along with SWITCH to remove the IDENTITY property from the column and retain previously assigned identity values. I drop the empty original table and rename the staging table to the original name along with constraints and indexes after the operation.

--create sample table and data
CREATE TABLE dbo.AddIdentityExample(
	  IncrementalColumn int NOT NULL
		CONSTRAINT PK_AddIdentityExample PRIMARY KEY
	, OtherData varchar(20) NULL
	);
CREATE INDEX idx_AddIdentityExample_OtherData
	ON dbo.AddIdentityExample(OtherData);
INSERT INTO dbo.AddIdentityExample VALUES
	  (1, 'Sample data one')
	, (2, 'Sample data two')
	, (3, 'Sample data three');
GO

SET XACT_ABORT ON;
BEGIN TRY
	BEGIN TRAN;
	--create staging table with same schema, indexes, and constraints
	CREATE TABLE dbo.AddIdentityExampleStaging(
		  IncrementalColumn int IDENTITY NOT NULL --IDENTITY column property added
			CONSTRAINT PK_AddIdentityExampleStaging PRIMARY KEY
		, OtherData varchar(20) NULL
		);
	CREATE INDEX idx_AddIdentityExampleStaging_OtherData
		ON dbo.AddIdentityExampleStaging(OtherData);

	ALTER TABLE dbo.AddIdentityExample
		SWITCH TO dbo.AddIdentityExampleStaging;
	DROP TABLE dbo.AddIdentityExample;
	--rename table
	EXEC sp_rename
		  @objname = N'dbo.AddIdentityExampleStaging'
		, @newname = N'AddIdentityExample'
		, @objtype = 'OBJECT';
	--rename constraints
	EXEC sp_rename
		  @objname = N'dbo.PK_AddIdentityExampleStaging'
		, @newname = N'PK_AddIdentityExample'
		, @objtype = 'OBJECT';
	--rename indexes
	EXEC sp_rename
		  @objname = N'dbo.AddIdentityExample.idx_AddIdentityExampleStaging_OtherData'
		, @newname = N'idx_AddIdentityExampleStaging_OtherData'
		, @objtype = N'INDEX';
	--seed IDENTITY with current max column value
	DBCC CHECKIDENT(N'dbo.AddIdentityExample');
	COMMIT;
END TRY
BEGIN CATCH
	IF @@TRANCOUNT > 0 ROLLBACK;
	THROW;
END CATCH;
GO

Add IDENTITY to an Existing Column
This script uses a staging table with the IDENTITY column property along with SWITCH to add the IDENTITY property while retaining the existing column values. After the SWITCH operation, DBCC CHECKIDENT seeds the next IDENTITY value greater than the current value in the column.

Note that an IDENTITY column must now allow NULL values so, in the case of a nullable column, the column must first be altered to NOT NULL before using this method.

--create sample table and data
CREATE TABLE dbo.RemoveIdentityExample(
	  IncrementalColumn int IDENTITY NOT NULL
		CONSTRAINT PK_RemoveIdentityExample PRIMARY KEY
	, OtherData varchar(20) NULL
	);
CREATE INDEX idx_RemoveIdentityExample_OtherData
	ON dbo.RemoveIdentityExample(OtherData);
INSERT INTO dbo.RemoveIdentityExample VALUES
	  ('Sample data one')
	, ('Sample data two')
	, ('Sample data three');
GO

SET XACT_ABORT ON;
BEGIN TRY
	BEGIN TRAN;
	--create staging table with same schema, indexes, and constraints
	CREATE TABLE dbo.RemoveIdentityExampleStaging(
		  IncrementalColumn int NOT NULL --IDENTITY column property removed
			CONSTRAINT PK_RemoveIdentityExampleStaging PRIMARY KEY
		, OtherData varchar(20) NULL
		);
	CREATE INDEX idx_RemoveIdentityExampleStaging_OtherData
		ON dbo.RemoveIdentityExampleStaging(OtherData);

	ALTER TABLE dbo.RemoveIdentityExample
		SWITCH TO dbo.RemoveIdentityExampleStaging;
	DROP TABLE dbo.RemoveIdentityExample;
	--rename table
	EXEC sp_rename
		  @objname = N'dbo.RemoveIdentityExampleStaging'
		, @newname = N'RemoveIdentityExample'
		, @objtype = 'OBJECT';
	--rename constraints
	EXEC sp_rename
		  @objname = N'dbo.PK_RemoveIdentityExampleStaging'
		, @newname = N'PK_RemoveIdentityExample'
		, @objtype = 'OBJECT';
	--rename indexes
	EXEC sp_rename
		  @objname = N'dbo.RemoveIdentityExample.idx_RemoveIdentityExampleStaging_OtherData'
		, @newname = N'idx_RemoveIdentityExampleStaging_OtherData'
		, @objtype = N'INDEX';
	COMMIT;
END TRY
BEGIN CATCH
	IF @@TRANCOUNT > 0 ROLLBACK;
	THROW;
END CATCH;
GO



--create sample table and data
CREATE TABLE dbo.AddIdentityExample(
	  IncrementalColumn int NOT NULL
		CONSTRAINT PK_AddIdentityExample PRIMARY KEY
	, OtherData varchar(20) NULL
	);
CREATE INDEX idx_AddIdentityExample_OtherData
	ON dbo.AddIdentityExample(OtherData);
INSERT INTO dbo.AddIdentityExample VALUES
	  (1, 'Sample data one')
	, (2, 'Sample data two')
	, (3, 'Sample data three');
GO

SET XACT_ABORT ON;
BEGIN TRY
	BEGIN TRAN;
	--create staging table with same schema, indexes, and constraints
	CREATE TABLE dbo.AddIdentityExampleStaging(
		  IncrementalColumn int IDENTITY NOT NULL --IDENTITY column property added
			CONSTRAINT PK_AddIdentityExampleStaging PRIMARY KEY
		, OtherData varchar(20) NULL
		);
	CREATE INDEX idx_AddIdentityExampleStaging_OtherData
		ON dbo.AddIdentityExampleStaging(OtherData);

	ALTER TABLE dbo.AddIdentityExample
		SWITCH TO dbo.AddIdentityExampleStaging;
	DROP TABLE dbo.AddIdentityExample;
	--rename table
	EXEC sp_rename
		  @objname = N'dbo.AddIdentityExampleStaging'
		, @newname = N'AddIdentityExample'
		, @objtype = 'OBJECT';
	--rename constraints
	EXEC sp_rename
		  @objname = N'dbo.PK_AddIdentityExampleStaging'
		, @newname = N'PK_AddIdentityExample'
		, @objtype = 'OBJECT';
	--rename indexes
	EXEC sp_rename
		  @objname = N'dbo.AddIdentityExample.idx_AddIdentityExampleStaging_OtherData'
		, @newname = N'idx_AddIdentityExampleStaging_OtherData'
		, @objtype = N'INDEX';
	DBCC CHECKIDENT(N'dbo.AddIdentityExample');
	COMMIT;
	UPDATE STATISTICS dbo.AddIdentityExample;
END TRY
BEGIN CATCH
	IF @@TRANCOUNT > 0 ROLLBACK;
	THROW;
END CATCH;
GO

Tiered Storage Partition Copy

In my last tiered storage sliding window post, I shared a sliding window script that also moves an older partition to a different filegroup as part of a tiered storage strategy. This approach allows one to keep actively used partitions on the fastest storage available while keeping older less frequently used data on less expensive storage. That version of the script moves data efficiently using a staging table and CREATE INDEX…DROP EXISTING but the downside is the data being moved is unavailable for querying from the time the partition is switched out of the main table until it is switched back in. Consequently, the maintenance needs to be scheduled during a window where data in the partition being moved isn’t needed.

This follow-up article shows an alternative copy technique instead. Data in the partition being moved is left in the main table until the after the copy completes. Once data are copied to the different filegroup, partition maintenance is performed to switch out the original partition and switch in the copied data, now on the older data filegroup. This allows data to remain online except during the final partition switches, which are fast meta-data operations. It is assumed that the data in the partition being moved is read-only during this process.

Copy Data to Slower Storage
Unfortunately, neither ALTER INDEX nor CREATE INDEX…DROP EXISTING provide the capability to repartition an individual partition of a table/index. ALTER INDEX allows one to target a specific partition during a REORGANIZE or REBUILD but not change the filegroup or partition scheme in the process. CREATE INDEX…DROP EXISTING allows a filegroup or partition scheme specification but applies to the entire index; individual partitions cannot be specified. Consequently DML (INSERT…SELECT) instead of DDL must be used in order to keep data online during the copy process.

I’ll assume you’ve already slid the window by purging expired data and preparing for new data as detailed in my last article with the only task remaining to move an older partition to a slower storage filegroup. The following steps will keep the data being moved online during the potentially long copy process. The offline operations performed in steps 3-6 are metadata operations that will complete quickly, once an exclusive table lock (to avoid deadlocks) can be granted. Note that all partition functions and schemes are identical and the staging table is empty before these steps are performed.

1) Move the empty staging table partition to the older data filgegroup
2) Load data into the moved partition of the staging table
3) Switch the copied data out of the main table into a second staging table
4) Switch the adjacent partition out of the main table into the first staging table
5) Move the main table partition to the older data filegroup
6) Switch the partitions from the first staging table back into the main table

The complete DDL and script is at the end of this article. It differs from my last article (which I’ll assume you’ve already perused) by the addition of a second staging table and this alternative move technique. Below are the individual steps.

Move the empty staging table partition to the older data filgegroup
Remember, the staging table is partitioned using the secondary partition function/scheme so that it can be repartitioned independently of the main table. The first task is to move the partition of the unused secondary partition scheme to the slower storage filegroup using MERGE and SPLIT:

1) MERGE the secondary partition function boundary of the moved month to remove it from the secondary partition function and scheme:

ALTER PARTITION FUNCTION PF_12MonthSlidingWindow_Secondary()
    MERGE RANGE (@MonthStartDateToMoveFromCurrentToOld);

2) Set the secondary partition scheme NEXT USED to slower storage filegroup:

ALTER PARTITION SCHEME PS_12MonthSlidingWindow_Secondary
    NEXT USED FG_OlderData;

3) SPLIT the secondary partition function for the moved month to recreate the partition on the slower storage filegroup:

ALTER PARTITION FUNCTION PF_12MonthSlidingWindow_Secondary()
    SPLIT RANGE (@MonthStartDateToMoveFromCurrentToOld);

Load the moved partition of the staging table
Now that the staging table partition is on the FG_OlderData filegroup, copy data for the partition to be moved into a staging table using INSERT…SELECT. Note that the WHERE clause specifies the source partition boundaries:

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);

Switch the copied data out of the main table into a second staging table
The second staging table is used to permanently remove data from the original partition, still on the NewerData filegroup.

TRUNCATE TABLE dbo.SlidingWindowTable_Staging2;
ALTER TABLE dbo.SlidingWindowTable
    SWITCH PARTITION $PARTITION.PF_12MonthSlidingWindow(@MonthStartDateToMoveFromCurrentToOld)
    TO dbo.SlidingWindowTable_Staging2 PARTITION $PARTITION.PF_12MonthSlidingWindow(@MonthStartDateToMoveFromCurrentToOld);

TRUNCATE TABLE dbo.SlidingWindowTable_Staging2;

Switch the adjacent partition out of the main table into the first staging table
Before we move the main table partition (now empty) to the OlderData filegroup, the adjacent partition is also switched out. This is technically not required but is done to follow Microsoft's recommended best practice of merging only empty partitions.

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));

Move the main table partition to the older data filegroup
The main table partition is moved from the NewerData filegroup to OlderData with merge. Keep in mind that the adjacent partition on the OlderData filegroup is empty. Data for both these partitions are in the staging table.

--remove partition from main table
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);

Switch the partitions from the first staging table back into the main table
Both main and staging table partition schemes are now identical. The partitions can now be switch back into the 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);

Scripts
Below are the complete example scripts to implement this copy sliding window technique. My last tiered storage sliding window post also includes the CREATE DATABASE, including filegroups.

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

Partitioned Tables and Tiered Storage

A sliding window has long been a mainstay use case for table partitioning. A table partitioning sliding window pattern provides an efficient way to purge data according to retention needs and allows individual partitions to be placed on segregated storage. Well-planned data placement can improve performance of parallel scans of related data, meet RTO objectives with piecemeal restores, and performance SLAs with tiered storage. One can place more actively used partitions on the fastest storage available while keeping older less often used data on slower storage, which is typically much less expensive and more abundant.

I’ve posted articles on this site that show how to automate retention of temporal data using partitioned tables and T-SQL scripts. In this post, I’ll combine those building blocks to implement a tiered storage strategy with an automated sliding window. This technique will keep the most current data on the fastest storage, move data to slower storage as temporal partitions age, purge the oldest partition, and finally prepare for new data. I’ll assume you’re already familiar with partitioning concepts, objects (PARTTION SCHEME and FUNCTION), DDL (SWITCH, SPLIT, MERGE), and partition alignment. Alignment is required in order to use the methods described here.

Tiered Storage Planning
Partitioned tables or not, remember that SQL Server provides control of data placement only at the filegroup level. Without partitioning, an entire table or index is placed on the same filegroup. SQL Server then distributes data among the underlying files according to its proportional fill algorithm. Table partitioning provides more granular control over data placement by allowing individual table/index partitions within a table to be placed on different filegroups when it is advantageous to do so to meet the objectives I mentioned earlier.

In order control placement of individual partitions on different storage, at least one filegroup for each storage tier is needed, each with at least one file on that storage tier. Avoid mixing files within a filegroup that reside on different types of storage if you want maximum control over performance. Consider that there may be multiple levels of storage abstraction (OS, virtualization, SAN, NAS, etc.) that ultimately determines where data are physical stored so one must be mindful of those implications in file placement within a storage tier filegroup.

I’ll walk through the implementation with this scenario. Let’s assume 1TB of solid state storage is available and an additional 5TB available on a spinning media SAN. Although I use only 2 storage tiers here, this technique can be extended for additional tiers if needed.

A 12 month sliding window is planned with a single partitioned table. We’ll keep the current month plus 3 prior months on solid state storage and the remaining 9 months of historical data on the SAN. This necessitates a monthly maintenance script that that performs the following tasks:

1) Purge expired data (older than 12 months)
2) Prepare for new data (on solid state storage)
3) Move data to slower storage (move the 4-month old partition from solid state to spinning media storage
4) Cleanup

I start by creating the partition function, partition scheme, partitioned table, and a staging table partitioned using the same partition scheme (complete DDL at the end of this article). The partitioned staging table avoids the need to create check constraints on the staging table in order to use switch partitions back into the main table. I also create a secondary partition function and partition scheme, identical the others but initially not used. These objects are used to move the partition and data, which I’ll discuss in detail later.

Note that I use a RANGE RIGHT partition function, which I consider to be more natural for incremental temporal data than RANGE LEFT. I also follow the recommended practice of a SPLIT and MERGE of only empty partitions in order to avoid data movement and excessive logging (about 4 times that of normal DML).

Monthly maintenance tasks are performed by a stored procedure scheduled by SQL Agent job after the start of each new month. This proc takes 3 date parameters and is run scheduled for execution after the start of a new month:

    @ExpiredMonthStartDate: partition boundary of expired data to purge.
    @MonthStartDateToMoveFromCurrentToOld: partition boundary of expired data to move to slower storage.
    @FutureMonthStartDate: partition boundary for future data to be created on fast storage.

Below are the details of the maintenance tasks.

Purge Expired data
The first task is the same with any sliding window where oldest data are purged:

1) Truncate the staging table, which is partitioned using a partition scheme identical to the main table:

TRUNCATE TABLE dbo.SlidingWindowTable_Staging;

2) Switch expired data partitions from to staging table into the corresponding staging table partition using SWITCH:

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);

3) Truncate staging table again:

TRUNCATE TABLE dbo.SlidingWindowTable_Staging;

4) Remove the first partition boundary with MERGE of the main table partition function:

ALTER PARTITION FUNCTION PF_12MonthSlidingWindow()
	MERGE RANGE (@ExpiredMonthStartDate);
ALTER PARTITION FUNCTION PF_12MonthSlidingWindow_Secondary()
	MERGE RANGE (@ExpiredMonthStartDate);

The secondary partition function is also merged to keep it identical to the main partition. Again, no objects yet use the secondary partition function/scheme.

Prepare for New Data
The second task is also similar to the typical sliding window pattern, except the new partition is specifically placed on the solid state filegroup and the secondary partition function/scheme is also modified:

1) Set NEXT USED partition schemes to solid state filegroup

ALTER PARTITION SCHEME PS_12MonthSlidingWindow
	NEXT USED FG_NewerData;
ALTER PARTITION SCHEME PS_12MonthSlidingWindow_Secondary
	NEXT USED FG_NewerData;

2) SPLIT main table partition function to create future boundary

ALTER PARTITION FUNCTION PF_12MonthSlidingWindow()
	SPLIT RANGE (@FutureMonthStartDate);
ALTER PARTITION FUNCTION PF_12MonthSlidingWindow_Secondary()
	SPLIT RANGE (@FutureMonthStartDate);

Move Data to Slower Storage
The final task is to move the 4 month old partition from solid state to spinning media storage. Unlike the previous MERGE and SPLIT, which are metadata-only operations due to the empty partitions, moving the non-empty partition requires physical movement of a potentially large amount of data. Although it is possible to do this with MERGE and SPLIT alone, that would be very inefficient. Instead, I use the DROP_EXISITNG clause of CREATE INDEX after switching the partition to the staging table. The DROP_EXISITNG clause leverages the existing index order to avoid an expensive sort operation.

Note that this technique switches the partition to be moved out of the main table so data is not available for querying during the move process. In my next post, I’ll show how to keep read-only data online during the move using INSERT…SELECT with detailed attention to performance.

1) SWITCH the partition of the month to be moved into the staging table (partitioned using the same partition scheme as the main table to ensure alignment):

ALTER TABLE dbo.SlidingWindowTable
	SWITCH PARTITION $PARTITION.PF_12MonthSlidingWindow(DATEADD(month, -1, @MonthStartDateToMoveFromCurrentToOld))
	TO dbo.SlidingWindowTable_Staging PARTITION $PARTITION.PF_12MonthSlidingWindow (DATEADD(month, -1, @MonthStartDateToMoveFromCurrentToOld));

2) Move the partition of the unused secondary partition scheme to the slower storage filegroup:

a. MERGE the secondary partition function boundary of the moved month to remove it from the secondary partition function and scheme:

ALTER PARTITION FUNCTION PF_12MonthSlidingWindow_Secondary()
	MERGE RANGE (@MonthStartDateToMoveFromCurrentToOld);

b. Set the secondary partition scheme NEXT USED to slower storage filegroup:

ALTER PARTITION SCHEME PS_12MonthSlidingWindow_Secondary
	NEXT USED FG_OlderData;

c. SPLIT the secondary partition function for the moved month to recreate the partition on the slower storage filegroup:

ALTER PARTITION FUNCTION PF_12MonthSlidingWindow_Secondary()
	SPLIT RANGE (@MonthStartDateToMoveFromCurrentToOld);

3) Rebuild the staging table on the secondary partition scheme. This will physically move the partition and data of the month to move (the only non-empty partition in the staging table) to the slower storage filegroup:

CREATE UNIQUE CLUSTERED INDEX PK_SlidingWindowTable_Staging
	ON dbo.SlidingWindowTable_Staging(PartitioningColumn, OtherKeyColumn)
	WITH(DROP_EXISTING=ON)
	ON PS_12MonthSlidingWindow_Secondary(PartitioningColumn);

4) SWITCH the partition prior to the moved month into the staging table so that the adjacent partitions of the main table are empty:

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));

5) MERGE the main table partition function to remove the moved month boundary:

ALTER PARTITION FUNCTION PF_12MonthSlidingWindow()
	MERGE RANGE (@MonthStartDateToMoveFromCurrentToOld);

6) Recreate the main table moved month partition on slower storage:

a. Set the main table partition scheme NEXT USED to the slower storage filegroup:

ALTER PARTITION SCHEME PS_12MonthSlidingWindow
	NEXT USED FG_OlderData;

b. SPLIT the main table partition scheme to recreate the moved month partition on the slower storage filegroup:

ALTER PARTITION FUNCTION PF_12MonthSlidingWindow()
	SPLIT RANGE (@MonthStartDateToMoveFromCurrentToOld);

7) SWITCH the moved and prior month partitions back into the staging 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);

Cleanup
1) Rebuild the staging table specifying the main table partition scheme to prepare for the next months maintenance. The secondary partition function and scheme are again unused and are identical to the main table partition function/scheme:

CREATE UNIQUE CLUSTERED INDEX PK_SlidingWindowTable_Staging
	ON dbo.SlidingWindowTable_Staging(PartitioningColumn, OtherKeyColumn)
	WITH(DROP_EXISTING=ON)
	ON PS_12MonthSlidingWindow(PartitioningColumn);

Complete Script
Below is a complete script to create the initial database, partition function, partition scheme, table DDL, sample data, partition maintenance stored procedure, and example usage. The D drive is on solid state storage, E and L drives are on a spinning disk SAN, with the L drive dedicated to log.

Note that the stored procedure acquires an exclusive table lock on the partitioned table to prevent deadlocking during partition maintenance. However, the duration of the transaction should be very short since only metadata operations are performed.

If you have multiple partition schemes (e.g. indexes on separate filegroups) using the same partition functions, you’ll need to create secondary schemes for those too and set the NEXT USED accordingly prior to SPLIT. I can’t think of a reason to also have different partition functions in this aligned table scenario since the functions must be identical anyway to provide alignment. If you also have different functions for the index partition schemes, those will need to be split and merged at the same time as the data partition scheme.

CREATE DATABASE SlidingWindowWithTieredStorage ON
	PRIMARY 
		( NAME = N'SlidingWindowWithTieredStorage'
		, FILENAME = N'D:\SqlDataFiles\MSSQLSERVER\SlidingWindowWithTieredStorage.mdf'
		, SIZE = 10MB
		, MAXSIZE = UNLIMITED
		, FILEGROWTH = 10MB ),
    FILEGROUP FG_NewerData 
		( NAME = N'NewerData1'
		, FILENAME = N'D:\SqlDataFiles\MSSQLSERVER\SlidingWindowWithTieredStorage_NewerData1.ndf'
		, SIZE = 1TB
		, MAXSIZE = UNLIMITED
		, FILEGROWTH = 10GB ),
    FILEGROUP FG_OlderData 
		( NAME = N'OlderData2'
		, FILENAME = N'D:\SqlDataFiles\MSSQLSERVER\SlidingWindowWithTieredStorage_OlderData2.ndf' 
		, SIZE = 1TB
		, MAXSIZE = UNLIMITED
		, FILEGROWTH = 10GB ),
    FILEGROUP FG_OlderData 
		( NAME = N'OlderData3'
		, FILENAME = N'D:\SqlDataFiles\MSSQLSERVER\SlidingWindowWithTieredStorage_OlderData3.ndf' 
		, SIZE = 1TB
		, MAXSIZE = UNLIMITED
		, FILEGROWTH = 10GB ),
    FILEGROUP FG_OlderData 
		( NAME = N'OlderData4'
		, FILENAME = N'D:\SqlDataFiles\MSSQLSERVER\SlidingWindowWithTieredStorage_OlderData4.ndf' 
		, SIZE = 1TB
		, MAXSIZE = UNLIMITED
		, FILEGROWTH = 10GB ),
    FILEGROUP FG_OlderData 
		( NAME = N'OlderData5'
		, FILENAME = N'D:\SqlDataFiles\MSSQLSERVER\SlidingWindowWithTieredStorage_OlderData5.ndf' 
		, SIZE = 1TB
		, MAXSIZE = UNLIMITED
		, FILEGROWTH = 10GB )
    LOG ON 
	( NAME = N'SlidingWindowWithTieredStorage_Log'
	, FILENAME = N'C:\SqlLogFiles\MSSQLSERVER\SlidingWindowWithTieredStorage_Log.ldf' 
	, SIZE = 1024KB 
	, MAXSIZE = 2048GB 
	, FILEGROWTH = 1GB);
GO

CREATE DATABASE SlidingWindowWithTieredStorage ON
	PRIMARY 
		( NAME = N'SlidingWindowWithTieredStorage'
		, FILENAME = N'D:\SqlDataFiles\MSSQLSERVER\SlidingWindowWithTieredStorage.mdf'
		, SIZE = 4096KB
		, MAXSIZE = UNLIMITED
		, FILEGROWTH = 1024KB ),
    FILEGROUP FG_NewerData 
		( NAME = N'NewerData'
		, FILENAME = N'D:\SqlDataFiles\MSSQLSERVER\SlidingWindowWithTieredStorage_NewerData.ndf'
		, SIZE = 4096KB
		, MAXSIZE = UNLIMITED
		, FILEGROWTH = 1024KB ),
    FILEGROUP FG_OlderData 
	( NAME = N'OlderData'
		, FILENAME = N'D:\SqlDataFiles\MSSQLSERVER\SlidingWindowWithTieredStorage_OlderData.ndf' 
		, SIZE = 4096KB 
		, MAXSIZE = UNLIMITED
		, FILEGROWTH = 1024KB )
    LOG ON 
	( NAME = N'SlidingWindowWithTieredStorage_Log'
	, FILENAME = N'C:\SqlLogFiles\MSSQLSERVER\SlidingWindowWithTieredStorage_Log.ldf' 
	, SIZE = 1024KB 
	, MAXSIZE = 2048GB 
	, FILEGROWTH = 1GB);
GO

USE SlidingWindowWithTieredStorage;
GO

--main table partition function (before start of next 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

--this is the 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

--load main table with 14M rows of sample data
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)) 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 <= 14000000;
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 XACT_ABORT ON;

BEGIN TRY

	BEGIN TRAN;

	--acquire exclusive lock on main table to prevent deadlocking during partition maintenance
	DECLARE @result int = (SELECT TOP (0) 1 FROM dbo.SlidingWindowTable WITH (TABLOCKX));

	--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);

	--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);
	
	--switch partition to be moved from FG_NewData to PS_12MonthSlidingWindow_Secondary into staging table
	ALTER TABLE dbo.SlidingWindowTable
		SWITCH PARTITION $PARTITION.PF_12MonthSlidingWindow(@MonthStartDateToMoveFromCurrentToOld)
		TO dbo.SlidingWindowTable_Staging PARTITION $PARTITION.PF_12MonthSlidingWindow(@MonthStartDateToMoveFromCurrentToOld);

	--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);

	--this will release the exclusve table lock but the data in the staging table temporarily unavailable
	COMMIT;

	--rebuild staging table using new partition scheme to move switched out data to old filegroup
	CREATE UNIQUE CLUSTERED INDEX PK_SlidingWindowTable_Staging
		ON dbo.SlidingWindowTable_Staging(PartitioningColumn, OtherKeyColumn)
		WITH(DROP_EXISTING=ON)
		ON PS_12MonthSlidingWindow_Secondary(PartitioningColumn);

	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));

	--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
	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);

	--rebuild staging table using same partition scheme as main table to prepare for next month
	CREATE UNIQUE CLUSTERED INDEX PK_SlidingWindowTable_Staging
		ON dbo.SlidingWindowTable_Staging(PartitioningColumn, OtherKeyColumn)
		WITH(DROP_EXISTING=ON)
		ON PS_12MonthSlidingWindow(PartitioningColumn);

	COMMIT;

END TRY
BEGIN CATCH

	IF @@TRANCOUNT > 0 ROLLBACK;

	THROW;

END CATCH;
GO

--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
	, @MonthStartDateToMoveFromCurrentToOld
	, @FutureMonthStartDate;

EXEC dbo.PerformMonthlySlidingWindowMaintenace
	  @ExpiredMonthStartDate = @ExpiredMonthStartDate
	, @MonthStartDateToMoveFromCurrentToOld = @MonthStartDateToMoveFromCurrentToOld
	, @FutureMonthStartDate = @FutureMonthStartDate;
GO

Table Partitioning Best Practices

SQL Server table partitioning has a number of gotchas without proper planning.  This article demonstrates those that commonly cause grief and recommends best practices to avoid them.

Implications of the Partition Function Range Specification

One needs a good understanding of how the RANGE LEFT/RIGHT specification affects partition setup and management.  The RANGE specification determines:

  • The partition created by SPLIT
  • The partition removed with MERGE
  • The permanent partition that can never be removed from partition schemes

Below are nuances of the RANGE specification that commonly surprise people.

The Partition Created by SPLIT

New partitions are created by splitting a partition function.  A partition function SPLIT splits an existing partition into 2 separate ones, changing all of the underlying partition schemes, tables, and indexes.  Below are the actions performed when a LEFT or RIGHT partition is SPLIT, with important the differences in bold:

The actions performed by a SPLIT of a RANGE LEFT partition function:

  • Identify existing partition to be split, which is the one that contains the new boundary (or the last partition if no existing boundaries are higher than the one being added)
  • Add the new boundary to the partition function, maintaining boundary order and incrementing subsequent partition numbers
  • Create a new partition to the left of the existing one on the NEXT USED filegroup of each partition scheme that uses the function
  • For each table/index using the affected partition scheme(s), move rows from the existing split partition that are less than or equal to the new boundary into the newly created partition on the left

The actions performed by a SPLIT of a RANGE RIGHT partition function:

  • Identify existing partition to be split, which is the one that contains the new boundary (or the first partition if no existing boundaries are less than the one being added)
  • Add the new boundary to the partition function, maintaining boundary order and incrementing subsequent partition numbers
  • Create a new partition to the right of the existing one on the NEXT USED filegroup for each partition scheme that uses the function
  • For each table/index using the affected partition scheme(s), move rows from the existing split partition that are greater than or equal to the new boundary into the newly create partition on the right

I generally suggest one use RANGE RIGHT instead because it is more natural, and helps avoid common pitfalls when adding incremental partition boundaries.  RANGE LEFT behavior is not intuitive and trips up many DBAs but I will discuss for completeness.  Consider this example of a RANGE LEFT partition function on a datetime column, where the initial setup is for 2 years of data (2013 and 2014):

CREATE DATABASE PartitioningDemo ON PRIMARY
        ( NAME = N'Primary', FILENAME = N'C:\SqlDataFiles\PartitioningDemo.mdf' , SIZE = 100MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB ),
    FILEGROUP FG_2013
        ( NAME = N'FG_2013_1', FILENAME = N'C:\SqlDataFiles\PartitioningDemo_FG_2013_1.ndf' , SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB ),
    FILEGROUP FG_2014
        ( NAME = N'FG_2014_1', FILENAME = N'C:\SqlDataFiles\PartitioningDemo_FG_2014_1.ndf' , SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB )
    LOG ON
        ( NAME = N'Log_1', FILENAME = N'C:\SqlLogFiles\PartitioningDemo_Log_1.ldf' , SIZE = 100MB , MAXSIZE = 10GB , FILEGROWTH = 10MB);
GO

USE PartitioningDemo;
GO

CREATE PARTITION FUNCTION PF_DateTimeLeft(datetime) AS
    RANGE LEFT FOR VALUES(
        N'2013-12-31T23:59:59.997'
    );

CREATE PARTITION SCHEME PS_DateTimeLeft AS
    PARTITION PF_DateTimeLeft TO(
        FG_2013
        , FG_2014
    );

CREATE TABLE dbo.PartitionedTableLeft(
    DateTimeColumn datetime
    ) ON PS_DateTimeLeft(DateTimeColumn);

INSERT INTO dbo.PartitionedTableLeft VALUES
      (N'2013-01-01T01:02:03.340')
    , (N'2013-02-03T04:05:06.780')
    , (N'2014-01-01T01:02:03.340')
    , (N'2014-02-03T04:05:06.780')
    , (N'2014-03-04T05:06:07.890');
GO

ALTER DATABASE PartitioningDemo
    ADD FILEGROUP FG_2015;

ALTER DATABASE PartitioningDemo
    ADD FILE
        ( NAME = N'FG_2015_1', FILENAME = N'C:\SqlDataFiles\PartitioningDemo_FG_2015_1.ndf' , SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB )
    TO FILEGROUP FG_2015;

ALTER PARTITION SCHEME PS_DateTimeLeft
    NEXT USED FG_2015;

ALTER PARTITION FUNCTION PF_DateTimeLeft()
    SPLIT RANGE(N'2014-12-31T23:59:59.997');

INSERT INTO dbo.PartitionedTableLeft VALUES
    (N'2015-01-01T01:02:03.340');
GO

This initial setup results in 2 partitions and data properly mapped to the 2 yearly filegroups of the scheme.  Now, we need to prepare for year 2015 so we add a new filegroup, set the NEXT USED filegroup, SPLIT the function for year 2015, and insert data for 2015:

ALTER DATABASE PartitioningDemo
    ADD FILEGROUP FG_2015;

ALTER DATABASE PartitioningDemo
    ADD FILE
        ( NAME = N'FG_2015_1', FILENAME = N'C:\SqlDataFiles\PartitioningDemo_FG_2015_1.ndf' , SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB )
    TO FILEGROUP FG_2015;

ALTER PARTITION SCHEME PS_DateTimeLeft
    NEXT USED FG_2015;

ALTER PARTITION FUNCTION PF_DateTimeLeft()
    SPLIT RANGE(N'2014-12-31T23:59:59.997');

INSERT INTO dbo.PartitionedTableLeft VALUES
    (N'2015-01-01T01:02:03.346');
GO

This SPLIT results in adding the new boundary to the function as expected:

CREATE PARTITION FUNCTION PF_DateTimeLeft(datetime) AS
    RANGE LEFT FOR VALUES(
          N'2013-12-31T23:59:59.997'
        , N'2014-12-31T23:59:59.997'
);

However, the partition scheme is not as desired.  The resultant partition scheme is:

CREATE PARTITION SCHEME PS_DateTimeLeft AS
    PARTITION PF_DateTimeLeft TO(
          FG_2013
        , FG_2015
        , FG_2014
    );

As you can see, the filegroup for 2015 was inserted into the scheme before the 2014 one, resulting in 2014 data in the FG_2015 filegroup and 2015 data in the FG_2014 filegroup.  Furthermore, not only is the filegroup mapping now wrong, data movement was required to move the entire year of 2014 data into the new partition.  No big deal here since only 3 rows were moved by this demo script but in a production table, this movement could be a show stopper.  Logging during SWITCH/MERGE data movement during is about 4 times that of normal DML, which is especially costly when working with large tables containing millions or billions of rows as is commony used in table partitioning.

Remember that the new partition by a SPLIT is the one that includes the specified boundary, which is to the left of the existing split partition with a RANGE LEFT function (the 2014 partition here).  Data are moved from the existing SPLIT partition into the newly created partition according to the boundaries of the new partition (greater than ‘2013-12-31T23:59:59.997’ and less than or equal to ‘2014-12-31T23:59:59.997’).

Again, I recommend using a RANGE RIGHT function to avoid this non-intuitive behavior.  Below is the equivalent RANGE RIGHT script for yearly partitions, which results in the desired filegroup mappings as well as no data movement.  Also, note that the datetime boundaries are exact date specifications with RANGE RIGHT, which is also more intuitive when working with temporal datetime, datetime2, and datetimeoffset data types that include a time component.

CREATE PARTITION FUNCTION PF_DateTimeRight(datetime) AS
    RANGE RIGHT FOR VALUES(
        N'2014-01-01T00:00:00.000'
    );

CREATE PARTITION SCHEME PS_DateTimeRight AS
    PARTITION PF_DateTimeRight TO(
          FG_2013
        , FG_2014
    );

CREATE TABLE dbo.PartitionedTableRight(
    DateTimeColumn datetime
    ) ON PS_DateTimeRight(DateTimeColumn);

INSERT INTO dbo.PartitionedTableRight VALUES
      (N'2013-01-01T01:02:03.340')
    , (N'2013-02-03T04:05:06.780')
    , (N'2014-01-01T01:02:03.340')
    , (N'2014-02-03T04:05:06.780')
    , (N'2014-03-04T05:06:07.890');
GO

ALTER PARTITION SCHEME PS_DateTimeRight
    NEXT USED FG_2015;

ALTER PARTITION FUNCTION PF_DateTimeRight()
    SPLIT RANGE(N'2015-01-01T00:00:00.000');

INSERT INTO dbo.PartitionedTableRight VALUES
    (N'2015-01-01T01:02:03.346');

The resultant partition function and scheme definitions are as desired with this RANGE RIGHT partition function:

CREATE PARTITION FUNCTION PF_DateTimeRight (datetime) AS
    RANGE LEFT FOR VALUES(
          N'2014-01-01T00:00:00.000'
        , N'2015-01-01T00:00:00.000'
    );

CREATE PARTITION SCHEME PS_DateTimeRight AS
    PARTITION PF_DateTimeRight TO(
          FG_2013
        , FG_2014
        , FG_2015
    );

The Partition Removed by MERGE

When a partition is removed with MERGE, the dropped partition is the one that includes the specified boundary.  If the dropped partition is not empty, all data will be moved into the adjacent remaining partition.  Like SPLIT, costly data movement during partition maintenance should be avoided so it is best to plan such than only empty partitions are removed.  A MERGE should typically done after a purge/archive of data using SWITCH.

Below is a summary of a RANGE LEFT partition function MERGE:

  • Identify existing partition to be removed, which is the one that includes (to the left of) the specified existing boundary
  • For each table/index using the affected partition scheme(s), move rows from this partition into the adjacent right partition
  • Remove boundary from the partition function, maintaining boundary order and decrementing subsequent partition numbers
  • Remove the filegroup from each partition scheme that uses the function, unless the filegroup is mapped to another partition or is the NEXT USED filegroup

Below is a summary of a RANGE RIGHT partition function MERGE:

  • Identify existing partition to be removed, which is the one that includes (to the right of) the specified existing boundary
  • For each table/index using the affected partition scheme(s), move rows from this partition into the adjacent left partition
  • Remove boundary from the partition function, maintaining boundary order and decrementing subsequent partition numbers
  • Remove the filegroup from each partition scheme that uses the function, unless the filegroup is mapped to another partition or is the NEXT USED filegroup

I suggest explicit partition boundaries for expected data ranges to facilitate using both MERGE and SPLIT, and an additional one for the permanent partition (which I’ll detail shortly).  This practice helps ensure data are both logically and physically aligned, providing more natural partition management.

The Permanent Partition

You might not be aware that each partition scheme has a permanent partition that can never be removed.  This is the first partition of a RANGE RIGHT function and the last partition of a RANGE LEFT one.  Be mindful of this permanent partition when creating a new partition scheme when multiple filegroups are involved because the filegroup on which this permanent partition is created is determined when the partition scheme is created and cannot be removed from the scheme.

My recommendation is that one create explicit partition boundaries for all expected data ranges plus a lower and upper boundary for data outside the expected range, and map these partitions to appropriately named filegroups.  This practice will make the purpose of each partition/filegroup clear and help avoid accidentally placing data on the wrong filegroup.

Consider mapping partitions containing data outside the expected range to a dummy filegroup with no underlying files.  This will guarantee data integrity much like a check constraint because data outside the allowable range cannot be inserted.  If you must accommodate errant data rather than rejecting it outright, instead map these partitions to a generalized filegroup like DEFAULT or one designated specifically for that purpose.

I suggest specifying a NULL value for the first boundary of a RANGE RIGHT partition function.  This NULL boundary serves as the upper boundary of the permanent first partition as well as the lower boundary for the second partition containing data outside the expected range.  No rows are less than NULL so the first partition will always be empty.  It is therefore safe to map the first partition to the previously mentioned dummy filegroup even if you need to house data outside the expected range.  That being said, there is no harm in mapping the first partition to another filegroup other than lack of clarity.

For the last boundary of a RANGE RIGHT function, I suggest specifying the lowest value outside the expected range and also mapping the partition to either the dummy filegroup, or one designated to contain unexpected data.  The boundaries between the first boundary (NULL) and this one designate partitions for expected data.

Summary

In summary, I recommend a RANGE RIGHT function with the following setup:

  • First boundary value NULL
  • Subsequent boundary values for expected data partitions
  • A final boundary value of greater than the expected range
  • Map first, second, and last partitions to either a dummy filegroup or one designated for unexpected data
  • Map remaining expected data partitions to appropriately named filegroups

A similar RANGE LEFT function can be setup as follows.  I’m including this for only for completeness as the RANGE RIGHT setup above is a best practice, in my humble opinion.

  • First boundary for data less than the expected range
  • Subsequent boundaries for expected data partitions
  • A final boundary value of the maximum allowable value for the partitioning data type (which is another kludge that bolsters the case for RANGE RIGHT)
  • Map first, second from last, and last partitions to either a dummy filegroup or one designated for unexpected data
  • Map remaining expected data partitions to appropriately named filegroups

Below is an example script of applying these techniques with a RANGE RIGHT function, including adding an incremental partition for a new year.  Partitions for data outside the expected data range are mapped to the FG_NoData filegroup (which contains no files) so any attempt to insert data outside the expected range will fail.

--*********************************
--*** initial setup for 2 years ***
--*********************************

--add a dummy filegroup with no files
ALTER DATABASE PartitioningDemo
    ADD FILEGROUP FG_NoData;
GO

CREATE PARTITION FUNCTION PF_DateTimeRightWithExplictBoundaries(datetime) AS
    RANGE RIGHT FOR VALUES(
          NULL --prior to year 2013 (outside expected range)
        , N'2013-01-01T00:00:00.000' --year 2013 (within expected range)
        , N'2014-01-01T00:00:00.000' --year 2014 (within expected range)
        , N'2015-01-01T00:00:00.000' --after year 2014 (outside expected range)
    );

CREATE PARTITION SCHEME PS_DateTimeRightWithExplictBoundaries AS
    PARTITION PF_DateTimeRightWithExplictBoundaries TO(
          FG_NoData --first partition permanent partition and cannot be removed
        , FG_NoData --second partition contains data prior to year 2013 and is outside expected range
        , FG_2013 --year 2013 (within expected range)
        , FG_2014 --year 2014 (within expected range)
        , FG_NoData --after year 2014 (outside expected range)
    );

CREATE TABLE dbo.PartitionedTableRightWithExplictBoundaries(
        DateTimeColumn datetime
    ) ON PS_DateTimeRightWithExplictBoundaries(DateTimeColumn);

INSERT INTO dbo.PartitionedTableRightWithExplictBoundaries VALUES
      (N'2013-01-01T01:02:03.346')
    , (N'2013-02-03T04:05:06.780')
    , (N'2014-01-01T01:02:03.346')
    , (N'2014-02-03T04:05:06.780')
    , (N'2014-03-04T05:06:07.890');
GO

--**********************
--*** add a new year ***
--**********************

--specify FG_NoData filegroup to scheme as next used
ALTER PARTITION SCHEME PS_DateTimeRightWithExplictBoundaries
    NEXT USED FG_NoData;

--add 2016 boundary as outside expected range, mapped to FG_NoData
ALTER PARTITION FUNCTION PF_DateTimeRightWithExplictBoundaries()
    SPLIT RANGE(N'2016-01-01T00:00:00.000');

--remove 2015 boundary, currently mapped to FG_NoData
ALTER PARTITION FUNCTION PF_DateTimeRightWithExplictBoundaries()
    MERGE RANGE(N'2015-01-01T00:00:00.000');

--specify FG_2015 filegroup to scheme as next used
ALTER PARTITION SCHEME PS_DateTimeRightWithExplictBoundaries
    NEXT USED FG_2015;

--re-add 2015 boundary as within expected range, now mapped to FG_2015
ALTER PARTITION FUNCTION PF_DateTimeRightWithExplictBoundaries()
    SPLIT RANGE(N'2015-01-01T00:00:00.000');

INSERT INTO dbo.PartitionedTableRightWithExplictBoundaries VALUES
    (N'2015-01-01T01:02:03.346');

 

Remediating a Table Partitioning Mess

Partitioning often involves exceptionally large tables, which are quite unforgiving when it comes to remediating a mess due to improper partition design or maintenance.  In this post, I’ll discuss the common design mistake of using a RANGE LEFT partition function containing exact date boundaries with a date type that may include a time component (datetime, datetime2 or datetimeoffset) and show how to change it to RANGE RIGHT with minimal data movement.

Background
The most common use case for table partitioning is an incremental value.  Partitioning on a date, datetime, datetime2 or datetimeoffset value that occurs naturally in the data allows incremental load using SPLIT and SWITCH operations and purge/archive using SWITCH and MERGE.  SWITCH is nearly instantaneous regardless of the amount of underlying data involved because only meta-data changes are needed to move partitions from one table to another.  SPLIT and MERGE are also very fast meta-data only operations as long as no data movement is required.  For this reason, it is critical to setup boundaries correctly and plan as to perform SPLIT and MERGE against empty partitions whenever possible.

Note that RANGE LEFT or RANGE RIGHT specification determines:

  • The partition that contains exact boundary value matches
  • The partition is created by SPLIT
  • The partition is removed by MERGE

A common design mistake is using exact date boundaries in a RANGE LEFT function with datetime, datetime2 or datetimeoffset data type.  This error can result in rows in the wrong partition because these data types include a time component and the implications are not obvious, especially to partitioning newbies.  Rows that exactly match the date boundary (i.e. midnight on boundary date) are inserted into the lower partition instead of the higher one as desired.  For example, consider a RANGE LEFT function is a monthly sliding window scenario and boundaries of ‘2013-01-01T00:00:00’ and ‘2013-02-01T00:00:00’:

CREATE PARTITION FUNCTION PF_Monthly(datetime2(0))
    AS RANGE LEFT FOR VALUES('2013-01-01T00:00:00', '2013-02-01T00:00:00');

CREATE PARTITION SCHEME PS_Monthly AS
    PARTITION PF_Monthly TO (December2012, January2013, February2013);

This setup looks reasonable at first glance but consider the actual partitions when a table is created on the partition scheme:

  • Partition 1 (December2012 filegroup):  <= ‘2013-01-01T00:00:00’
  • Partition 2 (January2013 filegroup):  > ‘2013-01-01T00:00:00’ AND <= ‘2013-01-02T00:00:00’
  • Partition 3 (February2013 filegroup):  > ‘2013-02-01T00:00:00’

A row with the January datetime of ‘2013-01-01T00:00:00’ will end up in the first partition (December2012 filegroup).  Sliding window maintenance, which purges the oldest month (December2012), will include the ‘2013-01-01T00:00:00’ row(s) inadvertently.  Similarly, a row with value ‘2013-02-01T00:00:00’ will end up in the second partition (January2013 fielgroup) and require both the second and third partitions to be accessed when data from all of February are queried.

Remediation
There are two ways to address this problem.  One is to change the boundary values of the existing RANGE LEFT function to the maximum inclusive value instead of an exact date. For example, instead of ‘2013-01-01T00:00:00’ for January, 2013, change the boundary values to the maximum value according to the partition function data type as follows:

  • *datetime: ‘2013-01-31T23:59:59.997’
  • datetime2(0): ‘2013-01-31T23:59:59’
  • datetime2(1): ‘2013-01-31T23:59:59.9’
  • datetime2(2): ‘2013-01-31T23:59:59.99’
  • datetime2(3): ‘2013-01-31T23:59:59.999’
  • datetime2(4): ‘2013-01-31T23:59:59.9999’
  • datetime2(5): ‘2013-01-31T23:59:59.99999’
  • datetime2(6): ‘2013-01-31T23:59:59.999999’
  • datetime2(7): ‘2013-01-31T23:59:59.9999999’
  • **datetimeoffset (0): ‘2013-01-31T23:59:59-05:00’’
  • **datetimeoffset (1): ‘2013-01-31T23:59:59.9-05:00’’
  • **datetimeoffset (2): ‘2013-01-31T23:59:59.99-05:00’’
  • **datetimeoffset (3): ‘2013-01-31T23:59:59.999-05:00’’
  • **datetimeoffset (4): ‘2013-01-31T23:59:59.9999-05:00’’
  • **datetimeoffset (5): ‘2013-01-31T23:59:59.99999-05:00’’
  • **datetimeoffset (6): ‘2013-01-31T23:59:59.999999-05:00’’
  • **datetimeoffset(7): ‘2013-01-31T23:59:59.9999999-05:00’’

*Importantly, one needs to specify 997 as the milliseconds value for datetime because a higher millisecond value will be rounded to the next exact date (e.g. ‘2013-02-01T00:00:00.000’)

**The actual offset for datetimeoffset partitioning depends on your environment but is typically the same for each boundary.

Rather than changing the boundary specification, I suggest instead changing the function from LEFT to RIGHT and continue using an exact date boundary.  The inclusive date boundary is more intuitive (in my opinion) and allows the same date boundaries to be used regardless of data type.  The same amount of work is required for a mass change of existing boundaries as is required to change from RANGE LEFT to RANGE RIGHT.

The script below converts a table partitioned using a RANGE LEFT function with exact date boundaries to one partitioned RANGE RIGHT as follows:

  1. create a staging table exactly like the source table and partitioned using the same scheme
  2. create a non-partitioned holding table with the same schema source but without constraints and indexes
  3. create a new RANGE RIGHT function with the same exact date boundaries as the existing RANGE LEFT scheme
  4. create a new partition scheme with the same filegroups as the source table partition scheme but based on the new RANGE RIGHT function
  5. create a new table exactly like the source but partitioned using the new RANGE RIGHT scheme
  6. for each existing partition
    1. switch source partition to staging table
    2. move rows with exact boundary matches to holding table
    3. create check constraint on staging table matching the target right boundaries
    4. switch staging partition to new table
    5. drop check constraint
    6. after all partitions are switched to the new table, insert exact boundary matching rows to new table
    7. drop the old table and staging tables
    8. rename the new table, constraints and indexes to the old names

Since this script uses SWITCH to accomplish the bulk of the work, the table must be aligned; data and indexes must be partitioned using the same (or functionally identical) partition function.  The SWITCH operations are nearly instantaneous and the only data movement required is moving rows that exactly match the boundary values to/from the holding table, which shouldn’t take long unless you have a lot of matching rows.  The long pole in the tent from a performance perspective is likely creating the needed check constraint on the staging table because all rows must be scanned.  However, this method is still considerably faster and requires less space than repartitioning the table by recreating indexes using the new partition scheme.

Here’s the setup script for the problem table and sample data:

--existing RANGE LEFT function with exact date boundaries
CREATE PARTITION FUNCTION PF_Left(datetime)
       AS RANGE LEFT FOR VALUES('20130101','20130201');
--existing scheme based on RANGE LEFT
CREATE PARTITION SCHEME PS_Left
       AS PARTITION PF_Left ALL TO ([DEFAULT]);
--existing table, constraint and index partitioned with RANGE LEFT scheme
CREATE TABLE dbo.OldTable(
       PartitioningColumn datetime NOT NULL
       ,DataColumn int NOT NULL
       ) ON PS_Left(PartitioningColumn);
ALTER TABLE dbo.OldTable
	ADD CONSTRAINT PK_OldTable PRIMARY KEY CLUSTERED (PartitioningColumn, DataColumn) 
	ON PS_Left(PartitioningColumn);
CREATE INDEX idx_OldTable_DataColumn ON dbo.OldTable(DataColumn) 
	ON PS_Left(PartitioningColumn);
--sample data
INSERT INTO dbo.OldTable VALUES
       ('20121231',1)
       ,('20130101',1)
       ,('20130102',2)
       ,('20130103',5)
       ,('20130131',2)
       ,('20130201',4)
       ,('20130202',3);
GO

And the remediation script to change to RANGE RIGHT:

--new RANGE RIGHT function with same boundaries as existing RANGE LEFT function
CREATE PARTITION FUNCTION PF_Right(datetime)
       AS RANGE RIGHT FOR VALUES('20130101','20130201');

--new scheme based on RANGE RIGHT with same filegroups as RANGE LEFT scheme
CREATE PARTITION SCHEME PS_Right
       AS PARTITION PF_Right ALL TO ([DEFAULT]);

--new staging table exactly line existing source table
CREATE TABLE dbo.OldTableStaging(
       PartitioningColumn datetime NOT NULL
	   ,DataColumn int NOT NULL
       ) ON PS_Left(PartitioningColumn);
ALTER TABLE dbo.OldTableStaging
	ADD CONSTRAINT PK_OldTableStaging PRIMARY KEY CLUSTERED (PartitioningColumn, DataColumn) 
	ON PS_Left(PartitioningColumn);
CREATE INDEX idx_OldTableStaging_DataColumn ON dbo.OldTableStaging(DataColumn) 
	ON PS_Left(PartitioningColumn);

--new holding table for exact boundary matches (no constraints or indexes needed)
CREATE TABLE dbo.OldTableStagingExactBoundaryMatches(
       PartitioningColumn datetime NOT NULL
	   ,DataColumn int NOT NULL
       ) ON [DEFAULT];

--new table exactly like existing table but partitioned with RANGE RIGHT scheme
CREATE TABLE dbo.NewTable(
       PartitioningColumn datetime NOT NULL
	   ,DataColumn int NOT NULL
       ) ON PS_Right(PartitioningColumn);
ALTER TABLE dbo.NewTable
	ADD CONSTRAINT PK_NewTable PRIMARY KEY CLUSTERED (PartitioningColumn, DataColumn) 
	ON PS_Right(PartitioningColumn);
CREATE INDEX idx_NewTable_DataColumn ON dbo.NewTable(DataColumn) 
	ON PS_Right(PartitioningColumn);
GO

--script to convert RANGE LEFT to RANGE RIGHT
SET NOCOUNT ON;
DECLARE 
       @LowerBoundary datetime
       ,@UpperBoundary datetime
       ,@PartitionNumber int
       ,@Sql nvarchar(MAX)
       ,@PartitionFunctionName sysname = N'PF_Right';

DECLARE PartitionBoundaries CURSOR LOCAL FAST_FORWARD FOR
WITH 
       PartitionNumbers AS (
              SELECT 1 AS PartitionNumber
              UNION ALL
              SELECT boundary.boundary_id + 1
              FROM sys.partition_functions AS pf
              JOIN sys.partition_range_values AS boundary ON pf.function_id = boundary.function_id
              WHERE pf.name = @PartitionFunctionName
              )
       ,PartitionBoundaries AS (
              SELECT
                     (SELECT CAST(boundary.value AS datetime)
                     FROM sys.partition_functions AS pf
                     JOIN sys.partition_range_values AS boundary ON pf.function_id = boundary.function_id
                     WHERE
                           pf.name = @PartitionFunctionName
                           AND boundary.boundary_id = PartitionNumbers.PartitionNumber - 1
                     ) AS LowerBoundary
                     ,(SELECT CAST(boundary.value AS datetime)
                     FROM sys.partition_functions AS pf
                     JOIN sys.partition_range_values AS boundary ON pf.function_id = boundary.function_id
                     WHERE
                           pf.name = @PartitionFunctionName
                           AND boundary.boundary_id = PartitionNumbers.PartitionNumber
                     ) AS UpperBoundary
                     ,PartitionNumbers.PartitionNumber
              FROM PartitionNumbers
              )
SELECT LowerBoundary, UpperBoundary, PartitionNumber
FROM PartitionBoundaries
ORDER BY PartitionNumber;

OPEN PartitionBoundaries;
WHILE 1 = 1
BEGIN
       --get next partition boundary
       FETCH NEXT FROM PartitionBoundaries INTO @LowerBoundary, @UpperBoundary, @PartitionNumber;
       IF @@FETCH_STATUS = -1 BREAK;

       --move partition from partitioned left table to staging
       RAISERROR('Switching out partition %d', 0, 1, @PartitionNumber) WITH NOWAIT;
       ALTER TABLE dbo.OldTable
              SWITCH PARTITION @PartitionNumber TO dbo.OldTableStaging PARTITION @PartitionNumber;

       --move exact boundary matches to holding table
       DELETE FROM dbo.OldTableStaging WITH(TABLOCKX)
       OUTPUT deleted.* INTO dbo.OldTableStagingExactBoundaryMatches
       WHERE PartitioningColumn = @UpperBoundary;
       RAISERROR('Moved %d exact boundary matches to holding table', 0, 1, @@ROWCOUNT) WITH NOWAIT;

       --create check constraint to match RANGE RIGHT boundary
       SET @Sql = N'ALTER TABLE dbo.OldTableStaging
              ADD CONSTRAINT CK_TestStaging CHECK (';
       IF @LowerBoundary IS NOT NULL
       BEGIN
              SET @Sql = @Sql + N'PartitioningColumn >= ''' + CONVERT(char(8), @LowerBoundary,112) + N'''';
              IF @UpperBoundary IS NOT NULL
              BEGIN
                     SET @Sql = @Sql + N' AND ';
              END;
       END;
       IF @UpperBoundary IS NOT NULL
       BEGIN
              SET @Sql = @Sql + N'PartitioningColumn < ''' + CONVERT(char(8), @UpperBoundary,112) + N'''';
       END;
       SET @Sql = @Sql + ');';
       RAISERROR('Creating check constraint: %s', 0, 1, @Sql) WITH NOWAIT;
       EXEC sp_executesql @Sql;

       --move staging table partition to partitioned right table
       RAISERROR('Switching in partition %d', 0, 1, @PartitionNumber) WITH NOWAIT;
       ALTER TABLE dbo.OldTableStaging
              SWITCH PARTITION @PartitionNumber TO dbo.NewTable PARTITION @PartitionNumber;

       --remove staging table constraint
       RAISERROR('Dropping check constraint', 0, 1, @PartitionNumber) WITH NOWAIT;
       ALTER TABLE dbo.OldTableStaging
              DROP CONSTRAINT CK_TestStaging;

END;
CLOSE PartitionBoundaries;
DEALLOCATE PartitionBoundaries;

--insert exact boundary matches
INSERT INTO dbo.NewTable WITH (TABLOCKX) (PartitioningColumn, DataColumn) 
       SELECT PartitioningColumn, DataColumn FROM dbo.OldTableStagingExactBoundaryMatches WITH (TABLOCKX);
RAISERROR('Inserted %d exact boundary matches from holding table', 0, 1, @@ROWCOUNT) WITH NOWAIT;

IF NOT EXISTS(SELECT * FROM OldTable)
BEGIN
	--drop old table
	DROP TABLE dbo.OldTable;
	--rename table, constraints and indexes to same names as original table
	EXEC sp_rename 'dbo.NewTable.idx_NewTable_DataColumn', 'idx_OldTable_DataColumn', 'INDEX';
	EXEC sp_rename 'dbo.PK_NewTable', 'PK_OldTable';
	EXEC sp_rename 'dbo.NewTable', 'OldTable';
END;
--drop staging tables
DROP TABLE dbo.OldTableStaging;
DROP TABLE dbo.OldTableStagingExactBoundaryMatches;
GO

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.

Before maintenance:

Rows

Partition Number

Filegroup

Lower Boundary

Upper Boundary

0

1

PartitioningDemo_OlderData

 

4/1/2010 12:00:00 AM

791,549

2

PartitioningDemo_OlderData

4/1/2010 12:00:00 AM

5/1/2010 12:00:00 AM

817,935

3

PartitioningDemo_OlderData

5/1/2010 12:00:00 AM

6/1/2010 12:00:00 AM

791,550

4

PartitioningDemo_OlderData

6/1/2010 12:00:00 AM

7/1/2010 12:00:00 AM

817,935

5

PartitioningDemo_OlderData

7/1/2010 12:00:00 AM

8/1/2010 12:00:00 AM

817,935

6

PartitioningDemo_OlderData

8/1/2010 12:00:00 AM

9/1/2010 12:00:00 AM

791,550

7

PartitioningDemo_OlderData

9/1/2010 12:00:00 AM

10/1/2010 12:00:00 AM

817,935

8

PartitioningDemo_OlderData

10/1/2010 12:00:00 AM

11/1/2010 12:00:00 AM

791,550

9

PartitioningDemo_OlderData

11/1/2010 12:00:00 AM

12/1/2010 12:00:00 AM

817,935

10

PartitioningDemo_OlderData

12/1/2010 12:00:00 AM

1/1/2011 12:00:00 AM

817,935

11

PartitioningDemo_OlderData

1/1/2011 12:00:00 AM

2/1/2011 12:00:00 AM

738,780

12

PartitioningDemo_NewerData

2/1/2011 12:00:00 AM

3/1/2011 12:00:00 AM

817,935

13

PartitioningDemo_NewerData

3/1/2011 12:00:00 AM

4/1/2011 12:00:00 AM

369,476

14

PartitioningDemo_NewerData

4/1/2011 12:00:00 AM

5/1/2011 12:00:00 AM

0

15

PartitioningDemo_NewerData

5/1/2011 12:00:00 AM

 

 

After maintenance:

Rows

Partition Number

Filegroup

Lower Boundary

Upper Boundary

0

1

PartitioningDemo_OlderData

 

4/1/2010 12:00:00 AM

791,549

2

PartitioningDemo_OlderData

4/1/2010 12:00:00 AM

5/1/2010 12:00:00 AM

817,935

3

PartitioningDemo_OlderData

5/1/2010 12:00:00 AM

6/1/2010 12:00:00 AM

791,550

4

PartitioningDemo_OlderData

6/1/2010 12:00:00 AM

7/1/2010 12:00:00 AM

817,935

5

PartitioningDemo_OlderData

7/1/2010 12:00:00 AM

8/1/2010 12:00:00 AM

817,935

6

PartitioningDemo_OlderData

8/1/2010 12:00:00 AM

9/1/2010 12:00:00 AM

791,550

7

PartitioningDemo_OlderData

9/1/2010 12:00:00 AM

10/1/2010 12:00:00 AM

817,935

8

PartitioningDemo_OlderData

10/1/2010 12:00:00 AM

11/1/2010 12:00:00 AM

791,550

9

PartitioningDemo_OlderData

11/1/2010 12:00:00 AM

12/1/2010 12:00:00 AM

817,935

10

PartitioningDemo_OlderData

12/1/2010 12:00:00 AM

1/1/2011 12:00:00 AM

817,935

11

PartitioningDemo_OlderData

1/1/2011 12:00:00 AM

2/1/2011 12:00:00 AM

738,780

12

PartitioningDemo_OlderData

2/1/2011 12:00:00 AM

3/1/2011 12:00:00 AM

817,935

13

PartitioningDemo_NewerData

3/1/2011 12:00:00 AM

4/1/2011 12:00:00 AM

369,476

14

PartitioningDemo_NewerData

4/1/2011 12:00:00 AM

5/1/2011 12:00:00 AM

0

15

PartitioningDemo_NewerData

5/1/2011 12:00:00 AM

 

 

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 INDEX cdx_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 INDEX cdx_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