Columnstore-Indizes – Entwurfsleitfaden

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-Datenbank in Microsoft Fabric

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.

Prerequisites

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.

Kennen Sie Ihre 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.
• Führt Ihr Workload viele Aktualisierungen und Löschungen aus? 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?
• Muss ich Analysen für eine Transaktionsarbeitsauslastung ausführen? Falls ja, lesen Sie die Columnstore-Entwurfsrichtlinien für operative Echtzeitanalysen.

Möglicherweise benötigen Sie keinen Columnstore-Index. Rowstore-Tabellen (oder B-Struktur) 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 Sie für große Data Warehouse-Tabellen einen gruppierten Columnstore-Index.

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 besser für Analyseabfragen als für Transaktionsabfragen, da bei Analyseabfragen tendenziell Operationen auf großen Wertebereichen ausgeführt werden, anstatt bestimmte Werte nachzuschlagen.

Ü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 auszufüllen, erhalten Sie möglicherweise nicht die Vorteile der Komprimierung und Abfrageleistung von Spaltenspeichern.
• 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 Workloads können von den Komprimierungs- und Abfrageleistungsgewinnen profitieren, die durch die Verwendung eines clustered Columnstore-Index erzielt werden.

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 einen Vorgang namens Neuorganisieren ausführen, der alle Daten in den Columnstore zwingt und die Fragmentierung entfernt. Weitere Informationen finden Sie unter Minimieren der Indexfragmentierung in Columnstore-Indizes.

Weitere Informationen finden Sie unter Columnstore-Indizes im Data Warehousing.

Verwenden Sie einen sortierten Columnstore-Index für große Data-Warehouse-Tabellen.

Die Verfügbarkeit des sortierten Columnstore-Indexes finden Sie unter Columnstore-Indizes: Übersicht.

Erwägen Sie die Verwendung eines geordneten Spaltenspeicherindex in den folgenden Szenarien:

• Wenn Daten relativ statisch sind (ohne häufige Schreib- und Löschvorgänge) und der Indexschlüssel des Spaltenspeichers statisch ist, können geordnete Columnstore-Indexes erhebliche Leistungsvorteile gegenüber nicht geordneten Columnstore-Indexes oder Zeilenspeicherindexes für analytische Belastungen bieten.
• Je mehr unterschiedliche Werte in der ersten Spalte des geordneten Columnstore-Indexschlüssels bestehen, desto besser sind die Leistungsgewinne. Das liegt an einer verbesserten Segmenteliminierung für Zeichenkettendaten. Weitere Informationen finden Sie unter Ein Segment entfernen.
• Wählen Sie einen Schlüssel eines sortierten Spaltenspeicherindex aus, der häufig abgefragt wird und der von dem Ausschluss von Segmenten profitieren kann, insbesondere die erste Spalte des Schlüssels. Leistungsgewinne durch die Eliminierung von Segmenten in anderen Spalten der Tabelle sind weniger vorhersagbar.
• Anwendungsfälle, in denen nur die neuesten Analysedaten abgefragt werden müssen, z. B. die letzten 15 Sekunden, können sortierte Columnstore-Indizes eine Segmentauslöschung für ältere Daten ermöglichen. Die erste Spalte im Schlüssel der sortierten Spaltenspeicherdaten muss die Datums-/Uhrzeitdaten sein, z. B. ein eingefügtes oder erstelltes Datum/Uhrzeit. Die Segmentauslöschung wäre in einem sortierten Columnstore-Index effektiver als in einem nicht sortierten Columnstore-Index.
• Erwägen Sie geordnete Columnstore-Indizes für Tabellen, die Schlüssel mit GUID-Daten enthalten, wobei der Uniqueidentifier-Datentyp jetzt zur Segmentlöschung verwendet werden kann.

Ein geordneter Columnstore-Index 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 Arbeitsauslastungen, bei denen viele Schreibvorgänge vorhanden sind, wird die Qualität der Segmentlöschung im Laufe der Zeit aufgrund der Zeilengruppenwartung durch den Tupelverschiebungsvorgang reduziert. Dies kann durch regelmäßige Wartung des Columnstore-Index mit ALTER INDEX REORGANIZE gemildert werden.

Nicht gruppierte B-Baum-Indizes für effiziente Tabellensuchen hinzufügen

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-Baum-Index wird aktualisiert, sobald Änderungen am Columnstore-Index auftreten. 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üsselbedingung erzwingen, indem Sie einen UNIQUE-Constraint für den B-Baum-Index verwenden. 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. Der B-Baum-Index kann die betreffenden Zeilen für Aktualisierungen und Löschungen schnell finden, ohne die gesamte Tabelle oder eine Partition der Tabelle vollständig durchsuchen zu müssen.
• Ihnen steht zusätzlicher Speicherplatz zum Speichern des B-Baum-Index 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 für eine datenträgerbasierte Rowstore-Tabelle oder eine In-Memory-OLTP-Tabelle erstellen. Dadurch ist es möglich, die Analyse für eine Transaktionstabelle in Echtzeit auszuführen. Während Transaktionen in der zugrunde liegenden Tabelle stattfinden, können Sie Analysen 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. Zum Beispiel könnte der Columnstore-Index zusätzliche 2 GB erfordern, wenn die komprimierte Rowstore-Tabelle 20 GB belegt. Wie viel zusätzlicher Speicher erforderlich ist, hängt auch von der Anzahl der Spalten im nicht gruppierten Columnstore-Index ab.

Ziehen Sie die Verwendung eines nicht gruppierten Spaltenspeicherindex in Betracht:

• Führen Sie Analysen in Echtzeit auf einer transaktionalen Rowstore-Tabelle aus. Sie können vorhandene B-Strukturindizes ersetzen, die für die Analyse eines nicht gruppierten Columnstore-Indexes erstellt wurden.

• Machen Sie ein separates Data Warehouse überflüssig. Traditionell verarbeiten Unternehmen Transaktionen in einer Rowstore-Tabelle und laden die Daten dann für Analysen in ein separates Data Warehouse. 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 ist einfach, es zu versuchen, da Sie einen nicht gruppierten Spaltenspeicherindex ohne Änderungen an Ihrer 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 Sie die Größe inkrementeller Sicherungen. Sie können Partitionen sichern, um Dateigruppen zu trennen und sie anschließend als schreibgeschützt zu kennzeichnen. Auf diese Weise überspringen zukünftige Sicherungen die schreibgeschützten Dateigruppen.
• 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.

Zusätzlich können Sie bei einem Columnstore-Index die Partitionierung verwenden, um:

• Sparen Sie weitere 30 % Speicherkosten. Sie können ältere Partitionen mit den COLUMNSTORE_ARCHIVE Komprimierungsoptionen komprimieren. Die Abfrageleistung kann langsamer sein, was akzeptabel sein könnte, wenn die Partition selten 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 Sie 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 Sie nicht mindestens eine Million Zeilen pro Partition haben, könnten die meisten Ihrer Zeilen im Deltastore landen, wo sie nicht von den Leistungsvorteilen der Columnstore-Komprimierung profitieren. Wenn Sie beispielsweise eine Million Zeilen in eine Tabelle mit 10 Partitionen laden und jede Partition 100.000 Zeilen empfängt, gehen alle Zeilen zu den Delta-Zeilengruppen.

Example:

• 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, um die beste Komprimierung und Abfrageleistung zu erzielen.

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 die Komprimierungsoption auswählen, wenn Sie den Index erstellen, oder sie später mit ALTER INDEX ... REBUILD ändern.

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 CREATE COLUMNSTORE INDEX verwenden, um die Tabelle in einen Clustered Columnstore-Index umzuwandeln. Im Folgenden werden einige Optimierungen beschrieben, mit deren Hilfe sich die Abfrageleistung nach der Konvertierung der Tabelle verbessert.

Verwenden Sie MAXDOP, um die Qualität von Zeilengruppen zu verbessern

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 möglicherweise zu langsam. Das Erhöhen von MAXDOP auf 4 funktioniert problemlos. Wenn dies zu einigen Zeilengruppen führt, die nicht über die optimale Anzahl von Zeilen verfügen, können Sie REORGANIZE ausführenALTER INDEX, um sie im Hintergrund zusammenzuführen.

Sortierreihenfolge eines B-Baum-Index beibehalten

Da der B-Baum-Index Zeilen bereits in sortierter Reihenfolge speichert, kann die Beibehaltung dieser Reihenfolge beim Komprimieren der Zeilen in den Columnstore-Index die Abfrageleistung verbessern.

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 Klausel DROP_EXISTING. Dadurch wird auch der Name des Indexes beibehalten. Wenn Sie Skripts haben, die bereits den Namen des Rowstore-Index verwenden, müssen Sie sie 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);
  

Segmenteliminierung verstehen

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.

Verwandte Aufgaben

Eine Zusammenfassung allgemeiner Aufgaben zum Erstellen und Verwalten von Spaltenspeicherindizes finden Sie unter "Verwandte Aufgaben".

Verwandte Inhalte

• Neuerungen in Columnstore-Indizes
• Columnstore-Indizes im Data-Warehousing
• Columnstore-Indizes: Abfrageleistung