Teil 4: Spaltenbasierte Indizes

SQL Server Indizes – Indexvarianten
Kommentare

In der abschließenden Folge der Artikelserie geht es um die noch relativ neuen spaltenbasierten Indizes, die aufgrund einer sehr effektiven Datenkompression zu einem erheblichen Performancegewinn beitragen können.

Die spaltenbasierten (Columnstore-)Indizes wurden mit SQL Server 2012 eingeführt, in den späteren Versionen jedoch noch deutlich erweitert, sodass im folgenden Text alle Varianten in chronologischer Reihenfolge beschrieben werden. Vorweg sei darauf hingewiesen, dass es sich bei den Columnstore-Indizes in allen Versionen um ein Enterprise-Feature handelt, das (wie viele Performancefeatures) nur in der größten SQL-Server-Edition verfügbar ist.

Während die klassischen Speicherstrukturen für Tabellen und Indizes darauf basieren, die Daten zeilenweise abzulegen, werden die Daten bei Columnstore-Indizes zunächst in Zeilengruppen zu jeweils ca. 1 Million Zeilen aufgeteilt. Diese werden dann wiederum in Segmente aufgeteilt, sodass jede Spalte einer Zeilengruppe in einem separaten Segment abgelegt ist. Diese Segmente werden in mehreren Schritten komprimiert:

  • Lauflängenkomprimierung
  • Wörterbuchkomprimierung
  • Binäre Komprimierung

Während die Lauflängen- und Wörterbuchkomprimierung wiederkehrende Werte effektiver abspeichern, wird für die binäre Komprimierung ein geheimer Algorithmus (xVelocity, früher unter dem Namen VertiPaq bekannt) verwendet, der auch in PowerPivot und im SSAS Tabular Data Model Anwendung findet. Die Kombination dieser Algorithmen führt (insbesondere bei häufig wiederholenden Spaltenwerten) zu sehr hohen Kompressionsfaktoren. Anschließend werden die Segmente im binären Format gespeichert.

Besonders effektiv sind Columnstore-Indizes bei der Abfrage von einzelnen Spalten, da die Segmente der nicht beteiligten Spalten in diesem Fall erst gar nicht betrachtet werden müssen. Dadurch, dass in den Segmenten jeweils auch der minimale und maximale Wert der jeweiligen Spalte gespeichert wird, lassen sich bei der Abfrage auf bestimmte Werte (oder Wertbereiche) weiterhin zahlreiche Segmente von vornherein ausschließen (man nennt das Segmentelimination).

Dabei muss man sich jedoch der Tatsache bewusst sein, dass die Spaltenwerte selbst – sowohl innerhalb der Segmente als auch segmentübergreifend – nicht sortiert gespeichert sind. Das belegt auch eine Abfrage auf die Systemtabelle sys.column_store_
segments
, die auch die minimalen und maximalen Werte der einzelnen Segmente zurückgibt, wodurch ersichtlich wird, dass es durchaus zu Überschneidungen kommen kann:

 title="">SELECT *
FROM sys.column_store_segments

Nachdem nun alle Varianten von Columnstore-Indizes im Allgemeinen geklärt sind, möchte ich im Folgenden die verschiedenen Entwicklungsstufen vorstellen (zumal nicht jeder in der glücklichen Lage ist, die neueste Version des SQL Server im Einsatz zu haben).

Schnell und überall: Datenzugriff mit Entity Framework Core 2.0

Dr. Holger Schwichtenberg (www.IT-Visions.de/5Minds IT-Solutions)

C# 7.0 – Neues im Detail

Christian Nagel (CN innovation)

Nicht gruppierte Read-only-Columnstore-Indizes (ab SQL 2012)

Die erste Generation von Columnstore-Indizes ist noch sehr vielen Einschränkungen unterworfen. So können diese weder als gefilterter noch als UNIQUE-Index definiert werden und auch keine INCLUDE-Spalten enthalten. Auch auf so genannten Sparse Columns kann kein Columnstore-Index angelegt werden. Erschwerend hinzu kommt die Tatsache, dass viele Datentypen nicht unterstützt werden, darunter:

  • uniqueidentifier
  • rowversion/timestamp
  • sql_variant
  • datetimeoffset mit einer Scale > 2
  • decimal/numeric mit einer Precision > 18
  • binary
  • alle LOB-(Large-Object-Block-)Typen (text, ntext, varchar(max), nvarchar(max), varbinary (max))
  • alle .NET-CLR-basierten Datentypen (z. B. hierarchyid, geometry, geography)

Die Tatsache, dass nur ein Columnstore-Index pro Tabelle möglich ist, fällt nicht weiter ins Gewicht. Da man darin alle benötigten Spalten unterbringen kann und die Reihenfolge der Spalten – im Gegensatz zu den klassischen Indexformen – hier keine Rolle spielt, ist ein zweiter Columnstore-Index auf derselben Tabelle weder erforderlich noch sinnvoll. Auch die nicht unterstützten Datentypen stellen kein allzu gravierendes Problem dar, da man den Index explizit auf die Spalten einschränken kann, deren Datentypen auch unterstützt werden. Die Syntax zur Erstellung eines Columnstore-Indexes ist mit der eines zeilenbasierten Index nahezu identisch. Lediglich das Schlüsselwort COLUMNSTORE muss ergänzt werden:

CREATE NONCLUSTERED COLUMNSTORE INDEX CIX_LastName_FirstName 
ON Person.Person(LastName, FirstName)

Die gravierendste Einschränkung ist jedoch, dass jede Tabelle, auf der ein Columnstore-Index definiert ist, nicht mehr geändert werden kann. Selbst Inhalte von Spalten, die gar nicht im Index enthalten sind, können mit SQL Server 2012 nicht geändert werden, sobald ein Columnstore-Index auf der Tabelle existiert. Ist dennoch eine Änderung nötig, so muss der Index erst deaktiviert und nach der Änderung per REBUILD wieder neu aufgebaut werden.

ALTER INDEX CIX_LastName_FirstName ON Person.Person DISABLE

 

UPDATE Person.Person

SET Title='Dr'

WHERE BusinessEntityID=1

 

ALTER INDEX CIX_LastName_FirstName ON Person.Person REBUILD

Dieser Zusatzaufwand macht den Columnstore-Index vor allem für Data-Warehousing-Szenarien sinnvoll verwendbar, da die Datenänderungen hier nicht fortlaufend stattfinden, sondern die Tabellen in Batchzeitfenstern (beispielsweise jeweils nachts oder am Wochenende) komplett neu befüllt werden. Anschließend ist ein Neuaufbau der Columnstore-Indizes problemlos möglich.

Ein großer Vorteil der Columnstore-Indizes liegt darin, dass sie keine speziellen Zugriffsmethoden benötigen, sondern die SQL-Server-Abfrage-Engine bei Erstellung des Ausführungsplans einer Abfrage selbst entscheidet, wann es Sinn macht, einen bestehenden Columnstore-Index zu verwenden (Abb. 1). So kann bei der folgenden Abfrage der oben erwähnte Index genutzt werden, da in ihm alle benötigten Spalten enthalten sind:

SELECT FirstName

FROM Person.Person

WHERE LastName = 'Garcia'
panther_SQLIndizes1_1

Abb. 1: Mögliche Ausführungspläne bei Nutzung eines Non-clustered-Columnstore-Index

Auswahl der effektiveren Indizes durch den Optimizer

Die in SQL Server integrierte Abfrage-Engine versucht stets den effektivsten Weg zu ermitteln, um eine Abfrage auszuführen. Wenn zu denselben Spalten einer Tabelle sowohl zeilenbasierte als auch spaltenbasierte Indizes existieren, kann es daher sein, dass die zeilenbasierte Variante aufgrund einer geringen Zeilenzahl (bei der diese Indexform meist effektiver arbeitet) bevorzugt wird. In diesem Fall kann man den zeilenbasierten Index testweise per ALTER INDEX … DISABLE deaktivieren und somit die Nutzung der spaltenbasierten Variante erzwingen.

Fragt man allerdings noch weitere Spalten ab, die nicht im Index enthalten sind, wird neben dem Zugriff auf den Index ein zusätzlicher Schritt notwendig, um diese Spalten zu lesen. Analog zur Verwendung von klassischen zeilenbasierten Indizes wird auch hier entweder eine Schlüsselsuche (Key Lookup) oder ein Row Lookup verwendet, je nachdem, ob für die Tabelle ein gruppierter Index existiert oder nicht. Daraus lässt sich schließen, dass auch im Columnstore-Index ein Verweis auf den Clustered Key (bei Vorhandensein eines gruppierten Index) oder aber die Kombination aus Nummer der Datenbankdatei, Nummer der Speicherseite und Nummer des Datensatzes auf der Speicherseite (auch bekannt als Row-ID) enthalten ist.

Gruppierte Columnstore-Indizes (ab SQL Server 2014)

Mit SQL Server 2014 wurden die gruppierten Columnstore-Indizes eingeführt, die nun endlich auch änderbar sind. Dazu wurden einige bisher nicht unterstützte Datentypen integriert:

  • uniqueidentifier
  • datetimeoffset mit einer Scale > 2
  • decimal/numeric mit einer Precision > 18
  • binary

Lediglich rowversion/timestamp, sql_variant sowie die LOB-Datentypen und die .NET-CLR-basierten Datentypen werden noch immer nicht unterstützt. Abgesehen von rowversion/timestamp wird sich das wahrscheinlich auch in künftigen Versionen nicht mehr ändern, da die restlichen Datentypen aufgrund ihrer sehr variablen Länge nicht zum spaltenorientierten Speicherkonzept passen.

Die fehlende Unterstützung dieser Datentypen stellt bei gruppierten Columnstore-Indizes sogar ein noch größeres Problem dar als bisher bei den nicht gruppierten, da ein gruppierter Index (egal ob zeilen- oder spaltenbasiert) stets alle Spalten einer Tabelle beinhaltet. Als weitere Einschränkung können auf einer Tabelle mit gruppiertem Columnstore-Index keine weiteren Indizes (unabhängig davon, welchen Typs) definiert werden.

Als mögliche Alternative werden die nicht gruppierten Columnstore-Indizes zwar nach wie vor unterstützt, haben aber auch nach wie vor zur Folge, dass die Tabelleninhalte dann nicht mehr änderbar sind. Man muss sich bei Verwendung von SQL Server 2014 also entscheiden, mit welchen Einschränkungen man eher leben kann.

Da die Spalten eines gruppierten Columnstore-Index unabhängig voneinander gespeichert werden und ohnehin alle Spalten enthalten sind, müssen für dessen Erstellung auch keine Spalten angegeben werden:

CREATE CLUSTERED COLUMNSTORE INDEX PK_Person_Columnstore
ON Person.Person

Um Datenänderungen bei gruppierten Columnstore-Indizes zu ermöglichen, wurden zwei neue Konstrukte eingeführt. Das eine ist die so genannte Deleted-Bitmap. Dabei handelt es sich um eine Baumstruktur, in der gespeichert wird, welche Zeilen gelöscht wurden. Auch wenn die Zeilen noch im eigentlichen Columnstore enthalten sind, sind sie über die Deleted-Bitmap als gelöscht markiert, sodass sie nicht mehr in Abfrageergebnissen zurückgegeben werden.

Das zweite Hilfskonstrukt ist der so genannte Delta-Store (Abb. 2). Darin werden, zeilenbasiert und auf Gruppen von maximal etwa eine Million Zeilen aufgeteilt, neu hinzugefügte Zeilen abgelegt. Werden im Columnstore bestehende Zeilen geändert, so werden beide Konstrukte kombiniert genutzt, indem in der Deleted-Bitmap die ungültige Variante der Zeile als gelöscht markiert und im Delta-Store die neue Variante der Zeile abgelegt wird. Löschungen und Änderungen von Zeilen, die sich im Delta-Store befinden, werden direkt dort ausgeführt. Ist die Grenze von einer Million Zeilen (genau 1 048 576) in einer Zeilengruppe erreicht, wird diese geschlossen und eine neue Zeilengruppe im Delta-Store erstellt. In dieser werden die nächsten neuen oder geänderten Zeilen aufgenommen.

Abb. 2: Gruppierter Columnstore-Index mit Delta-Store und Deleted-Bitmap

Abb. 2: Gruppierter Columnstore-Index mit Delta-Store und Deleted-Bitmap

Um die Daten aus dem Delta-Store in den Columnstore zu integrieren, existiert ein Hintergrundprozess namens Tuple Mover, der alle fünf Minuten automatisch ausgeführt wird und den Columnstore mit allen geschlossenen Delta-Store-Zeilengruppen zusammenführt. Alternativ kann man dies auch explizit per SQL-Anweisung durch einen Index-REORGANIZE anstoßen:

ALTER INDEX PK_Person_Columnstore ON Person.Person REORGANIZE

Führt man stattdessen einen REBUILD des gruppierten Columnstore-Index aus, so werden auch die offenen Zeilengruppen des Delta-Store in den Columnstore integriert. Dazu werden auch die in der Deleted-Bitmap als gelöscht markierten Zeilen im Columnstore entfernt und anschließend die Deleted-Bitmap selbst geleert. Diese Wartungsaufgabe kann zwar recht zeitaufwändig sein, dafür ist der Index danach am effektivsten.

Um einen Einblick in die vorhandenen Zeilengruppen zu bekommen, kann man die folgende Abfrage auf der Systemtabelle sys.column_store_row_groups nutzen:

 SELECT *, OBJECT_NAME(object_id) AS ObjectName

FROM sys.column_store_row_groups

Dabei werden unter anderem die Anzahl der in der Zeilengruppe gespeicherten Zeilen (total_rows) sowie die daraus gelöschten Zeilen (deleted_rows) angezeigt. Die wichtigste Information ist aber der Status der Zeilengruppe, der sowohl als Code (state) als auch im Klartext (state_description) ausgegeben wird. Dabei sind folgende Werte möglich:

  • 0INVISIBLE: Eine komprimierte Zeilengruppe, die gerade im Columnstore aufgebaut wird.
  • 1OPEN: Eine offene Zeilengruppe im Delta-Store, in die noch weitere Zeilen hinzugefügt werden können.
  • 2CLOSED: Eine geschlossene Zeilengruppe im Delta-Store, die bereits die kritische Größe von einer Million Zeilen erreicht hat und darauf wartet, über den Tuple Mover in den Columnstore integriert zu werden.
  • 3COMPRESSED: Eine Zeilengruppe, die bereits komprimiert im Columnstore abgelegt ist.

Wenn man diese Abfrage mit weiteren Systemtabellen verknüpft, lässt sich leicht herausfinden, um welche Indizes es sich genau handelt und auf welche Tabellen sie sich beziehen. Dadurch lässt sich das Abfrageergebnis mit einer entsprechenden WHERE-Klausel auch leicht auf einen einzelnen Index beschränken, um zu beobachten, wie sich REORGANIZE und REBUILD auf die Zeilengruppen dieses Index auswirken (Listing 1).

 
SELECT SCHEMA_NAME(o.schema_id) AS SchemaName,
OBJECT_NAME(rg.object_id) AS ObjectName,
i.name AS IndexName, i.type_desc AS IndexType, rg.*
FROM sys.column_store_row_groups rg
INNER JOIN sys.objects o ON rg.object_id=o.object_id
INNER JOIN sys.indexes i
ON rg.index_id=i.index_id AND rg.object_id=i.object_id
WHERE i.name = 'CIX_LastName_FirstName'

Nicht gruppierte Columnstore-Indizes (ab SQL 2016)

Mit SQL Server 2016 kommt nun die Möglichkeit hinzu, auch beschreibbare, nicht gruppierte Columnstore-Indizes anzulegen. Die Beschränkung der unterstützten Datentypen (keine LOB-Datentypen, keine .NET-CLR-basierten Datentypen, kein sql_variant, kein rowversion/timestamp) besteht zwar nach wie vor, da dieser Index nun aber nahezu beliebig mit anderen Indexformen kombinierbar ist, kann man Spalten der nicht unterstützten Datentypen einfach aus dem Columnstore-Index herauslassen und dafür separate Indizes eines anderen Typs definieren.

Damit wird der Columnstore-Index nun auch für OLTP-Datenbanken interessant, zumal die bestehenden Strukturen und Indizes beibehalten und zusätzliche nicht gruppierte Columnstore-Indizes einfach definiert werden können. Man kann sich also ohne eine komplette Umstellung bestehender Datenbankarchitekturen langsam an das Thema Columnstore-Indizes herantasten, indem man minimal-invasiv an ausgewählten Stellen einen zusätzlichen Columnstore-Index definiert und beobachtet, ob er bei bestimmten Abfragen zu einer besseren Ausführungsgeschwindigkeit führt. Trotzdem muss natürlich beachtet werden, dass der Wartungsaufwand von Columnstore-Indizes bei sehr häufigen Änderungen deutlich höher ist als bei den klassischen zeilenbasierten Indizes.

SQL Server 2016 bietet aber noch weitere Kombinationsmöglichkeiten von Indizes an. So können nun auch auf Tabellen mit gruppierten Columnstore-Indizes zusätzliche nicht gruppierte Indizes (zeilen- oder spaltenbasiert) erstellt werden. Während die Erstellung zusätzlicher spaltenbasierter Indizes wenig sinnvoll erscheint (da sie ohnehin im gruppierten Columnstore-Index enthalten sind), kann ein zusätzlicher zeilenbasierter Index hilfreich sein, da diese in einigen Fällen effektiver arbeiten als die neuen spaltenbasierten Indizes. Das trifft insbesondere dann zu, wenn viele Spalten einer einzelnen Zeile abgefragt werden, da bei der geringen Zeilenanzahl die Stärke von spaltenbasierten Indizes nicht zum Tragen kommt, stattdessen aber deren Nachteil, da pro Spalte ein anderes Segment gelesen werden muss.

Mit SQL Server 2016 ist es sogar möglich, Columnstore-Indizes auf In-Memory-Tabellen zu erstellen, was insbesondere im Zusammenhang mit Echtzeitauswertungen empfohlen wird. Da die seit SQL Server 2014 verfügbare In-Memory-Technologie jedoch ein eigenes, sehr komplexes Thema ist, wird diese Variante hier nicht im Detail behandelt.

Neben diesen neuen Kombinationsmöglichkeiten räumt SQL Server 2016 auch mit einigen weiteren Beschränkungen der Vorgängerversionen auf. So können nun auch für Tabellen, die Foreign Keys oder Trigger nutzen, spaltenbasierte Indizes erstellt werden.

Weitere Informationen zu Columnstore-Indizes finden sich auf MSDN, den zahlreichen Blogbeiträgen des Columnstore-Spezialisten Niko Neugebauer sowie in einem Whitepaper, das mehrere Microsoft-Entwickler verfasst haben, um die technischen Grundlagen der mit SQL Server 2014 eingeführten gruppierten Columnstore-Indizes zu beschreiben. Ein Überblick über die mit den verschiedenen SQL-Server-Versionen verfügbaren Columnstore-Index-Features ist ebenfalls auf MSDN zu finden.

Fazit

Nachdem im Laufe dieser Artikelserie alle für SQL Server verfügbaren Indexvarianten beschrieben wurden, stellt sich natürlich die Frage, wann welche Variante am besten einzusetzen ist. Der Einsatzzweck der Indextypen für spezielle Verwendungszecke aus dem dritten Teil der Serie ergibt sich aufgrund des jeweiligen Datentyps quasi von selbst. Lediglich bei Textfeldern mit fest vorgegebener Maximallänge ist zu überlegen, ob hier ein Volltextindex eingesetzt wird oder einer der anderen Indextypen. Dabei macht der Volltextindex vor allem dann Sinn, wenn der Suchbegriff gleichzeitig in mehreren Spalten gesucht werden soll oder die erweiterten Funktionen zur Ähnlichkeitssuche (Flexionsformen, Synonyme etc.) benötigt werden.

Somit bleiben die gruppierten oder nicht gruppierten zeilen- oder spaltenbasierten Indizes. Noch vor Kurzem galt die Regel, dass spaltenbasierte Indizes für Data-Warehouse-Lösungen ideal sind, während man für OLTP-Anwendungen zeilenbasierte Indizes verwendet. Spätestens durch die neuen Möglichkeiten und Varianten von SQL Server 2016 ist dies jedoch nicht mehr so einfach, sodass man inzwischen anhand einer ganzen Reihe von Entscheidungskriterien abwägen muss, wann welche Variante sinnvoller erscheint.

Für nicht gruppierte Indizes sprechen:

  • Häufiges Suchen in wenigen Spalten
  • Abfragen, die nur wenige Zeilen zurückgeben
  • Suchen nach verschiedenen, voneinander unabhängigen Kriterien

Für gruppierte Indizes sprechen:

  • häufiges Abfragen vieler Spalten
  • Abfragen von Zeilenbereichen

Für zeilenbasierte Indizes sprechen:

  • häufige Schreibaktivität
  • stark unterschiedliche Spalteninhalte

Für spaltenbasierte Indizes sprechen:

  • Abfrage von großen Zeilenmengen
  • häufiges Suchen/Filtern/Gruppieren nach einzelnen Spalten
  • sich häufig wiederholende Spalteninhalte
  • geringe Schreibaktivität

Da in der Praxis natürlich verschiedene der soeben beschriebenen Anforderungen auftreten, lassen sich die verschiedenen Indexvarianten (insbesondere ab SQL Server 2016) glücklicherweise auch kombiniert einsetzen, sodass es gilt, den jeweils idealen Mix zu finden.

Abschließend sei an dieser Stelle noch einmal darauf hingewiesen, dass auch stets die regelmäßige Pflege und Wartung der Indizes (und sofern für die jeweilige Indexvariante relevant auch deren Indexstatistiken) berücksichtigt werden müssen, damit die Indizes effektiv verwendet werden.

Windows Developer

Windows DeveloperDieser Artikel ist im Windows Developer erschienen. Windows Developer informiert umfassend und herstellerneutral über neue Trends und Möglichkeiten der Software- und Systementwicklung rund um Microsoft-Technologien.

Natürlich können Sie den Windows Developer über den entwickler.kiosk auch digital im Browser oder auf Ihren Android- und iOS-Devices lesen. In unserem Shop ist der Windows Developer ferner im Abonnement oder als Einzelheft erhältlich.

Unsere Redaktion empfiehlt:

Relevante Beiträge

Meinungen zu diesem Beitrag

X
- Gib Deinen Standort ein -
- or -