TSQL: Partition by in Kombination mit einem DELETE

Nicht ganz so spektakulär, aber vor kurzem hatte ich das Problem, dass ich mit PARTITION BY Datensätze selektieren wollte, die ich danach löschen wollte.Vorab einfach mal, was macht den überhaupt ein PARTITION BY? Eigentlich sehr simpel, ich selektiere Datensätze, versehe diese mit einer Row_Number damit jeder DS eine eindeutige ID erhält und danach Gruppiere ich diese um somit Partitionen von Datensätzen zu bilden.

Beispiel anhand der Northwind Datenbank:

SELECT * FROM (
SELECT CustomerID, PostalCode, City, row_number()
OVER (Partition by PostalCode, City ORDER BY PostalCode, City) As Row
FROM dbo.Customers) t
WHERE t.Row > 1

Ergebnis:

 

 

 

Nun den, so haben wir alle Datensätze wo ich eine doppelte PLZ und einen doppelten Ort habe, in einer Partition. Aber das Partion by ist ja schnell geschrieben, aber die Partition bilde ich ja in der Feldliste des SELECT’s, und nicht in der WHERE Condition. Wie lösche ich nun diese Datensätze?

Ganz einfach … aber vielleicht auch etwas ungewöhlich(?)

DELETE FROM t from  (
SELECT CustomerID, PostalCode, City, row_number()
OVER (Partition by PostalCode, City ORDER BY PostalCode, City) As Row
FROM dbo.Customers2) t
WHERE t.Row > 1

 

Komprimierung von Partitionen

Datenbanken wachsen. Wenn der Speicherplatz knapp wird, können einzelne Tabellen komprimiert werden, jedoch sollte dies mit Vorsicht angegangen werden, denn wenn hoch frequentierte Tabellen komprimiert werden, kostet das unnötig Zeit und CPU Ressourcen. Daher sollte diese Technologie eher bei partitionierten Tabellen zum Einsatz gebracht werden, wenn man ältere und nicht häufig verwendete Daten von den aktuellen differenziert werden können. Die Partitionen können dann durch die Zeilen/Seiten Komprimierung des SQL Servers komprimiert werden.

Hinweis:
Dieser Artikel bezieht sich auf die folgende Artikel in den Beispielen:
Patritionierung von bestehenden Tabellen
Partitionierung von SQL Server Tabellen

Nimmt man das Beispiel „Patritionierung von bestehenden Tabellen“ als Grundlage und fügt dort weitere Daten ein (pro Partition 2000 Zeilen):
DECLARE @counter INT = 9
WHILE @counter < 10000
BEGIN
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (@counter, 'Januar 2008', '20080308', '100');
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (@counter, @counter);
SET @counter = @counter + 1
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (@counter, 'März 2009', '20090308', '100');
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (@counter, @counter);
SET @counter = @counter + 1
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (@counter, 'Februar 2010', '20100209', '100');
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (@counter, @counter);
SET @counter = @counter + 1
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (@counter, 'Januar 2011', '20110120', '100');
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (@counter, @counter);
SET @counter = @counter + 1
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (@counter, 'Januar 2012', '20120115', '300');
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (@counter, @counter);
SET @counter = @counter + 1
END

Nun ergibt sich folgende Speicherbelegung (SQL Server Bericht, Speicherbelegung durch Partitionen):

Wenn nun die Partitionen durch folgendes Statement komprimiert werden …
ALTER TABLE [dbo].[buchungen] REBUILD PARTITION = 1 WITH(DATA_COMPRESSION = ROW )
ALTER TABLE [dbo].[buchungen] REBUILD PARTITION = 2 WITH(DATA_COMPRESSION = ROW )
ALTER TABLE [dbo].[buchungen] REBUILD PARTITION = 3 WITH(DATA_COMPRESSION = ROW )
ALTER TABLE [dbo].[buchungen] REBUILD PARTITION = 5 WITH(DATA_COMPRESSION = ROW )

… ergibt sich folgendes Bild:

Gesamt ergibt das in diesem Beispiel ~100 KB an Speicherplatz-Gewinn. Die Komprimierung kann auch hervorragend durch den Assistenten des SMSS durchgeführt werden.

Partitionierung von bestehenden Tabellen

In dem Artikel Partitionierung von SQL Server Tabellen habe ich bereits beschrieben, wie man die Partionierung auf eine neue Tabelle anwendet. In der Praxis führt man solche Technologien doch eher an bestehenden Tabellen ein, wenn man merkt, das die Tabelle doch größer als erwartet wird. Daher stellt sich die Frage: Wie partitioniere ich eine bestehende Tabelle?

Gründsätzlich könnte man die bestehende Tabelle auslagern, neu erstellen und dann wieder alle Daten reinkopieren. Das ist u.U nicht immer ganz einfach, besonders dann, wenn die Tabelle referentielle Integritäten besitzt (Fremdschlüssel, bzw. „Verlinkungen in andere Tabellen“) und man durch ein Löschen und Neuerstellen doch mehr Arbeit produziert, als notwendig.

Zuerst erstellen wir uns die Umgebung (auf Basis des Beispiels von dem Artikel: Partitionierung von SQL Server Tabellen):
CREATE TABLE dbo.buchungen (
[uid] INT NOT NULL PRIMARY KEY,
[name] VARCHAR(50),
[datum] DATETIME DEFAULT GETDATE() NOT NULL,
[wert] MONEY);
GO

CREATE TABLE dbo.buchungen_Log (
[uid] INT NOT NULL PRIMARY KEY,
[buchungen_uid] INT NOT NULL);
GO

ALTER TABLE dbo.buchungen_Log ADD CONSTRAINT FK_buchungen_Log_buchungen FOREIGN KEY
(buchungen_uid) REFERENCES dbo.buchungen
(uid)
GO

INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (1, 'Januar 2008', '20080308', '100');
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (2, 'März 2009', '20090308', '100');
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (3, 'Februar 2010', '20100209', '100');
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (4, 'Januar 2011', '20110120', '100');
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (5, 'Januar 2012', '20120115', '300');
GO
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (1, 1);
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (2, 2);
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (3, 3);
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (4, 4);
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (5, 5);
GO

Nun haben wir die Tabelle „Buchungen“ die die zu partitionierenden Daten enthält und auch den Fremschlüssel für die Tabelle „buchungen_log“ stellt.
Ansich geht man nun wir bei der Partitionierung einer neuen Tabelle vor, indem man zuerst die Dateigruppen und die jeweiligen Dateien erstellt:

ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2009]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2010]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2011]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2012]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv]
GO

ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2009',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2009.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2009]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2010',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2010.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2010]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2011',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2011.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2011]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2012',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2012.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2012]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'Archiv',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\Archiv.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv]
GO

Nun benötigt man die Partitionierungs-Funktion und das Schema für die Datenverteilung:
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2009]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2010]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2011]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2012]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv]
GO

ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2009',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2009.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2009]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2010',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2010.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2010]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2011',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2011.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2011]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2012',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2012.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2012]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'Archiv',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\Archiv.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv]
GO

Nun wird die Partionierungsfunktion wie auch das Schema erstellt, um die Datentrennung zu regeln:
CREATE PARTITION FUNCTION ArchivierungsFunktion (DATETIME) AS RANGE RIGHT
FOR VALUES (
'20090101' -- Kleiner als 01.01.2009
, '20100101' -- Kleiner als 01.01.2010, größer als 01.01.2009
, '20110101' -- Kleiner als 01.01.2011, größer als 01.01.2010
, '20120101' -- Kleiner als 01.01.2012, größer als 01.01.2011
);

CREATE PARTITION SCHEME ArchivierungsSchema AS PARTITION ArchivierungsFunktion
TO ('Archiv', 'Archiv2009', 'Archiv2010', 'Archiv2011', 'Archiv2012');

Nun benötigt man einen Primär Schlüssel, wo jedoch auch das Datums Feld (Kennzeichen für die Partionierung) enthalten ist. Dafür muss jedoch der Schlüssel erstmal entfernt werden, wie auch die Fremschlüssel Verbindung:
ALTER TABLE [dbo].[buchungen_Log] DROP CONSTRAINT [FK_buchungen_Log_buchungen]
GO
ALTER TABLE [dbo].[buchungen] DROP CONSTRAINT [PK__buchunge__DD7012647F60ED59]
GO

Nun kann der Primärschlüssel erstellt werden, der dann an die Partionierungsfunktion gehängt wird (dieser muss ein Gruppierter Index sein, eine HEAP Tabelle (ohne Gruppierten Index) wird hier nicht unterstützt, da die Daten sonst nicht verschoben werden können):
ALTER TABLE dbo.buchungen ADD CONSTRAINT
PK_buchungen PRIMARY KEY CLUSTERED
([uid], [datum]) on ArchivierungsSchema(datum);

Kontrolliert werden kann dies folgendermaßen:
SELECT
sys.filegroups.name 'Partitionsname' , sys.partitions.rows 'Zeilenanzahl'
FROM
sys.partitions INNER JOIN sys.allocation_units
ON sys.allocation_units.container_id = sys.partitions.hobt_id
INNER JOIN sys.filegroups ON sys.filegroups.data_space_id = sys.allocation_units.data_space_id
WHERE sys.partitions.object_id = OBJECT_ID('dbo.buchungen')

Das wars.

Partitionierung von SQL Server Tabellen

In diesem Blog Eintrag wird die Partitionierung von Tabellen mit dem Microsoft SQL Server 2008R2 beschrieben.
Diese Funktion wurde mit der Version 2005 eingeführt und ermöglicht die Verteilung von Daten einer Tabelle in verschiedene Dateigruppen um diese bspw. Archivieren zu können indem die Daten anhand eines Merkmals in verschiedene Dateigruppen unterteilt werden.

Folgendes Beispiel beschreibt die einer Buchungstabelle in der die Datensätze anhand des Buchungsdatums in einzelne Dateigruppen unterteilt werden können. So kann bei anwachsen der Datenbank eine einzelne Datei, wo die Datensätze nicht mehr benötigt werden, gesichert und geleert werden. Dadurch wird die Datenbank wieder kleiner und erhöht gleichzeitig die Performance der Datenbank.

Vorab möchte ich noch darauf hinweisen, dass die Partitionierungs-Funktion nur in der Enterprise/Datacenter Edition verügbar ist.

Ausgangssituation für folgendes Beispiel:

  • Eine Buchungstabelle soll erstellt werden in der Daten aus einer anderen Datenbank übernommen werden
  • Es werden Daten aus den Kalenderjahren 2010, 2009 und früher übernommen.
  • Die Daten die früher als 2009 eingegeben wurden sind nur wenige
  • Die Partitionierung soll erweitert werden können um zukünftige Geschäftsjahre aufnehmen zu können

Lösungsmöglichkeit: Zuerst werden 4 Dateigruppen in die Datenbank eingefügt für die Geschäftsjahre 2011, 2010, 2009 und eine Datei für alle vorherigen Jahre:
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2009]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2010]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2011]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv]
Nun wird zu den 4 Dateigruppen jeweils eine Datendatei hinzugefügt:
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2009',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.VERTEILER\MSSQL\DATA\2009.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2009]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2010',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.VERTEILER\MSSQL\DATA\2010.ndf' ,SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2010]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2011',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.VERTEILER\MSSQL\DATA\Aktuell.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2011]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'Archiv',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.VERTEILER\MSSQL\DATA\Archiv.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv]
GO

Nun wird eine Partitionierungs-Funktion erstellt, die auf ein DATETIME Feld prüft und die Daten entsprechend verteilt:
CREATE PARTITION FUNCTION ArchivierungsFunktion (DATETIME) AS RANGE RIGHT
FOR VALUES (
'20090101' -- Kleiner als 01.01.2009
, '20100101' -- Kleiner als 01.01.2010, größer als 01.01.2009
, '20110101' -- Kleiner als 01.01.2011, größer als 01.01.2010 geht
);

Nun wird das Schema erstellt was die Funktion als Basis für die Datenverteilung nutzt und diese dann in die Dateigruppen aufteilt:
CREATE PARTITION SCHEME ArchivierungsSchema AS PARTITION ArchivierungsFunktion
TO ('Archiv', 'Archiv2009', 'Archiv2010', 'Archiv2011');
Als nächstes wird die Buchungstabelle erstellt die dem Schema zugeordnet wird. Das Datumsfeld, welches die Daten an die Funktion zur Verteilung weitergibt, muss mit in den Primärschlüssel aufgenommen werden:
CREATE TABLE dbo.buchungen (
[uid] uniqueidentifier default NEWID() NOT NULL,
[name] VARCHAR(50),
[datum] DATETIME DEFAULT GETDATE() NOT NULL,
[wert] MONEY) ON ArchivierungsSchema(datum);
GO
ALTER TABLE dbo.buchungen ADD CONSTRAINT
PK_buchungen PRIMARY KEY CLUSTERED
([uid], [datum]);

INSERT INTO dbo.buchungen (name, datum, wert) VALUES ('Januar 2008', '20080308', '100');
GO
-- Insert in Archiv2009
INSERT INTO dbo.buchungen (name, datum, wert) VALUES ('März 2009', '20090308', '100');
GO
-- Insert in Archiv2010
INSERT INTO dbo.buchungen (name, datum, wert) VALUES ('Februar 2010', '20100209', '100');
GO
-- Insert in Archiv2011
INSERT INTO dbo.buchungen (name, datum, wert) VALUES ('Januar 2011', '20110120', '100');
Um zu prüfen ob die eingetragenen Datensätze sich in den richtigen Partionen befinden, kann folgendes Statement verwendet werden:
SELECT
sys.filegroups.name 'Partitionsname' , sys.partitions.rows 'Zeilenanzahl'
FROM
sys.partitions INNER JOIN sys.allocation_units
ON sys.allocation_units.container_id = sys.partitions.hobt_id
INNER JOIN sys.filegroups ON sys.filegroups.data_space_id = sys.allocation_units.data_space_id
WHERE sys.partitions.object_id = OBJECT_ID('dbo.buchungen')

Hier sehen wir dann die Datenverteilung wie die Datensätze sich durch die Funktion in die richtigen Dateien verteilt haben.

 

Problematisch wird es dann wenn ein neues Geschäftsjahr anbricht und Datensätze für 2012 eingetragen werden. Diese Datensätze befinden sich dann in der Partition für 2011: INSERT INTO dbo.buchungen (name, datum, wert) VALUES ('Januar 2012', '20120115', '300'); Dieses Problem kann dann durch Erstellen einer neuen Dateigruppe und dem Anpassen der Funktion gelöst werden:
-- Neue Dateigruppe für 2012
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2012]
GO

-- Neue Datei für die Dateigruppe 2012
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2012', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.VERTEILER\MSSQL\DATA\2012.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2012]

Nun muss das Schema angepasst werden um die neue Dateigruppen zu verwenden, diese wird dann als Standard hinten dran gestellt.
Weiterhin wird durch die SPLIT() Funktion eine neue Begrenzung für 2012 eingefügt. Dadurch werden dann die bestehenden Daten geprüft und neu verteilt:
ALTER PARTITION SCHEME ArchivierungsSchema
NEXT USED [Archiv2012]
GO
ALTER PARTITION FUNCTION ArchivierungsFunktion ()
SPLIT RANGE ('20120101');

Hier ist dann zu sehen das die neue Dateigruppe 2012 verwendet wird, und der Datensatz aus der Partition 2011 in die Partition 2012 verschoben worden ist.

 

Download: