Continuing the discussion on VLDBs and SQL Server we need to look at the daily load of millions of rows. As stated before, doing this to an existing table with indexes will force the system to do a lot more work than is necessary and therefore may take hours. The answer is to use an empty partitioned table and will then take a matter of minutes. Let’s take a look.
In Andrew Novick’s presentation on “Big Data” at the PASS Summit in Seattle, he let us know that loading a million rows to a 400 million table with 12 indexes took 12 hours. Not good. Adding the same million rows to an identical but empty table without indexes then adding the indexes after the load took just 5 minutes. So how is that going to help us? Enter Data Partitioning and the “Sliding Window” strategy.
SQL Server 2005 supported Data Partitioning for the first time and it heralded Microsoft’s commitment to compete in the VLDB marketplace. Oracle and IBM’s DB2 have supported Data Partitioning for many years so now Microsoft at last had a story in that arena. Microsoft decided to use the existing Filegroup object as the partition. Partitioning gives obvious performance gains based on multiple partitions on multiple disk drives. The Database Engine will only use the partitions necessary to satisfy the query so this gives performance gains also. But in this context, it is the ability to instantly “switch” a partition from one table to another that gives this solution its “magic”.
Switching a partition involves no copying, moving or deleting of data. Internally it is a mere matter of resetting a pointer from one table partition to another identically structured table partition. As you can imagine, this speeds up processes like archiving millions of rows of data into an almost instant operation. In our context, it helps us load data rapidly to an empty partitioned table and then switch it to an existing partitioned table that contains all the data. Bingo.
In a daily load strategy, you would have a partition per day, but you are allowed up to 1000 partitions on a table so you have almost 3 years to work out an archive strategy. (Just kidding;-). Of course, you need to plan and test the solution thoroughly before embarking on such a strategy. Microsoft provides a good document on the subject: Planning Guidelines for Partitioned Tables and Indexes . The Sliding Window strategy allows new partitions to be added to a table while older ones are merged into an archive table and Microsoft gives us a code sample based on a monthly archive process: Designing Partitions to Manage Subsets of Data .
To create a Partitioned Table you will need to create a Partition Function, a Partition Scheme and then create the table using the Partition Scheme. You are allowed to partition by a single column in the table and the value of that column determines which partition a row lands on. The column is usually a variation on a date for obvious reasons. The Partition Function defines the boundaries of the partitions by defining the values which serve as the “dividing lines” between partitions. The Partition Scheme defines the Filegroups that will serve as partitions including the extra “Next Used” Filegroup which will be used when a new partition is needed. I view this as a “spare tire” for the table. Once the Partition Function and Scheme are created, you can use the ON clause of the CREATE TABLE statement to reference the Partition Scheme. In previous releases, you could only add a table to a single Filegroup but now the Partition Scheme will work out which Partition/Filegroup the row will be part of, based on the named partitioning column.
Using these features and strategies, you can create a viable scenario where millions of rows can be added to an existing table in a snap.
Of course, then we can start looking at adding billions of rows…
Cheers
Brian
Recent Posts: Filegroups vs RAID?
