Usually when one thinks of partitioning, the first thing that comes to mind is storage—the ability to split the data in a single table across multiple filegroups, and potentially multiple physical drives. But recently, I’ve also become aware of its usefulness in rapidly moving data across tables, via the SWITCH expression.

In this article I’ll detail how I’ve used this feature to maintain a highly available data warehouse, reducing hours of downtime every night to a fraction of a second—and perhaps saving myself from incineration in the process.

A Rather Unpleasant Morning

My employer is in the, well, the dragon industry—dragon distribution, that is. We purchase dragons from a number of breeders, and sell them online and in stores across four continents. Given the rise of recreational dragon sports, and the declining life expectancy of dragons, our SQL Server data warehouse naturally has millions of rows to account for, with nightly data feeds coming from a variety of source systems.

My predecessor had set up a nightly ETL process that worked well enough, but it had several downsides. First, due to limitations of the source system, we are unable to set up a differential data load (pulling only the rows that have been changed or modified), so our only option has been to run TRUNCATE TABLE [dbo].[factDragonSales] every night and load the data from scratch. At first this process would take only a few hours, but as we began to take on new breeders the ETL grew more complex, and would take up to six or seven hours every morning to complete. Being a global company with executives working around the clock, this quickly became unsustainable.

Then came—a rather unpleasant morning. Our CIO walked into work after a long holiday weekend filled with relaxing dragon polo, only to pull up his reports and notice that nothing was there. To make a long story short, it turns out one of our suppliers fed us an invalid date value, which we failed to handle correctly, causing the ETL to crash. Let me tell you, our CIO is unpleasant to confront on days like this—especially with his Miniature Transylvanian Flame-Belcher curled up on his desk blowing smoke out of its nostrils. Something had to change.

Setting Up Partitioning

As you might imagine with so many dragons roaming about, “fired” is not just a metaphor here. As I looked to the ash-pot containing the remains of poor old Jimmy, I felt firm in my resolve not to repeat his errors.

I needed a solution that would allow us to perform our nightly update, while still maintaining the previous day’s data available for querying. With that in mind, I decided I would create three tables with identical schemas:

• [dbo].[factDragonSales]
• [dbo].[factDragonSalesStaging]
• [dbo].[factDragonSalesGarbage]

The tables must be almost completely identical, including the same ordering of columns and the same indexes. This will allow me to instantaneously “switch” a partition of data from one table to the other. The data will not physically have to be moved, making this much faster than a bulk insert operation would be.

We are running on virtual servers, so I did not see a pressing need to use multiple filegroups at present (although we may consider implementing that down the road). With that in mind, I set up my partition on the column DataSourceID, which is unique to each dragon-breeder source system. This will enable me, if I so desire, to switch in new data for only a single system without interfering with the other data.

Here is the code I used to create my partition function, detailing the ranges of values that go in each partition:

USE DragonDW;
CREATE PARTITION FUNCTION [IHopeMyBossDoesntKillMe](int)
 AS RANGE LEFT FOR VALUES (999, 1999, 2999, 3999, 4999, 5999, 6999, 7999);
GO

I then created the following partition scheme, which simply directs SQL Server to store all the partitions on the [PRIMARY] filegroup for now:

CREATE PARTITION SCHEME [OrFeedMeToHisPetForBreakfast] AS PARTITION [IHopeMyBossDoesntKillMe]
TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY],
    [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]);
GO

Granted, I probably should reconsider my naming convention for these. I was a bit stressed at the time.

The Magic SWITCH

Each table is set up with a nonclustered columnstore index (we are running SQL Server 2012), which means that to modify the data the index must first be dropped:

IF EXISTS(SELECT * FROM sys.indexes WHERE name='NCI_factDragonSalesStaging' AND object_id = OBJECT_ID('[dbo].[factDragonSalesStaging]'))
BEGIN
  DROP INDEX NCI_factDragonSalesStaging
    ON [dbo].[factDragonSalesStaging];
END

Now my strategy is simple: load the new data into the staging partition, then when it is all ready, switch out the data from [dbo].[factDragonSales] into [dbo].[factDragonSalesGarbage], and switch in the data from [dbo].[factDragonSalesStaging]. Once I’m sure that has completed successfully, I can TRUNCATE the data from [dbo].[factDragonSalesGarbage] (or keep it around as an extra backup in case we want to revert to the earlier version of the data).

To make sure all of the partitions get moved, but only if there is data present, I constructed this little while loop, which I have made into a stored procedure:

DECLARE @partitionID INT, @RowCnt INT;
DECLARE @partitionBoundaries TABLE (boundary_id INT);
-- call stored procedure to recreate indexes on staging table in preparation for partition switch
EXEC [p].[AddIndex_fDragonSalesStaging];
-- load table variable with the boundary IDs for the partition function
INSERT INTO @partitionBoundaries
SELECT
 prv.boundary_id
FROM sys.partition_functions pf
INNER JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id
WHERE pf.name = 'IHopeMyBossDoesntKillMe';
-- for each partition boundary, check if there is data; if so, switch partition into fact table
SELECT @partitionID = MIN(boundary_id) FROM @partitionBoundaries
WHILE @partitionID IS NOT NULL
BEGIN
  SELECT @RowCnt = COUNT(*)
  FROM [dbo].[factDragonSalesStaging]
  WHERE $Partition.IHopeMyBossDoesntKillMe(DataSourceID) = @partitionID;
  IF @RowCnt > 0
  BEGIN
    -- switch partition from fact table to staging table
    ALTER TABLE [dbo].[factDragonSales] SWITCH PARTITION @partitionID
    TO [dbo].[factDragonSalesGarbage]PARTITION @partitionID;
   
    -- switch partition from staging table to fact table
    ALTER TABLE [dbo].[factDragonSalesStaging] SWITCH PARTITION @partitionID
    TO [dbo].[factDragonSales] PARTITION @partitionID;
  END
  DELETE FROM @partitionBoundaries WHERE boundary_id = @partitionID;
  SELECT @partitionID = MIN(boundary_id) FROM @partitionBoundaries;
END

Good as New! Well, Sort Of.

The partition switch is just about instantaneous—and if for some reason there’s an error in the ETL, the CIO will still see yesterday’s data populated on the reports, up until we get the issue corrected.

It does concern me, though, to think how capriciously my boss disposes of the lives of his subordinates. I have yet to get on his bad side, but nonetheless I’m a little nervous.

And I miss Jimmy. He had a lovely tenor voice.