Share via

Columnstore Indizes in SQL Server “Denali”

  • Article

Eine der wichtigern Neuerungen im Data Warehouse Bereich die mit SQL Server “Denali” kommen wird sind Columnstore Indizes. Diese Indizes können Data Warehouse Abfragen über große Datenmengen sehr stark beschleunigen, oft um den Faktor 10 oder mehr.

In traditionellen Indizes, auch in Clustered Indizes und Tabellen ohne Index (Heaps) sind die Daten zeilenweise in einer Baumstruktur gespeichert. In den Blättern dieses Baumes stehen also Zeile für Zeile alle Spalten, die im Index enthalten sind. Das geschieht normalerweise unkomprimiert, bei der Verwendung von Datenkompression auch pro Datenseite komprimiert.

Soll nun, wie in Data Warehouses üblich, eine große Aggregation gebildet werden wie diese:

SELECT SUM(umsatz_vk_brutto)
FROM Artikelbewegung INNER JOIN Artikel
ON Artikelbewegung.Art_nr = Artikel.Art_Nr
WHERE Artikel.Produktgruppe = 'AfG'

so muss der Index der Faktentabelle Artikelbewegung(sofern er alle benötigten Felder, hier also Art_Nr, VK_Brutto enthält, sonst die ganze Tabelle) über seine Baumstruktur durchsucht und dann zeilenweise gelesen werden um die benötigten Zeilen auszufiltern. Enthält der Index oder die Tabelle mehr Spalten als für die Abfrage benötigt werden so werden diese nicht benötigten Werte ebenfalls gelesen und dann ignoriert. Danach werden die VK_Brutto Werte aufsummiert.

Bei einem Columnstore Index werden die Daten in hochkomprimierter Form spaltenweise gespeichert. Das bedeutet, dass jede Spalte im Index einzeln gespeichert und komprimiert wird. Durch die in denormalisierten Data Warehouses typische hohe Ähnlichkeit der Werte in einer Spalte werden hier sehr hohe Komprimierungen erreicht. Das bedeutet, dass die von Disk zu lesenden Datenmengen deutlich geringer sind als bei traditionellen Indizes. Bei einer Abfrage werden dann auch nur die in der Abfrage enthaltenen Spalten von der Platte gelesen, was zusätzlich IO und Verarbeitungszeit einspart.

Die Columnstore Technologie basiert auf derselben Basis (Vertipaq) die derzeit auch schon in PowerPivot verwendet wird.

Schauen wir uns das Ergebnis der Verarbeitung mal für eine Abfrage an:

SELECT k.Jahr, f.Einzugsbereich, ar.Produktgruppe, SUM(a.Teile_Ges) Teile, SUM(a.umsatz_vk_brutto) Umsatz
FROM Artikelbewegung a INNER JOIN Kalender k ON a.Datum = k.Datum
INNER JOIN artikel ar ON a.Art_Nr = ar.Art_Nr
INNER JOIN Filialen f ON a.Filial_Nr = f.Filial_Nr
GROUP BY k.Jahr, f.Einzugsbereich, ar.Produktgruppe
ORDER BY k.Jahr, ar.Produktgruppe, f.Einzugsbereich

Ohne Column Store Index wird hier, da die ganze Tabelle aggregiert wird, ein Table Scan gemacht. CSTableScan

IO-Kosten:

Artikelbewegung-Tabelle. Scananzahl 9, logische Lesevorgänge 25898

CPU-Kosten:

SQL Server-Ausführungszeiten:
CPU-Zeit = 33413 ms, verstrichene Zeit = 5229 ms

Mit Column Store Index werden nur die benötigten Spalten in komprimierter Form gelesen, die Tabelle selbst wird nicht gelesen:

CSColstore

Schon am geringeren Anteil an den Gesamtkosten der Abfrage sieht man den Effekt des Columnstore. Und auch in Zahlen drückt sich das aus:

IO-Kosten:

Artikelbewegung-Tabelle. Scananzahl 8, logische Lesevorgänge 4520

CPU-Kosten:

SQL Server-Ausführungszeiten:
CPU-Zeit = 2103 ms, verstrichene Zeit = 902 ms

Die Abfrage ist also insgesamt um mehr als den Faktor 5 schneller geworden, hat nur rund 6% (!) der CPU Zeit verbraucht und das Lesen des Column Store hat nur 17% der IO-Vorgänge gebraucht. Das alles bei einer recht kleinen Faktentabelle (nur ca. 4 Millionen Zeilen), bei großeren Tabellen ist der Effekt noch deutlich größer.

Wichtig ist, dass sich Columnstore Indizes nur für Data Warehouse Szenarien eignen wo große Aggregationen, vor allem Summierungen über große Datenmengen gebildet werden.

Ein Columnstore Index macht die Tabelle, auf der er erstellt wurde Read Only. Daher gibt es nur zwei Möglichkeiten, Daten in der Basistabelle zu ändern

1.: Columnstore Index löschen, Daten anfügen, Columnstore Index neu erstellen

oder

2. Partitionsumschaltung: Tabelle gleicher Struktur erstellen, Daten laden, Columnstore Index auf neue Tabelle erstellen, neue Tabelle als Partition in vorhandene Tabelle hineinschalten

Die Syntax für die Erstellung von Columnstore Indizes ist fast dieselbe wie für traditionelle Nonclustered Indizes:

CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_Artikelbewegung_ColumnStore] ON [dbo].[Artikelbewegung]
(
[Datum],
[Filial_Nr],
[Art_Nr],
[Gebinde_Ges],
[Teile_Ges],
[Umsatz_VK_brutto],
[Umsatz_vk_netto]
)WITH (DROP_EXISTING = OFF) ON [PRIMARY]

und auch eine schöne Oberfläche gibt es dafür:CSEdit

Columnstore Indizes werden in der kommenden SQL Server “Denali” CTP enthalten sein. Anmeldungen dafür unter https://www.sqlserverlaunch.com

Gruß,
Steffen

Comments

  • Anonymous
    January 01, 2003
    Aussagen zur Lizenzierung und zur SKU Matrix gibt es noch nicht, ich kenne die Planung auch nicht. Aber da das Ganze ganz klar auf große Data Warehouses abzielt...

  • Anonymous
    June 29, 2011
    Hallo Steffen, meine letzte Info war, dass dies Feature nur in den Premium-Editionen enthalten sein würde. Wie ist hier der aktuelle Stand: Wird das in den Standard- bzw. Enterprise-Editionen enthalten sein? Viele Grüße Thomas