Introduction
Earlier articles of this series discussed what partitioning in SQL Server is; the different kinds of partitioning options, why and when we should go for partitioning and the benefits a partitioned table/index provides. Then I talked about different partitioning concepts like partition function, partition scheme, guidelines on choosing a partitioning column and creating a partition on table or index. We also learned about partitioned index and how an index is aligned and storage aligned with the base table partitioning, with an example.
This article provides you the steps and guidance needed to manage a sliding window scenario, with an example.
Sliding Window Scenario
The concept of a sliding window scenario is to manage and keep the same number of partitions on a partitioned table over time. When a new period starts, a new partition is created to accommodate the new data and at the same time the oldest partition is taken out from the partitioned table to maintain the same number of partitions. The oldest partition that has been taken out of the partitioned table either can be dropped or archived. The best part of managing a sliding window scenario in SQL Server is its meta data operation, and hence it is significantly faster.
These are the steps you need to follow for setting up a sliding window scenario:
- Create a table, which will act like a staging table and which will have same structure and will reside on the same file group as the oldest partition. Next you need to create matching clustered indexes and optionally non clustered indexes.
- Take out the oldest partition from the partitioned table into the staging table, as created above with use of the SWITCH clause with the ALTER TABLE statement.
- Modify the partition function to remove the boundary value of the oldest partition with the MERGE clause with ALTER PARTITION FUNCTION statement.
- Modify the partition scheme to designate a new file group to be used by the newest partition with the NEXT USED clause of the ALTER PARTITION SCHEME statement.
- Modify the partition function to add the boundary value for the newest partition with the SPLIT clause with the ALTER PARTITION FUNCTION statement.
The data in the staging table, which now contains the data from the oldest partition of the partitioned table, can be either deleted/dropped or can be archived to another table or another partitioned archived table.
And now, there are two ways to load data into newest partition. The first one, when you execute the INSERT statement, it moves the record to the appropriate partition and the second one, is to load bulk data using the following steps:
- Create a table, which will act like a staging table and which will have the same structure and will reside on the same file group as the newest partition.
- Load data into this staging table; please note, as this staging table is not associated to your partitioned table the bulk data load operation into this staging table will not have an impact on the partitioned table.
- Next you need to create matching clustered indexes and optionally non clustered indexes and ensure these indexes are aligned.
- Take the staging table into the newest partition of the partitioned table with use of the SWITCH clause with the ALTER TABLE statement.
- Update the index statistics to ensure indexes are utilized appropriately for the queries.
Setting up the Sliding Window Scenario
I discussed the different steps to get started with setting up a sliding window scenario for your partitioned table. Here is the code with which you can automate the processing of managing a sliding window scenario. The procedure has been created to manage a sliding window scenario for a partitioned table, which we created in earlier articles and you can further modify it to accommodate your specific need. Please test it thoroughly before using it in a production environment to ensure it suits your requirements.
As the partitioned table, which we created in the earlier article of the series, has yearly partitions, this stored procedure can be scheduled to run on the first day of every year. The procedure checks if the partition for the current year is already created or not; if not then it does following:
- Take out the oldest partition from the partitioned table into the staging table.
- Modify the partition function to remove the boundary value of the oldest partition with the MERGE clause with ALTER PARTITION FUNCTION statement.
- Modify the partition scheme to designate the new file group to be used by the newest partition with the NEXT USED clause of the ALTER PARTITION SCHEME statement.
- Modify the partition function to add the boundary value of the newest partition with the SPLIT clause with the ALTER PARTITION FUNCTION statement.
CREATE PROCEDURE [dbo].[ManageFactSlidingWindow] /***************************************************************************** PROCEDURE NAME: [ManageFactSlidingWindow] AUTHOR: Arshad Ali CREATED: 02/24/2013 DESCRIPTION: This stored procedure manages sliding window for the partitioned table VERSION HISTORY: DATE EMAIL Company DESCRIPTION ****************************************************************************/ AS BEGIN BEGIN TRY --Start the Try Block DECLARE @MinYear int DECLARE @MaxYear int DECLARE @CurrentYear int = YEAR(GETDATE()) SELECT @MinYear = MIN(CONVERT(int, Value)), @MaxYear = MAX(CONVERT(int, Value)) FROM sys.partition_functions f INNER JOIN sys.partition_range_values r ON f.function_id = r.function_id WHERE f.name = 'FactPartitionFunction' IF @MaxYear < @CurrentYear AND NOT EXISTS (SELECT TOP 1 1 FROM dbo.ArchiveFactResellerSalesWithPartition) BEGIN BEGIN TRANSACTION ALTER TABLE dbo.FactResellerSalesWithPartition SWITCH PARTITION 1 TO dbo.ArchiveFactResellerSalesWithPartition ALTER PARTITION FUNCTION FactPartitionFunction() MERGE RANGE (@MinYear) ALTER PARTITION SCHEME FactPartitionScheme NEXT USED DM ALTER PARTITION FUNCTION FactPartitionFunction() SPLIT RANGE (@MaxYear+1) COMMIT TRANSACTION END END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRAN --RollBack in case of Error END CATCH END
The staging table in which we moved data from the oldest partition can be either dropped or can be archived. Even to archive, we have two options, move data into a regular table or move data into a partitioned archive table. In the code below, I am moving data from the staging table to another table in another database for archival.
CREATE PROCEDURE [dbo].[ArchiveFact] /***************************************************************************** PROCEDURE NAME: [ArchiveFact] AUTHOR: Arshad Ali CREATED: 02/24/2013 DESCRIPTION: This stored procedure is archive data from stage table to archive tables in archive database VERSION HISTORY: DATE EMAIL Company DESCRIPTION ****************************************************************************/ AS BEGIN BEGIN TRY --Start the Try Block IF OBJECT_ID('ArchiveFactResellerSalesWithPartition') IS NOT NULL BEGIN BEGIN TRANSACTION INSERT INTO AdventureWorksDW2012Archive.dbo.FactResellerSalesWithPartition SELECT * FROM AdventureWorksDW2012.dbo.ArchiveFactResellerSalesWithPartition TRUNCATE TABLE AdventureWorksDW2012.dbo.ArchiveFactResellerSalesWithPartition COMMIT TRANSACTION END END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRAN --RollBack in case of Error END CATCH END
Conclusion
The concept of a sliding window scenario is to manage and keep the same number of partitions on a partitioned table over time. In this article, I provided you the steps and guidance needed to manage a sliding window scenario with an example.
Resources
Partitioned Tables and Indexes
Implementing Partitioned Tables and Indexes
