SQL Server 2014 Columnstore Indizes

 

Version 2.0 der Columnstore Indzes. Microsoft gab in der SQL Server 2014 CTP1 den Blick frei auf die kommende Genration der spaltenorientierten Indizes.

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_image002clip_image004

 

In zeilenorientierten Tabellen müssen bei jeder Anweisung ganze Datensätze gelesen werden. Spaltenorientierte Tabellen werden lediglich die benötigten Spalten auslesen müssen.

 

Jede Zeilengruppe wird anschließend parallel codiert und komprimiert und pro Spalte in ein sogenanntes Columnsegment (Spaltensegement) abgelegt. Als Kompressionsverfahren kann für Spalten eine sogenannte Wörterbuchkompression (Dictionary encoding) (2) zur Verwendung kommen, bei der letztendlich nach wiederkehrenden mustern gesucht wird und dafür Ersatzzeichen geschaffen werden.

Für Spalten, die eine Wörterbuchkompression – blieben wir später bei den englischen Fachbegriffen – verwenden, werden auch eine Anzahl Wörterbücher produziert. Hierbei handelt es sich entweder um lokale Wörterbücher, die innerhalb des Segments verbleiben oder globale, die alle Segmente abdecken. Ein Verzeichnis hilft dabei, um eine Übersicht über die Segmente zu bekommen und lokalisiert den Ort der Segmente und Wörterbücher. Außerdem weiß das Verzeichnis über die Anzahl der Zeilen, Größen und über die Art der Kompression Bescheid und kennt die darin befindlichen Min und Max Werte. Die Daten im Columnstore Index sind übrigens weder im Columnstore selbst noch in den Segmenten sortiert.

clip_image006

 

Die Umsetzung erfolgte mittels eines Non Clustered Index. Das hört sich zunächst nicht sehr spektakulär an, technisch wurde jedoch ein Ansatz verfolgt, der sich gezielt an optimiertes Lesen richtete: Statt immer eine Zeile nach der anderen zu lesen, werden Spalten gelesen, und zwar nur diese, die für die jeweilige Abfrage gebraucht werden. Column Store Indizes verwenden hierbei sehr aggressiv Read Ahead Vorgänge und laden die Daten in den Speicher – nicht in den Buffer Pool, sondern einen neuen Cache Pool für große Objekte – und können bei Abfragen im sehr schnellen Batchmodus (Mengenbasierend) verarbeitet werden.

Die Daten des CSIX sind zudem stark komprimiert,was wiederum eine geringere Belastung des Arbeitsspeichers bedeutet. Vergleicht man das die Kompressionsverfahren des CSIX und einer Zeilenbetrachtenden Kompression, so wird deutlich, daß eine spaltenorientierte Kompression wesentlich höhere Kompressionswerte erreichen. Immerhin gäbe es auch bei 12 Billionen Zeilen trotzdem nur knapp 200 Länder.

 

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. Die Daten lassen sich dadurch auch deutlich besser komprimieren. Microsoft legt noch ein Päckchen drauf und spendiert einen zusätzlichen effizienteren Archival ColumnStore Kompressionsalgorithmus, 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)

Die Tabelle Fact besteht aus ca 12 Millionen Datensätzen. Die Tabelle FactCS ist identisch, allerdings in Form eines Clustered Columnstore Index. Mittels sp_spaceused werden der verwedente Speicher auf der Disk untersucht.

clip_image008

 

Der Kompressionsfaktor betrug in einem Versuch das 26-fache gegenüber der herkömmlichen Tabellenkompression die lediglich das 5-fache schaffte.

Somit liegt es auf der Hand, dass CSIX in erster Linie besonders für sehr große Tabellen und Aggregatsabfragen geeignet sind und dort ihre Vorteile ausspielen können.

Bei all diesen Vorteilen gibt es auch eine Reihe gravierender Nachteile. Column Store Indizes waren nicht updatebar. Der Batchmodus wird nicht in jedem Statement automatisch verwendet, der Clustered Index ist weiterhin zeilenbasierend und nicht alle Datentypen wurden supported. Genau an diesen Stellen legt SQL Server 2014 nach.

Für einen korrekten Vergleich sollte man allerdings auch die zeilenorientierte Tabelle komprimieren…

Vergleich

Vergleicht man eine Tabelle ohne Kompression mit einer Columnstore Tabelle, so reduziert sich das Volumen auf der Disk auf ein 1/11.

Wird die zeilenorientierte Tabelle nun ebenfalls komprimiert, so ist der Unterschied immer noch 1:2. (Abbildung 4)

Setzt man nun auf der CSIX Tabelle noch die Archival Columnstore Compression ein, so ist das Verhältnis zur zeilenorientierten Tabelle 1:4 und zur unkomprimierten Tabelle 1:26. (Abbildung 5)

clip_image009

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.

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.

Die Theorie jedoch gibt der Praxis Recht. Der Column Store Index bezieht bekanntlich seine Leistung größtenteils aus der Kompression der Spalten. Um einen geeigneten Kompressionsalgorithmus zu finden sind jedoch vielen Daten notwendig. Daher ist es durchaus lohnenswert neue beziehungsweise geänderte Datensätze zunächst in das für das Schreiben vorteilhaftere Zeilenorientierte System zu übertragen und erst verzögert, wenn genügend Daten für die Kompression vorhanden sind, in den Column Store Index zu übertragen.

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. (1) 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. Während beispielweise für eine exemplarische Abfragemim Zeilenmodus ca 600 CPU Instructions benötigt wurden, so werden im Batchmodus lediglich 85 oder sogar noch weniger verwendet. 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

Ausführungsplan im Batchmodus

clip_image013

Batchmodus auch bei anderen JOIN Arten (hier LEFT JOIN)

DELTASTORES

 

Column Store Indizes sind nun sicherlich nicht die eierlegende Wollmilchsau. Sie Der Grund liegt in DML Anweisungen. Datenänderungen werden nicht sofort in die komprimierten Spalten geschrieben, sondern wandern zuerst in Deltastores (zeilenorientiert). Dies gilt für Inserts, sowie für Updates. (Updates werden als Insert und Delete behandelt). Der Grund ist dafür sehr einleuchtend. Bevor ein effizienter Kompressionsalgorithmus gefunden werden kann, müssen genügend Datensätze vorhanden sein. Daher werden Datenzeilen zuerst in die zeilenorientierten Deltastores geschrieben bis einen genügend große Menge vorhanden ist. 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 geringe 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

 

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

Performancevergleich durch Rebuild Performance Vergleich 5 Millionen gelöschter Datensätze

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

 

Und nun die Abfrage auf die Tabelle mit ColumnStore Index.

select Productkey , sum(TotalCost) from FACTCS

group by ProductKey

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

clip_image018

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. Auch das Argument über fehlende Aktualisierbarkeit, mittelmäßige Verwaltung des Speichers, eher kaum prognostizierbaren und selten verwendeten Batchmodus gepaart mit Einschränkungen der Datentypen nimmt wohl auch den letzten Gegner bald die Luft aus den Segeln. Somit lassen sich OLTP Systeme problemlos aktualisieren und bleiben stetig aktuell.

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 neuen in-memory Tabellen eine herausragende Rolle.

Anhang

Script zum Rekonstruieren der Beispiele

use ContosoRetailDW

GO

drop table Fact

GO

drop table FactCS

GO

create Table Fact

(

[OnlineSalesKey] [int] NOT NULL,

[DateKey] [datetime] NOT NULL,

[StoreKey] [int] NOT NULL,

[ProductKey] [int] NOT NULL,

[PromotionKey] [int] NOT NULL,

[CurrencyKey] [int] NOT NULL,

[CustomerKey] [int] NOT NULL,

[SalesOrderNumber] [nvarchar](20) NOT NULL,

[SalesOrderLineNumber] [int] NULL,

[SalesQuantity] [int] NOT NULL,

[SalesAmount] [money] NOT NULL,

[ReturnQuantity] [int] NOT NULL,

[ReturnAmount] [money] NULL,

[DiscountQuantity] [int] NULL,

[DiscountAmount] [money] NULL,

[TotalCost] [money] NOT NULL,

[UnitCost] [money] NULL,

[UnitPrice] [money] NULL,

[ETLLoadID] [int] NULL,

[LoadDate] [datetime] NULL,

[UpdateDate] [datetime] NULL

)

GO

create Table FactCS

(

[OnlineSalesKey] [int] ,

[DateKey] [datetime] NOT NULL,

[StoreKey] [int] NOT NULL,

[ProductKey] [int] NOT NULL,

[PromotionKey] [int] NOT NULL,

[CurrencyKey] [int] NOT NULL,

[CustomerKey] [int] NOT NULL,

[SalesOrderNumber] [nvarchar](20) NOT NULL,

[SalesOrderLineNumber] [int] NULL,

[SalesQuantity] [int] NOT NULL,

[SalesAmount] [money] NOT NULL,

[ReturnQuantity] [int] NOT NULL,

[ReturnAmount] [money] NULL,

[DiscountQuantity] [int] NULL,

[DiscountAmount] [money] NULL,

[TotalCost] [money] NOT NULL,

[UnitCost] [money] NULL,

[UnitPrice] [money] NULL,

[ETLLoadID] [int] NULL,

[LoadDate] [datetime] NULL,

[UpdateDate] [datetime] NULL

)

GO

insert into FactCS

select * from dbo.factOnlineSales

Go

insert into Fact

select * from dbo.factOnlineSales

Go

CREATE CLUSTERED COLUMNSTORE INDEX [CS_CLIX]

ON [dbo].[FactCS] WITH (DROP_EXISTING = OFF) ON [PRIMARY]

GO

USE [ContosoRetailDW]

ALTER TABLE [dbo].[FactCS] REBUILD PARTITION = ALL

WITH

(DATA_COMPRESSION = COLUMNSTORE_ARCHIVE

)

GO

Die Datenbank ContosoRetailDW läßt sich hier runterladen: ContosoRetailDW

Author: Fumus

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert