Verwenden von gruppierten Columnstore-Indizes
Aufgaben für die Verwendung gruppierter Columnstore-Indizes in SQL Server.
Eine Übersicht über Columnstore-Indizes finden Sie unter Columnstore Indexes Described.
Informationen zum Verwenden von gruppierten Columnstore-Indizes finden Sie unter Using Clustered Columnstore Indexes.
Inhalte
Erstellen eines gruppierten Columnstore-Indexes
Um einen gruppierten Columnstore-Index zu erstellen, erstellen Sie zuerst eine Rowstore-Tabelle als Heap oder gruppierten Index, und verwenden Sie dann die CREATE CLUSTERED COLUMNSTORE INDEX -Anweisung (Transact-SQL), um die Tabelle in einen gruppierten Columnstore-Index zu konvertieren. Wenn der gruppierte Columnstore-Index denselben Namen erhalten soll wie der gruppierte Index, verwenden Sie die Option DROP_EXISTING.
In diesem Beispiel wird eine Tabelle als Heap erstellt und anschließend in einen gruppierten Columnstore-Index mit dem Namen cci_Simple konvertiert. Dadurch wird der Speicher für die gesamte Tabelle von rowstore in columnstore geändert.
CREATE TABLE T1(
ProductKey [int] NOT NULL,
OrderDateKey [int] NOT NULL,
DueDateKey [int] NOT NULL,
ShipDateKey [int] NOT NULL);
GO
CREATE CLUSTERED COLUMNSTORE INDEX cci_T1 ON T1;
GO
Weitere Beispiele finden Sie im Abschnitt Beispiele unter CREATE CLUSTERED COLUMNSTORE INDEX (Transact-SQL).
Löschen eines gruppierten Columnstore-Indexes
Verwenden Sie die DROP INDEX -Anweisung (Transact-SQL), um einen gruppierten Columnstore-Index zu löschen. Bei diesem Vorgang wird der Index gelöscht, und die Columnstore-Tabelle wird in einen Rowstore-Heap konvertiert.
Laden von Daten in einen gruppierten Columnstore-Index
Sie können einem vorhandenen gruppierten Columnstore-Index Daten hinzufügen, indem Sie eine der Standardladenmethoden verwenden. Zum Beispiel das bcp-Tool zum Massenladen, Integration Services und INSERT ... SELECT kann alle Daten in einen gruppierten Columnstore-Index laden.
Gruppierte Columnstore-Indizes nutzen den Deltastore, um eine Fragmentierung der Spaltensegmente im Columnstore zu verhindern.
Laden von Daten in eine partitionierte Tabelle
Bei partitionierten Daten weist SQL Server zuerst jede Zeile einer Partition zu und führt dann Columnstore-Vorgänge für die Daten innerhalb der Partition aus. Jede Partition verfügt über eigene Zeilengruppen und mindestens einen Deltastore.
Ladeszenarien für Deltastores
Zeilen werden im Deltastore gesammelt, bis ihre Anzahl die maximale Anzahl der Zeilen erreicht, die für eine Zeilengruppe zulässig sind. Wenn der Deltastore die maximale Anzahl von Zeilen pro Zeilengruppe enthält, markiert SQL Server die Zeilengruppe als "CLOSED". Ein Hintergrundprozess, der als "Tupel-Mover" bezeichnet wird, sucht die CLOSED-Zeilengruppe und wechselt in den Columnstore, wo die Zeilengruppe in Spaltensegmente komprimiert wird und die Spaltensegmente im Columnstore gespeichert werden.
Für jeden gruppierten Columnstore-Index können mehrere Deltastores vorhanden sein.
Wenn ein Deltastore gesperrt ist, versucht SQL Server, eine Sperre für einen anderen Deltastore abzurufen. Wenn keine Deltastores verfügbar sind, erstellen SQL Server einen neuen Deltastore.
Für eine partitionierte Tabelle sind für jede Partition ein oder mehrere Deltastores vorhanden.
Die folgenden Szenarien gelten nur für gruppierte Columnstore-Indizes und veranschaulichen, in welchen Fällen geladene Zeilen direkt in den Columnstore und in welchen Fällen sie in den Deltastore aufgenommen werden.
In diesem Beispiel kann jede Zeilengruppe 102.400 bis 1.048.576 Zeilen pro Zeilengruppe enthalten.
| Zeilen für Massenladevorgang | Dem Columnstore hinzugefügte Zeilen | Dem Deltastore hinzugefügte Zeilen |
|---|---|---|
| 102.000 | 0 | 102.000 |
| 145,000 | 145,000 Zeilengruppengröße: 145,000 |
0 |
| 1,048,577 | 1,048,576 Zeilengruppengröße: 1.048.576 |
1 |
| 2,252,152 | 2,252,152 Zeilengruppengrößen: 1.048.576, 1.048.576, 155.000 |
0 |
Im folgenden Beispiel werden die Ergebnisse des Ladens von 1.048.577 Zeilen in eine Partition angezeigt. Aus den Ergebnissen wird ersichtlich, dass eine COMPRESSED-Zeilengruppe im Columnstore (in Form von komprimierten Spaltensegmenten) und eine Zeile im Deltastore vorhanden sind.
SELECT * FROM sys.column_store_row_groups;
Ändern der Daten in einem gruppierten Columnstore-Index
Gruppierte Columnstore-Indizes unterstützen DML-Vorgänge zum Einfügen, Aktualisieren und Löschen.
Verwenden Sie INSERT (Transact-SQL), um eine Zeile einzufügen. Die Zeile wird dem Deltastore hinzugefügt.
Verwenden Sie DELETE (Transact-SQL), um eine Zeile zu löschen.
Wenn sich die Zeile im Columnstore befindet, markiert SQL Server die Zeile als logisch gelöscht, aber der physische Speicher für die Zeile wird erst wieder abgerufen, wenn der Index neu erstellt wird.
Wenn sich die Zeile im Deltastore befindet, löscht SQL Server die Zeile logisch und physisch.
Verwenden Sie UPDATE (Transact-SQL), um eine Zeile zu aktualisieren.
Wenn sich die Zeile im Columnstore befindet, markiert SQL Server die Zeile als logisch gelöscht und fügt dann die aktualisierte Zeile in den Deltastore ein.
Wenn sich die Zeile im Deltastore befindet, aktualisiert SQL Server die Zeile im deltastore.
Neuerstellen eines gruppierten Columnstore-Indexes
Verwenden Sie CREATE CLUSTERED COLUMNSTORE INDEX (Transact-SQL) oder ALTER INDEX (Transact-SQL), um einen vorhandenen gruppierten Columnstore-Index vollständig neu zu erstellen. Darüber hinaus können Sie ALTER INDEX verwenden ... NEU ERSTELLEN, um eine bestimmte Partition neu zu erstellen.
Neuerstellungsprozess
Um einen gruppierten Columnstore-Index neu zu erstellen, SQL Server:
Abrufen einer exklusiven Sperre für die Tabelle oder Partition, während die Neuerstellung ausgeführt wird. Die Daten sind während der Neuerstellung „offline“ und nicht verfügbar.
Defragmentieren des Columnstore, indem physisch Zeilen gelöscht werden, die logisch aus der Tabelle gelöscht wurden. Die gelöschten Bytes werden auf dem physischen Medium freigegeben.
Führt die Rowstore-Daten im Deltastore mit den Daten im Columnstore zusammen, bevor der Index neu erstellt wird. Nachdem die Neuerstellung abgeschlossen ist, sind alle Daten im Columnstore-Format gespeichert, und der Deltastore ist leer.
Neukomprimierung aller Daten im Columnstore. Während die Neuerstellung ausgeführt wird, gibt es zwei Kopien des columnstore-Indexes. Wenn die Neuerstellung abgeschlossen ist, löscht SQL Server den ursprünglichen Columnstore-Index.
Empfehlungen zum Neuerstellen eines gruppierten Columnstore-Indexes
Das Neuerstellen eines gruppierten Columnstore-Index ist sinnvoll, um Fragmentierungen zu beseitigen und um alle Zeilen in den Columnstore zu verschieben. Berücksichtigen Sie die folgenden Empfehlungen:
Erstellen Sie nur eine Partition neu und nicht die gesamte Tabelle.
Wenn der Index groß ist, nimmt das Neuerstellen der gesamten Tabelle viel Zeit in Anspruch, und es muss ausreichend Speicherplatz verfügbar sein, um während der Neuerstellung eine zusätzliche Kopie des Indexes speichern zu können. In der Regel muss nur die zuletzt verwendete Partition neu erstellt werden.
Bei partitionierten Tabellen müssen Sie nicht den gesamten Columnstore-Index neu erstellen, da die Fragmentierung wahrscheinlich nur in den Partitionen vorliegt, die kürzlich geändert wurden. Faktentabellen und große Dimensionstabellen werden i. d. R. partitioniert, um Sicherungs- und Verwaltungsvorgänge für Segmente der Tabelle auszuführen.
Erstellen Sie eine Partition nach intensiven DML-Vorgängen neu.
Durch das Neuerstellen einer Partition wird die Partition defragmentiert und der benötigte Festplattenspeicherplatz reduziert. Beim Neuerstellen werden alle zum Löschen markierten Zeilen aus dem Columnstore gelöscht und alle Zeilen aus dem Deltastore in den Columnstore verschoben.
Erstellen Sie eine Partition neu, nachdem Sie Daten geladen haben.
Dadurch wird sichergestellt, dass alle Daten im Columnstore gespeichert sind. Wenn mehrere Ladevorgänge zur gleichen Zeit ausgeführt werden, kann dies ggf. dazu führen, dass für jede Partition mehrere Deltastores vorhanden sind. Durch das Neuerstellen werden alle Deltastore-Zeilen in den Columnstore verschoben.
Neuorganisieren eines gruppierten Columnstore-Indexes
Beim Neuorganisieren eines gruppierten Columnstore-Indexes werden alle Closed-Zeilengruppen in den Columnstore-Index verschoben. Um eine Neuorganisation durchzuführen, verwenden Sie ALTER INDEX (Transact-SQL) mit der Option REORGANIZE.
Die Neuorganisation ist für das Verschieben von CLOSED-Zeilengruppen in den Columnstore nicht zwingend erforderlich. Zuletzt werden alle CLOSED-Zeilengruppen vom TM (Tuple Mover)-Vorgang gesucht und verschoben. Da der Tuple Mover jedoch in einem einzelnen Thread ausgeführt wird, werden die Zeilengruppen für Ihre Arbeitsauslastung u. U. nicht schnell genug verschoben.
Empfehlungen zum Neuorganisieren
In folgenden Fällen muss ein gruppierter Columnstore-Index neuorganisiert werden:
- Neuorganisieren Sie einen gruppierten Columnstore-Index nach einem oder mehreren Datenladevorgängen, um so schnell wie möglich die bessere Abfrageleistung nutzbar zu machen. Beim Neuorganisieren sind zunächst zusätzliche CPU-Ressourcen zum Komprimieren der Daten erforderlich. Dies kann die Gesamtleistung des Systems beeinträchtigen. Sobald die Daten jedoch komprimiert sind, kann sich die Abfrageleistung verbessern.