Maximieren der Zeilengruppenqualität für die Leistung des Spaltenspeicherindexes

Die Zeilengruppenqualität wird durch die Anzahl der Zeilen in einer Zeilengruppe bestimmt. Das Erhöhen des verfügbaren Arbeitsspeichers kann die Anzahl der Zeilen maximieren, die ein Columnstore-Index in jeder Zeilengruppe komprimiert. Verwenden Sie diese Methoden, um komprimierungsraten und Abfrageleistung für Spaltenspeicherindizes zu verbessern.

Warum die Größe der Zeilengruppe wichtig ist

Da ein Columnstore-Index eine Tabelle durch Scannen von Spaltensegmenten einzelner Zeilengruppen durchsucht, verbessert die Maximierung der Anzahl von Zeilen in jeder Zeilengruppe die Abfrageleistung. Wenn Zeilengruppen eine hohe Anzahl von Zeilen aufweisen, verbessert die Datenkomprimierung dies, was bedeutet, dass weniger Daten vom Datenträger gelesen werden können.

Weitere Informationen zu Zeilengruppen finden Sie im Columnstore Indexes Guide.For more information about rowgroups, see Columnstore Indexes Guide.

Zielgröße für Zeilengruppen

Um eine optimale Abfrageleistung zu erzielen, besteht das Ziel darin, die Anzahl der Zeilen pro Zeilengruppe in einem Spaltenspeicherindex zu maximieren. Eine Zeilengruppe kann maximal 1.048.576 Zeilen enthalten. Es ist in Ordnung, nicht die maximale Anzahl von Zeilen pro Zeilengruppe zu haben. Columnstore-Indizes erzielen eine gute Leistung, wenn Zeilengruppen mindestens 100.000 Zeilen aufweisen.

Zeilengruppen können während der Komprimierung gekürzt werden.

Während eines Massenladens oder der Neuerstellung eines Spaltenspeicherindex steht manchmal nicht genügend Arbeitsspeicher zur Verfügung, um alle Zeilen zu komprimieren, die für die jeweiligen Zeilengruppen bestimmt sind. Bei Arbeitsspeicherdruck verkleinern Columnstore-Indizes die Zeilengruppengrößen, damit die Komprimierung in den Columnstore erfolgreich sein kann.

Wenn nicht genügend Arbeitsspeicher vorhanden ist, um mindestens 10.000 Zeilen in jede Zeilengruppe zu komprimieren, wird ein Fehler generiert.

Weitere Informationen zum Massenladen finden Sie unter "Massenladen in einen gruppierten Spaltenspeicherindex".

So überwachen Sie die Qualität von Zeilengruppen

Die dynamische Verwaltungsansicht (DYNAMIC Management View, DMV) (sys.dm_db_column_store_row_group_physical_stats enthält die Ansichtsdefinition, die SQL DB abgleicht), die nützliche Informationen verfügbar macht, z. B. die Anzahl von Zeilen in Zeilengruppen und den Grund für das Kürzen, wenn eine Kürzung erfolgte. Sie können die folgende Sicht als eine praktische Möglichkeit zum Abfragen dieser DMV erstellen, um Informationen zur Kürzung von Zeilengruppen abzurufen.

CREATE VIEW dbo.vCS_rg_physical_stats
AS
WITH cte
AS
(
select   tb.[name]                    AS [logical_table_name]
,        rg.[row_group_id]            AS [row_group_id]
,        rg.[state]                   AS [state]
,        rg.[state_desc]              AS [state_desc]
,        rg.[total_rows]              AS [total_rows]
,        rg.[trim_reason_desc]        AS trim_reason_desc
,        mp.[physical_name]           AS physical_name
FROM    sys.[schemas] sm
JOIN    sys.[tables] tb               ON  sm.[schema_id]          = tb.[schema_id]
JOIN    sys.[pdw_table_mappings] mp   ON  tb.[object_id]          = mp.[object_id]
JOIN    sys.[pdw_nodes_tables] nt     ON  nt.[name]               = mp.[physical_name]
JOIN    sys.[dm_pdw_nodes_db_column_store_row_group_physical_stats] rg      ON  rg.[object_id]     = nt.[object_id]
                                                                            AND rg.[pdw_node_id]   = nt.[pdw_node_id]
                                        AND rg.[distribution_id]    = nt.[distribution_id]
)
SELECT *
FROM cte;

Die trim_reason_desc Spalte gibt an, ob die Zeilengruppe gekürzt wurde (trim_reason_desc = NO_TRIM impliziert, dass es keine Kürzung gab und die Zeilengruppe von optimaler Qualität ist). Die folgenden Begründungen für eine Kürzung geben eine vorzeitige Kürzung der Zeilengruppe an:

• BULKLOAD: Dieser Grund für eine Kürzung wird verwendet, wenn der eingehende Zeilenbatch für den Ladevorgang weniger als 1 Million Zeilen umfasst hat. Die Datenbank-Engine erstellt komprimierte Zeilengruppen, wenn mehr als 100.000 Zeilen eingefügt werden (statt sie im Deltaspeicher einzufügen), der Grund für die Kürzung wird jedoch auf BULKLOAD festgelegt. In diesem Szenario soll der Batchladevorgang so erhöht werden, dass mehr Zeilen einbezogen werden. Überprüfen Sie außerdem das Partitionierungsschema neu, um sicherzustellen, dass es nicht zu präzise ist, da Zeilengruppen keine Partitionsgrenzen überschreiten können.
• MEMORY_LIMITATION: Um Zeilengruppen mit 1 Millionen Zeilen zu erstellen, wird vom Modul ein bestimmter Arbeitsspeicher benötigt. Wenn der verfügbare Arbeits­speicher der Lade­sitzung kleiner als der erforderliche Arbeits­speicher­bedarf ist, werden Zeilen­gruppen vorzeitig gekürzt. In den folgenden Abschnitten wird erläutert, wie Sie den erforderlichen Arbeitsspeicher schätzen und mehr Arbeitsspeicher zuweisen.
• DICTIONARY_SIZE: Dieser Grund für eine Kürzung gibt an, dass die Zeilengruppenkürzung aufgetreten ist, weil mindestens eine Zeichenfolgenspalte mit breiten Zeichenfolgen oder Zeichenfolgen mit hoher Statusanzahl vorhanden war. Die Wörterbuchgröße ist auf 16 MB im Arbeitsspeicher begrenzt, und sobald dieser Grenzwert erreicht ist, wird die Zeilengruppe komprimiert. Wenn diese Situation zutrifft, sollten Sie die problematische Spalte in eine separate Tabelle isolieren.

Wie man die Speicheranforderungen schätzt

Der maximal erforderliche Arbeitsspeicher zum Komprimieren einer Zeilengruppe ist ungefähr wie folgt:

• 72 MB +
• #rows * #columns * 8 Bytes +
• #rows * #short-string-columns * 32 Bytes +
• #long-string-columns * 16 MB für Kompressionswörterbuch

Lange Zeichenfolgen werden mit einer Komprimierungsmethode komprimiert, die zum Komprimieren von Text konzipiert ist. Diese Komprimierungsmethode verwendet ein Wörterbuch zum Speichern von Textmustern. Die maximale Größe eines Wörterbuchs beträgt 16 MB. Es gibt nur ein Wörterbuch für jede lange Zeichenketten-Spalte in der Zeilengruppe.

Möglichkeiten zur Reduzierung der Speicheranforderungen

Verwenden Sie die folgenden Techniken, um die Speicheranforderungen zum Komprimieren von Zeilengruppen in Spaltenspeicherindizes zu reduzieren.

Weniger Spalten verwenden

Entwerfen Sie die Tabelle nach Möglichkeit mit weniger Spalten. Wenn eine Zeilengruppe in den Columnstore komprimiert wird, komprimiert der Spaltenspeicherindex jedes Spaltensegment separat. Daher erhöhen sich die Speicheranforderungen zum Komprimieren einer Zeilengruppe, wenn die Anzahl der Spalten zunimmt.

Weniger Zeichenfolgenspalten verwenden

Spalten von Zeichenfolgendatentypen erfordern mehr Arbeitsspeicher als numerische Datentypen und Datumsdatentypen. Um die Speicheranforderungen zu verringern, sollten Sie Zeichenfolgenspalten aus Faktentabellen entfernen und in kleinere Dimensionstabellen einfügen.

Zusätzliche Speicheranforderungen für die Zeichenfolgenkomprimierung:

• Zeichenfolgendatentypen bis zu 32 Zeichen können 32 zusätzliche Bytes pro Wert erfordern.
• Zeichenfolgendatentypen mit mehr als 32 Zeichen werden mithilfe von Wörterbuchmethoden komprimiert. Jede Spalte in der Zeilengruppe kann bis zu 16 MB benötigen, um das Wörterbuch zu erstellen.

Vermeiden Sie übermäßiger Partitionierung

Spaltenspeicherindizes erstellen eine oder mehrere Zeilengruppen pro Partition. Bei Data Warehouse in Azure Synapse Analytics wächst die Anzahl der Partitionen schnell, da die Daten verteilt werden und jede Verteilung partitioniert wird. Wenn die Tabelle zu viele Partitionen aufweist, sind möglicherweise nicht genügend Zeilen vorhanden, um die Zeilengruppen auszufüllen. Das Fehlen von Zeilen erzeugt während der Komprimierung keinen Arbeitsspeicherdruck, führt aber zu Zeilengruppen, die nicht die beste Abfrageleistung im Spaltenspeicher erzielen.

Ein weiterer Grund, die Überpartitionierung zu vermeiden, ist der Arbeitsspeicheraufwand beim Laden von Zeilen in einen Spaltenspeicherindex in einer partitionierten Tabelle. Während einer Last können viele Partitionen die eingehenden Zeilen empfangen, die im Arbeitsspeicher gespeichert werden, bis jede Partition über genügend Zeilen verfügt, um komprimiert zu werden. Wenn zu viele Partitionen vorhanden sind, entsteht zusätzlicher Arbeitsspeicherdruck.

Vereinfachen der Ladeabfrage

Die Datenbank teilt die Speichererteilung für eine Abfrage zwischen allen Operatoren in der Abfrage. Wenn eine Ladeabfrage komplexe Sortierungen und Verknüpfungen aufweist, wird der für die Komprimierung verfügbare Arbeitsspeicher reduziert.

Entwerfen Sie die Ladeabfrage so, dass sie sich nur auf das Laden der Abfrage konzentriert. Wenn Sie Transformationen für die Daten ausführen müssen, führen Sie sie getrennt von der Ladeabfrage aus. Staffeln Sie die Datei, z.B. in einem Heap, führen sie die Transformationen aus, und laden Sie anschließend die Stagingtabelle in den Columnstore-Index.

MAXDOP anpassen

Jede Verteilung komprimiert Zeilengruppen parallel in den Columnstore, wenn pro Verteilung mehr als ein CPU-Kern verfügbar ist. Die Parallelität erfordert zusätzliche Speicherressourcen, was zu Speicherdruck und Zeilengruppenkürzung führen kann.

Um den Arbeitsspeicherdruck zu verringern, können Sie den MAXDOP-Abfragehinweis verwenden, um die Ausführung des Ladevorgangs im seriellen Modus innerhalb jeder Verteilung zu erzwingen.

CREATE TABLE MyFactSalesQuota
WITH (DISTRIBUTION = ROUND_ROBIN)
AS SELECT * FROM FactSalesQuota
OPTION (MAXDOP 1);

Möglichkeiten zum Zuweisen von mehr Arbeitsspeicher

Die DWU-Größe und die Benutzerressourcenklasse bestimmen zusammen, wie viel Arbeitsspeicher für eine Benutzerabfrage verfügbar ist. Um die Speichererteilung für eine Ladeabfrage zu erhöhen, können Sie entweder die Anzahl der DWUs erhöhen oder die Ressourcenklasse erhöhen.

• Informationen zum Erhöhen der DWUs finden Sie unter Wie skaliere ich die Leistung?
• Informationen zum Ändern der Ressourcenklasse für eine Abfrage finden Sie unter Ändern eines Beispiels für eine Benutzerressourcenklasse.

Nächste Schritte

Weitere Möglichkeiten zur Verbesserung der Leistung in Synapse SQL finden Sie in der Leistungsübersicht.