MICROSOFT SQL SERVER 2005 Partitioning (English Version)

Microsoft worked well in the Microsoft SQL Server 2005 (even better in MSSQL Server 2008), bringing a shine to their RDBMS engine and building it to be a real Enterprise Class Product.

I’m not going to write about all the new features of the OLTP Engine, I will focus this article on Database Partition, giving you a real case scenario.

I. Benefits and Limitations of Partitioning (HEAPS/CLUSTERED/NONCLUSTERED).

II. Implementation Methodology.  
 
III. Partitioning Management.  

 
I. Benefits and Limitations of Partitioning (HEAPS/CLUSTERED/NONCLUSTERED): 
 
+ 
 
Data can be accessed through multiple partitions in parallel, this will results in faster DML.

Different partitions can be managed separately.  
 
Management of history more efficient (without having to leave the table).  
 
– 
 
Limit of 1000 Partitions by Table.

 
Inability to use Indexed Views.  
 

II. Implementation Methodology.

I’m going to focus in the most common scenario:

Typically, in a DW 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 s 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 you Database and continue adding Filegroups to the Partition Scheme and continuing to add dates to the Partition Function.

Add a FG to the Partition Scheme:

ALTER PARTITION PSFactTableMonthly SCHEME

NEXT USED [FG_FactTable_200903].  

Add a new boundary to the Partition Function:

ALTER PARTITION FUNCTION PFFactTableMonthly ()

SPLIT RANGE (‘20090301’)  

Know the detail about the Partition Function :

SELECT * FROM sys.partition_functions

WHERE name = ‘PFFactTableMonthly’ 

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’

Show 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 this is the best statement there is available to have a neat overview of your partitioned table, because it shows the INDEX_ID, Partition_Number, Filegroup that is associated 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 !

Paulo Condeça. paulo at citen dot net