🗜️ SQL Server Tabellenkomprimierung
ROW‑ und PAGE‑Komprimierung, Indizes, Speicherplatz sparen, Performance steigern – Der vollständige Leitfaden
📌 Warum Tabellen komprimieren?
ÜBERBLICK
Die Datenkomprimierung in SQL Server (eingeführt mit SQL Server 2008) reduziert die Größe von Tabellen und Indizes auf der Festplatte. Das bringt zwei zentrale Vorteile:
- ✅ Weniger Speicherplatz – Sie sparen teuren Storage, besonders in großen Data Warehouses.
- ✅ Weniger I/O – Mehr Daten passen in den Buffer Pool, weniger Lesevorgänge von der Platte/SSD.
💡 Wichtige Voraussetzung: Komprimierung ist in der Enterprise Edition enthalten, ab SQL Server 2016 SP1 auch in der Standard Edition (bei Tabellen und Indizes, jedoch nicht bei Columnstore). Prüfen Sie Ihre Lizenz.
🔍 Arten der Komprimierung
ROW vs PAGE vs COLUMNSTORE
SQL Server bietet zwei Hauptarten zeilenbasierter Komprimierung (ROW, PAGE) sowie die deutlich stärkere Columnstore-Komprimierung. In diesem Artikel konzentrieren wir uns auf ROW und PAGE für herkömmliche Heap‑/B‑Baum‑Tabellen.
| Typ | Beschreibung | Ersparnis (typisch) | Einsatz |
|---|---|---|---|
| ROW | Kompression fester Datentypen (z. B. INT, CHAR, FLOAT) in variable Längen, Metadatenoptimierung. Keine Wiederholungsmuster über mehrere Zeilen. | ca. 20–40 % | OLTP, gemischte Workloads |
| PAGE | Wie ROW + zusätzlich Prefix‑ und Dictionary‑Komprimierung auf Seitene bene (Wiederholung von Werten). | ca. 40–60 % (oft mehr) | Data Warehouse, große Tabellen mit Redundanzen |
| COLUMNSTORE | Spaltenbasierte Vektor-Komprimierung, extrem hohe Dichte. | bis zu 90 % | Analytische Workloads (nicht In‑Scope dieses Artikels) |
-- Aktivierung der ROW‑Komprimierung
ALTER TABLE dbo.GrosseTabelle
REBUILD WITH (DATA_COMPRESSION = ROW);
-- PAGE‑Komprimierung (stärker)
ALTER TABLE dbo.GrosseTabelle
REBUILD WITH (DATA_COMPRESSION = PAGE);
📌 Hinweis: PAGE enthält implizit die ROW-Komprimierung – Sie müssen nicht beide nacheinander aktivieren.
📇 Komprimierung von Indizes
NONCLUSTERED & CLUSTERED
Indizes können unabhängig von der Tabelle komprimiert werden. Das ist wichtig, denn bei häufigen Updates kann ein komprimierter Index mehr CPU benötigen. Oft wählt man für die Tabelle PAGE und für Non‑Clustered Indizes ROW – oder lässt die Indexkomprimierung ganz aus.
-- Clustered Index mit PAGE komprimieren
ALTER INDEX PK_GrosseTabelle ON dbo.GrosseTabelle
REBUILD WITH (DATA_COMPRESSION = PAGE);
-- Non‑Clustered Index separat konfigurieren
ALTER INDEX IX_Suche ON dbo.GrosseTabelle
REBUILD WITH (DATA_COMPRESSION = ROW);
-- Alle Indizes einer Tabelle neu aufbauen mit PAGE (sinnvoll bei Data Warehouse)
ALTER TABLE dbo.GrosseTabelle
REBUILD WITH (DATA_COMPRESSION = PAGE);
💡 Best Practice: Testen Sie zuerst mit ROW, dann mit PAGE. Überwachen Sie die CPU-Auslastung und die I/O‑Reduktion. In Data Warehouses ist PAGE fast immer ein Gewinn.
🔎 Vorhandene Komprimierung ermitteln
ANALYSE
Bevor Sie eine Tabelle komprimieren, sollten Sie wissen, welcher Komprimierungstyp bereits aktiv ist – und wie viel Speicher Sie sparen könnten.
-- Komprimierungsstatus von Tabellen und Indizes abfragen
SELECT
SCHEMA_NAME(t.schema_id) AS SchemaName,
t.name AS TableName,
i.name AS IndexName,
p.data_compression_desc AS CompressionType
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE t.name = 'IhreTabelle';
-- Geschätzte Speicherersparnis durch Komprimierung (ungefähr)
EXEC sp_estimate_data_compression_savings
@schema_name = 'dbo',
@object_name = 'GrosseTabelle',
@index_id = NULL,
@partition_number = NULL,
@data_compression = 'PAGE';
📊 Achtung:
sp_estimate_data_compression_savings erstellt eine temporäre Kopie eines Samples – das kann auf sehr großen Tabellen I/O und Speicherplatz kosten. Führen Sie es außerhalb der Spitzenlast durch.
🛠️ Komprimierung aktivieren – Online oder Offline?
IMPLEMENTIERUNG
Das Aktivieren der Komprimierung erfordert einen Index‑Rebuild (bei Heap‑Tabellen ebenfalls). Sie können diesen online durchführen – vorausgesetzt, Ihre Edition unterstützt Online‑Rebuilds (Enterprise ab SQL Server 2012, Standard erst ab SQL Server 2022? – Achtung: In Standard ist Online‑Rebuild nicht für alle Vorgänge erlaubt. Prüfen Sie Ihre Version!)
-- Offline (Tabelle wird gesperrt, aber schneller)
ALTER TABLE dbo.GrosseTabelle
REBUILD WITH (DATA_COMPRESSION = PAGE, ONLINE = OFF);
-- Online (nur Enterprise Edition; minimale Sperrung am Ende)
ALTER TABLE dbo.GrosseTabelle
REBUILD WITH (DATA_COMPRESSION = PAGE, ONLINE = ON);
-- Für Non‑Clustered Index einzeln (online möglich)
ALTER INDEX IX_Suche ON dbo.GrosseTabelle
REBUILD WITH (DATA_COMPRESSION = PAGE, ONLINE = ON);
💡 Planung: Bei Offline‑Rebuild ist die Tabelle während des Vorgangs nicht verfügbar. Planen Sie ein Wartungsfenster. Bei sehr großen Tabellen kann der Rebuild Stunden dauern.
📦 Standardkomprimierung für zukünftige Tabellen
AUTOMATISIERUNG
Sie können für eine Dateigruppe oder direkt für eine Tabelle beim Erstellen die Komprimierung vorgeben.
-- Tabelle direkt mit PAGE-Komprimierung anlegen
CREATE TABLE dbo.NeueTabelle (
Id INT IDENTITY PRIMARY KEY,
TextSpalte NVARCHAR(500)
) WITH (DATA_COMPRESSION = PAGE);
-- Standardkomprimierung für eine Partition festlegen
ALTER TABLE dbo.PartitionierteTabelle
REBUILD PARTITION = 2 WITH (DATA_COMPRESSION = PAGE);
📌 Tipp: Für Partitionierung können Sie unterschiedliche Komprimierungen pro Partition verwenden – z. B. alte Daten mit PAGE, aktuelle mit ROW oder gar keine.
📊 Effizienz prüfen – Hat sich die Komprimierung gelohnt?
MONITORING
Nach der Aktivierung sollten Sie den Speicherplatzverbrauch und die I/O‑Zahlen vergleichen. Nutzen Sie
sp_spaceused oder DMVs.
-- Aktuellen Speicherplatzverbrauch anzeigen
EXEC sp_spaceused 'dbo.GrosseTabelle';
-- Detaillierte Seitengrößen pro Index
SELECT
i.name AS IndexName,
SUM(p.used_page_count) * 8 / 1024 AS SizeMB
FROM sys.dm_db_partition_stats p
JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE p.object_id = OBJECT_ID('dbo.GrosseTabelle')
GROUP BY i.name;
-- I/O‑Reduktion über Zeit messen (mit Query Store)
SELECT
qs.avg_logical_io_reads,
qs.avg_logical_io_writes,
qt.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qt.text LIKE '%GrosseTabelle%';
💡 Interpretation: Wenn die Größe um 50 % gesunken ist und die CPU nur moderat gestiegen ist, war die Komprimierung erfolgreich. Achten Sie auf vermehrte Page Latch‑Wartezeiten – das kann auf zu viel CPU durch Komprimierung hindeuten.
⚖️ Vor- und Nachteile pro Workload
ENTSCHEIDUNGSHILFE
| Workload | ROW‑Komprimierung | PAGE‑Komprimierung |
|---|---|---|
| OLTP (viele Schreibzugriffe) | oft sinnvoll, geringer CPU‑Aufpreis | kritisch – hohe CPU durch Dekomprimierung bei Updates |
| Data Warehouse / Reporting | gut, aber PAGE bringt mehr | sehr empfehlenswert – hohe I/O‑Reduktion |
| Archivtabellen (read‑only) | ja | ideal, maximale Kompression |
| Große LOB‑Spalten | nur bei VARCHAR(MAX)/NVARCHAR(MAX) begrenzt | PAGE komprimiert LOB‑Daten nicht (Ausnahme: Row Overflow) |
🚨 Warnung: In einer hochfrequenten OLTP-Umgebung mit vielen Updates kann PAGE die CPU auf ein Vielfaches steigern. Testen Sie unbedingt vor Produktivsetzung.
↩️ Komprimierung deaktivieren
REVERSIBEL
Wenn die Komprimierung unerwünschte Nebenwirkungen hat, können Sie sie jederzeit rückgängig machen.
-- Komprimierung für Tabelle entfernen (wieder auf NONE setzen)
ALTER TABLE dbo.GrosseTabelle
REBUILD WITH (DATA_COMPRESSION = NONE);
-- Nur für einen bestimmten Index
ALTER INDEX PK_GrosseTabelle ON dbo.GrosseTabelle
REBUILD WITH (DATA_COMPRESSION = NONE);
💡 Wichtig: Auch das Deaktivieren erfordert einen Rebuild. Planen Sie genügend Zeit und Log‑Speicherplatz ein.
✅ Checkliste – So starten Sie mit der Komprimierung
PRAXIS
- Testen Sie zuerst auf einer Testumgebung oder einer Kopie der Produktion.
- Messen Sie die aktuelle Größe und I/O (z. B. mit
sp_spaceusedundsys.dm_db_index_physical_stats). - Nutzen Sie
sp_estimate_data_compression_savingsals grobe Orientierung. - Beginnen Sie mit ROW, testen Sie dann PAGE. Bei Data Warehouse sofort PAGE.
- Planen Sie ein Wartungsfenster für den Rebuild – oder setzen Sie Online‑Rebuild ein (nur Enterprise).
- Überwachen Sie nach der Aktivierung CPU‑ und I/O‑Trends (Performance Monitor, Query Store).
- Dokumentieren Sie die Komprimierungseinstellungen für jede Tabelle/Index.
- Berücksichtigen Sie die Komprimierung bei Backup‑Strategien – komprimierte Tabellen führen zu kleineren Backups und schnelleren Wiederherstellungen.
⚠️ Häufige Irrtümer über Tabellenkomprimierung
AUFRÄUMEN
- ❌ „Komprimierung ist immer gut für die Performance.“ → Falsch. Bei schreibintensiven Systemen kann die CPU massiv steigen.
- ❌ „ROW und PAGE gleichzeitig aktivieren bringt mehr.“ → Nein, PAGE enthält ROW bereits.
- ❌ „Eine komprimierte Tabelle kann nicht partitioniert werden.“ → Doch, Partitionierung und Komprimierung sind unabhängig.
- ❌ „Nach der Komprimierung verliere ich Daten.“ → Absolut nicht, es ist eine verlustfreie Komprimierung.
- ❌ „Komprimierte Backups sind immer kleiner.“ → Stimmt, aber SQL Server nutzt die native Backup‑Komprimierung zusätzlich – beides zusammen bringt kaum Vorteile. Deaktivieren Sie am besten die Backup‑Komprimierung, wenn Ihre Tabellen schon komprimiert sind.
🚀 Fazit: Tabellenkomprimierung ist ein mächtiges Werkzeug, aber kein Selbstläufer. Analysieren Sie Ihre Workload, testen Sie gründlich, und setzen Sie die Komprimierung gezielt ein – dann werden Sie mit weniger Speicherplatz und oft auch schnelleren Abfragen belohnt.