SH

Sascha Hess

xenosystems.de - IT-Consulting & Data Management

www.xenosystems.de

STRATEGISCHE WISSENS-ROADMAP 2026

SQL Server Partitionierung

grosser Tabellen

Partition Switch, Elimination und Rolling Window — Wartung in Sekunden statt Stunden

1

Partition Function & Scheme

Vollstaendige Implementierung von RANGE RIGHT/LEFT bis Filegroup-Design

2

Partition Switching

Daten in Millisekunden laden und archivieren — ohne Table-Lock

3

Partition Elimination

Sicherstellen dass der Planner nur relevante Partitionen liest

4

Rolling Window Automatisierung

Monatlich neue Partition hinzufuegen, alte archivieren — ohne Downtime

5

30-Tage-Partitionierungsplan

Von der Kandidaten-Analyse bis zur produktiven Partitionierung

WAS SIE IN DIESEM KIT ERWARTEN DUERFEN

■  Partition Function & Scheme — vollstaendige Implementierung von Grund auf mit allen Optionen.

■  Partition Switch — Daten in Millisekunden laden und archivieren, ohne Table-Lock.

■  Index-Alignment — ausgerichtete Indizes, Einschraenkungen und Loesungsstrategien.

■  Partition Elimination — sicherstellen dass der Planner nur relevante Partitionen liest.

■  30-Tage-Plan — von der Analyse bestehender Tabellen bis zur produktiven Partitionierung.

ZIEL DIESES KITS

Nach dem Lesen und Umsetzen dieses Kits koennen Sie grosse Tabellen zuverlaessig partitionieren, Daten per Partition Switch in Sekunden laden und archivieren, Abfragen auf Partition Elimination pruefen und optimieren, Partitionen in Produktion sicher aufteilen und zusammenfuehren und den Gesundheitszustand Ihrer Partitionierung kontinuierlich ueberwachen.

SCHICHT 1 — PARTITION FUNCTION (WIE wird aufgeteilt?):

  Definiert die Grenzwerte (Boundaries) und den Datentyp der Partitionsspalte.

  Bestimmt: Welcher Wert gehoert in welche Partition?

  Beispiel:  Grenzen 2023-01-01, 2024-01-01, 2025-01-01

             → 4 Partitionen: bis 2022, 2023, 2024, ab 2025

SCHICHT 2 — PARTITION SCHEME (WO wird gespeichert?):

  Ordnet jede Partition einer Filegroup zu.

  Bestimmt: Auf welchem Datentraeger liegt welche Partition?

  Optionen:  Alle auf PRIMARY, jede auf eigener Filegroup, nach Alter aufteilen

SCHICHT 3 — TABELLE / INDEX (WAS wird partitioniert?):

  Tabelle oder Index wird auf dem Partition Scheme erstellt.

  Partitionsspalte: muss in der Partition Function definiertem Typ entsprechen.

  Clustered Index: bestimmt die physische Reihenfolge der Daten.

  Alignment:       Clustered Index und Tabelle auf gleichem Scheme = ausgerichtet.

-- RANGE LEFT: Grenzwert gehoert zur LINKEN (niedrigeren) Partition

CREATE PARTITION FUNCTION pf_links (DATE) AS RANGE LEFT FOR VALUES

    ('2023-12-31', '2024-12-31', '2025-12-31');

-- Partition 1: <= 2023-12-31

-- Partition 2: 2024-01-01 bis 2024-12-31

-- Partition 3: 2025-01-01 bis 2025-12-31

-- Partition 4: >= 2026-01-01

-- RANGE RIGHT: Grenzwert gehoert zur RECHTEN (hoeheren) Partition

CREATE PARTITION FUNCTION pf_rechts (DATE) AS RANGE RIGHT FOR VALUES

    ('2024-01-01', '2025-01-01', '2026-01-01');

-- Partition 1: < 2024-01-01  (= bis 2023-12-31)

-- Partition 2: 2024-01-01 bis 2024-12-31

-- Partition 3: 2025-01-01 bis 2025-12-31

-- Partition 4: >= 2026-01-01

-- EMPFEHLUNG fuer Datum/Zeit:

-- RANGE RIGHT mit dem ersten Tag jedes Zeitraums als Grenzwert.

-- Intuitiver: '2024-01-01' als Grenzwert bedeutet "ab 2024".

-- RANGE LEFT mit dem letzten Tag: '2023-12-31' bedeutet "bis Ende 2023".

-- Beide Varianten sind gleichwertig — Konsistenz ist entscheidend!

-- Vollstaendiges Beispiel: Jahres-Partitionierung einer Transaktions-Tabelle

-- Schritt 1: Filegroups anlegen (optional aber empfohlen fuer grosse Systeme)

ALTER DATABASE IhreDB ADD FILEGROUP fg_archiv_2022;

ALTER DATABASE IhreDB ADD FILEGROUP fg_archiv_2023;

ALTER DATABASE IhreDB ADD FILEGROUP fg_archiv_2024;

ALTER DATABASE IhreDB ADD FILEGROUP fg_aktiv;

-- Datenbankdateien den Filegroups zuweisen

ALTER DATABASE IhreDB ADD FILE (

    NAME = 'archiv_2022',

    FILENAME = 'D:\Data\IhreDB_archiv_2022.ndf',

    SIZE = 5120MB, FILEGROWTH = 1024MB

) TO FILEGROUP fg_archiv_2022;

-- (Wiederholen fuer 2023, 2024, aktiv)

-- Schritt 2: Partition Function

CREATE PARTITION FUNCTION pf_transaktion_datum (DATE)

AS RANGE RIGHT FOR VALUES (

    '2022-01-01',   -- Grenze 1: ab hier Partition 2 (Jahr 2022)

    '2023-01-01',   -- Grenze 2: ab hier Partition 3 (Jahr 2023)

    '2024-01-01',   -- Grenze 3: ab hier Partition 4 (Jahr 2024)

    '2025-01-01'    -- Grenze 4: ab hier Partition 5 (ab 2025, aktiv)

);

-- Ergebnis: 5 Partitionen

-- P1: < 2022-01-01 (Altdaten)

-- P2: 2022-01-01 bis 2022-12-31

-- P3: 2023-01-01 bis 2023-12-31

-- P4: 2024-01-01 bis 2024-12-31

-- P5: >= 2025-01-01 (aktuelle Daten)

-- Schritt 3: Partition Scheme

CREATE PARTITION SCHEME ps_transaktion_datum

AS PARTITION pf_transaktion_datum

TO (

    fg_archiv_2022,   -- P1 Altdaten

    fg_archiv_2022,   -- P2 Jahr 2022

    fg_archiv_2023,   -- P3 Jahr 2023

    fg_archiv_2024,   -- P4 Jahr 2024

    fg_aktiv          -- P5 aktuelle Daten

);

-- Alternative: Alle auf PRIMARY wenn Filegroup-Trennung nicht noetig

-- TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]);

-- Schritt 4: Tabelle auf Partition Scheme erstellen

CREATE TABLE dbo.Transaktionen (

    TransaktionID    BIGINT IDENTITY(1,1) NOT NULL,

    Transaktions_Dat DATE NOT NULL,          -- PARTITIONSSPALTE

    Kunden_ID        INT NOT NULL,

    Artikel_ID       INT NOT NULL,

    Menge            DECIMAL(18,4) NOT NULL,

    Betrag           MONEY NOT NULL,

    Status           TINYINT NOT NULL DEFAULT 1,

    Erstellt_Am      DATETIME2 DEFAULT GETDATE(),

    -- Clustered Index auf Partition Scheme: ALIGNMENT sichergestellt

    CONSTRAINT PK_Transaktionen

        PRIMARY KEY CLUSTERED (Transaktions_Dat, TransaktionID)

        ON ps_transaktion_datum(Transaktions_Dat)

        -- Partitionsspalte MUSS Teil des Clustered Index sein!

);

-- Hinweis: Wenn TransaktionID als einzige PK-Spalte gewuenscht:

-- IDENTITY-Werte sind global eindeutig, Constraint muss beide Spalten enthalten

-- da Partitionsspalte immer im Clustered Index sein muss.

-- Loesung: Surrogate Key + Partitionsspalte als zusammengesetzter PK.

RANGE-PARTITIONIERUNG (nativ in SQL Server):

  Wann:    Zeitreihen, Zahlenbereiche, sequenzielle Daten

  Vorteil: Partition Elimination bei Datumsfiltern, einfache Archivierung

  Granularitaet: Jahr, Quartal, Monat, Woche — je nach Datenvolumen

  Faustregel:    Pro Partition 1-50 GB Zieldaten, max. 500-1.000 Partitionen

  Jahrespartitionierung:   geeignet fuer 50-500 GB Gesamtdaten

  Monatspartitionierung:   geeignet fuer 500 GB - 10 TB Gesamtdaten

  Tagespartitionierung:    nur fuer sehr grosse Systeme (> 10 TB), vorsichtig!

LISTE (emuliert via Computed Column + Range):

  Wann:    Kategorische Daten (Region, Mandant, Produktgruppe)

  Beispiel: Mandant_ID in (1,2,3,4,5) → 5 Partitionen

  Vorteil: Mandanten-Isolation, schnelle Mandanten-spezifische Abfragen

  Nachteil: Neue Mandanten erfordern Partition-Split (Wartungsaufwand)

HASH (emuliert via Computed Column):

  Wann:    Gleichmaessige Verteilung ohne erkennbares Muster

  Beispiel: ABS(CHECKSUM(KundenID)) % 8 → 8 Partitionen

  Vorteil:  Gleichmaessige Datenverteilung, parallele I/O-Nutzung

  Nachteil: Keine Partition Elimination, kein einfaches Switching

  Empfehlung: Selten sinnvoll in SQL Server OLTP

50 - 200 GB

Jaehrlich

5 - 15

10 - 40 GB

200 GB - 2 TB

Quartalsweise

12 - 40

15 - 50 GB

2 TB - 10 TB

Monatlich

24 - 120

20 - 80 GB

> 10 TB

Monatlich / Woechentlich

60 - 500

20 - 50 GB

-- KRITERIEN fuer die ideale Partitionsspalte:

-- 1. Kardinalitaet passt zur Granularitaet

--    Datum → Jahres-/Monatspartitionierung: perfekt

--    IDENTITY-Spalte → Range ueber Zahlenbereiche: moeglich

--    Status (0/1/2) → nur 3 Partitionen: zu wenig

-- 2. Abfragen filtern haeufig nach dieser Spalte

SELECT COUNT(*) FROM dbo.Transaktionen

WHERE Transaktions_Dat >= '2025-01-01'  -- filtert auf Partitionsspalte

  AND Transaktions_Dat < '2026-01-01';  -- → Partition Elimination aktiv

-- 3. Schreiblast verteilt sich gleichmaessig

--    Datetime der Einfuegung → aktuellste Partition bekommt alle Inserts

--    Das ist normal und akzeptabel fuer OLTP!

--    Problem nur wenn alle Partitionen gleichzeitig beschrieben werden muessen.

-- 4. Nullable-Spalten VERMEIDEN als Partitionsspalte

--    NULL-Werte landen immer in der ersten Partition

--    → ungleiche Verteilung, schwer zu kontrollieren

--    Besser: NOT NULL Constraint + sinnvoller Default

-- 5. Datentyp waehlen:

--    DATE:      ideale fuer tagesbasierte Partitionierung (4 Bytes)

--    DATETIME2: wenn Uhrzeit benoetigt wird (6-8 Bytes)

--    INT/BIGINT: fuer ID-basierte Partitionierung

--    Kein VARCHAR als Partitionsspalte — Performance-Probleme bei Elimination

-- SZENARIO: Bestehende Tabelle dbo.Transaktionen_Alt (nicht partitioniert)

-- soll online ohne langen Downtime partitioniert werden.

-- METHODE: Neue partitionierte Tabelle erstellen, Daten per Switch einspielen

-- Vorteil: Kein langer Table-Lock, minimale Unterbrechung

-- PHASE 1: Partition Function und Scheme erstellen (bereits in Kapitel 2 gezeigt)

-- Voraussetzung: pf_transaktion_datum und ps_transaktion_datum existieren

-- PHASE 2: Neue partitionierte Tabelle erstellen (leer, gleiche Struktur)

CREATE TABLE dbo.Transaktionen_Neu (

    TransaktionID    BIGINT NOT NULL,

    Transaktions_Dat DATE NOT NULL,

    Kunden_ID        INT NOT NULL,

    Betrag           MONEY NOT NULL,

    -- alle weiteren Spalten identisch zu Transaktionen_Alt

    CONSTRAINT PK_Transaktionen_Neu

        PRIMARY KEY CLUSTERED (Transaktions_Dat, TransaktionID)

        ON ps_transaktion_datum(Transaktions_Dat)

) ON ps_transaktion_datum(Transaktions_Dat);

-- PHASE 3: Indizes auf neuer Tabelle erstellen

CREATE INDEX IX_Transaktionen_Neu_KundenID

    ON dbo.Transaktionen_Neu (Kunden_ID)

    ON ps_transaktion_datum(Transaktions_Dat);  -- ausgerichtet

-- PHASE 4: Daten in Batches einfuegen (keine Single-Insert-Schleife!)

-- Fuer jede Partition separat:

INSERT INTO dbo.Transaktionen_Neu

    SELECT * FROM dbo.Transaktionen_Alt

    WHERE Transaktions_Dat >= '2022-01-01'

      AND Transaktions_Dat < '2023-01-01';

-- Commit nach jedem Jahres-Batch → Transaktion bleibt ueberschaubar

-- Wiederholbar: bereits eingefuegte Daten koennen mit DELETE vorher bereinigt werden

-- PHASE 5: Umbenennung (kurzer Lock — Sekunden)

BEGIN TRANSACTION;

    EXEC sp_rename 'dbo.Transaktionen_Alt', 'Transaktionen_Alt_Backup';

    EXEC sp_rename 'dbo.Transaktionen_Neu', 'Transaktionen';

COMMIT;

-- PHASE 6: Alttabelle nach Validierung droppen

-- DROP TABLE dbo.Transaktionen_Alt_Backup;

-- Fuer sehr grosse Tabellen (TB-Bereich): SWITCH-basierte Migration

-- Voraussetzung: Beide Tabellen auf gleichem Filegroup oder Scheme

-- Schritt 1: Neue Staging-Tabelle mit identischer Struktur (partitioniert)

-- Schritt 2: Daten Partition fuer Partition per SWITCH uebertragen

-- Schritt 3: Jede Partition in < 1 Sekunde — kein langer Lock

-- Beispiel: Partition 2 (Jahr 2022) aus alter Tabelle in neue uebertragen

ALTER TABLE dbo.Transaktionen_Alt

    SWITCH PARTITION 2

    TO dbo.Transaktionen_Neu PARTITION 2;

-- Ergebnis: Partition 2 ist sofort in Transaktionen_Neu, Alt ist leer

-- Dauer: Millisekunden — unabhaengig von der Datenmenge!

-- Validierung nach jedem Switch:

SELECT $PARTITION.pf_transaktion_datum(Transaktions_Dat) AS Partition_Nr,

       MIN(Transaktions_Dat) AS Von,

       MAX(Transaktions_Dat) AS Bis,

       COUNT(*) AS Zeilen

FROM dbo.Transaktionen_Neu

GROUP BY $PARTITION.pf_transaktion_datum(Transaktions_Dat)

ORDER BY Partition_Nr;

Partitionsspalte nicht im Clustered Index

Msg 1908: Partitionsspalte muss Teil des Schluessel sein

Partitionsspalte in PK aufnehmen

Tabellen auf verschiedenen Filegroups

Switch nicht moeglich

Beide Tabellen auf gleiches Scheme legen

CHECK Constraint fehlt fuer Switch

Msg 4982: Grenzwert verletzt

CHECK Constraint auf Staging-Tabelle setzen

Non-aligned Index verhindert Switch

Msg 4972

Index droppen, neu ausgerichtet erstellen

NULL in Partitionsspalte

Alle NULL in P1, ungleiche Verteilung

NOT NULL Constraint + Default setzen

Falsche Boundary-Richtung

Daten in falscher Partition

RANGE RIGHT/LEFT konsistent einhalten

AUSGERICHTETER INDEX (Aligned):

  Definition:   Index liegt auf gleichem Partition Scheme wie Tabelle

  Syntax:       ON ps_transaktion_datum(Transaktions_Dat)

  Vorteil:      Partition Switch moeglich (Archivierung in ms)

  Vorteil:      Index Rebuild pro Partition moeglich (kein Full-Table-Lock)

  Vorteil:      Partition Elimination auch ueber diesen Index aktiv

  Pflicht:      Der Clustered Index MUSS immer ausgerichtet sein

NICHT-AUSGERICHTETER INDEX (Non-Aligned):

  Definition:   Index liegt auf anderer Filegroup / kein Partition Scheme

  Syntax:       ON [PRIMARY]  oder andere Filegroup

  Vorteil:      Kann Spalten enthalten die nicht in Partitionsspalte passen

  Nachteil:     Partition Switch NICHT moeglich solange dieser Index existiert

  Nachteil:     Index Rebuild immer ueber gesamte Tabelle (langer Lock)

  Nachteil:     Muss vor Switch gedroppt, danach neu erstellt werden

  Wann sinnvoll: Seltener Spezialfall — normalerweise vermeiden

-- AUSGERICHTETE Indizes (empfohlen):

-- Non-Clustered Index ausgerichtet (Partitionsspalte im Scheme angegeben)

CREATE NONCLUSTERED INDEX IX_Trans_KundenID

    ON dbo.Transaktionen (Kunden_ID, Transaktions_Dat)

    ON ps_transaktion_datum(Transaktions_Dat);

-- Index mit INCLUDE-Spalten ausgerichtet

CREATE NONCLUSTERED INDEX IX_Trans_Betrag

    ON dbo.Transaktionen (Transaktions_Dat, Betrag)

    INCLUDE (Kunden_ID, Artikel_ID)

    ON ps_transaktion_datum(Transaktions_Dat);

-- NICHT-AUSGERICHTETER Index (nur wenn zwingend noetig):

CREATE NONCLUSTERED INDEX IX_Trans_Global_Artikel

    ON dbo.Transaktionen (Artikel_ID)

    INCLUDE (Betrag)

    ON [PRIMARY];  -- explizit auf PRIMARY, nicht auf Partition Scheme

-- WARNUNG: Dieser Index muss vor jedem SWITCH gedroppt werden!

-- Pruefen ob alle Indizes ausgerichtet sind:

SELECT

    i.name AS Index_Name,

    i.type_desc AS Index_Typ,

    ps.name AS Partition_Scheme,

    CASE WHEN i.data_space_id = t.data_space_id

         THEN 'AUSGERICHTET' ELSE 'NICHT AUSGERICHTET' END AS Alignment

FROM sys.indexes i

JOIN sys.tables t ON i.object_id = t.object_id

LEFT JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id

WHERE t.name = 'Transaktionen'

ORDER BY i.type, i.name;

-- PROBLEM: Unique Constraint muss Partitionsspalte enthalten

-- SQL Server kann Eindeutigkeit nur innerhalb einer Partition garantieren

-- ohne Partitionsspalte im Schluessel.

-- FALSCH: Unique nur auf GeschaeftsID (ohne Partitionsspalte)

-- Msg 1908: Partitionierung nicht moeglich

-- RICHTIG: Partitionsspalte in Unique Constraint einbeziehen

ALTER TABLE dbo.Transaktionen

    ADD CONSTRAINT UQ_Trans_GeschaeftsID

    UNIQUE NONCLUSTERED (GeschaeftsID, Transaktions_Dat)

    ON ps_transaktion_datum(Transaktions_Dat);

-- Wenn globale Eindeutigkeit zwingend benoetigt:

-- Option 1: Separate Lookup-Tabelle mit dem Unique Constraint (nicht partitioniert)

-- Option 2: Application-seitige Eindeutigkeit sicherstellen

-- Option 3: Index auf PRIMARY (non-aligned) — verliert Switch-Faehigkeit

-- SZENARIO: Daten aus 2022 archivieren (aus aktiver Tabelle entfernen)

-- Schritt 1: Archivtabelle vorbereiten (IDENTISCHE Struktur, partitioniert)

CREATE TABLE dbo.Transaktionen_Archiv (

    TransaktionID    BIGINT NOT NULL,

    Transaktions_Dat DATE NOT NULL,

    Kunden_ID        INT NOT NULL,

    Betrag           MONEY NOT NULL,

    -- alle Spalten identisch zu Haupttabelle

    CONSTRAINT PK_Trans_Archiv

        PRIMARY KEY CLUSTERED (Transaktions_Dat, TransaktionID)

        ON ps_transaktion_datum(Transaktions_Dat)

) ON ps_transaktion_datum(Transaktions_Dat);

-- Schritt 2: CHECK Constraint auf Archivtabelle (PFLICHT fuer Switch!)

-- SQL Server prueft vor Switch: passen alle Daten in die Zieltabelle?

ALTER TABLE dbo.Transaktionen_Archiv

    ADD CONSTRAINT CK_Archiv_Datum_2022

    CHECK (Transaktions_Dat >= '2022-01-01'

       AND Transaktions_Dat < '2023-01-01');

-- Schritt 3: Partition Nummer ermitteln

SELECT $PARTITION.pf_transaktion_datum('2022-06-15') AS Partition_Nr;

-- Ergebnis: 2 (Jahr 2022 liegt in Partition 2)

-- Schritt 4: SWITCH ausfuehren

-- Dauer: Millisekunden — unabhaengig von Zeilenanzahl!

ALTER TABLE dbo.Transaktionen

    SWITCH PARTITION 2               -- Quell-Partition (2022)

    TO dbo.Transaktionen_Archiv PARTITION 2;

-- Ergebnis:

-- Transaktionen: Partition 2 ist leer (Daten weg, Struktur bleibt)

-- Transaktionen_Archiv: Partition 2 enthaelt alle 2022-Daten

-- Dauer: < 1 Sekunde fuer beliebige Datenmenge!

-- Schritt 5: Validierung

SELECT COUNT(*) AS Zeilen_Haupttabelle

FROM dbo.Transaktionen

WHERE Transaktions_Dat >= '2022-01-01' AND Transaktions_Dat < '2023-01-01';

-- Erwartetes Ergebnis: 0

SELECT COUNT(*) AS Zeilen_Archiv

FROM dbo.Transaktionen_Archiv

WHERE Transaktions_Dat >= '2022-01-01' AND Transaktions_Dat < '2023-01-01';

-- Erwartetes Ergebnis: alle 2022-Zeilen

-- SZENARIO: Vorbereitete Staging-Tabelle in Produktion einschalten

-- Typisch: Monatlicher Datenimport, ETL-Load, Batch-Verarbeitung

-- Schritt 1: Staging-Tabelle befuellen (kann stundenlang dauern — kein Lock!)

CREATE TABLE dbo.Transaktionen_Staging (

    TransaktionID    BIGINT NOT NULL,

    Transaktions_Dat DATE NOT NULL,

    Kunden_ID        INT NOT NULL,

    Betrag           MONEY NOT NULL,

    CONSTRAINT PK_Staging PRIMARY KEY CLUSTERED (Transaktions_Dat, TransaktionID)

    ON ps_transaktion_datum(Transaktions_Dat)

) ON ps_transaktion_datum(Transaktions_Dat);

-- CHECK Constraint fuer die Zielpartition (Maerz 2026 = Partition 5)

ALTER TABLE dbo.Transaktionen_Staging

    ADD CONSTRAINT CK_Staging_2026_03

    CHECK (Transaktions_Dat >= '2026-03-01'

       AND Transaktions_Dat < '2026-04-01');

-- Staging befuellen (ohne Produktions-Lock)

INSERT INTO dbo.Transaktionen_Staging

SELECT * FROM Quelltabelle WHERE Monat = '2026-03';

-- Indizes auf Staging aufbauen

CREATE INDEX IX_Staging_KundenID ON dbo.Transaktionen_Staging (Kunden_ID)

    ON ps_transaktion_datum(Transaktions_Dat);

-- Schritt 2: Leere Partition in Haupttabelle sicherstellen

-- (neue Partition muss leer sein fuer Switch-IN)

SELECT COUNT(*) FROM dbo.Transaktionen

WHERE Transaktions_Dat >= '2026-03-01' AND Transaktions_Dat < '2026-04-01';

-- Muss 0 sein!

-- Schritt 3: Switch-IN (Millisekunden!)

ALTER TABLE dbo.Transaktionen_Staging

    SWITCH PARTITION 5

    TO dbo.Transaktionen PARTITION 5;

-- Ergebnis: Maerz-Daten sofort in Produktionstabelle verfuegbar

-- Staging-Tabelle Partition 5 ist danach leer

-- Automatisierter Rolling-Window-Job (monatlich via SQL Agent)

-- Fuegt neue Partition hinzu, archiviert aelteste Partition

DECLARE @NeueBoundary DATE = DATEADD(MONTH, 1,

    CAST(YEAR(GETDATE()) AS VARCHAR) + '-' +

    RIGHT('0' + CAST(MONTH(GETDATE()) AS VARCHAR), 2) + '-01');

DECLARE @AltePartition INT;

-- Schritt 1: Naechste Partition hinzufuegen (Split der letzten Partition)

-- Naechste Filegroup fuer neue Partition als NEXT USED setzen

ALTER PARTITION SCHEME ps_transaktion_datum

    NEXT USED fg_aktiv;

ALTER PARTITION FUNCTION pf_transaktion_datum()

    SPLIT RANGE (@NeueBoundary);

-- Schritt 2: Aelteste Partition ermitteln und ausschalten

SELECT @AltePartition = MIN($PARTITION.pf_transaktion_datum(Transaktions_Dat))

FROM dbo.Transaktionen;

-- Archivtabelle muss vorbereitet sein

ALTER TABLE dbo.Transaktionen

    SWITCH PARTITION @AltePartition

    TO dbo.Transaktionen_Archiv PARTITION @AltePartition;

-- Leere Partition mergen (optional: Boundary entfernen)

-- ALTER PARTITION FUNCTION pf_transaktion_datum() MERGE RANGE ('2021-01-01');

-- BEISPIEL: Abfrage die Partition Elimination ausloesen sollte

SELECT SUM(Betrag), COUNT(*)

FROM dbo.Transaktionen

WHERE Transaktions_Dat >= '2025-01-01'

  AND Transaktions_Dat < '2026-01-01';

-- Execution Plan pruefen: Partition Access Range

-- Im Plan: "Partitions Accessed: 5" statt "Partitions Accessed: 1 TO 5"

-- Das bedeutet: nur Partition 5 wird gelesen!

-- WICHTIG: Elimination funktioniert nur wenn:

-- 1. Filter direkt auf der Partitionsspalte liegt

-- 2. Werte zur Kompilierzeit bekannt sind (Literals oder Parameter)

-- 3. KEINE Funktionen auf der Partitionsspalte angewendet werden!

-- FALSCH: Elimination NICHT aktiv (Funktion auf Partitionsspalte)

WHERE YEAR(Transaktions_Dat) = 2025        -- SQL muss alle Partitionen pruefen!

WHERE CONVERT(VARCHAR, Transaktions_Dat, 104) = '01.01.2025'  -- kein Elimination!

-- RICHTIG: Elimination aktiv (direkte Vergleiche)

WHERE Transaktions_Dat >= '2025-01-01'

  AND Transaktions_Dat < '2026-01-01'

-- RICHTIG mit Parametern:

DECLARE @Von DATE = '2025-01-01';

DECLARE @Bis DATE = '2026-01-01';

SELECT * FROM dbo.Transaktionen

WHERE Transaktions_Dat >= @Von AND Transaktions_Dat < @Bis;

-- Elimination aktiv wenn Parameter beim Kompilieren bekannt sind (OPTION RECOMPILE wenn noetig)

-- Welche Partitionen werden fuer eine Abfrage zugegriffen?

SELECT

    p.partition_number AS Partition_Nr,

    prv_left.value AS Grenze_Von,

    prv_right.value AS Grenze_Bis,

    p.rows AS Zeilen,

    ROUND(SUM(a.total_pages) * 8.0 / 1024, 2) AS Groesse_MB

FROM sys.partitions p

JOIN sys.tables t ON p.object_id = t.object_id

JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id

JOIN sys.allocation_units a ON p.partition_id = a.container_id

JOIN sys.partition_functions pf ON pf.name = 'pf_transaktion_datum'

JOIN sys.partition_range_values prv_right

    ON prv_right.function_id = pf.function_id

    AND prv_right.boundary_id = p.partition_number

LEFT JOIN sys.partition_range_values prv_left

    ON prv_left.function_id = pf.function_id

    AND prv_left.boundary_id = p.partition_number - 1

WHERE t.name = 'Transaktionen'

  AND i.type = 1   -- Clustered Index

GROUP BY p.partition_number, prv_left.value, prv_right.value, p.rows

ORDER BY p.partition_number;

-- Partition-Zugriff fuer eine spezifische Abfrage pruefen:

-- Execution Plan (XML) → PartitionRange Element zeigt Partitions Accessed

-- Alternativ: SET STATISTICS IO ON → zeigt gelesene Pages je Partition

WHERE YEAR(Datum) = 2025

NEIN

WHERE Datum >= '2025-01-01' AND Datum < '2026-01-01'

WHERE CONVERT(varchar, Datum) = '...'

NEIN

Direkte Datumsvergleiche verwenden

Partitionsspalte in JOIN-Bedingung

Bedingt

Filter explizit in WHERE-Klausel hinzufuegen

LIKE auf Partitionsspalte

NEIN

Datum-Partitionen nicht mit LIKE filtern

Parameter DECLARE ohne Wert

Bedingt

OPTION (RECOMPILE) erzwingt Neukompilierung

Dynamisches SQL mit EXEC

NEIN

sp_executesql mit Parametern verwenden

-- Index Rebuild fuer eine einzelne Partition (kein Full-Table-Lock!)

ALTER INDEX PK_Transaktionen ON dbo.Transaktionen

    REBUILD PARTITION = 5           -- Nur aktuelle Partition

    WITH (ONLINE = ON,              -- Online = kein Lese-Lock

          MAXDOP = 4,               -- Max 4 parallele Threads

          FILLFACTOR = 90);

-- Alle Partitionen nacheinander rebuilden (kontrolliert)

DECLARE @PartNr INT = 1;

DECLARE @MaxPart INT;

SELECT @MaxPart = MAX(partition_number)

FROM sys.partitions

WHERE object_id = OBJECT_ID('dbo.Transaktionen') AND index_id = 1;

WHILE @PartNr <= @MaxPart

BEGIN

    IF (SELECT avg_fragmentation_in_percent

        FROM sys.dm_db_index_physical_stats(

            DB_ID(), OBJECT_ID('dbo.Transaktionen'), 1,

            @PartNr, 'LIMITED')) > 30

    BEGIN

        ALTER INDEX PK_Transaktionen ON dbo.Transaktionen

            REBUILD PARTITION = @PartNr WITH (ONLINE = ON, MAXDOP = 4);

    END

    SET @PartNr = @PartNr + 1;

END;

-- Statistik-Update pro Partition

UPDATE STATISTICS dbo.Transaktionen

    WITH RESAMPLE ON PARTITIONS (5);  -- Nur Partition 5 aktualisieren

-- Neue Boundary hinzufuegen (naechster Monat)

-- ACHTUNG: Split teilt eine bestehende Partition in zwei!

-- Die aktuelle "letzte" Partition wird aufgeteilt.

-- Schritt 1: NEXT USED Filegroup setzen

ALTER PARTITION SCHEME ps_transaktion_datum

    NEXT USED fg_aktiv;   -- oder neue Filegroup fuer neue Periode

-- Schritt 2: Split ausfuehren

ALTER PARTITION FUNCTION pf_transaktion_datum()

    SPLIT RANGE ('2026-04-01');  -- April 2026 als neue Grenze

-- WICHTIG: Split auf gefuellte Partition erzeugt Datenbewegung!

-- Alle Daten der gesplitteten Partition werden neu verteilt.

-- → Immer in der LEEREN "Zukunfts-Partition" splitten!

-- Faustregel: Rolling-Window: Split immer eine Periode voraus planen.

-- Pruefen ob Split erfolgreich:

SELECT boundary_id, value AS Grenze

FROM sys.partition_range_values

WHERE function_id = (

    SELECT function_id FROM sys.partition_functions

    WHERE name = 'pf_transaktion_datum')

ORDER BY boundary_id;

-- Zwei Partitionen zusammenfuehren (Boundary entfernen)

-- Typisch: nach Archivierung der aeltesten Partition

-- Schritt 1: Sicherstellen dass beide Partitionen um die Grenze LEER sind

-- (oder Merge verursacht Datenbewegung — kostspielig!)

SELECT $PARTITION.pf_transaktion_datum(Transaktions_Dat) AS Partition_Nr,

       COUNT(*) AS Zeilen

FROM dbo.Transaktionen

WHERE Transaktions_Dat < '2022-01-01'  -- Partitionen rund um die Grenze

GROUP BY $PARTITION.pf_transaktion_datum(Transaktions_Dat);

-- Sollte 0 Zeilen zeigen fuer betroffene Partitionen

-- Schritt 2: Merge ausfuehren

ALTER PARTITION FUNCTION pf_transaktion_datum()

    MERGE RANGE ('2021-01-01');  -- Grenzwert entfernen

-- Ergebnis: Eine Partition weniger

-- Daten in den gemergten Partitionen bleiben erhalten (landen in der verbliebenen)

-- Bei leeren Partitionen: Millisekunden — bei gefuellten: kann lange dauern!

EMPFOHLENER WARTUNGSPLAN:

TAEGLICH (SQL Agent Job, nachts):

  → Statistik-Update: nur aktuelle Partition (letzte 1-2 Perioden)

  → DLQ / Alert-Pruefung: Partition-Groessen-Monitoring (Kapitel 9)

WOECHENTLICH:

  → Index-Reorganize oder -Rebuild aktuelle Partitionen (ONLINE = ON)

  → Pruefung: naechste Periode-Partition vorhanden? (Rolling Window)

MONATLICH:

  → Neue Partition per SPLIT hinzufuegen (ein Monat voraus)

  → Aelteste Partition per SWITCH archivieren

  → Index-Rebuild aelterer Partitionen pruefen (Fragmentierung > 30 %)

  → Partition-Groessen-Report fuer Kapazitaetsplanung

JAEHRLICH:

  → Jahres-Partitionsstruktur pruefen und ggf. anpassen

  → Archiv-Partitionen auf sekundaere Filegroup / Datentraeger auslagern

  → Partitionierungsstrategie reviewen: noch optimal fuer aktuelles Datenvolumen?

-- Vollstaendiger Partitions-Status-Report

SELECT

    p.partition_number AS [Partition],

    -- Grenzwerte

    ISNULL(CAST(prv_left.value AS VARCHAR), '< MIN') AS [Von],

    ISNULL(CAST(prv_right.value AS VARCHAR), '>= MAX') AS [Bis],

    -- Daten

    p.rows AS [Zeilen],

    ROUND(SUM(a.total_pages) * 8.0 / 1024, 2) AS [Groesse_MB],

    ROUND(SUM(a.used_pages) * 8.0 / 1024, 2) AS [Genutzt_MB],

    -- Filegroup

    fg.name AS [Filegroup],

    -- Bewertung

    CASE

        WHEN p.rows = 0 THEN 'LEER'

        WHEN SUM(a.total_pages) * 8.0 / 1024 > 50000 THEN 'GROSS (> 50 GB)'

        WHEN SUM(a.total_pages) * 8.0 / 1024 > 10000 THEN 'Mittel (> 10 GB)'

        ELSE 'Normal'

    END AS [Status]

FROM sys.partitions p

JOIN sys.tables t ON p.object_id = t.object_id

JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id

JOIN sys.allocation_units a ON p.partition_id = a.container_id

JOIN sys.filegroups fg ON a.data_space_id = fg.data_space_id

JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id

JOIN sys.partition_functions pf ON pf.function_id = ps.function_id

JOIN sys.partition_range_values prv_right

    ON prv_right.function_id = pf.function_id

    AND prv_right.boundary_id = p.partition_number

LEFT JOIN sys.partition_range_values prv_left

    ON prv_left.function_id = pf.function_id

    AND prv_left.boundary_id = p.partition_number - 1

WHERE t.name = 'Transaktionen' AND i.type = 1

GROUP BY p.partition_number, prv_left.value, prv_right.value,

         p.rows, fg.name

ORDER BY p.partition_number;

-- Partition Skew: Ungleichmaessige Verteilung erkennen

-- Ideal: alle Partitionen aehnlich gross

-- Problem: eine Partition viel groesser als andere = Skew

WITH PartStats AS (

    SELECT

        p.partition_number,

        p.rows,

        SUM(a.total_pages) * 8.0 / 1024 AS Groesse_MB

    FROM sys.partitions p

    JOIN sys.tables t ON p.object_id = t.object_id

    JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id

    JOIN sys.allocation_units a ON p.partition_id = a.container_id

    WHERE t.name = 'Transaktionen' AND i.type = 1

    GROUP BY p.partition_number, p.rows

),

Stats AS (

    SELECT

        AVG(Groesse_MB) AS Avg_MB,

        MAX(Groesse_MB) AS Max_MB,

        MIN(Groesse_MB) AS Min_MB,

        STDEV(Groesse_MB) AS Stddev_MB

    FROM PartStats WHERE rows > 0

)

SELECT

    ps.partition_number,

    ps.rows,

    ROUND(ps.Groesse_MB, 1) AS Groesse_MB,

    ROUND(ps.Groesse_MB / NULLIF(s.Avg_MB, 0), 2) AS Verhaeltnis_zum_Avg,

    CASE

        WHEN ps.Groesse_MB > s.Avg_MB * 3 THEN 'STARKER SKEW — pruefen!'

        WHEN ps.Groesse_MB > s.Avg_MB * 2 THEN 'Moderater Skew'

        WHEN ps.rows = 0                  THEN 'Leer'

        ELSE 'Normal'

    END AS Skew_Bewertung

FROM PartStats ps, Stats s

ORDER BY ps.Groesse_MB DESC;

-- Fragmentierung je Partition ermitteln (nur fuer aktive Partitionen)

SELECT

    p.partition_number AS Partition_Nr,

    i.name AS Index_Name,

    ROUND(ps.avg_fragmentation_in_percent, 1) AS Fragmentierung_Pct,

    ps.page_count AS Seiten,

    CASE

        WHEN ps.avg_fragmentation_in_percent > 30 THEN 'REBUILD empfohlen'

        WHEN ps.avg_fragmentation_in_percent > 10 THEN 'REORGANIZE empfohlen'

        ELSE 'OK'

    END AS Empfehlung

FROM sys.dm_db_index_physical_stats(

    DB_ID(), OBJECT_ID('dbo.Transaktionen'), NULL, NULL, 'LIMITED') ps

JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id

JOIN sys.partitions p ON ps.object_id = p.object_id

    AND ps.index_id = p.index_id AND ps.partition_number = p.partition_number

WHERE ps.page_count > 100  -- Nur relevante Partitionen

  AND ps.avg_fragmentation_in_percent > 10

ORDER BY ps.avg_fragmentation_in_percent DESC;

-- Ueberblick: Welche Tabellen sind partitioniert? Wie viele Partitionen?

SELECT

    t.name AS Tabelle,

    pf.name AS Partition_Function,

    pf.type_desc AS Typ,

    pf.fanout AS Anzahl_Partitionen,

    SUM(p.rows) AS Gesamtzeilen,

    ROUND(SUM(a.total_pages) * 8.0 / 1048576.0, 2) AS Gesamtgroesse_GB,

    MIN(p.rows) AS Min_Zeilen_Partition,

    MAX(p.rows) AS Max_Zeilen_Partition

FROM sys.tables t

JOIN sys.indexes i ON t.object_id = i.object_id AND i.type <= 1

JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id

JOIN sys.partition_functions pf ON ps.function_id = pf.function_id

JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id

JOIN sys.allocation_units a ON p.partition_id = a.container_id

GROUP BY t.name, pf.name, pf.type_desc, pf.fanout

ORDER BY Gesamtgroesse_GB DESC;

VOR DEM START

Backup der Datenbank anfertigen und Restore testen. Enterprise Edition oder Developer Edition sicherstellen (Partitionierung nicht in Standard Edition). Ausreichend freien Speicherplatz pruefen (mind. 1,5x Tabellengroesse fuer die Migration). Maintenance Window mit dem Fachbereich abstimmen.

ERGEBNIS NACH 30 TAGEN

Nach 30 Tagen haben Sie: eine vollstaendig partitionierte Tabelle mit nachgewiesener Partition Elimination, automatisches Rolling Window das neue Partitionen hinzufuegt und alte archiviert, Index-Rebuild in Minuten statt Stunden durch partition-spezifische Wartung, ein Monitoring-Dashboard das Partition-Groessen und Fragmentierung im Blick behaelt und eine vollstaendige Dokumentation inklusive Rollback-Plan.

Partitionierungs-Konzept & Implementierung

Analyse Ihrer grossen Tabellen, Erstellung des Partitionierungskonzepts und begleitete Implementierung in Produktion — inklusive Rolling-Window-Automatisierung.

SQL Server Performance-Tuning

Tiefgehende Analyse mit Extended Events und Query Store, Optimierung von Blocking, Deadlocks und langsamen Abfragen in partitionierten und nicht-partitionierten Umgebungen.

ERP-Datenbankoptimierung

Spezialisiertes Tuning fuer ERP-Datenbanken — Partitionierung grosser Bewegungsdaten-Tabellen, Archivierungsstrategien und Wartungsplan-Design.

DB Health Check mit Partitionierungs-Review

Vollstaendiger Audit inkl. Pruefung aller partitionierten Tabellen auf Skew, Alignment-Fehler und suboptimale Konfiguration.

Interim IT-Management

Uebernahme der IT-Steuerung auf Zeit — Budgetplanung, Dienstleister-Management, strategische IT-Ausrichtung.