Columnstore Indizes next Genration


Andreas Rauch

Version 2.0 der Columnstore Indzes. Microsoft gab in der SQL Server 2014 CTP1 den Blick frei auf die kommende Genration der spaltenorientierten Indizes. Was ist zu erwarten und was bekommt man?

Das Prinzip der Columnstore Indizes ist klar. Zeilen statt Spalten. Wer frägt denn schon mit Select * Tabellen ab? Hoffentlich keiner! Columnstore Indzies widmen sich vor allem den OLAP Systemen, Abfragen über sehr große Datenmengen stark zu beschleunigen. Eine Optimierung kann grundsätzlich durch 3 Hardwarekomponenten erreicht werden. Entlastung des Datenträger, Entlastung der CPU und möglichst viel Daten im Arbeitsspeicher. Leider ist das Optimierungspotential seitens der Hardware etwas eingeschränkt. Die CPU wird kaum noch schneller, Arbeitspeicher dagegen immer günstiger. Ergo: CPU entlasten RAM mit Daten vollpumpen.

Genau dieses Ziel verfolgen Columnstore Indizes.

Zunächst ein Vergleich zwischen zeilenorientierten und spaltenorientierten Tabellen.

 

clip_image002_thumb2clip_image004_thumb1

 

In zeilenorientierten Tabellen müssen bei jeder Anweisung ganze Datensätze gelesen werden. Spaltenorientierte Tabellen werden lediglich die benötigten Spalten auslesen müssen.  Mit diesen Prinzip vor Augen untersuchen wir die Neuerungen in SQL Server 2014. 

 

Kompression

Neu in SQL Server 2014 ist, dass Column Store Indizes als Clustered Index gespeichert werden können. Folglich gibt es nicht mehr eine Tabelle mit zusätzlichen Index Daten, sondern die Tabelle selbst liegt in spaltenorientierte Form vor. DZusätzlich wurde ein weiterer Kompressionsalgorithmus  - Archival ColumnStore – beigefügt , mit dem die Datenseiten um ein vielfaches besser komprimiert werden können, als bei einer herkömmlichen Kompression. In einem Versuch konnte gegenüber der herkömmlichen Tabellenkompression, die den Faktor 5 erreichte, einen Kompressionsfaktor von 26festgestellt werden! (Abbildung 4)

 

clip_image009_thumb

 

Mit sp_Spaceused ergab sich ein Vorteil vom 26-fache gegenüber der herkömmlichen Tabellenkompression, die lediglich das 5-fache schaffte.

Zahlen sagen oft mehr als Worte: Die Tabelle mit dem Columnstore Index inkl. Der Archival Columnstore Compression ist auf der Disk statt 2 GB nur noch 76 MB groß! Nachdem Columnstore Indizes in Memory arbeiten, fällt auch hier eine deutlichen Entlastung des Arbeitsspeichers an, der natürlich durch Dekompressionsarbeit während der Abfragen seitens der CPU kompensiert werden muss.

Weiterer Vorteil: Columnstore Indizes sind DML-fähig, sprich aktualisierbar. Insert, update, delete, alter… alles möglich.

INSERT, UPDATE und DELETE

Der größte Wermutstropfen an SQL Server 2012 Column Store Indizes war die fehlende Unterstützung für DML Operationen. Gute Nachrichten! SQL Server 2014 Column Store Indizes sind aktualisierbar. Die Lösung dazu scheint jedoch zunächst sehr fraglich. Datenmanipulationen werden nur dann in den Column Store Index aufgenommen, wenn genügend Daten vorhanden sind. Sind es zu wenige, werden sogenannte Deltatabellen gefüllt, die wiederum in zeilenorientiert Form gespeichert werden. In diesen Deltatabellen landen nicht also nicht nur Datensätze aus Inserts, sondern auch aus Updates. Datensätze werden nicht gelöscht, sondern nur als gelöscht markiert und mittels eines Bitmapfilters aus Abfrageergebnissen entfernt. Ist eine genügend große Anzahl an Datenmengen aus Inserts und Updates vorhanden, werden die Änderungen in den Column Store übertragen, was der sogenannte Tuple Mover erledigt..

DELTASTORES

Für Inserts und Updates (Updates werden als Insert und Delete behandelt) werden sogenannte Delatstores verwendet, ein zeilenorientierter Speicher. Der Grund ist dafür sehr einleuchtend. Bevor ein effizienter Kompressionsalgorithmus gefunden werden kann, müssen genügend Datensätze vorhanden sein. SQL Server 2014 unterscheidet hier zwischen Trickle und Bulk Insert. Sofern schon eine große Menge an Daten eingefügt werden (Bulk: meist bei select into) können diese gleich komprimiert werden. Die Menge liegt bei knapp über 1 Mio Zeilen. Einzelne oder geringere Datenmengen wandern in den Deltastore.

Der Status der Deltastoregruppen ist entweder Open oder Closed. Daten werden nun solange in die Gruppen geschrieben, solange der Status auf Open steht. Ist eine Gruppe erst mal geschlossen, komprimiert der sogenannte Tuple Mover im Hintergrund die Daten, währenddessen sie noch weiterhin gelesen werden können. Ist der Tuple Mover mit seiner Arbeit fertig, werden neu erstellten und komprimierten Segment sichtbar gemacht und die im Deltastore unsichtbar. Sind laufenden Scans auf die Deltastore fertig, werden die Segmente auch dort komplett entfernt. Dieses Löschen der Deltastore ist sehr effizient und vergrößert das Transkationsprotokoll nicht.

Mit Hilfe einer Systemsicht kann und durch folgendes Script kann man dieses Verhalten sehr gut nachvollziehen:

--Einfügen von Datensätzen

insert into FactCS

select top 102000 * from dbo.factOnlineSales

GO 11

insert into FactCS

select top 1048577 * from dbo.factOnlineSales

go 2

--Zur Kontrolle: Aufruf der Systemsicht

SELECT total_rows, state_description, delta_store_hobt_id,

deleted_rows, size_in_bytes,

100*(total_rows - ISNULL(deleted_rows,0))/total_rows AS PercentFull

FROM sys.column_store_row_groups

clip_image014_thumb

 

Deletes dagegen sind zunächst nur Eintragungen in den Bitmap Filter, einem B-Tree, der pro ColumnStore nur einmal existiert. Nur komprimierte Segmente verwenden dieses „Lösch“-Bitmap. Im Prinzip ist ein Bitmap Filter sehr performant, wer jedoch viel löscht, wird bei Abfragen im hohen Maße den Bitmap Filter bemühen müssen. Der worst case, den gesamten Inhalt einer Tabelle zu löschen, führt in der Beispieltabelle zu 153099 Lesevorgängen benötigt über eine Sekunde.

(Table 'FactCS'. Scan count 6, logical reads 153099), obwohl eigentlich keine Daten mehr vorhanden sind!

Deutlich günstiger ist es, die Datensätze tatsächlich aus der Tabelle zu entfernen, das nur mit einem Rebuild des Index zu bewerkstelligen ist. (Tabelle 1) Der wiederum kann bei großen Datenmengen relativ lange dauern und zum anderen erzeugt dies CPU Lasten, und Locks. Allerdings werden nur Updates und Deletes geblockt, Lesen ist weiterhin erlaubt.

Leider gibt es keine Möglichkeit die Deltastore direkt zu kontrollieren.

ColumnStore vor Rebuild

ColumnStore nach Rebuild

CPU time = 1702 ms, elapsed time = 415 ms

CPU time = 516 ms, elapsed time = 271 ms

Für den Endanwender verhalten sich Clustered Column Store Indizes transparent.  

SELECT

Ein weiteres Manko des Column Store Index des SQL Server 2012 betraf die leider nicht gleichbleibende Abfragequalität. Nur im Batchprocessing erreichte man eine deutliche reduziertere CPU Auslastung. Leider unterstütze SQL Server 2012 lediglich Scan, Filter, Project, hash (inner) joins und hash aggregate.

Während der Ausführungsphase des Query werden mehrere Threads gebildet, die im Speicher die Hash Table aufbauen. Kein Thread behindert den anderen und jeder Thread kann nach seinen Batch Job auch sofort den nächsten Batch Job übernehmen bis der komplette Job fertig ist. Allerdings müssen dazu alle Daten in den Speicher passen, sonst fällt der Prozess in den Zeilenmodus zurück. Dieses Verfahren ist äußerst CPU schonend. Neu unter SQL Server 2014 ist, das sowohl row-by-row und Batchprocessing in einer Abfrage verwendet werden, dem sogenannten mixed-Mode.

SQL Server 2014 unterstützt nun alle Hash Join Varianten (Anti Semi Left usw.) , Union All und scalar Aggregate.

clip_image011_thumb2

Ausführungsplan im Batchmodus

 

 

 

clip_image013_thumb2

 

 

Batchmodus auch bei anderen JOIN Arten (hier LEFT JOIN)  

 

 

 

 

In der Praxis

Column Store Indizes gewinnen erst mit größeren Tabellen an Bedeutung bzw Performance. Je mehr Daten komprimiert werden, desto größer der Vorteil gegenüber herkömmlichen Tabellen. In einem Vergleich zwischen zwei identischen Tabellen, eine mit und die andere ohne CSIX, mit ca 12,5 Mio Zeilen benötigt die herkömmliche Tabelle das 14-fache an CPU, das 12 fache an Zeit und muss ca. eine 7-fache Menge an Seiten lesen!

Hier zum Vergleich die Abfrage auf ein die Tabelle Fact ohne Columnstore Index.

select Productkey , sum(TotalCost) from FACT

group by ProductKey

/* Table 'Fact'. Scan count 7, logical reads 44807*/

clip_image016_thumb1

 

Und nun die Abfrage auf die Tabelle FactFS mit ColumnStore Index.

select Productkey , sum(TotalCost) from FACTCS

group by ProductKey

/* Table 'FactCS'. Scan count 6, logical reads 7779 */

clip_image018_thumb1

Dauer und CPU Leitung zum Vergleich beim einem Column Store IX

Fazit

Mit Sicherheit hat hier Microsoft einen großen Wurf gelandet. Auch wenn man noch von der CTP1 spricht, so zeigen die Versuche eine gewaltige Steigerung der Performance. Vor allem in der Praxis wird der Column Store Index viele Freunde gewinnen, da es keine zusätzlich Engine erfordert, sondern integraler Bestandteil des SQL Servers ist und für die Anwender absolut transparent erscheint.

Vorsicht sollte allerdings geboten sein, wenn man ColumnStore Indizes für OLTP Systeme verwenden möchte. Der Umweg über Deltastore ist ebenso gut wie schlecht zu beurteilen. Für OLAP Systeme , die in der Regel seltener ihre Daten aktualisieren, ist der Columnstore Index eine sehr bequeme und performante Lösung. Für OLTP Systeme, die häufig auf eine hohe Schreibleistung angewiesen sind, ist der CSIX eher die schlechtere Wahl. Hier spielen die (nur in SQL 2014) neuen in-memory Tabellen eine herausragende Rolle.

Kommentare sind geschlossen