Getting Ready for Race Day

Our MCR calculations look great, and are BCR calculations are pretty close to the MCR, so we’re good to go, right? Well, those are indications of how well the CPUs will process your workload, but the original reason for this hardware project was to address the the disk throughput issue. We have a lot more throughput now with the additional HBA paths, but is our data stored in the DB files properly to take advantage of the new configuration?

Since we are conducting a hardware migration, and not an in-place upgrade, we definitely have the opportunity to make any corrections to the data model and storage configuration overlooked in the original design. There is no better time to fix them than now as we can continue to run on the old gear in parallel while we fine-tune the new configuration to run optimally.

Our particular data warehouse implementation had a flaw regarding how we scaled out our databases with partitioning. It dealt with choosing between a RANGE RIGHT and a RANGE LEFT partition function and remembering to do partition splits in a timely fashion.

In the original design, we partitioned our fact table using a RANGE LEFT partition function with quarterly time periods from January 2012 through December 2016. That would be equal to 4 partitions for each of the five years, plus an additional spillover partition for items that lay outside the right-hand boundary after December 2016. The catch with a RANGE LEFT function is that if and when you need to split the function to add new time periods, you want to watch out for data having to move between filegroups. The process is sluggish and generates an enormous amount of transaction logging, typically a factor of 4x the size of the data being shuffled.

Here’s an example using a single partitioned table with a four-year partition scheme…

USE [TPC_H]
GO

CREATE PARTITION FUNCTION [PF_YEAR_LEFT](date) AS RANGE LEFT FOR VALUES (N'1992-12-31', N'1993-12-31', N'1994-12-31')
GO

CREATE PARTITION SCHEME [PS_YEAR_LEFT] AS PARTITION [PF_YEAR_LEFT] TO ([DATA_1992], [DATA_1993], [DATA_1994], [DATA_1995])
GO

CREATE TABLE [dbo].[Orders]
	(OrderID int IDENTITY(1,1) NOT NULL,
	 OrderDate date NOT NULL,
CONSTRAINT PK_Orders PRIMARY KEY
	(OrderID,OrderDate) ON PS_YEAR_LEFT(OrderDate))
ON PS_YEAR_LEFT(OrderDate)
GO

SET IDENTITY_INSERT [dbo].[Orders] ON
GO

INSERT INTO [Orders](OrderID,OrderDate) VALUES (1,'1992-01-05');
INSERT INTO [Orders](OrderID,OrderDate) VALUES (2,'1992-01-09');
INSERT INTO [Orders](OrderID,OrderDate) VALUES (3,'1992-01-13');
-- a total of 495 inserts
INSERT INTO [Orders](OrderID,OrderDate) VALUES (493,'1997-05-26');
INSERT INTO [Orders](OrderID,OrderDate) VALUES (494,'1997-05-30');
INSERT INTO [Orders](OrderID,OrderDate) VALUES (495,'1997-06-03');

SET IDENTITY_INSERT [dbo].[Orders] OFF
GO

USE [TPC_H]

CREATE PARTITION FUNCTION [PF_YEAR_LEFT](date) AS RANGE LEFT FOR VALUES (N'1992-12-31', N'1993-12-31', N'1994-12-31')

CREATE PARTITION SCHEME [PS_YEAR_LEFT] AS PARTITION [PF_YEAR_LEFT] TO ([DATA_1992], [DATA_1993], [DATA_1994], [DATA_1995])

CREATE TABLE [dbo].[Orders]

(OrderID int IDENTITY(1,1) NOT NULL,

OrderDate date NOT NULL,

CONSTRAINT PK_Orders PRIMARY KEY

(OrderID,OrderDate) ON PS_YEAR_LEFT(OrderDate))

ON PS_YEAR_LEFT(OrderDate)

SET IDENTITY_INSERT [dbo].[Orders] ON

INSERT INTO [Orders](OrderID,OrderDate) VALUES (1,'1992-01-05');

INSERT INTO [Orders](OrderID,OrderDate) VALUES (2,'1992-01-09');

INSERT INTO [Orders](OrderID,OrderDate) VALUES (3,'1992-01-13');

-- a total of 495 inserts

INSERT INTO [Orders](OrderID,OrderDate) VALUES (493,'1997-05-26');

INSERT INTO [Orders](OrderID,OrderDate) VALUES (494,'1997-05-30');

INSERT INTO [Orders](OrderID,OrderDate) VALUES (495,'1997-06-03');

SET IDENTITY_INSERT [dbo].[Orders] OFF

First, let’s see how the rows got distributed across the filegroups in the partition scheme…

SELECT p.Partition_Number 
	,fg.name Filegroup_Name
	,p.Rows
FROM sys.partitions p
INNER JOIN sys.allocation_units au ON au.container_id = p.hobt_id
INNER JOIN sys.filegroups fg ON fg.data_space_id = au.data_space_id
WHERE p.object_id = OBJECT_ID('Orders')

SELECT p.Partition_Number

,fg.name Filegroup_Name

,p.Rows

FROM sys.partitions p

INNER JOIN sys.allocation_units au ON au.container_id = p.hobt_id

INNER JOIN sys.filegroups fg ON fg.data_space_id = au.data_space_id

WHERE p.object_id = OBJECT_ID('Orders')

OK, so we’re top-heavy in the last filegroup since we got rows not only from 1995 but also 1996 and 1997 in here. We need to create at least three new filegroups, one each for 1996, 1997 and 1998, tag each filegroup in order for the partition function and split the function so that rows incorrectly placed in DATA_1995 get moved to their proper filegroup.

Microsoft’s documentation of the ALTER PARTITION FUNCTION statement , warns you that having to move data from populated filegroups in a partition scheme during a split is very inefficient due to the heavy transaction logging (up to 4x the amount of data being shuffled) and severe locking. Furthermore, using a RANGE LEFT partition function makes the last filegroup indicated when creating the partition scheme the permanent filegroup for all values beyond the upper-most boundary. Dan Guzman’s blog covering partitioning best practices walks you through this particular gotcha and advocates for using a RANGE RIGHT partition instead.

Moving a handful of rows from one table is not worth writing about, but what if you have nearly 100 tables with 500 GB of data that need to be shuffled? Do you have 2 TB of Tlog space to spare? All the more reason to make the correction on the new server with a proper partition function and scheme.

For my project, I needed to change all my tables using a RANGE LEFT partition to use a RANGE RIGHT partition. I also wanted to set the partition function to cover all dates from project start until probably the end-of-life for the hardware we just acquired, keeping with a best practice of having empty partitions beyond the upper-most and lower-most boundaries. I figured our gear would be running until the end of 2020. So we would have nine years * four quarters each = 36 partitions +1 spillover partition, nearly double the 21 partitions from the original design. So, we had to come up with the new partition function, new corresponding partition scheme, new corresponding partition file groups with the correct number and size of data files, and then restripe every table that was partitioned in the top-heavy RANGE LEFT scheme to the new partition scheme. And, did I mention that I had close to 100 tables subject to this move?

How can we move 100 tables with their indexes to new partition schemes? You can do this with T-SQL manually, but the volume of tables is too much to do it in a timely fashion. You have to break out the big guns.

How’s your Power Shell and your .NET programming?