Teilen über

Leistungsoptimierung mit sortierten Columnstore-Indizes

Gilt für: SQL Server 2022 (16.x) und höhere Versionen Azure SQL DatabaseAzure SQL Managed InstanceSQL-Datenbank in Microsoft Fabric

Sortierte Columnstore-Indizes können eine schnellere Leistung bieten, indem große Mengen sortierter Daten übersprungen werden, die nicht mit dem Abfrage-Prädikat übereinstimmen. Während das Laden von Daten in einen geordneten Columnstore-Index und die Aufrechterhaltung der Reihenfolge über die Indexerstellung länger dauert als in einem nicht sortierten Index, können indizierte Abfragen schneller mit sortiertem Spaltenspeicher ausgeführt werden.

Wenn eine Abfrage einen Columnstore-Index liest, überprüft das Datenbankmodul die minimalen und maximalen Werte, die in den einzelnen Spaltensegmenten gespeichert sind. Der Prozess entfernt Segmente, die außerhalb der Grenzen des Abfrage-Prädikats liegen. Mit anderen Worten, sie überspringt diese Segmente beim Lesen von Daten vom Datenträger oder Arbeitsspeicher. Eine Abfrage wird schneller abgeschlossen, wenn die Anzahl der zu lesenden Segmente und deren Gesamtgröße deutlich kleiner ist.

Bei bestimmten Datenlademustern sind Daten in einem Spaltenspeicherindex möglicherweise bereits sortiert. Wenn daten beispielsweise täglich geladen werden, werden die Daten möglicherweise nach einer load_date Spalte sortiert. In diesem Fall kann die Abfrageleistung bereits von dieser impliziten Reihenfolge profitieren. Das Explizite Sortieren des Spaltenspeicherindexes nach derselben load_date Spalte ist wahrscheinlich kein zusätzlicher Leistungsvorteil.

Die Verfügbarkeit des sortierten Columnstore-Indexes in verschiedenen SQL-Plattformen und SQL Server-Versionen finden Sie unter Verfügbarkeit des sortierten Spaltenspeicherindexes.

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 die Daten in jeder Spalte jeder Zeilengruppe in einem separaten Segment komprimiert. Jedes Segment enthält Metadaten, die seine Mindest- und Höchstwerte beschreiben, sodass der Abfrageausführungsprozess Segmente überspringen kann, die außerhalb der Grenzen des Abfrage-Prädikats liegen.

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 weitere Segmente lesen, um die erforderlichen Daten abzurufen. Daher kann der Abschluss von Abfragen 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 und weniger Daten gelesen werden können.

Segmentüberlappung reduzieren

Wenn Sie einen sortierten Columnstore-Index erstellen, sortiert das Datenbankmodul die Daten nach bestem Aufwand. 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) ist die Sortierung für sortierte Spaltenspeicherindizes möglicherweise voll ohne Segmentüberlappung oder teilweise mit einigen Segmentüberlappungen.

In der folgenden Tabelle wird die resultierende Sortierreihenfolge beschrieben, wenn Sie einen geordneten Columnstore-Index erstellen oder neu erstellen, abhängig von den Indexbuildoptionen.

Voraussetzungen Sortierart
ONLINE = ON und MAXDOP = 1 Alles
ONLINE = OFF, MAXDOP = 1, und die zu sortierenden Daten passen vollständig in den Speicher des Abfragearbeitsbereichs. Alles
Alle anderen Fälle Partial

Wenn sowohl ONLINE = ON als auch MAXDOP = 1 im ersten Fall, ist die Sortierung nicht durch den Arbeitsspeicher des Abfragearbeitsbereichs beschränkt, da ein Onlineaufbau eines sortierten Columnstore-Index die tempdb-Datenbank verwendet, um Daten auszulagern, die nicht in den Arbeitsspeicher passen. Durch diesen Ansatz kann der Indexerstellungsprozess aufgrund der zusätzlichen tempdb E/A verlangsamt werden. Da der Indexbuild jedoch online ausgeführt wird, können Abfragen weiterhin den vorhandenen Index verwenden, während der neue sortierte Index erstellt wird.

Ebenso wird bei einer Offline-Neuerstellung eines partitionierten Columnstore-Indexes eine Partition nach der anderen rekonstruiert. Andere Partitionen bleiben für Abfragen verfügbar.

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, führt jedoch zu überlappenden Segmenten als bei Verwendung eines einzelnen Threads.

Tipp

Auch wenn die Sortierung in einem sortierten Columnstore-Index teilweise ist, können Segmente weiterhin eliminiert werden (übersprungen). Eine vollständige Sortierung ist nicht erforderlich, um Vorteile der Abfrageleistung zu erzielen, wenn eine partielle Sortierung viele Segmentüberlappungen vermeidet.

Informationen zur Anzahl der überlappenden und nicht überlappenden Segmente in einem sortierten Columnstore-Index finden Sie unter " Ermitteln der Sortierqualität für ein geordnetes Columnstore-Indexbeispiel ".

Sie können geordnete Spaltenspeicherindizes nur auf einigen SQL-Plattformen und SQL Server-Versionen online erstellen oder neu erstellen. Weitere Informationen finden Sie in der Funktionsübersicht für Produktversionen.

In SQL Server sind Onlineindexvorgänge in allen Editionen nicht verfügbar. Weitere Informationen finden Sie unter Editionen und unterstützte Features von SQL Server 2025 und Ausführen von Indexvorgängen online.

Hinzufügen neuer Daten oder Aktualisieren vorhandener Daten

Die neuen Daten, die sich aus einem DML-Batch oder einem Massenladevorgang für einen sortierten Spaltenspeicherindex ergeben, werden nur innerhalb dieses Batches sortiert. Es gibt keine globale Sortierung, die vorhandene Daten in der Tabelle enthält. Um Segmentüberlappungen nach dem Einfügen neuer Daten oder aktualisieren vorhandener Daten zu reduzieren, erstellen Sie den Index neu.

Abfrageleistung

Der Leistungsgewinn aus einem sortierten Columnstore-Index hängt von den Abfragemustern, der Größe von Daten, der Sortierqualität 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 eines Datenladevorgangs 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, aber Abfragen können danach schneller ausgeführt werden.

Examples

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

Suchen Sie nach sortierten Spalten und der Ordnungszahl der Sortierung.

SELECT OBJECT_SCHEMA_NAME(c.object_id) AS schema_name,
       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;

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

Ermitteln der Sortierqualität für einen sortierten Spaltenspeicherindex

In diesem Beispiel wird die Sortierqualität für alle sortierten Spaltenspeicherindizes in der Datenbank bestimmt. In diesem Beispiel wird die Sortierqualität als Verhältnis von nicht überlappenden Segmenten zu allen Segmenten für jede Bestellspalte definiert, ausgedrückt als Prozentsatz.

WITH ordered_column_segment
AS (SELECT p.object_id,
           i.name AS index_name,
           ic.column_store_order_ordinal,
           cls.row_count,
           cls.column_id,
           cls.min_data_id,
           cls.max_data_id,
           LAG(max_data_id) OVER (
               PARTITION BY cls.partition_id, ic.column_store_order_ordinal
               ORDER BY cls.min_data_id
           ) AS prev_max_data_id,
           LEAD(min_data_id) OVER (
               PARTITION BY cls.partition_id, ic.column_store_order_ordinal
               ORDER BY cls.min_data_id
           ) AS next_min_data_id
    FROM sys.partitions AS p
         INNER JOIN sys.indexes AS i
             ON p.object_id = i.object_id
            AND p.index_id = i.index_id
         INNER JOIN sys.column_store_segments AS cls
             ON p.partition_id = cls.partition_id
         INNER JOIN sys.index_columns AS ic
             ON ic.object_id = p.object_id
            AND ic.index_id = p.index_id
            AND ic.column_id = cls.column_id
    WHERE ic.column_store_order_ordinal > 0)
SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name,
       OBJECT_NAME(object_id) AS object_name,
       index_name,
       INDEXPROPERTY(object_id, index_name, 'IsClustered') AS is_clustered_column_store,
       COL_NAME(object_id, column_id) AS order_column_name,
       column_store_order_ordinal,
       SUM(row_count) AS row_count,
       SUM(is_overlapping_segment) AS overlapping_segments,
       COUNT(1) AS total_segments,
       (1 - SUM(is_overlapping_segment) / COUNT(1)) * 100 AS order_quality_percent
FROM ordered_column_segment
CROSS APPLY (SELECT CAST (IIF (prev_max_data_id > min_data_id
                 OR next_min_data_id < max_data_id, 1, 0) AS FLOAT) AS is_overlapping_segment
            ) AS ios
GROUP BY object_id, index_name, column_id, column_store_order_ordinal
ORDER BY schema_name, object_name, index_name, column_store_order_ordinal;

Verwandte Inhalte

  • Last updated on