Partitioning (Public)


Submitted by	Category	Review Cycle	.	Public
Joachim Mutter/sysarc on 10/04/2019 at 02:43 PM	SQLServer

Partitioning (Public)

There are a lot of articles in the web regarding partitioning of a SQL-Server database table, but none of them describes a full approach how to implement partitioning.
Due to this I had to get all bits and pieces togehther and with some help of SSmS I was able to create a more or less automated approach for creating partitions on fact tables which could be done in a schmeatic SSiS package to load data into a datawarehouse.

There are a few steps for creating a partition on a table:

· You need a column which could be used for partitioning, which is regulary a datetime columns or a derived one from such a column by converting the datetime to a bigint via

CAST(CAST(YEAR(PostingDate) as nvarchar(4))

+ RIGHT(''00'' + CAST(MONTH(PostingDate) as nvarchar(2)),2)

+ RIGHT(''00'' + CAST(DAY(PostingDate) as nvarchar(2)),2) as bigint)

· Create a partition function
· Create a partition scheme based on the partition function
· You need a index based on this column, which is regulary a "CLUSTERED COLUMNSTORE INDEX" based on the created partition scheme and the partition column
(if you check the code genereated by SSmS for a new partition)

This is the needed code (why the system creates an additional clustered index is not 100% understand, but I guess due to the physical order of the records)

BEGIN TRANSACTION

CREATE PARTITION FUNCTION [PF_ARTICLEITEM_Months_LEFT](bigint) AS RANGE LEFT FOR VALUES ()

CREATE PARTITION SCHEME [PS_ARTICLEITEM_Months_LEFT] AS PARTITION [PF_ARTICLEITEM_Months_LEFT] TO ([ARTICLEITEM_PART2019])

/* Create an clustered Index to order the data by the PartitionColumnID and drop it afterwards (guess to order the data physically) */

CREATE CLUSTERED INDEX [ClusteredIndex_on_PS_ARTICLEITEM_Months_LEFT] ON [facts].[tblArticleItem]([

PartionColumnID

])

WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PS_ARTICLEITEM_Months_LEFT]([

PartionColumnID

])

DROP INDEX [ClusteredIndex_on_PS_ARTICLEITEM_Months_LEFT] ON [facts].[tblArticleItem]

/* Create a columnstore index with reference to the partitionscheme

and PartitionColumnID */

CREATE CLUSTERED COLUMNSTORE INDEX [ClusteredColumnStoreIndex-tblArticleItemWithNewPartitioning] ON [facts].[tblArticleItem]

WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0, DATA_COMPRESSION = COLUMNSTORE) ON [PS_ARTICLEITEM_Months_LEFT]([

PartionColumnID

])

COMMIT TRANSACTION

· Afterwards you need to define the boundaries for the partitions, (i.e. by given amount of boundaries) and switching the partitions to an empty table with the same structure than the main table (Afterwards all partitions are empty)
By

ALTER PARTITION SCHEME [PS_ARTICLEITEM_Months_LEFT] NEXT USED [ARTICLEITEM_PART2019]

ALTER PARTITION FUNCTION [PS_ARTICLEITEM_Months_LEFT]() SPLIT RANGE (20191000)

· You should empty the partition by an empty copy (Buffer) of the main table
(for this the Buffer table must be also partitoned)

ALTER TABLE [facts].[tblArticleItem] SWITCH PARTITION yyyy TO [facts].[tblArticleItemBUffer] PARTITION xxxx;

· Incremental loads will be done with the same command, but with a filled table
(i.e.filled by a SSiS task)
(for this the Switch table must be also partitoned and the target partiton MUST be empty)

ALTER TABLE [facts].[tblArticleItemSwitch] SWITCH PARTITION yyyy TO [facts].[tblArticleItem] PARTITION xxxx;

Due to the described scheme, we can implement a handy amount of stored procedures, which could by called to dynamically create
· A Filegroup
· A partition with a partition scheme and a partition function
· the needed boundaries.

In principle it is a 4 step approach

1. Prepare the meta data table which defines the month to be loaded
This is normally done where the data is loaded into stage
2. Create / Prepare / Clean the partition
by the meta data table

3. Load the data into the switch table

4. Transfer the data from switch into Main table
by the meta data table
(To make the code robust, we have to check here if the partition is empty, if not clean it like done in step 2)

This is the code to test this

/* Partitioning Test */

set nocount on

DECLARE @RunSecondTime int = 0 --with same data

declare @ImportYear varchar(4) = '2019'

,@ImportMonth varchar(2) = '07'

,@msg nvarchar(max)

,@strLoadControlData varchar(20) = 'ARTICLEITEM'

,@Count int

,@ProcessID varchar(40) = newID()

/* Truncate Switch Table */

if @RunSecondTime = 0 BEGIN

truncate table [facts].[tblArticleItem]

truncate table [facts].[tblArticleItemSwitch]

raiserror('===== Cleaned switch and Main table',0,1) WITH NOWAIT

END ELSE BEGIN

goto MovePartition

END

delete [audit].[tblLoadControlDetailDW]

WHERE [Data] = @strLoadControlData

and [Source] = 'NAV'

and [DataVersion] = 'ACT'

and [DataVersionType] = 'ACT'

set @msg = '===== Removed Records [audit].[tblLoadControlDetailDW] = ' + cast(@@rowcount as varchar) + ')'; raiserror(@msg,0,1) WITH NOWAIT

/* 1. Fill the metadata table */

insert into [JWAnalytics_DW].[audit].[tblLoadControlDetailDW] (

[Data],[FactYear],[FactMonth],[DataVersion], [DataVersionType],[DocType]

,[Source], Amount, LastBatchID, IsETLToDWSuccessfullyFinished,ETLToSTAGEFinishedDate

)

select * from (

SELECT distinct

@strLoadControlData as [Data]

,YEAR(Postingdate) as [FactYear]

,MONTH(Postingdate) as [FactMonth]

,N'ACT' as [DataVersion]

,N'ACT' as [DataVersionType]

,3 as [DoctType]

,N'NAV' as [Source]

,COUNT(*) as Amount

,newID() LastBatchID

,1 IsETLToDWSuccessfullyFinished

,getDate() ETLToSTAGEFinishedDate

FROM [JWAnalytics_STAGE].[etl].[vwArticleItem]

GROUP BY YEAR(Postingdate)

,MONTH(Postingdate)

UNION ALL

SELECT distinct

@strLoadControlData as [Data]

,YEAR(Postingdate) as [FactYear]

,MONTH(Postingdate) as [FactMonth]

,N'ACT' as [DataVersion]

,N'ACT' as [DataVersionType]

,4 as [DoctType]

,N'NAV' as [Source]

,COUNT(*) as Amount

,newID() LastBatchID

,1 IsETLToDWSuccessfullyFinished

,getDate() ETLToSTAGEFinishedDate

FROM [JWAnalytics_STAGE].[etl].[vwArticleItem]

GROUP BY YEAR(Postingdate)

,MONTH(Postingdate)

) A

where FactYear = CAST(@ImportYear as int) and FactMonth >= CAST(@ImportMonth as int)

ORDER BY [FactYear],[FactMonth],[DataVersion]

set @msg='===== Filled Control table [tblLoadControlDetailDW] (Records=' + CAST(@@rowcount as varchar) + ')'; raiserror(@msg,0,1) WITH NOWAIT

/* 2. Create/Clean the partitions */

raiserror('===== Prepare Switch Table tblArticleItemSwitch',0,1) WITH NOWAIT

EXECUTE [etl].[sp_PartitioningMonthsRangeCheck] @strLoadControlData = 'ARTICLEITEM'

, @strTable = 'tblArticleItem'

, @debug = 1

, @schema = 'facts'

, @ProcessID = @ProcessID

/* 3. Load data */

insert into [facts].[tblArticleItemSwitch]

select * from [JWAnalytics_Stage].[etl].[vwArticleItem]

where PostingDate > CAST(@ImportYear+@ImportMonth+'01' as date)

set @msg = '===== Datatransfer to switch (' + cast(@@rowcount as varchar) + ')'; raiserror(@msg,0,1) WITH NOWAIT

/* 4. Switch Table to Main */

MovePartition:

raiserror('===== Move Switch Table to Main',0,1) WITH NOWAIT

EXECUTE etl.[sp_PartitioningMovePartitionSwitchToMain] @strLoadControlData='ARTICLEITEM'

, @Maintable='tblArticleItem'

, @SwitchTable='tblArticleItemSwitch'

, @Debug=1

, @ProcessID = @ProcessID

raiserror('===== END Switch Table to Main',0,1) WITH NOWAIT

/* 5. Show some Log informations */

/* ===================================================================================

Log some results

=================================================================================== */

select top 20 * from [JWAnalytics_STAGE].[audit].[ETLLog_Detail] order by Date desc

/* Show table Counts */

select 'tblArticleItemSwitch' [Table], COUNT(*) [Count] from facts.tblArticleItemSwitch

union all

select 'tblArticleItemBuffer' [Table], COUNT(*) [Count] from facts.tblArticleItemBuffer

union all

select 'tblArticleItem' [Table], COUNT(*) [Count] from facts.tblArticleItem

/* Show partition Counts */

SELECT 'tblArticleItem' [Table]

, $PARTITION.PF_ARTICLEITEM_Months_LEFT(PartitionColumnId) AS Partition

,COUNT(*) AS [COUNT]

FROM facts.tblArticleItem

GROUP BY $PARTITION.PF_ARTICLEITEM_Months_LEFT(PartitionColumnId)

ORDER BY Partition ;

Create Filegroup	Create Partition	Prepare Partitioning Month	Get partitionID	Move table Switch to Main	Logging
sp_PartitionCreateNewFilegroup.sql	sp_PartitioningCreatePartition.sql	sp_PartitioningMonthsRangeCheck.sql	sp_PartitioningGetPartitionIdForMonths.sql	sp_PartitioningMovePartitionSwitchToMain.sql	sp_PartitioningLog.sql

Zur SysArc Homepage ...