Schlagwort-Archiv Partitionsfunktion

Relationale Partitionierungen von SQL Tabellen #1

Grundlagen

Wenn es um Massendaten im SQL-Server geht, dann kommt man relational kaum bis gar nicht um eine Partitionierung herum. Das Feature gibt es leider nur in der Enterprise Edition des SQL-Servers.

Ich möchte mich in dieser Blogreihe detailiert um die Planung und Einrichtung einer partitionierten Tabelle beziehen.

Das A und O einer korrekten Partitionierung ist die Planung vorweg, bevor man an den Aufbau der eigentlichen Partitionsfunktion und Partitionsschemas geht.

Als Beispiel soll für diesen Artikel die Tabelle „dbo.FactProductInventory“ dienen. Diese Tabelle bietet sich zum einen wegen der Datumsspalte und zum anderen durch die große Anzahl an Datensätzen an. Das Jahr 2014 wird als aktuelles Jahr angenommen.

Vorbereitungen

Um eine Partitionierung einer Tabelle einrichten zu können wird in der Tabelle eine Spalte benötigt, die für die „Einsortierung“ der Datensätze in die jeweiligen „Partition-Ranges“ verwendet werden kann.

Grundsätzlich ist zu empfehlen, dass keine Datentypen verwendet werden, die von Hause aus nicht zu den schnellsten zu verarbeiteten Datentypen gehören. Dazu zählen u.a. die Date und DateTime Datentypen.

Wird eine Einteilung auf Tag, Monats oder Jahres-Basis in Betracht gezogen, ist die beste Vorgehensweise, eine separate Spalte für die Partitionierung anzulegen, die Datums-Datentypen castet.

In dem Fallbeispiel erübrigt sich das, da die DateKey-Spalte schon eine Integer-Spalte ist.

Screen1-Spalte fuer Partitionierung

Hat man sich für eine Einteilung der Partition-Ranges entschieden geht es an die Definition des Partionschemes und Partitionfunction.

Partitionscheme und Partitionfunction

Der Kern jeder Partitionierung bildet die Partionscheme und Partitionfunction.

Das Paritionschema teilt die einzelnen Partition-Ranges in zugordnete Filegroups auf, wohin gegen die Partitionfunction die Grenzen der einzelnen Partition-Ranges definiert.

Zufinden sind die beiden Bereiche unter:

Screen2-Storage

Einem Scheme muss eine Function zugewiesen werden. D.h. ohne eine Function, kann auch kein Scheme erstellt werden.

Wenn einmal ein Partition-Scheme einer Tabelle zugeordnet ist, kann ein Scheme und eine Function nicht mehr gelöscht werden. Auch ein Ändern ist dann sehr umständlich.

Bevor eine Function erstellt werden kann, müssen die Daten in der Tabelle analysiert werden. In dem Beispiel gibt es Datensätze für die Jahre von 2010 bis 2014. Wie bereits oben geschrieben, wird in diesem Beispiel davon ausgegangen, dass das aktuelle Jahr – 2014 ist. In der Tabelle befinden sich Daten zwischen 2010 und 2014.

Was rät Microsoft? Was ist Best Practice?

Grundsätzlich empfiehlt Microsoft, dass die erste und letzte Partition in einer Function keine Daten enthalten soll. Dadurch wird gewährleistet, dass Daten problemlos angefügt werden können, da ein Split auf eine leere Partition  sehr schnell geht und keine physischen Daten verschoben werden müssen. Und zum anderen würde ein nachträgliches Ergänzen von noch älteren Daten somit auch kein Problem darstellen.

Zur Demonstration soll es genügen, wenn die Partition-Ranges so gewählt werden, dass alle Daten eines Jahres in Jahres Ranges eingeteilt werden.

Partition-Function

Partition-Functions bilden innerhalb der relationalen Partitionierung die jeweiligen Bereiche ab, in die die Daten einsortiert werden sollen. Hierbei kann man beim Anlegen der Function bestimmen, ob der Bereich an dem angegebenen Wert endet oder beginnt.

RANGE LEFT:

– 20091215 Daten würden in untenstehender Function in die zweite Range einsortiert werden.

–> Dadurch entsteht aber eine Unstimmigkeit, wenn man die einzelnen Ranges abfragt. 2009er Daten stehen in einer Range, die mit 20100000 benannt ist.

Umgehen kann man das, in dem direkt mit RANGE RIGHT gearbeitet wird. Die Daten vom 15.12.2009 würden dann in die letzte Partitionrange fallen, die quasi ein „Auffangbecken“ abbildet. Das kann man als Nachteil der RANGE RIGHT Methode ansehen, da die Daten vor dem Jahr 2010 in den anderen Ranges erstmal gefunden werden müssen. Durch ein Anlegen einer PartitionRange die weit in der Vergangenheit liegt, kann man dieses Problem lösen.

Code für Erzeugen einer Partion-Function mit „RANGE LEFT“

CREATE PARTITION FUNCTION [PFYearsLeft](INT) AS RANGE LEFT
FOR VALUES (20050000,20100000,20110000,20120000,20130000,20140000,20150000)
GO

Und hier die Version mit „RANGE  RIGHT“

CREATE PARTITION FUNCTION [PFYearsRight](INT) AS RANGE RIGHT
FOR VALUES (20050000,20100000,20110000,20120000,20130000,20140000)
GO

Das Ergebnis mit „RANGE LEFT“:

Screen5-CreatePartionFunctionLeft

Partition-Scheme

Bevor mit dem Anlegen eines Schemes begonnen werden kann, muss definiert werden, wie die Daten zukünftigt gespeichert werden sollen. Außerdem ist zu empfehlen, eigene Filegroups für bestimmte Inhaltsbereiche festzulegen. Jede Filegroup benötigt mindest ein zugeordnetes File.

Filegroup

Um flexibel mit den Daten auch in der Zukunft umgehen zu können werden 2 Filegroups mit jeweils einem File angelegt.

1.Filegroup: ProductInventoriesHist (Historische Daten bis einschließlich 31.12.2013)

2.Filegroup: ProductInventoriesCur (Aktuelles Jahr)

Ich rate davon ab, dass man z.B. Sales Daten aus dem Ecommerce-Geschäft mit Product-Inventories in einer Filegroup ablegt. Für unterschiedliche Inhalte, sollten auch eigene Filegroups angelegt werden.

Screen3-CreateFilegroup

Wichtig bei der Vergabe der Dateinnamen ist daran zu denken, dass die Files keinerlei Hinweise darauf haben, zu welcher Datenbank sie gehören. Daher sollte der Datenbankname auch in der Benennung der Files eine Rolle spielen.

Screen4-CreateFiles

Die Optimierung der Filegrößen ist ein Thema für sich und wird hier in diesem Post nicht weiter behandelt.

Und hier der T-SQL Code:

USE [master]
GO
ALTER DATABASE [AdventureWorksDW2014] ADD FILEGROUP [ProductInventoriesCur]
go
ALTER DATABASE [AdventureWorksDW2014] ADD FILEGROUP [ProductInventoriesHist]
go
 
ALTER DATABASE [AdventureWorksDW2014] 
	ADD FILE ( NAME = N'AdventureWorksDW2014_ProductInventoriesCur', FILENAME = N'd:\SQLData\AdventureWorksDW2014_ProductInventoriesCur.ndf' , SIZE = 307200KB , FILEGROWTH = 16384KB ) TO FILEGROUP [ProductInventoriesCur]
GO
ALTER DATABASE [AdventureWorksDW2014] 
	ADD FILE ( NAME = N'AdventureWorksDW2014_ProductInventoriesHist', FILENAME = N'd:\SQLData\AdventureWorksDW2014_ProductInventoriesHist.ndf' , SIZE = 307200KB , FILEGROWTH = 16384KB ) TO FILEGROUP [ProductInventoriesHist]
GO

Nach erfolgreichem Erstellen, kann mit der Erstellung der Partion-Scheme und Function begonnen werden.

Scheme-Erstellung

Ein Scheme kann nur über T-SQL erstellt werden. Eine GUI für diesen Vorgang gibt es in der 2014er Version des Management Studios nicht.

Hier ist der Code dafür:

CREATE PARTITION SCHEME [PSProductInventoriesRight] AS PARTITION [PFYearsRight]
TO ([ProductInventoriesHist],[ProductInventoriesHist],[ProductInventoriesHist], [ProductInventoriesHist], [ProductInventoriesCur], [ProductInventoriesCur], [ProductInventoriesCur])
GO

 

Zuweisen eines Partitionschemes zu einer Tabelle

Als nächstes soll das neu angelegte Scheme der Tabelle factProductInventory zugeordnet werden.

Grundsätzlich gibt es hierfür 2 Wege.

  1. Neue Tabelle mit Partitionierung anlegen und die Daten aus der alten Tabelle in die neu einfügen. Danach Renaming der Tabellen.
  2. Bestehende Tabelle so modifizieren, dass das Partitionschema verwendet wird.

Beide Vorschläge haben ihre Vor- und Nachteile. Im Folgenden schauen wir uns diese genauer an.

Vorschlag 1: Neue Tabelle mit Partitionierung anlegen

Vom Vorgehen her, ist dieser Ansatz sicherlich der einfachste. Jedoch muss bedacht werden, dass auf der alten Tabelle auch evtl. Foreignkey Einschränkungen liegen. Diese müssten dann auf allen Quellen geändert werden. Außerdem werden die Indizes durch die Umbenennung der neuen Tabelle nicht mit geändert, was zur Folge hat, dass die Indizes wahrscheinlich noch im Namen einen Teil enthalten, der auf die alte Tabelle hinweißt.

Vorschlag 2: Bestehende Tabelle so modifizieren, dass das Partitionschema verwendet wird

In diesem Abschnitt schauen wir uns nun die 2.Möglichkeit mal genauer an.

Um ein Partitionscheme einer Tabelle zuweisen zu können, müssen als erstes alle Constraints und Indizes von der Tabelle runtergenommen werden.

-- *****************************************************************************************
-- Alle Constraints in der Tabelle entfernen
-- *****************************************************************************************
ALTER TABLE [dbo].[FactProductInventory] DROP CONSTRAINT [FK_FactProductInventory_DimProduct]
GO
ALTER TABLE [dbo].[FactProductInventory] DROP CONSTRAINT [FK_FactProductInventory_DimDate]
GO
--ALTER TABLE [dbo].[FactProductInventory] DROP CONSTRAINT [PK_FactProductInventory]
--GO
-- *****************************************************************************************
-- Index löschen fals vorhanden (PartitionColumnId)
-- *****************************************************************************************
DROP INDEX [IXFactProductInventoryDateKey] ON [dbo].[FactProductInventory]
GO

Tipp:

Man kann sich nach diesen einzelnen Steps die Tabelle skripten lassen, um den Fortschritt festzustellen.

Als nächstes müssen wir der Tabelle einen Clustered Index  verpassen, der dem neuen Partitionscheme zugewiesen wird, um ihn später durch einen Non-Clustered Index zu ersetzen. Desweiteren wird gleichzeitig ein weiteres sehr nützliches Feature der Enterprise Edition des SQL Servers genutzt. Die PAGE bzw. ROW COMPRESSION. In unserem Fall gehen wir davon aus, dass ausschließlich die Notwendigkeit besteht, aus der Tabelle schnell Lesen zu können. Die PAGE Compresssion ist hierfür gut geeignet.

-- *****************************************************************************************
-- Clustered Index auf die DateKey Spalte legen (PartitionColumnId)
-- *****************************************************************************************
CREATE CLUSTERED INDEX [IXFactProductInventoryDateKey] ON [dbo].[FactProductInventory] (
[DateKey])
WITH  (DATA_COMPRESSION = PAGE)    ON [PSProductInventoriesRight]([DateKey])
;
-- *****************************************************************************************
-- Diesen Clustered Index wieder entfernen. Dadurch bleibt aber das PartitionScheme
-- auf der Tabelle.
-- *****************************************************************************************
DROP INDEX [IXFactProductInventoryDateKey] ON  [dbo].[FactProductInventory];
;

Abschließend wird ein Non-Clustered Index erzeugt, der ebenfalls mit dem Scheme verbunden werden muss.

-- *****************************************************************************************
-- Non-Clusterd Index auf die DateKey Spalte legen (PartitionColumnId)
-- *****************************************************************************************
CREATE NONCLUSTERED INDEX [IXFactProductInventoryDateKey] ON [dbo].[FactProductInventory]
(
[DateKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF
, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, DATA_COMPRESSION = PAGE) ON [PSProductInventoriesRight]([DateKey])
;

Wenn wir uns nun das Skript anschauen, welches bei Erzeugen eines Create-Skripts über das Management-Studio generiert wird, kann man erkennen, dass das Partitionscheme nun einerseits auf der Tabelle liegt und andereseits auf dem Index.

CREATE TABLE [dbo].[FactProductInventory](
[ProductKey] [INT] NOT NULL,
[DateKey] [INT] NOT NULL,
[MovementDate] [DATE] NOT NULL,
[UnitCost] [MONEY] NOT NULL,
[UnitsIn] [INT] NOT NULL,
[UnitsOut] [INT] NOT NULL,
[UnitsBalance] [INT] NOT NULL
) ON [PSProductInventoriesRight]([DateKey])
WITH
(
DATA_COMPRESSION = PAGE
)
GO
/****** Object:  Index [IXFactProductInventoryDateKey]    Script Date: 28.05.2016 07:45:31 ******/
CREATE NONCLUSTERED INDEX [IXFactProductInventoryDateKey] ON [dbo].[FactProductInventory]
(
[DateKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = PAGE) ON [PSProductInventoriesRight]([DateKey])
GO

Der nächste Codeteil liefert dann die Aufteilung der Daten auf die Partitionen zurück.

SELECT
    SysTables.OBJECT_ID                                    AS [OBJECT_ID]
    ,object_schema_name(SysPartitions.OBJECT_ID)        AS [TABLE_SCHEMA]
    ,SysTables.name                                        AS [TABLE_NAME]
    ,SysPartitions.partition_number                        AS [PARTITION_NUMBER]
    ,SysRangeValue.VALUE                                AS [PARTITIONCOLUMNID_FROM]
    ,SysRangeValue.boundary_id                            AS [BOUNDARY_ID]
    ,SysPartitions.ROWS                                    AS [PARTITION_ROWS]
    ,SysPartitionFunctions.name                            AS [PARTITION_FUNCTION]
    ,SysSchemes.name                                    AS [PARTITION_SCHEMA]
FROM sys.partitions AS [SysPartitions]
    INNER join sys.indexes AS [SysIndizes]
        ON SysPartitions.OBJECT_ID = SysIndizes.OBJECT_ID
            and SysPartitions.index_id = SysIndizes.index_id
    INNER join sys.tables AS SysTables
        ON SysPartitions.OBJECT_ID = SysTables.OBJECT_ID
    INNER join sys.partition_schemes AS SysSchemes
        ON SysIndizes.data_space_id = SysSchemes.data_space_id
    INNER join sys.partition_functions AS SysPartitionFunctions
        ON SysPartitionFunctions.function_id = SysSchemes.function_id
    INNER join sys.partition_range_values AS SysRangeValue
        ON SysRangeValue.function_id = SysSchemes.function_id
            and SysPartitions.partition_number = SysRangeValue.boundary_id

Bei dieser Methode muss beachtet werden, dass bei großen Datenmengen dieser Vorgang mehrer Stunden dauern kann und somit das System für längere Zeit beinträchtigt ist. Durch die Möglichkeit, den Index Online neu aufzubauen (Enterprise Feature) kann aber vermieden werden, dass das System überhaupt nicht zur Verfügung steht.

Fazit:

Beide Herangehensweisen haben ihren Charm, wobei ich sagen muss, dass die 2. Variante mir am Besten gefällt. Der einfachste Weg ist aber immer noch das Anlegen und Zuweisen eines Partitionschemes bevor Daten in die Tabelle geladen wurden.