Freigeben über

Leistungsoptimierung mit geordneten gruppierten Columnstore-Indizes

  • Artikel

Gilt für: SQL Server 2022 (16.x) Azure SQL-Datenbank Azure SQL Managed Instance

Durch die Aktivierung einer effizienten Segmententfernung bieten sortierte gruppierte Columnstore-Indizes (CCI) eine wesentlich schnellere Leistung, indem große Mengen sortierter Daten übersprungen werden, die nicht mit dem Abfrage-Prädikat übereinstimmen. Das Laden von Daten in eine geordnete CCI-Tabelle kann aufgrund des Datensortiervorgangs länger als das Laden in eine nicht geordnete CCI-Tabelle dauern. Abfragen können jedoch später mit einer geordneten CCI-Tabelle schneller ausgeführt werden.

Wenn Benutzer eine Columnstore-Tabelle abfragen, überprüft der Optimierer die in den einzelnen Segmenten gespeicherten Mindest- und Höchstwerte. Segmente, die sich außerhalb der Grenzen des Abfrageprädikats befinden, werden nicht vom Datenträger in den Arbeitsspeicher gelesen. Eine Abfrage kann schneller abgeschlossen werden, wenn die Anzahl der zu lesenden Segmente und deren Gesamtgröße gering ist.

Informationen zur Verfügbarkeit des sortierten Spaltenspeicherindexes finden Sie unter Verfügbarkeit des geordneten Spaltenindexes.

Vergleich sortierter und nicht sortierter gruppierter Columnstore-Indizes

Standardmäßig erstellt eine interne Komponente (Index-Generator) für jede Tabelle, die ohne Indexoption erstellt wurde, einen nicht geordneten gruppierten Columnstore-Index (CCI). Die Daten den einzelnen Spalten werden in einem separaten CCI-Zeilengruppensegment komprimiert. Für den Wertebereich jedes Segments gibt es Metadaten, sodass Segmente, die sich außerhalb der Grenzen des Abfrageprädikats befinden, während der Abfrageausführung nicht von der Festplatte gelesen werden. CCI bietet den höchsten Grad an Datenkomprimierung und verringert die Größe der zu lesenden Segmente, damit Abfragen schneller ausgeführt werden. Da der Index-Generator die Daten jedoch nicht sortiert, bevor er sie in Segmente komprimiert, können Segmente mit überlappenden Wertebereichen auftreten, was dazu führt, dass Abfragen mehr Segmente vom Datenträger lesen muss und die Fertigstellung länger dauert.

Beim Erstellen einer sortierten CCI sortiert das SQL-Datenbank Modul die vorhandenen Daten im Arbeitsspeicher nach den Orderschlüsseln, bevor der Index-Generator sie in Indexsegmente komprimiert. Bei sortierten Daten wird die Überlappung von Segmenten verringert, sodass Abfragen Segmente effizienter entfernen können und somit eine schnellere Leistung aufweisen, da die Anzahl der Segmente, die vom Datenträger gelesen werden sollen, kleiner ist. Die Überlappung von Segmenten lässt sich vermeiden, wenn alle Daten im Arbeitsspeicher gleichzeitig sortiert werden. Aufgrund großer Tabellen in Data Warehouses kommt dieses Szenario nicht häufig vor.

Wenn Sie die Segmentbereiche für eine Spalte überprüfen möchten, führen Sie den folgenden Befehl unter Angabe Ihres Tabellen- und Spaltennamens aus:

SELECT
o.name, pnp.index_id,
cls.row_count, pnp.data_compression_desc,
cls.segment_id,
cls.column_id,
cls.min_data_id, cls.max_data_id,
cls.max_data_id-cls.min_data_id as difference 

FROM sys.partitions AS pnp
 INNER JOIN sys.tables AS t ON pnp.object_id = t.object_id 
 INNER JOIN sys.objects AS o ON t.object_id = o.object_id
 INNER JOIN sys.column_store_segments AS cls ON pnp.partition_id = cls.partition_id
 INNER JOIN sys.columns as cols ON o.object_id = cols.object_id AND cls.column_id = cols.column_id
WHERE o.name = '<Table Name>' and cols.name = '<Column Name>'  
ORDER BY o.name, pnp.index_id, cls.min_data_id;

Hinweis

In einer sortierten CCI-Tabelle werden die neuen Daten aus dem gleichen Batch von DML- oder Datenladevorgängen innerhalb dieses Batches sortiert, es findet jedoch keine globale Sortierung aller Daten in der Tabelle statt. Benutzer können die geordnete CCI-Tabelle neu erstellen (REBUILD), um alle Daten in der Tabelle zu sortieren. Bei einer partitionierten Tabelle erfolgt die Neuerstellung (REBUILD) der Partitionen nacheinander. Die Daten in der Partition, die neu erstellt wird, sind „offline“ und nicht verfügbar, bis die Neuerstellung (REBUILD) für diese Partition beendet ist.

Abfrageleistung

Der Leistungsgewinn einer Abfrage aus einer geordneten CCI-Tabelle hängt von den Abfragemustern, der Größe der Daten, der Art der Sortierung der Daten, der physischen Struktur von Segmenten und der DWU und Ressourcenklasse ab, die für die Ausführung der Abfrage ausgewählt wurden. Benutzer sollten alle diese Faktoren überprüfen, bevor Sie die Ordnungsspalten beim Entwerfen einer geordneten CCI-Tabelle auswählen.

Abfragen mit all diesen Mustern werden normalerweise schneller mit einer geordneten CCI-Tabelle ausgeführt.

  • Die Abfragen verfügen über Gleichheits-, Ungleichheits- oder Bereichsprädikate.
  • Die Prädikatspalten und die geordneten CCI-Spalten sind identisch.

In diesem Beispiel hat die Tabelle T1 einen gruppierten Spaltenspeicherindex in der Reihenfolge von Col_C, Col_Bund .Col_A

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON  T1
ORDER (Col_C, Col_B, Col_A);

Für Abfrage 1 und Abfrage 2 können sortierte CCI-Daten einen größeren Leistungsvorteil bieten als für andere Abfragen, da sie auf alle sortierten CCI-Spalten verweisen.

-- Query #1:

SELECT * FROM T1 WHERE Col_C = 'c' AND Col_B = 'b' AND Col_A = 'a';

-- Query #2

SELECT * FROM T1 WHERE Col_B = 'b' AND Col_C = 'c' AND Col_A = 'a';

-- Query #3
SELECT * FROM T1 WHERE Col_B = 'b' AND Col_A = 'a';

-- Query #4
SELECT * FROM T1 WHERE Col_A = 'a' AND Col_C = 'c';

Leistung beim Laden von Daten

Die Leistung beim Laden von Daten in eine sortierte CCI-Tabelle ähnelt dem Laden einer partitionierten Tabelle. Das Laden von Daten in eine geordnete CCI-Tabelle kann aufgrund des Datensortiervorgangs länger als das Laden in eine nicht geordnete CCI-Tabelle dauern. Abfragen können jedoch später mit einer geordneten CCI-Tabelle schneller ausgeführt werden.

Verringern der Überlappung von Segmenten

Die Anzahl der sich überlappenden Segmente hängt von der Größe der zu sortierenden Daten, dem verfügbaren Arbeitsspeicher und der Einstellung des maximalen Parallelitätsgrads (MAXDOP) bei der Erstellung einer geordneten CCI-Tabelle ab. Die folgenden Strategien reduzieren Segmentüberschneidungen beim Erstellen sortierter CCI.

  • Erstellen Sie eine sortierte CCI-Tabelle mit OPTION (MAXDOP = 1). Jeder Thread, der für die Erstellung eines sortierten CCI verwendet wird, verarbeitet eine Teilmenge an Daten und sortiert diese lokal. Für Daten, die von unterschiedlichen Threads sortiert wurden, gibt es keine globale Sortierung. Mithilfe paralleler Threads kann die Zeit zum Erstellen eines sortierten CCI verkürzt werden. Dabei werden jedoch mehr überlappende Segmente generiert als bei der Verwendung eines einzelnen Threads. Die Verwendung eines einzelnen Threads für einen Vorgang liefert die höchste Komprimierungsqualität. Sie können MAXDOP mit den CREATE INDEX Befehlen oder CREATE TABLE Befehlen angeben. Zum Beispiel:
CREATE TABLE Table1 WITH (DISTRIBUTION = HASH(c1), CLUSTERED COLUMNSTORE INDEX ORDER(c1) )
AS SELECT * FROM ExampleTable
OPTION (MAXDOP 1);
  • Sortieren Sie die Daten vorab nach den Sortierschlüsseln, bevor Sie sie in Tabellen laden.

Im Folgenden finden Sie ein Beispiel für die Verteilung einer sortierten CCI-Tabelle ohne Segmentüberlappungen, bei der die oben genannten Empfehlungen beachtet wurden. Die sortierte CCI wird auf einer großen Spalte ohne Duplikate sortiert.

Screenshot von Textdaten ohne Segmentüberlappung.

Erstellen eines sortierten CCI für große Tabellen

Das Erstellen eines sortierten CCI ist ein Offlinevorgang. Bei Tabellen ohne Partitionen sind die Daten für Benutzer erst verfügbar, wenn der Erstellungsprozess des sortierten CCI abgeschlossen ist. Da die Engine sortierte CCI-Partitionen für partitionierte Tabellen nach Partition erstellt, können die Benutzer auf die Daten in den Partitionen weiterhin zugreifen, für die die Erstellung des sortierten CCI nicht gerade erfolgt. Mit dieser Option können Sie die Ausfallzeiten beim Erstellen eines sortierten CCI für große Tabellen minimieren:

  1. Erstellen Sie Partitionen für die große Zieltabelle (Table_A).
  2. Erstellen Sie eine leere sortierte CCI-Tabelle (Table_B), die dasselbe Tabellen- und Partitionsschema wie Table_A aufweist.
  3. Fügen Sie eine Partition von Table_A in Table_B ein.
  4. Führen Sie ALTER INDEX <Ordered_CCI_Index> ON <Table_B> REBUILD PARTITION = <Partition_ID> für Table_B aus, um die eingefügte Partition neu zu erstellen.
  5. Wiederholen Sie die Schritte 3 und 4 für jede Partition in Table_A.
  6. Nachdem alle Partitionen von Table_A in Table_B eingefügt und neu erstellt wurden, löschen Sie Table_A, und benennen Sie Table_B in Table_A um.

SQL Server 2022-Funktionen

In SQL Server 2022 (16.x) wurden sortierte gruppierte Columnstore-Indizes eingeführt, die mit dem Feature in dedizierten SQL-Pools in Azure Synapse vergleichbar sind.

  • SQL Server 2022 (16.x) und höhere Versionen und andere SQL-Plattformen unterstützen erweiterte Segmentlöschfunktionen für Zeichenfolgen-, Binär- und GUID-Datentypen sowie den Datentyp "datetimeoffset " für die Skalierung größer als zwei. Bisher gilt diese Segmentlöschung für numerische Datentypen, Datums- und Uhrzeitdatentypen und den Datentyp "datetimeoffset " mit einer Skalierung kleiner oder gleich zwei.
  • Derzeit unterstützen nur SQL Server 2022 (16.x) und höhere Versionen und andere SQL-Plattformen die Eliminierung gruppierter Spaltenspeicherzeilengruppen für das Prädikat, LIKE z. B column LIKE 'string%'. . Die Segmenteliminierung wird für die Verwendung von LIKE ohne Präfix wie z. B. column LIKE '%string' nicht unterstützt.

Informationen zur Verfügbarkeit des sortierten Spaltenspeicherindexes finden Sie unter Verfügbarkeit des geordneten Spaltenindexes.

Weitere Informationen finden Sie unter Neuerungen in Columnstore-Indizes.

Informationen zu sortierten Columnstore-Indizes in dedizierten SQL-Pools in Azure Synapse Analytics finden Sie unter Leistungsoptimierung mit sortierten gruppierten Columnstore-Indizes.

Beispiele

A. So suchen Sie sortierte Spalten und die Ordnungszahl der Sortierung:

SELECT object_name(c.object_id) table_name, c.name column_name, i.column_store_order_ordinal
FROM sys.index_columns i
JOIN sys.columns c ON i.object_id = c.object_id AND c.column_id = i.column_id
WHERE column_store_order_ordinal <>0;

B. Fügen Sie Spalten aus der Sortierliste hinzu, oder entfernen Sie sie, wenn Sie die Ordnungszahl der Spalte ändern möchten oder von einem CCI zu einem sortierten CCI wechseln möchten:

CREATE CLUSTERED COLUMNSTORE INDEX InternetSales ON dbo.InternetSales
ORDER (ProductKey, SalesAmount)
WITH (DROP_EXISTING = ON);

Zugehöriger Inhalt