Partitioning a Table that Contains Future Dates

One of my clients in the banking industry has an application that constantly gets data feeds with details of financial transactions worldwide. Recently, my client’s application reached the point of getting about 130,000 records per day, or about three million records per month. Because my client is currently required to store a year’s worth of data, their TBL_TransactionDetails table, which stores transactions, now contains more than 35 million records.

With the number of companies using the application constantly growing, my client predicts that the number of transactions per day will increase at least three to four times within the next year. To efficiently archive stored data and increase query performance, I implemented table partitioning in my client’s environment. Because bank requirements are constantly changing, flexible partitions were a must. I also faced the challenge of dealing with always present future dated transactions. A transaction’s business owner, for example, might schedule a single transaction to execute on a particular future day or schedule some type of financial activity on a regular basis and enter all these transactions in the system with future statement dates. Of course, the percentage of such future dated transactions isn’t that high, but these transactions always exist in the system. These always present future dates required me to modify the well-known “sliding window” algorithm to be able to handle records with future dates. Here’s how you can implement the modified algorithm in your environment to partition a table.

Creating and Loading the Table

In this article, we will deal with the simplified table structure, which Web Listing 1 shows. Data is loaded into the table and recorded in the system via the Statement Date column, which is represented by the integer column in the YYYYMMDD format. I chose to use the Statement Date (DT) column as the partition key. To align the table and indexes and minimize table fragmentation, add a partition key to the table’s primary key. As a result, the primary key will consist of two columns: DT and TransactionDetailID (the IDENTITY column). Note that for future dated transactions, having TransactionDetailID in the primary key isn’t enough to guarantee that the record will be placed in the correct partition. Adding DT to the primary key ensured that records will be placed in the correct partition and prevents records from being moved to a different partition when we split the Future Dates partition.

I chose a month as the unit of partitioning. Every month, transactions that have a statement date older than 12 months from the current date will be removed from the table. Therefore, create at least 12 monthly partitions for the past year. In addition, create a partition for the current month, two partitions for the following two months, and a partition for all other future dates. Having a partition that contains future dates solves the problem of having transactions with a specified future date. Dedicating two partitions for the next two months minimizes the amount of data that will be moved when the Future Dates partition is split.

Once a month, I run a job that manipulates the partitions in this table. Based on my client’s current storage requirements, partitions that contain transactions older than 12 months are deleted from the table. At the same time, the last partition, which contains the future dates, is split, and a new one-month partition will be created off of that partition, keeping the number of partitions the same (16). This is my version of the “sliding window” algorithm.

If you decide to decrease the number of stored months (e.g., storing only the last nine months worth of data), the same job would remove the last four partitions from the table and split the partition that contains future dates. If you decide to increase the number of stored months (e.g., storing the last 15 months worth of data), the same monthly job would split the leftmost partition into two partitions (these partitions will be empty at the beginning) and then split the partition containing future dates. Note that data won’t be deleted from the table the following two months because the empty partition will be switched out and new records will be placed into the new partitions. Now let’s look at creating the filegroups that will hold the partitions.

Mapping Partitions to Filegroups

You can use the code in Listing 1 to create the sample MyDB database, which will contain the filegroups that the partitions are stored in. Originally, the MyDB database had only one filegroup (PRIMARY), which held a single .mdf file. Following recommendations from project REAL (www.microsoft.com/technet/prodtechnol/sql/2005/realpart.mspx), map each partition to its own filegroup, with one file in each filegroup. Doing so will let you be more flexible with the placement of the files and the backup and restore strategy.

A new partitioned version of the TBL_TransactionDetails table will be placed in partitions across the newly created 16 filegroups (FG1 through FG16). Each partition will contain of one .ndf file. Initially, place all the files from these filegroups into the same folder as the .mdf file. (Later, you can move some or all of them into a different drive or folder.) The initial size of these files should be based on the anticipated number of rows in each partition. You can use the code in Web Listing 2 to create the new filegroups. The code in Web Listing 3 creates files in each filegroup.

You can reuse filegroups when you implement the sliding window algorithm if there’s no change in the number of partitions. When the partition with the oldest data is deleted (i.e., switched out), the new partition will be placed in the same filegroup. In the event you need to add new filegroups, simply run a corresponding stored procedure that accepts the folder location and initial file size as parameters. After creating the filegroups, you can move data from the original table in the [Primary] filegroup to a partitioned table.

Converting a Non-Partitioned Table into a Partitioned Table

The process of moving data to a partitioned table consists from several steps. First, rename the original non-partitioned table to TBL_TransactionDetail_Old. Next, create the partition scheme and partition function. Because we are using a month as the unit of partitioning, records with a different month portion of the Statement Date will be placed on a different partition. The last day of the month will be the partition border.

Following recommendations by Kimberly Tripp, create a LEFT partition function. Using a LEFT partition function means that values in the partition function are defined as upper borders for the first 15 partitions. The reason I suggest using a LEFT partition rather than a RIGHT partition is because LEFT partitions optimize MERGE operations. (A solution using RIGHT partitions is described in “Maintaining Partitions” at www.sqlmag.com/Articles/ArticleID/45877/45877.html?Ad=1.) For example, if you run the monthly partitioning process on 10/15/2007, the following values will be in the partition function: 20060930, 20061031, 20061130, 20061231, 20070131, 20070228, 20070331, 20070430, 20070531, 20070630, 20070731, 20070831, 20070930, 20071031, and 20071130. These 15 numbers define 16 partitions. These borders belong to the LEFT partition, and the Future Dates partition will include values starting with 20071201. The script that creates this partition function is similar to the code in Web Listing 4. When you automate the process, these values are calculated relative to the date when the job ran. The partition function includes the last days of 13 previous months, the last days of the current month, and one future month.

The partition scheme is defined by the code in Listing 2. Next, create a partitioned table on the partition scheme PS_TBL_TransactionDetail, using the command shown in Listing 3. Note that I changed the seed for the IDENTITY column in this command. Before I configured partitioning, the maximum value of the IDENTITY column (in my case) was almost 1,500,000. I used this value as the new seed. Keeping the old seed of 1 would have created duplicate records when new records were inserted into the partitioned table.

The script that initially populates all the partitions uses a loop. In each iteration, it

1. selects the next available filegroup 
2. defines it as the default filegroup 
3. calculates the borders for the next partition that will be placed on this filegroup 
4. creates the TBL_Temp table in the new filegroup with the same structure as the TBL_TransactionDetail table 
5. inserts the proper records into TBL_Temp from the TBL_TransactionDetail_Old table. (Note that you can combine steps 1 and by using the SELECT INTO command, which runs faster than the INSERT INTO operation.) 
6. uses the SWITCH operation to load the records from TBL_Temp into the partition on the default filegroup 
7. deletes the TBL_Temp table

After the data is inserted into the table, recreate the primary key and the required indexes on the partitioned table using the code in Listing 4. After all the indexes have been created, make PRIMARY the default partition because all the other tables are located on this filegroup, using Listing 5. Then you can verify the final data allocation by using Web Listing 5.

Creating an SSIS Package to Handle Monthly Operations

After the data is successfully copied to a partitioned table, run the job that executes the SQL Server Integration Services (SSIS) package that either removes the partition with the oldest data and splits the Future Dates partition, creates new extra partitions on the left if it’s required to increase the length of time transactions are stored, or removes extra partitions with the oldest data if it’s required to decrease the length of time transactions are stored. Figure 1 shows the package structure. The first task executes the Insert_TBL_TrxDet_Partition_Log stored procedure, which inserts records into the log table TBL_TrxDet_Partition_Log and removes old log records. Then, the Get_Operation_Type stored procedure determines the type of operation each package will execute. The only parameter of this stored procedure specifies how many full months of data should be kept in the partitioned table. It returns 0 if no new partitions are required, >0 if new partitions must be created, and <0 if a few partitions must be dropped.

The ArchiveAndSplitTrxDetPartitions stored procedure shown in Figure 1 is called if no new partitions are required. Note that if there isn’t 12 months of data prior to the current date (or a value in the parameter @KeepMonths), the stored procedure will quit running. This stored procedure
 
8. checks to see if it’s time to archive data
9. finds the partition with the oldest data and makes a filegroup where it’s stored as the default filegroup
10. creates a new table called TBL_TransactionDetailArchive under the default filegroup with the same structure as partitioned table TBL_TransactionDetail
11. switches the partition with the oldest data to a new table called TBL_TransactionDetailArchive
12. changes the partition function to remove the lowest boundary by merging the two furthest left partitions
13. drops the TBL_TransactionDetailArchive table
14. assigns the freed filegroup to be the next-used filegroup in which the new partition will be created
15. alters the partition function to add a new boundary on the right for next month
16. writes the statistics into the log table
17. assigns [Primary] to be default filegroup

The VerifyPartUp stored procedure in Figure 1 ensures that there’s always a partition for the current month, the following two months, and future dates. If there are less than two partitions between the partition that stores the current month’s data and the partition for the future dates, a new filegroup will be created with a new partition in it. This procedure includes the following five parameters:

If the VerifyPartUp stored procedure determines that new filegroups (a maximum of three) are required, it adds a filegroup to database, inserts a file in the specified folder of a specified size, designates the new filegroup as the default filegroup, and specifies that the next new partition will be created in this filegroup. At the end of the procedure, the stored procedure modifies the partition function by splitting it for future dates.

The IncreaseTrxDetPartitions stored procedure shown in Figure 1 is used when you’re required to increase number of months to store the data. This procedure creates a new filegroup with a single file and designates this new filegroup as the default filegroup; then it assigns this filegroup as the next-used for the new partition coming off the leftmost partition. The new partition then becomes the leftmost partition, and it has a right border one month earlier than before. Obviously, it will be an empty partition at the beginning.

The DecreaseTrxDetPartitions stored procedure shown in Figure 1 is used when you need to reduce number of months to store the data. This procedure does the opposite to the previous stored procedure. It finds the filegroup with the leftmost partition and makes this filegroup the default filegroup. Then it creates a new table called TBL_TransactionDetailArchive on the default filegroup that has the same structure as partitioned table, switches the earliest partition out into this table, changes the partition function to remove the lowest boundary, and merges the two leftmost partitions. At the end, it drops the staging archiving table TBL_TransactionDetailArchive, designates [Primary] as the default partition, and removes the file and filegroup in which the switched out partition was located.

The results of the execution of this and all the other procedures are then logged. Finally, the Setup_Default_Filgroup stored procedure ensures that [Primary] is the default filegroup. See Web Listing 6 for more details about the configuration and execution of the SSIS package.

Partitioning Tables with Always Present Future Dates

The method described here shows real picture of maintaining and moving data in partitioned table that contains future dates. If your table partitioning requirements are constant, then your automated process would basically execute only one stored procedure, similar to the ArchiveAndSplitTrxDetPartitions stored procedure. But if you have changing data storage requirements in your partitioned environment, you’ll need to implement this method as an SSIS package, as I just discussed.