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

Artikel
06/01/2023

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 van belang is

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

Wanneer rijgroepen een groot aantal rijen hebben, wordt de gegevenscompressie verbeterd, wat betekent dat er minder gegevens van de schijf kunnen worden gelezen.

Zie Columnstore Indexes Guide (Handleiding voor 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 hebben.

Het is prima om niet het maximum aantal rijen per rijgroep te hebben. Columnstore-indexen leveren goede prestaties wanneer rijgroepen ten minste 100.000 rijen hebben.

Rijgroepen kunnen worden ingekort tijdens compressie

Tijdens het bulksgewijs laden of opnieuw opbouwen van een columnstore-index is er soms onvoldoende geheugen beschikbaar om alle rijen te comprimeren die voor elke rijgroep zijn aangewezen. Wanneer geheugendruk aanwezig is, snijden columnstore-indexen de grootte van de rijgroep in, zodat de compressie in de columnstore 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.

De kwaliteit van de rijgroep 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 SQL DB) die nuttige informatie weergeeft, zoals het aantal rijen in rijgroepen en de reden voor het inkorten, als er is ingekort.

U kunt de volgende weergave maken als een handige manier om een query uit te voeren op deze DMV om informatie op te halen over het inkorten van rijgroepen.

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 bijgesneden (trim_reason_desc = NO_TRIM impliceert dat er geen bijsnijding is en dat de rijgroep van optimale kwaliteit is). De volgende knipredenen geven aan dat de rijgroep voortijdig wordt afgekapt:

BULKSGEWIJS LADEN: deze reden voor bijsnijden wordt gebruikt wanneer de binnenkomende batch rijen voor de belasting 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 het Delta-archief), maar stelt de reden voor bijsnijden in op BULKLOAD. In dit scenario kunt u overwegen om de batchbelasting te verhogen om meer rijen op te nemen. Evalueer ook het partitieschema 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 reden voor bijsnijden geeft aan dat het bijsnijden van de rijgroep is opgetreden omdat er ten minste één tekenreekskolom is met tekenreeksen met brede en/of hoge kardinaliteit. 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 wel tegen komt, kunt u overwegen om de problematische kolom in een afzonderlijke tabel te isoleren.

Geheugenvereisten schatten

Als u een schatting wilt weergeven van de geheugenvereisten voor het comprimeren van een rijgroep van maximale grootte in een columnstore-index, kunt u de voorbeeldweergave maken dbo.vCS_mon_mem_grant. Deze query toont de grootte van de geheugentoestemming die een rijgroep nodig heeft voor compressie in de columnstore.

Het maximaal vereiste geheugen voor het comprimeren van één rijgroep is ongeveer

72 MB +
#rows * #columns * 8 bytes +
#rows * #short-string-columns * 32 bytes +
#long-tekenreekskolommen * 16 MB voor compressiewoordenlijst

Notitie

Short-string-columns gebruiken tekenreeksgegevenstypen van <= 32 bytes en lange-tekenreekskolommen gebruiken tekenreeksgegevenstypen van > 32 bytes.

Lange tekenreeksen worden gecomprimeerd met een compressiemethode die is ontworpen voor het comprimeren van tekst. Bij deze compressiemethode wordt een woordenlijst gebruikt om tekstpatronen op te slaan. De maximale grootte van een woordenlijst is 16 MB. Er is slechts één woordenlijst voor elke lange tekenreekskolom 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 de tabel indien mogelijk 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

Kolommen met tekenreeksgegevenstypen vereisen meer geheugen dan numerieke gegevenstypen en datumgegevenstypen. Om de geheugenvereisten te verminderen, kunt u overwegen om tekenreekskolommen uit feitentabellen te verwijderen en deze in kleinere dimensietabellen te plaatsen.

Aanvullende geheugenvereisten voor tekenreekscompressie:

Tekenreeksgegevenstypen van maximaal 32 tekens kunnen 32 extra bytes per waarde vereisen.
Tekenreeksgegevenstypen met meer dan 32 tekens worden gecomprimeerd met behulp van woordenlijstmethoden. Elke kolom in de rijgroep kan maximaal 16 MB extra nodig hebben om de woordenlijst te maken.

Overpartitionering voorkomen

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

Als de tabel te veel partities heeft, zijn er mogelijk niet voldoende rijen om de rijgroepen te vullen. Het ontbreken van rijen zorgt niet voor geheugendruk tijdens het comprimeren. Maar het leidt tot rijgroepen die niet de beste prestaties van columnstore-query's behalen.

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

Tijdens het laden kunnen veel partities de binnenkomende rijen ontvangen, die in het geheugen worden bewaard totdat elke partitie voldoende rijen heeft om te worden gecomprimeerd. Te veel partities zorgt voor extra geheugenbelasting.

De laadquery vereenvoudigen

De database deelt de geheugentoestemming voor een query tussen alle operators in de query. Wanneer een laadquery complexe sorteringen en joins heeft, wordt het geheugen dat beschikbaar is voor compressie verminderd.

Ontwerp de laadquery zodat deze zich alleen richt op het laden van de query. Als u transformaties wilt uitvoeren op de gegevens, voert u deze los van de laadquery uit. U kunt bijvoorbeeld de gegevens in een heaptabel faseren, de transformaties uitvoeren en vervolgens de faseringstabel laden in de columnstore-index.

Tip

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

MAXDOP aanpassen

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

Het parallellisme vereist extra geheugenresources, wat kan leiden tot geheugendruk en het inkorten van rijgroepen.

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

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 gebruikersresourceklasse bepalen samen hoeveel geheugen beschikbaar is voor een gebruikersquery.

Als u de geheugentoestemming voor een loadquery wilt verhogen, kunt u het aantal DWU's verhogen of de resourceklasse verhogen.

Als u de DWU's wilt verhogen, raadpleegt u Hoe kan ik schaalprestaties?
Zie Voorbeeld van een gebruikersresourceklasse wijzigen als u de resourceklasse voor een query wilt wijzigen.

Volgende stappen

Zie prestatieoverzicht voor meer manieren om de prestaties voor toegewezen SQL-pool te verbeteren.

Delen via