Leitfaden zum Entwerfen von Columnstore-Indizes

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Allgemeine Empfehlungen für das Entwerfen von Columnstore-Indizes. Mit wenigen guten Entwurfsentscheidungen erzielen Sie die hohe Datenkomprimierung und Abfrageleistung, für die Columnstore-Indizes entworfen wurden.

Voraussetzungen

In diesem Artikel wird davon ausgegangen, dass Sie mit der Columnstore-Architektur und -Terminologie vertraut sind. Weitere Informationen finden Sie unter Columnstore-Indizes: Overview and Columnstore Index Architecture.

Informationen zur Datenanforderungen

Bevor Sie einen Columnstore-Index erstellen, sollten Sie Ihre Datenanforderungen so gut wie möglich kennen. Überlegen Sie sich beispielsweise die Antworten auf die folgenden Fragen:

• Wie groß ist Ihre Tabelle?
• Werden mit meinen Abfragen überwiegend Analysen durchgeführt, bei denen große Wertebereiche gescannt werden? Columnstore-Indizes sind für das Scannen großer Bereiche besser geeignet als für das Suchen bestimmter Werte.
• Werden im Rahmen Ihrer Arbeitsauslastung zahlreiche Aktualisierungen und Löschvorgänge durchgeführt? Columnstore-Indizes eignen sich gut für stabile Daten. Bei Abfragen sollten weniger als 10 % der Zeilen aktualisiert und gelöscht werden.
• Verfügen Sie über Fakten- und Dimensionstabellen für ein Data Warehouse?
• Müssen Sie Analysen für eine Transaktionsarbeitsauslastung ausführen? In diesem Fall finden Sie Informationen zu operativen Echtzeitanalysen in den Designrichtlinien für Columnstore-Indizes.

Möglicherweise benötigen Sie keinen Columnstore-Index. Rowstore- (oder B-Struktur-)Indizes mit Heaps oder gruppierten Indizes zeigen die beste Leistung bei Abfragen, die in den Daten suchen, nach einem bestimmten Wert suchen oder einen kleinen Bereich von Werten abfragen. Verwenden Sie Rowstore-Indizes für Transaktionsarbeitsauslastungen, da sie tendenziell eher Suchvorgänge in Tabellen als Scans umfangreicher Tabellen erfordern.

Auswählen des für Ihre Anforderungen am besten geeigneten Columnstore-Indexes

Ein Columnstore-Index ist ein gruppierter oder ein nicht gruppierter Index. Ein gruppierter Columnstore-Index kann über einen oder mehrere nicht gruppierte B-Strukturindizes verfügen. Sie können Columnstore-Indizes ganz einfach ausprobieren. Wenn Sie eine Tabelle als Columnstore-Index erstellen, können Sie diese problemlos wieder in eine Rowstore-Tabelle konvertieren, indem Sie den Columnstore-Index löschen.

Im Folgenden finden Sie eine Übersicht über die Optionen und Empfehlungen.

Verwenden eines Columnstore-Indexes für umfangreiche Data Warehouse-Tabellen

Der gruppierte Columnstore-Index ist nicht nur ein Index, sondern er ist der primäre Tabellenspeicher. Er erreicht beim Data Warehousing eine hohe Datenkomprimierung sowie eine deutlich bessere Abfrageleistung bei umfangreichen Fakten- und Dimensionstabellen. Gruppierte Columnstore-Indizes eignen sich weniger gut für Transaktionsabfragen, jedoch besonders gut für Analyseabfragen, da bei Analyseabfragen in der Regel Operationen für große Wertebereiche durchgeführt werden und nicht nach bestimmten Werten gesucht wird.

Überlegen Sie in folgenden Fällen, ob ein gruppierter Columnstore-Index sinnvoll ist:

• Jede Partition besteht aus mindestens einer Million Zeilen. Columnstore-Indizes enthalten in jeder Partition Zeilengruppen. Wenn die Tabelle zu klein ist, um eine Zeilengruppe in jeder Partition zu füllen, können Sie nicht von den Vorteilen der Columnstore-Komprimierung und Abfrageleistung profitieren.
• Abfragen führen in erster Linie Analysen für Wertebereiche durch. Bei einer Abfrage müssen beispielsweise alle Spaltenwerte gescannt werden, um den Durchschnittswert einer Spalte zu ermitteln. Anschließend werden die Werte durch Summieren gesammelt, um den Durchschnittswert zu ermitteln.
• Die meisten Einfügevorgänge werden für umfangreiche Datenvolumen mit minimalen Aktualisierungen und Löschvorgängen durchgeführt. Bei vielen Arbeitsauslastungen wie beim Internet der Dinge (Internet of Things, IOT) werden mit minimalen Aktualisierungen und Löschvorgängen große Datenvolumen eingefügt. Diese Arbeitsauslastungen können von der besseren Komprimierung und Abfrageleistung profitieren, die sich aus der Verwendung eines gruppierten Columnstore-Indexes ergibt.

Verwenden Sie in den folgenden Fällen keinen gruppierten Columnstore-Index:

• Die Tabelle erfordert die Datentypen varchar(max), nvarchar(max) oder varbinary(max). Oder entwerfen Sie den Columnstore-Index so, dass er diese Spalten nicht enthält (Gilt für: SQL Server 2016 (13.x) und frühere Versionen).
• Die Tabellendaten sind nicht dauerhaft. Überlegen Sie, ob die Verwendung einer Heap-Tabelle oder einer temporären Tabelle sinnvoll ist, wenn Sie die Daten schnell speichern und löschen müssen.
• Die Tabelle hat weniger als einer Million Zeilen pro Partition.
• Mehr als 10 % der Vorgänge in der Tabelle sind Aktualisierungs- und Löschvorgänge. Eine große Anzahl von Aktualisierungs- und Löschvorgängen führt zu einer Fragmentierung. Die Fragmentierung wirkt sich auf die Komprimierungsraten und die Abfrageleistung aus, bis Sie eine Reorganisation durchführen, bei der alle Daten in den Columnstore gezwungen werden und eine Defragmentierung durchgeführt wird. Weitere Informationen finden Sie unter Minimieren der Indexfragmentierung in Columnstore-Indizes.

Weitere Informationen finden Sie unter Columnstore-Indizes in Data Warehouse.

Verwenden eines sortierten Columnstore-Indexes für umfangreiche Data Warehouse-Tabellen

Die Verfügbarkeit des sortierten Columnstore-Index finden Sie unter Columnstore-Indizes: Overview#ordered-columnstore-index-availability).

Erwägen Sie den Einsatz eines sortierten gruppierten Columnstore-Indexes in den folgenden Szenarien:

• Wenn Daten relativ statisch sind (ohne häufig geschriebene und gelöschte Daten), und der sortierte gruppierte Columnstore-Indexschlüssel statisch ist, können sortierte gruppierte Columnstore-Indizes erhebliche Leistungsvorteile gegenüber nicht sortierten gruppierten Columnstore-Indizes oder gruppierten Indizes für analytische Workloads bieten.
• Je mehr unterschiedliche Werte in der ersten Spalte des gruppierten Spaltenspeicherindexschlüssels bestehen, desto besser sind die Leistungsgewinne für sortierte gruppierte Columnstore-Indizes. Das liegt an einer verbesserten Segmenteliminierung für Zeichenkettendaten. Weitere Informationen finden Sie unter Ein Segment entfernen.
• Wählen Sie einen sortierten gruppierten Columnstore-Indexschlüssel aus, der häufig abgefragt und von der Segmenteliminierung profitieren kann, insbesondere der ersten Spalte des Schlüssels. Leistungsgewinne aufgrund der Segmenteliminierung bei anderen Spalten in der Tabelle sind weniger vorhersagbar.
• In Anwendungsfällen, in denen nur die neuesten Analysedaten abgefragt werden müssen, z. B. die letzten 15 Sekunden, können sortierte gruppierte Columnstore-Indizes eine Segmenteliminierung für ältere Daten ermöglichen. Die erste Spalte im Schlüssel der sortierten gruppierten Columnstore-Daten müssen die Datums-/Uhrzeitdaten sein, z. B. ein eingefügtes oder erstelltes Datum/Uhrzeit. Die Segmenteliminierung wäre in einem sortierten gruppierten Columnstore-Index effektiver als in einem nicht sortierten gruppierten Columnstore-Index.
• Erwägen Sie geordnete gruppierte Columnstore-Indizes für Tabellen, die Schlüssel mit GUID-Daten enthalten, wobei der Uniqueidentifier-Datentyp jetzt für die Segmenteliminierung verwendet werden kann.

Ein sortierter gruppierter Spaltenspeicherindex ist in diesen Szenarien möglicherweise nicht so effektiv:

• Ähnlich wie bei anderen Columnstore-Indizes könnte eine hohe Anzahl von Einfügeaktivitäten zu übermäßigen Speicher-E/A-Vorgängen führen.
• Bei Workloads mit vielen Schreibvorgängen wird die Qualität der Segmenteliminierung im Laufe der Zeit aufgrund der Zeilengruppenwartung durch den Tupelverschiebungsvorgang reduziert. Das kann durch regelmäßige Wartung des Columnstore-Indexes mit ALTER INDEX REORGANIZE ausgeglichen werden.

Hinzufügen eines nicht gruppierten B-Strukturindexes für effiziente Suchvorgänge in Tabellen

Ab SQL Server 2016 (13.x) können Sie nicht gruppierte B-Struktur- oder Rowstore-Indizes als sekundäre Indizes für einen gruppierten Columnstore-Index erstellen. Der nicht gruppierte B-Strukturindex wird bei Änderungen am Columnstore-Index aktualisiert. Diese leistungsstarke Funktion können Sie sich zunutze machen.

Mithilfe des sekundären B-Strukturindexes können Sie effizient nach bestimmten Zeilen suchen, ohne alle Zeilen scannen zu müssen. Auch weitere Optionen werden dadurch verfügbar. Beispielsweise können Sie eine Primär- oder Fremdschlüsseleinschränkung erzwingen, indem Sie eine UNIQUE-Bedingung auf den B-Strukturindex anwenden. Da ein nicht eindeutiger Wert nicht in den B-Strukturindex eingefügt werden kann, kann SQL Server den Wert nicht in den Columnstore einfügen.

Einen B-Strukturindex sollten Sie in folgenden Fällen in einem Columnstore-Index verwenden:

• Ausführen von Abfragen, die nach bestimmten Werten oder kleinen Wertebereichen suchen.
• Durchsetzen einer Einschränkung wie etwa einer Primärschlüssel- oder Fremdschlüsseleinschränkung.
• Effizientes Durchführen von Aktualisierungs- und Löschvorgängen. Mit dem B-Strukturindex können die Zeilen für Updates und Löschvorgänge schnell gefunden werden, ohne die Partition einer Tabelle oder die gesamte Tabelle scannen zu müssen.
• Ihnen steht zusätzlicher Speicherplatz zum Speichern des B-Strukturindexes zur Verfügung.

Verwendung eines nicht gruppierten Columnstore-Indexes für die Echtzeitanalyse

Ab SQL Server 2016 (13.x) können Sie einen nicht gruppierten Columnstore-Index in einer datenträgerbasierten Rowstore-Tabelle oder in einer In-Memory-Tabelle verwenden. Dadurch ist es möglich, die Analyse für eine Transaktionstabelle in Echtzeit auszuführen. Transaktionen werden in der zugrunde liegenden Tabelle ausgeführt. Eine Analyse können Sie dagegen für den Columnstore-Index ausführen. Da beide Indizes von einer Tabelle verwaltet werden, sind Änderungen sowohl für Rowstore- als auch für Columnstore-Indizes in Echtzeit verfügbar.

Da mit einem Columnstore-Index im Vergleich zu einem Rowstore-Index eine zehnfach höhere Datenkomprimierung erzielt werden kann, ist nur wenig zusätzlicher Speicherplatz erforderlich. Während die komprimierte Rowstore-Tabelle beispielsweise 20 GB belegt, benötigt der Columnstore-Index nur 2 GB zusätzlich. Wie viel zusätzlicher Speicher erforderlich ist, hängt auch von der Anzahl der Spalten im nicht gruppierten Columnstore-Index ab.

Überlegen Sie in folgenden Fällen, ob ein nicht gruppierter Columnstore-Index sinnvoll ist:

• Ausführen von Analysen für eine Rowstore-Transaktionstabelle in Echtzeit Sie können vorhandene B-Strukturindizes ersetzen, die für die Analyse eines nicht gruppierten Columnstore-Indexes erstellt wurden.

• Ein separates Data Warehouse ist nicht erforderlich. Bisher haben Unternehmen Transaktionen für eine Rowstore-Tabelle ausgeführt und die Daten zur Analyse in ein separates Data Warehouse geladen. Bei vielen Arbeitsauslastungen sind der Ladevorgang und das separate Data Warehouse nicht mehr erforderlich, wenn ein nicht gruppierter Columnstore-Index für Transaktionstabellen erstellt wird.

SQL Server 2016 (13.x) bietet verschiedene Strategien, damit dieses Szenario leistungsfähig wird. Es lässt sich sehr einfach ausprobieren, da Sie einen nicht gruppierten Columnstore-Index ohne Änderungen an der OLTP-Anwendung aktivieren können.

Sie können die Analyse für eine lesbare sekundäre Datenbank ausführen, um zusätzliche Verarbeitungsressourcen hinzuzufügen. Bei Verwendung einer lesbaren sekundären Datenbank werden die Verarbeitung der Transaktionsarbeitsauslastung und die der Analysearbeitsauslastung voneinander getrennt.

Weitere Informationen finden Sie unter "Erste Schritte mit Columnstore" für Echtzeit-Betriebsanalysen

Weitere Informationen zur Auswahl des am besten geeigneten Columnstore-Indexes finden Sie im Blog Which columnstore index is right for my workload? (Welcher Columnstore-Index eignet sich für meine Arbeitsauslastung) von Sunil Agarwal.

Verwenden von Tabellenpartitionen für eine bessere Datenverwaltung und Abfrageleistung

Columnstore-Indizes unterstützen die Partitionierung, die eine gute Möglichkeit zum Verwalten und Archivieren von Daten bietet. Durch die Partitionierung wird die Abfrageleistung verbessert, da Vorgänge auf eine oder mehrere Partitionen begrenzt werden.

Verwenden von Partitionen zur einfacheren Verwaltung von Daten

Bei umfangreichen Tabelle lassen sich Datenbereiche praktisch nur mithilfe von Partitionen verwalten. Die Vorteile von Partitionen für Rowstore-Tabellen gelten auch für Columnstore-Indizes.

Sowohl in Rowstore- als auch in Columnstore-Tabellen werden Partitionen in folgenden Fällen verwendet:

• Steuern der Größe von inkrementellen Sicherungen. Sie können Partitionen sichern, um Dateigruppen zu trennen und sie anschließend als schreibgeschützt zu kennzeichnen. Auf diese Weise werden die schreibgeschützten Dateigruppen bei zukünftigen Sicherungen übersprungen.
• Sparen bei Speicherkosten durch Verschieben älterer Partitionen in kostengünstigeren Speicher. Durch Partitionswechsel können Sie beispielsweise eine Partition an einen kostengünstigeren Speicherort verschieben.
• Effizientes Durchführen von Vorgängen durch Begrenzen der Vorgänge auf eine Partition. Sie können beispielsweise nur die fragmentierten Partitionen für die Indexwartung verwenden.

Zudem erreichen Sie mit der Partitionierung bei einem Columnstore-Index Folgendes:

• Sparen Sie weitere 30 % Speicherkosten. Sie können ältere Partitionen mit den COLUMNSTORE_ARCHIVE Komprimierungsoptionen komprimieren. Die Daten werden aus Gründen der Abfrageleistung langsamer. Das ist akzeptabel, wenn die Partition unregelmäßig abgefragt wird.

Verwenden von Partitionen zum Steigern der Abfrageleistung

Durch die Verwendung von Partitionen können Sie für Ihre Abfragen festlegen, dass nur bestimmte Partitionen gescannt werden. Dadurch wird die Anzahl der Zeilen begrenzt, die gescannt werden müssen. Wenn der Index beispielsweise nach Jahr partitioniert ist und mit der Abfrage Daten aus dem letzten Jahr analysiert werden, müssen nur die Daten in einer Partition gescannt werden.

Verwenden von weniger Partitionen für einen Columnstore-Index

Ein Columnstore-Index zeigt die beste Leistung, wenn Sie im Vergleich zu einem Rowstore-Index weniger Partitionen verwenden, außer Sie verfügen über eine ausreichend große Datengröße. Wenn die Partitionen nicht jeweils mindestens eine Million Zeilen aufweisen, gelangen die meisten Zeilen in den Deltastore. Dort profitieren sie nicht von der Leistungssteigerung der Columnstore-Komprimierung. Wenn Sie beispielsweise eine Million Zeilen in eine Tabelle mit 10 Partitionen laden und jede Partition erhält 100.000 Zeilen, gelangen alle Zeilen in Delta-Zeilengruppen.

Beispiel:

• Laden Sie 1.000.000 Zeilen in eine Partition oder in eine nicht partitionierte Tabelle. Daraufhin erhalten Sie eine komprimierte Zeilengruppe mit 1.000.000 Zeilen. Dies ist für eine starke Datenkomprimierung und eine optimale Abfrageleistung nützlich.
• Laden Sie 1.000.000 Zeilen gleichmäßig in 10 Partitionen. Jede Partition erhält 100.000 Zeilen, also weniger als die Mindestanzahl für die Columnstore-Komprimierung. Daher kann der Columnstore-Index 10 Delta-Zeilengruppen mit je 100.000 Zeilen aufweisen. Es gibt Möglichkeiten, ein Ablegen der Delta-Zeilengruppen im Columnstore durchzusetzen. Wenn dies jedoch die einzigen Zeilen im Columnstore-Index sind, sind die komprimierten Zeilengruppen zu klein für eine optimale Komprimierung und Abfrageleistung.

Weitere Informationen zur Partitionierung finden Sie im Blogbeitrag Should I partition my columnstore index? (Sollte ich meinen Columnstore-Index partitionieren) von Sunil Agarwal.

Auswählen der geeigneten Datenkomprimierungsmethode

Der Columnstore-Index biete zwei Möglichkeiten für die Datenkomprimierung: die Columnstore-Komprimierung und die Archivkomprimierung. Sie können eine Komprimierungsoption wählen, wenn Sie den Index erstellen oder die Einstellung später mit ALTER INDEX ... REBUILD anpassen.

Verwenden der Columnstore-Komprimierung für eine optimale Abfrageleistung

Mit der Columnstore-Komprimierung werden im Vergleich zu Rowstore-Indizes in der Regel zehnfach höhere Komprimierungsraten erzielt. Sie ist die Standardkomprimierungsmethode für Columnstore-Indizes und ermöglicht eine hohe Abfrageleistung.

Verwenden der Archivkomprimierung für eine optimale Datenkomprimierung

Die Archivkomprimierung ist in Fällen, in denen die Abfrageleistung eine untergeordnete Rolle spielt, für eine maximale Komprimierung vorgesehen. Sie erzielt im Vergleich zur Columnstore-Komprimierung höhere Datenkomprimierungsraten, hat jedoch ihren Preis. Die Komprimierung und Dekomprimierung der Daten dauert länger. Daher eignet sich diese Methode nicht für eine hohe Abfrageleistung.

Verwenden von Optimierungen beim Konvertieren einer Rowstore-Tabelle in einen Columnstore-Index

Wenn sich Ihre Daten bereits in einer Rowstore-Tabelle befinden, können Sie die Tabelle mit CREATE COLUMNSTORE INDEX in einen gruppierten Columnstore-Index konvertieren. Im Folgenden werden einige Optimierungen beschrieben, mit deren Hilfe sich die Abfrageleistung nach der Konvertierung der Tabelle verbessert.

Verwenden von MAXDOP zur Verbesserung der Zeilengruppenqualität

Sie können die maximale Anzahl von Prozessoren für die Konvertierung eines Indexes mit Heap-Struktur oder eines gruppierten B-Strukturindexes in einen Columnstore-Index konfigurieren. Verwenden Sie für die Konfiguration der Prozessoren die Option für den maximalen Grad an Parallelität (Maximum Degree of Parallelism, MAXDOP).

Wenn Sie über große Datenmengen verfügen, ist MAXDOP 1 voraussichtlich zu langsam. Erhöhen Sie in diesem Fall auf MAXDOP 4. Wenn dabei einige Zeilengruppen entstehen, die keine optimale Zeilenanzahl aufweisen, können Sie ALTER INDEX REORGANIZE ausführen, um diese im Hintergrund zusammenzuführen.

Beibehalten der Sortierreihenfolge eines B-Strukturindexes

Da der B-Strukturindex Zeilen bereits in sortierter Reihenfolge speichert, können Sie die Abfrageleistung steigern, indem Sie diese beim Komprimieren der Zeilen im Columnstore-Index beibehalten.

Im Columnstore-Index werden die Daten nicht sortiert. Es werden jedoch Metadaten zum Überwachen der Mindest- und Höchstwerte der einzelnen Spaltensegmente in den jeweiligen Zeilengruppen verwendet. Beim Scannen eines Wertebereichs kann schnell berechnet werden, wenn eine Zeilengruppe übersprungen werden kann. Wenn die Daten sortiert werden, können mehr Zeilengruppen übersprungen werden.

So können Sie die Sortierreihenfolge bei der Konvertierung beibehalten:

• Verwenden Sie CREATE COLUMNSTORE INDEX mit der DROP_EXISTING-Klausel. Dadurch wird auch der Name des Indexes beibehalten. Wenn Sie Skripts verwenden, in denen der Name des Rowstore-Indexes enthalten ist, müssen Sie diese nicht aktualisieren.

  In diesem Beispiel wird ein gruppierter Rowstore-Index in einer Tabelle mit dem Namen MyFactTable in einen gruppierten Columnstore-Index konvertiert. Der Indexname ClusteredIndex_d473567f7ea04d7aafcac5364c241e09 bleibt unverändert.

  CREATE CLUSTERED COLUMNSTORE INDEX ClusteredIndex_d473567f7ea04d7aafcac5364c241e09
  ON MyFactTable
  WITH (DROP_EXISTING = ON);
  

Grundlegende Informationen zur Segmenteliminierung

Jede Zeilengruppe enthält ein Spaltensegment für jede Spalte in der Tabelle. Jedes Spaltensegment wird zusammenhängend komprimiert und auf physischen Medien gespeichert.

Es gibt Metadaten für jedes Segment, damit Segmente, ohne sie zu lesen, schnell eliminiert werden können. Datentypauswahlen haben möglicherweise erhebliche Auswirkungen auf die Abfrageleistung basierend auf allgemeinen Filterprädikaten für Abfragen im Columnstore-Index. Weitere Informationen finden Sie unter Ein Segment entfernen.

Related Tasks

Hierbei handelt es sich um Aufgaben zum Erstellen und Verwalten von Columnstore-Indizes.

Zugehöriger Inhalt

So erstellen Sie einen leeren Columnstore-Index für:

• Informationen zu SQL Server oder SQL-Datenbank finden Sie unter CREATE TABLE (Transact-SQL).
• Informationen zu Azure Synapse Analytics finden Sie unter CREATE TABLE (Azure Synapse Analytics).

Weitere Informationen zum Konvertieren eines vorhandenen Rowstore-Indexes mit Heap- oder B-Struktur in einen gruppierten Columnstore-Index oder zum Erstellen eines nicht gruppierten Columnstore-Indexes finden Sie unter CREATE COLUMNSTORE INDEX (Transact-SQL).

• Neuerungen in Columnstore-Indizes
• Columnstore-Indizes in Data Warehouse
• Columnstore-Indizes: Abfrageleistung