Rijgroepkwaliteit maximaliseren voor columnstore-indexprestaties

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 hoeven te worden gelezen.

Zie Columnstore Indexes Guide (Handleiding voor kolomopslagindexen) 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 als u niet het maximum aantal rijen per rijgroep hebt. Columnstore-indexen leveren goede prestaties wanneer rijgroepen ten minste 100.000 rijen hebben.

Rijgroepen kunnen worden ingekort tijdens het comprimeren

Tijdens het herbouwen van een columnstore-index of bulksgewijs laden is er soms onvoldoende geheugen beschikbaar om alle rijen te comprimeren die voor elke rijgroep zijn aangewezen. Wanneer er geheugendruk 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 dynamische beheerweergave (DMV) (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 wordt 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 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 kolom geeft aan of de rijgroep is ingekort (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 ingekort:

• BULKSGEWIJS LADEN: deze trimreden 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 het knippen in op BULKLOAD. In dit scenario kunt u overwegen om de batchbelasting te verhogen om meer rijen op te nemen. Evalueer ook uw partitioneringsschema opnieuw om ervoor te zorgen dat het niet te gedetailleerd is, omdat rijgroepen de partitiegrenzen niet kunnen omvatten.
• MEMORY_LIMITATION: als u rijgroepen met 1 miljoen rijen wilt maken, 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 knipreden geeft aan dat het bijsnijden van de rijgroep is opgetreden omdat er ten minste één tekenreekskolom is met brede en/of hoge kardinaliteitsreeksen. De grootte van de woordenlijst is beperkt tot 16 MB in het geheugen en zodra deze limiet is bereikt, wordt de rijgroep gecomprimeerd. Als deze situatie zich voordeed, kunt u overwegen om de kolom met problemen te isoleren in een afzonderlijke tabel.

Geheugenvereisten schatten

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

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

Notitie

Waar korte-tekenreekskolommen tekenreeksgegevenstypen van <= 32 bytes gebruiken en lange-tekenreekskolommen tekenreeksgegevenstypen van > 32 bytes gebruiken.

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 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 indien mogelijk de tabel met minder kolommen. Wanneer een rijgroep wordt gecomprimeerd in de columnstore, comprimeert de columnstore-index elk kolomsegment afzonderlijk. Daarom 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. Als u de geheugenvereisten wilt 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 datawarehousing 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 onvoldoende rijen om de rijgroepen te vullen. Het ontbreken van rijen zorgt niet voor geheugendruk tijdens het comprimeren, maar leidt tot rijgroepen die niet de beste columnstore-queryprestaties 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 opgeslagen 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 loadquery complexe sorteringen en joins heeft, wordt het geheugen dat beschikbaar is voor compressie verminderd.

Ontwerp de laadquery zo dat 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 heap-tabel faseren, de transformaties uitvoeren en vervolgens de faseringstabel in de columnstore-index laden.

MAXDOP aanpassen

Elke distributie comprimeert rijgroepen parallel in de columnstore wanneer er meer dan één CPU-kern beschikbaar is per distributie. De parallelle uitvoering vereist extra geheugenresources, wat kan leiden tot geheugendruk en het inkorten van rijengroepen.

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 prestaties schalen?
• 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 in Synapse SQL te verbeteren.