SQL Server 2012 Columnstore Indexes – Spaltenorientierter Index

Bisher war man der Meinung, dass in einem bestehenden Datenbanksystem ein spaltenorientierter Index eigentlich nachträglich kaum zu machen wäre. Mit SQL Server 11 bringt Microsoft allerdings eine neue Indextechnologie mit, die Abfragen in einem Datawharehouse deutlich beschleunigen können. Der Beschleunigungsfaktor kann hier deutlich über 10 liegen.

Wie fiunktioniert der Columnstore Index?

Das Phänomen ist uns schon seit PowerPivot bekannt, dass in Excel 2010 auch über 100 Mio Zeilen eine Sekunden Geschichte ist. Auch PowerPivot verwendet bereist spaltenorientierung.

Bisher wurden die Daten eines Index zeilenorientiert abgelegt. Das hieß, dass in den jeweiligen Seiten des Index Zeile für Zeile, die Daten des Index abgelegt wurden. je mehr Spalten der Index aufwies, umso größer wurde auch der dementsprechende Index.

Je nach Abfrage musste SQL Server die Seiten und vor allem die Zeilen des Index durchgehen, um die Daten herauszuholen. Je mehr Daten, desto mehr Seiten. Je mehr Seiten desto mehr IO Zugriffe. Was aber wenn Aggregate auf verschiedene Spalten gebildet werden sollen?  Hier muss SQL Server alle entprechenden Seiten durchgehen – inklusive den Sapltenwerten, die man nicht benötigt!

 

Gruppierter (zeilenorientierter) Index

Angenommen, wir haben eine Tabelle mit 30 Spalten, so müssten theoretisch bei den entsprechenden Indexseiten nicht nur die Werte durchgekaut werden, di man tatsächlich in der Ausgabe sehen will, sondern alle andere Werte des entsprechenden Zeilen ebenso.

 

Columnstore Indexes – spaltenorientierte Indizes

 

Was ist nun anders bei Columnstore Indexes. Hier werden die die Index angegebenen Spalten separat in “ihre” Seiten gespeichert. Der Index ist so organisiert, dass die Spalten einen eigenen Bereich im Index bekommen. Sofern eine Abfrage nun spezielle Werte aus einer Spalte benötigt, so können aus dem Index heraus gezielt die Spaltenseiten verwendet werden. Das bedeutet eine deutlich geringere Abfrageleistung, da Werte aus Spalten, die nicht benötigt auch nicht gescannt werden müssen. Fazit: Weniger Werte, weniger IO zugriffe, schnellere Abfragen.

Der Index, der bisher entweder in einem Heap oder per B-Tree druchsucht wurde, kann nun gezielt einzelne Spaltenwerte herausholen.

 

Gerade bei Datawharehouse lassen sich Abfragen mit Aggregaten enorm beschleunigen. Laut einem Whitepaper hatte ein Datawharehouse mit ca 1 TB Daten und 256 GB RAM einen Beschleunigungsfaktor von:

CPU 16-fach und Dauer 455-fach !!!

 

Wie läßt sich nun so ein Columnstore Index anlegen?

Create Columnstore Index ColStoreInd on dbo.dwtab
   (
    Sp1,
    Sp2,
    Sp3,
    Sp4,
   ....
   )

Easy, oder?!

Ob nun Reporting Service, Analysis Service, T-SQL auf die Daten zugreifen, spielt nun keine Rolle. Wer auf diese Daten im SQL Server 11 zugreift profitiert von dem columnstore Index.

Für wen lohnt sichs?

• Optimal für Star Joins
• Weniger für Fact-to-fact Joins und Abfragen über viele Spalten

Sofern der SQL Optimierer erkennt, dass columnstore Index schlecht geeignet ist, verwendet er wieder die guten alten B-Tree Indizes

 

Zu sehen ab SQL Server 11 CTP2