Partitioning in SQL Server: Managing Sliding Window Scenario

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

Using Partitioned Views

Partitioned Tables and Indexes

Implementing Partitioned Tables and Indexes

Special Guidelines for Partitioned Indexes

See all articles by Arshad Ali