Data Warehouse Partition Strategies

Microsoft put a great deal of effort into SQL Server 2005 and 2008 to ensure that that the platform it is a real Enterprise class product. I’m not going to write about all the new features in the OLTP Engine, in this article I will focus on Database Partitioning and provide a real world scenario. This article will cover the following points:

I. Benefits and Limitations of Partitioning (HEAPS/CLUSTERED/NONCLUSTERED).
II. Implementation Methodology.
III. Partitioning Management.

I. Benefits and Limitations of Partitioning (HEAPS/CLUSTERED/NONCLUSTERED)
Positives
- Data can be accessed through multiple partitions in parallel, this results in faster DML.
- Different partitions can be managed separately.
- Management of history more efficient (without having to leave the table).

Negatives
- Limit of 1000 Partitions by Table.
- Inability to use Indexed Views.

II. Implementation Methodology
I’m going to focus on the most common scenario. Typically, in a Data Warehouse environment there is a large Fact Table (a.k.a the Monster).

In this article the Fact Table contains all the banking movements of all accounts within the last 4 years in the MAIN database and the remaining years in a HISTORY database. In this case, because there is a chronology to follow, I will use the extract day date column of the table as the KEY to our Partition Strategy. In this situation, I recommend the creation of a Partitioned Clustered Index because typically all access to the fact table always has the date in the SARG. The fact table will be partitioned by month.

Technical approach:
Storage Arquitecture - 3 RAID-5 Partition (2TB each), 1 RAID 01 for Transaction Log and 1 more RAID 01 for
Non-Clustered indexes.

First off all you will need to develop the Partition Function. The Partition Function defines the values which the partition scheme will bind with the Filegroups. Our boundary will be the date, and right range (boundary right aligned):

CREATE PARTITION FUNCTION PFFactTableMonthly (SMALLDATETIME)
AS RANGE RIGHT
FOR VALUES
(
'2007-01-01','2007-02-01','2007-03-01','2007-04-01','2007-05-01',
'2007-06-01','2007-07-01','2007-08-01','2007-09-01','2007-10-01',
'2007-11-01','2007-12-01','2008-01-01','2008-02-01','2008-03-01',
'2008-04-01','2008-05-01','2008-06-01','2008-07-01','2008-08-01',
'2008-09-01','2008-10-01','2008-11-01', '2008-12-01','2009-01-01',
'2009-02-01'
)

Create the Datafiles and assign each file to a differente Filegroup. In this situation 27 Datafiles/Filegroups will be created. Now that the Filegroups are created, assign them to a Partition Scheme that acts like a ROUTER, because it will forward the data to a specified path. The Partition Scheme will use the Partition Function to link the logical layer to the physical layer:

CREATE PARTITION SCHEME PSFactTableMonthly
AS PARTITION PFFactTableMonthly
TO (
FG_FactTable_BASE,FG_FactTable_200701,FG_FactTable_200702,
FG_FactTable_200703,FG_FactTable_200704,FG_FactTable_200705,
FG_FactTable_200706,FG_FactTable_200707,FG_FactTable_200708,
FG_FactTable_200709,FG_FactTable_200710,FG_FactTable_200711,
FG_FactTable_200712,FG_FactTable_200801,FG_FactTable_200802,
FG_FactTable_200803,FG_FactTable_200804,FG_FactTable_200805,
FG_FactTable_200806,FG_FactTable_200807,FG_FactTable_200808,
FG_FactTable_200809,FG_FactTable_200810,FG_FactTable_200811,
FG_FactTable_200812,FG_FactTable_200901,FG_FactTable_200902
)

Now that we have the Partition Scheme, just create the Clustered Index on the Fact table with a FILLFACTOR 90% with PAD_INDEX on.

CREATE CLUSTERED INDEX [IDXC_FACTTABLE] ON MIS.FACTTABLE
(
datecolumn
)
WITH( PAD_INDEX = ON, FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
on PSFactTableMonthly (DATECOLUMN)
III. Partitioning Management
Now, that our table is partitioned, you are wondering, what will happen if there is an insert that has a date >= '2009-03-01'? What will happen is that all the data will be placed in the FG_FactTable_200902 because the data is right aligned as specified when we created the Partition Function.

To maintain the data equally distributed between Filegroups, you must plan the future growth of your Database and continue adding Filegroups to the Partition Scheme and continue to add dates to the Partition Function.
To add a File Group to the Partition Scheme:

ALTER PARTITION PSFactTableMonthly SCHEME
NEXT USED [FG_FactTable_200903]

To add a new boundary to the Partition Function:
ALTER PARTITION FUNCTION PFFactTableMonthly ()
SPLIT RANGE ('20090301')

To show the details about the Partition Function:
SELECT * FROM sys.partition_functions
WHERE name = 'PFFactTableMonthly'

To show the boundaries of the Partition Function:
SELECT a.name, b.* FROM sys.partition_functions a, sys.partition_range_values b
WHERE a.function_id = b.function_id
and a.name = 'PFFactTableMonthly'

To show the Allocation Units:
SELECT object_name(object_id) AS name,
partition_id, partition_number AS pnum, rows,
allocation_unit_id AS au_id, type_desc as page_type_desc,
total_pages AS pages
FROM sys.partitions p JOIN sys.allocation_units a
ON p.partition_id = a.container_id
WHERE object_id=object_id('schema.FactTable)

From my point of view the following statement is the best statement available to provide an overview of your partitioned table, because it shows the INDEX_ID, Partition_Number, Filegroup that is associated with the respective Partition_Number, ROWS, PAGES and the date that is associated with the FileGroup.

SELECT OBJECT_NAME(i.object_id) as Object_Name,
i.index_id AS Index_ID,
p.partition_number,
fg.name AS Filegroup_Name,
rows,
au.total_pages,
CASE boundary_value_on_right
WHEN 1 THEN 'less than'
ELSE 'less than or equal to' END as 'comparison', value
FROM sys.partitions p JOIN sys.indexes i
ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.partition_schemes ps
ON ps.data_space_id = i.data_space_id
JOIN sys.partition_functions f
ON f.function_id = ps.function_id
LEFT JOIN sys.partition_range_values rv
ON f.function_id = rv.function_id
AND p.partition_number = rv.boundary_id
JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = ps.data_space_id
AND dds.destination_id = p.partition_number
JOIN sys.filegroups fg
ON dds.data_space_id = fg.data_space_id
JOIN (SELECT container_id, sum(total_pages) as total_pages
FROM sys.allocation_units
GROUP BY container_id) AS au
ON au.container_id = p.partition_id
WHERE OBJECT_NAME(i.object_id) = 'FactTable'

Some useful Tips:

• Always use a rule on DataFiles/FileGroup nomenclature, to make it easier to understand the dependencies.
• When partitioning tables, don’t randomize the creation of the DataFiles, for example put the DF1 on StorageA, DF2 on StorageB, DF3 on StorageC, DF4 on StorageA,DF4 on StorageB...
• For best performance align your non-clustered indexes with the partition.
• After the end of the previous month, rebuild the respective previous partition number.
• Be creative