De kwaliteit van de rijgroep maximaliseren voor columnstore-indexen in een toegewezen SQL-pool

De kwaliteit van de rijgroep wordt bepaald door het aantal rijen in een rijgroep. Door het beschikbare geheugen te vergroten, kan het aantal rijen dat een columnstore-index in elke rijgroep comprimeert, worden gemaximaliseerd. Gebruik deze methoden om compressiesnelheden en queryprestaties voor columnstore-indexen te verbeteren.

Waarom de grootte van de rijgroep belangrijk is

Omdat een columnstore-index een tabel scant door kolomsegmenten van afzonderlijke rijgroepen te scannen, verbetert het aantal rijen in elke rijgroep de queryprestaties.

Wanneer rijengroepen een groot aantal rijen hebben, verbetert de gegevenscompressie, wat betekent dat er minder gegevens van de schijf moeten worden gelezen.

Zie de handleiding columnstore-indexen voor meer informatie over rijgroepen.

Doelgrootte voor rijgroepen

Voor de beste queryprestaties is het doel om het aantal rijen per rijgroep in een columnstore-index te maximaliseren. Een rijgroep kan maximaal 1.048.576 rijen bevatten.

Het is geen probleem om niet het maximale aantal rijen per rijgroep te hebben. Columnstore-indexen leveren goede prestaties wanneer rijengroepen ten minste 100.000 rijen hebben.

Rijengroepen kunnen tijdens compressie worden ingekort

Tijdens het laden in bulk of het opnieuw opbouwen van een columnstore-index, is er soms onvoldoende geheugen beschikbaar om alle rijen die voor elke rijgroep zijn aangewezen te comprimeren. Als er geheugendruk aanwezig is, verkleinen de kolomopslagindexen de grootte van de rijgroep zodat de compressie in de kolomopslag kan slagen.

Wanneer er onvoldoende geheugen is om ten minste 10.000 rijen in elke rijgroep te comprimeren, wordt er een fout gegenereerd.

Zie Bulksgewijs laden in een geclusterde columnstore-index voor meer informatie over bulksgewijs laden.

Hoe rijgroepkwaliteit te bewaken

De DMV sys.dm_pdw_nodes_db_column_store_row_group_physical_stats (sys.dm_db_column_store_row_group_physical_stats bevat de weergavedefinitie die overeenkomt met de SQL-database) geeft nuttige informatie weer, zoals het aantal rijen in rijgroepen en de reden voor trimmen, als er sprake was van trimmen.

U kunt de volgende weergave maken als een handige manier om een query uit te voeren op deze DMV voor informatie over het bijsnijden van rijengroepen.

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;

De trim_reason_desc geeft aan of de rijgroep is ingekort (trim_reason_desc = NO_TRIM impliceert dat er geen bijsnijding is en rijgroep van optimale kwaliteit is). De volgende redenen voor bijsnijden veroorzaken voortijdig inkorten van de rijgroep:

• BULKLOAD: Deze trim-reden wordt gebruikt wanneer de binnenkomende batch rijen voor de lading van minder dan 1 miljoen rijen had. De engine maakt gecomprimeerde rijgroepen als er meer dan 100.000 rijen worden ingevoegd (in plaats van in te voegen in de Delta Store), maar de trimreden wordt ingesteld voor BULKLOAD. In dit scenario kunt u overwegen om de batch te vergroten om meer rijen op te nemen. Evalueer ook uw partitioneringsschema opnieuw om ervoor te zorgen dat het niet te gedetailleerd is omdat rijgroepen geen partitiegrenzen kunnen omvatten.
• MEMORY_LIMITATION: Voor het maken van rijgroepen met 1 miljoen rijen is een bepaalde hoeveelheid werkgeheugen vereist voor de engine. Wanneer het beschikbare geheugen van de laadsessie kleiner is dan het vereiste werkgeheugen, worden rijgroepen voortijdig ingekort. In de volgende secties wordt uitgelegd hoe u het vereiste geheugen kunt schatten en meer geheugen kunt toewijzen.
• DICTIONARY_SIZE: Deze trimreden geeft aan dat het bijsnijden van de rijgroep plaatsvond omdat er ten minste één tekstkolom met brede en/of hoge kardinaliteit aan tekenreeksen was. De grootte van de woordenlijst is beperkt tot 16 MB in het geheugen en zodra deze limiet is bereikt, wordt de rijgroep gecomprimeerd. Als u deze situatie tegenkomt, kunt u overwegen om de problematische kolom te isoleren in een afzonderlijke tabel.

Hoe de geheugenvereisten te schatten

Als u een schatting wilt bekijken van de geheugenvereisten voor het comprimeren van een rijgroep met maximale grootte in een columnstore-index, kunt u overwegen om de voorbeeldweergave te maken dbo.vCS_mon_mem_grant. Deze query toont de grootte van de geheugen toewijzing die een rijgroep nodig heeft voor compressie naar de columnstore.

Het maximale geheugen dat nodig is om één rijgroep te comprimeren, is ongeveer

• 72 MB +
• #rijen * #kolommen * 8 bytes +
• #rijen * #kolommen-korte-reeks * 32 bytes +
• #long-tekenreekskolommen * 16 MB voor compressiewoordenlijst

Opmerking

Kolommen met korte tekenreeksen gebruiken tekenreeksgegevenstypen van <= 32 bytes en lange tekenreekskolommen maken gebruik van tekenreeksgegevenstypen van > 32 bytes.

Lange tekenreeksen worden gecomprimeerd met een compressiemethode die is ontworpen voor het comprimeren van tekst. Deze compressiemethode maakt gebruik van een woordenlijst om tekstpatronen op te slaan. De maximale grootte van een woordenlijst is 16 MB. Er is slechts één woordenlijst per lange tekstkolom in de rijgroep.

Manieren om geheugenvereisten te verminderen

Gebruik de volgende technieken om de geheugenvereisten voor het comprimeren van rijgroepen in columnstore-indexen te verminderen.

Minder kolommen gebruiken

Ontwerp indien mogelijk de tabel met minder kolommen. Wanneer een rijgroep wordt gecomprimeerd in de columnstore, comprimeert de columnstore-index elk kolomsegment afzonderlijk.

Als zodanig nemen de geheugenvereisten voor het comprimeren van een rijgroep toe naarmate het aantal kolommen toeneemt.

Minder tekenreekskolommen gebruiken

Voor kolommen met tekenreeksgegevenstypen is meer geheugen nodig dan numerieke en datumgegevenstypen. Door tekstkolommen uit feitentabellen te verwijderen en in kleinere dimensietabellen te plaatsen, kunt u de geheugenvereisten verminderen.

Aanvullende geheugenvereisten voor tekenreekscompressie:

• Tekenreeksgegevenstypen tot 32 tekens kunnen 32 extra bytes per waarde vereisen.
• Tekenreeksgegevenstypen met meer dan 32 tekens worden gecomprimeerd met behulp van woordenlijstmethoden. Voor elke kolom in de rijgroep kan een extra 16 MB nodig zijn om de woordenlijst te maken.

Overpartitionering voorkomen

Columnstore-indexen maken een of meer rijgroepen per partitie. Voor een toegewezen SQL-pool in Azure Synapse Analytics groeit het aantal partities snel omdat de gegevens worden gedistribueerd en elke distributie wordt gepartitioneerd.

Als de tabel te veel partities heeft, zijn er mogelijk onvoldoende rijen om de rijgroepen te vullen. Het ontbreken van rijen creëert geen geheugendruk tijdens compressie. Maar het leidt tot rijengroepen die niet de beste prestaties van columnstore-query's behalen.

Een andere reden om overpartitionering te voorkomen, is er een geheugenoverhead voor het laden van rijen in een columnstore-index in een gepartitioneerde tabel.

Tijdens een belasting kunnen veel partities de binnenkomende rijen ontvangen, die in het geheugen worden bewaard totdat elke partitie voldoende rijen heeft om te worden gecomprimeerd. Als er te veel partities zijn, ontstaat er extra geheugendruk.

De laadquery vereenvoudigen

De database verdeelt de geheugentoewijzing voor een query onder alle operatoren in de query. Als een laadquery complexe sorteringen en joins heeft, wordt het beschikbare geheugen voor compressie beperkt.

Ontwerp de laadquery zodat deze zich alleen richt op het laden van de query. Als u transformaties op de gegevens wilt uitvoeren, doe dit dan apart van de laadquery. Faseer bijvoorbeeld de gegevens in een heap-tabel, voer de transformaties uit en laad de faseringstabel vervolgens in de columnstore-index.

Hint

U kunt de gegevens ook eerst laden en vervolgens het MPP-systeem gebruiken om de gegevens te transformeren.

MAXDOP aanpassen

Elke distributie comprimeert rijgroepen parallel in de columnstore wanneer er per distributie meer dan één CPU-kern beschikbaar is.

Voor het parallellisme zijn extra geheugenbronnen vereist, wat kan leiden tot geheugendruk en het bijsnijden van rijengroepen.

Als u de geheugenbelasting wilt verminderen, kunt u de MAXDOP-query hint gebruiken om de laadbewerking in seriële modus binnen elke distributie af te dwingen.

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

Manieren om meer geheugen toe te wijzen

DWU-grootte en de resourceklasse van de gebruiker bepalen samen hoeveel geheugen beschikbaar is voor een gebruikersquery.

Als u de geheugentoewijzing voor een laadquery wilt verhogen, kunt u het aantal DWU's verhogen of de resourceklasse aanpassen.

• Zie Hoe kan ik de prestaties schalen om de DWU's te verhogen?
• Als u de resourceklasse voor een query wilt wijzigen, raadpleegt u het voorbeeld van een gebruikersresourceklasse wijzigen.

Volgende stappen

Zie het overzicht van prestaties voor meer manieren om de prestaties voor een toegewezen SQL-pool te verbeteren.