Freigeben über

Leistungsoptimierung mit sortierten Columnstore-Indizes

  • Artikel

Gilt für: SQL Server 2022 (16.x) Azure SQL-DatenbankAzure SQL Managed InstanceSQL-Datenbank in Microsoft Fabric

Durch die Aktivierung einer effizienten Segmentlöschung bieten sortierte Columnstore-Indizes eine schnellere Leistung, indem große Mengen sortierter Daten übersprungen werden, die nicht mit dem Abfrage-Prädikat übereinstimmen. Das Laden von Daten in einen geordneten Columnstore-Index und das Beibehalten dieser Ordnung durch Indexneuerstellungen kann aufgrund des Datensortierungsvorgangs länger dauern als bei einem nicht sortierten Index; jedoch können bei geordneten Spaltenspeicherindizes Abfragen anschließend 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 kleiner sind.

Weitere Informationen zur Verfügbarkeit von sortierten Columnstore-Indizes finden Sie unter Verfügbarkeit von sortierten Columnstore-Indizes.

Weitere Informationen zu kürzlich hinzugefügten Features für Spaltenspeicherindizes finden Sie unter Neuerungen in Columnstore-Indizes.

Sortierter Index im Vergleich zu nicht sortierten Spaltenspeichern

In einem Spaltenspeicherindex werden Daten in jeder Spalte jeder Zeilengruppe in einem separaten Segment komprimiert. Jedes Segment enthält Metadaten, die seine Mindest- und Höchstwerte beschreiben, sodass Segmente, die sich außerhalb der Grenzen des Abfragededikats befinden, während der Abfrageausführung nicht vom Datenträger gelesen werden.

Wenn ein Spaltenspeicherindex nicht sortiert ist, sortiert der Index-Generator die Daten nicht, bevor er in Segmente komprimiert wird. Das bedeutet, dass Segmente mit überlappenden Wertbereichen auftreten können, was dazu führt, dass Abfragen mehr Segmente vom Datenträger lesen und länger dauern.

Wenn Sie einen sortierten Spaltenspeicherindex erstellen, sortiert das Datenbankmodul die vorhandenen Daten nach den von Ihnen angegebenen Orderschlüsseln, bevor der Index-Generator sie in Segmente komprimiert. Bei sortierten Daten wird die Segmentüberschneidung reduziert oder eliminiert, sodass Abfragen eine effizientere Segment eliminierung und damit eine schnellere Leistung erzielen können, da weniger Segmente vom Datenträger gelesen werden können.

Abhängig vom verfügbaren Arbeitsspeicher, der Datengröße, dem Grad der Parallelität, dem Indextyp (gruppiert oder nicht gruppiert) und dem Typ des Indexbuilds (offline vs. online) kann die Sortierung für sortierte Spaltenspeicherindizes voll (keine Segmentüberlappung) oder teilweise (einige Segmentüberlappungen) sein. Beispielsweise tritt eine partielle Sortierung auf, wenn der verfügbare Arbeitsspeicher für eine vollständige Sortierung nicht ausreicht. Abfragen, die einen sortierten Columnstore-Index verwenden, werden häufig schneller ausgeführt als bei einem nicht sortierten Index, auch wenn der sortierte Index mit einer teilweisen Sortierung erstellt wurde.

Die vollständige Sortierung wird für sortierte gruppierte Spaltenspeicherindizes bereitgestellt, die sowohl mit ONLINE = ON als auch mit MAXDOP = 1 Optionen erstellt oder neu erstellt wurden. In diesem Fall ist die Sortierung nicht durch den verfügbaren Arbeitsspeicher beschränkt, da die tempdb-Datenbank verwendet wird, um die Daten auszulagern, die nicht in den Arbeitsspeicher passen. Dies kann dazu führen, dass der Indexaufbauprozess aufgrund der zusätzlichen tempdb E/A langsamer wird. Bei einer Onlineindexerstellung können Abfragen jedoch weiterhin den vorhandenen Index verwenden, während der neue sortierte Index neu erstellt wird.

Eine vollständige Sortierung kann auch für sortierte gruppierte und nicht gruppierte Columnstore-Indizes bereitgestellt werden, die sowohl mit ONLINE = OFF als auch mit MAXDOP = 1 Optionen erstellt oder neu erstellt werden, wenn die zu sortierende Datenmenge ausreichend klein ist, um vollständig in den verfügbaren Arbeitsspeicher zu passen.

In allen anderen Fällen ist die Sortierung in geordneten Spaltenspeicherindizes teilweise.

Hinweis

Derzeit können sortierte Columnstore-Indizes nur in Azure SQL-Datenbank und in Azure SQL Managed Instance mit der Updaterichtlinie Immer auf dem neuesten Stand erstellt oder neu erstellt werden.

Um die Segmentbereiche für eine Spalte zu überprüfen und festzustellen, ob es eine Segmentüberlappung gibt, verwenden Sie die folgende Abfrage und ersetzen Sie dabei die Platzhalter durch Ihr Schema sowie Ihre Tabellen- und Spaltennamen.

SELECT OBJECT_SCHEMA_NAME(o.object_id) AS schema_name,
       o.name AS table_name,
       cols.name AS column_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
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 OBJECT_SCHEMA_NAME(o.object_id) = '<Schema Name>'
      AND
      o.name = '<Table Name>'
      AND
      cols.name = '<Column Name>'
ORDER BY o.name, pnp.index_id, cls.min_data_id;

Die Ausgabe aus dieser Abfrage für einen vollständig sortierten Spaltenspeicherindex könnte z. B. wie folgt aussehen. Beachten Sie, dass es keine Überlappungen in den min_data_id und max_data_id Spalten für verschiedene Segmente gibt.

schema_name table_name column_name index_id row_count data_compression_desc segment_id column_id min_data_id max_data_id
----------- ---------- ----------- -------- --------- --------------------- ---------- --------- ----------- -----------
dbo         Table1     Column1     1        479779    COLUMNSTORE           0          1         -17         1469515
dbo         Table1     Column1     1        887658    COLUMNSTORE           1          1         1469516     2188146
dbo         Table1     Column1     1        930144    COLUMNSTORE           2          1         2188147     11072928

Hinweis

In einem sortierten Columnstore-Index werden die neuen Daten, die sich aus demselben Batch von DML- oder Datenladevorgängen ergeben, nur innerhalb dieses Batches sortiert. Es gibt keine globale Sortierung, die vorhandene Daten in der Tabelle enthält.

Um Daten im Index nach dem Einfügen neuer Daten zu sortieren oder vorhandene Daten zu aktualisieren, erstellen Sie den Index neu.

Bei einem Offline-Wiederaufbau eines partitionierten Columnstore-Indexes erfolgt der Wiederaufbau Partition für Partition. Die Daten in der neu erstellten Partition sind nicht verfügbar, bis die Neuerstellung für diese Partition abgeschlossen ist.

Daten bleiben während einer Online-Neuerstellung verfügbar. Weitere Informationen finden Sie unter Ausführen von Indexvorgängen online.

Abfrageleistung

Der Leistungsgewinn aus einem sortierten Columnstore-Index hängt von den Abfragemustern, der Größe der Daten, der Sortierung der Daten, der physischen Struktur von Segmenten und den für die Abfrageausführung verfügbaren Computeressourcen ab.

Abfragen mit den folgenden Mustern werden in der Regel schneller mit geordneten Spaltenspeicherindizes ausgeführt.

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

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);

Die Leistung von Abfrage 1 und 2 kann von sortierten Spaltenspeicherindex mehr als Abfrage 3 und 4 profitieren, da sie auf alle sortierten 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 des Ladens von Daten in eine Tabelle mit einem sortierten Spaltenspeicherindex ähnelt einer partitionierten Tabelle. Das Laden von Daten kann länger dauern als bei einem nicht sortierten Columnstore-Index aufgrund des Datensortierungsvorgangs. Abfragen können jedoch später schneller ausgeführt werden.

Verringern der Überlappung von Segmenten

Die Anzahl der überlappenden Segmente hängt von der Größe der zu sortierenden Daten, dem verfügbaren Speicher und dem maximalen Grad der Parallelität (MAXDOP) beim Aufbau eines geordneten Columnstore-Index ab. Die folgenden Strategien reduzieren Segmentüberlappungen, können jedoch den Indexerstellungsprozess verlängern.

  • Wenn die Onlineindexerstellung verfügbar ist, verwenden Sie sowohl die ONLINE = ON- als auch die MAXDOP = 1-Optionen, um einen angeordneten gruppierten Columnstore-Index zu erstellen. Dadurch wird ein vollständig sortierter Index erstellt.
  • Wenn der Onlineindexbuild nicht verfügbar ist, verwenden Sie die Option MAXDOP = 1.
  • Sortieren Sie die Daten vor dem Laden nach den Sortierschlüsseln vor.

Wenn MAXDOP größer als 1 ist, arbeitet jeder Thread, der für den geordneten Aufbau von Spaltenspeicherindizes verwendet wird, an einer Teilmenge der Daten und sortiert sie lokal. Für Daten, die von unterschiedlichen Threads sortiert wurden, gibt es keine globale Sortierung. Die Verwendung paralleler Threads kann die Zeit zum Erstellen des Indexes reduzieren, generiert jedoch mehr überlappende Segmente als bei Verwendung eines einzelnen Threads. Die Verwendung eines einzelnen Threads für einen Vorgang liefert die höchste Komprimierungsqualität. Sie können MAXDOP mit dem Befehl CREATE INDEX angeben.

Beispiele

Suchen Sie nach sortierten Spalten und der Ordnungszahl der Sortierung.

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

Erstellen eines geordneten Spaltenspeicherindexes

Gruppierter sortierter Columnstore-Index:

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1, Column2);

Nicht gruppierter sortierter Columnstore-Index:

CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1
(
Column1, Column2, Column3
)
ORDER (Column1, Column2);

Hinzufügen oder Entfernen von Bestellspalten und Neuerstellen eines vorhandenen geordneten Spaltenspeicherindex

Gruppierter sortierter Columnstore-Index:

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1, Column2)
WITH (DROP_EXISTING = ON);

Nicht gruppierter sortierter Columnstore-Index:

CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1
(
Column1, Column2, Column3
)
ORDER (Column1, Column2)
WITH (DROP_EXISTING = ON);

Erstellen eines sortierten gruppierten Columnstore-Index online mit vollständiger Sortierung in einer Heap-Tabelle

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1)
WITH (ONLINE = ON, MAXDOP = 1);

Neuerstellen eines sortierten gruppierten Columnstore-Index online mit vollständiger Sortierung

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1)
WITH (DROP_EXISTING = ON, ONLINE = ON, MAXDOP = 1);

Zugehöriger Inhalt