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.

Target size for rowgroups

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. When memory pressure is present, columnstore indexes trim the rowgroup sizes so compression into the columnstore can succeed.

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.

How to monitor rowgroup quality

The DMV sys.dm_pdw_nodes_db_column_store_row_group_physical_stats (sys.dm_db_column_store_row_group_physical_stats contains the view definition matching SQL DB) that exposes useful information such as number of rows in rowgroups and the reason for trimming, if there was trimming.

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). The following trim reasons indicate premature trimming of the rowgroup:

• BULKLOAD: This trim reason is used when the incoming batch of rows for the load had less than 1 million rows. The engine will create compressed row groups if there are greater than 100,000 rows being inserted (as opposed to inserting into the delta store) but sets the trim reason to BULKLOAD. In this scenario, consider increasing your batch load to include more rows. 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: This trim reason indicates that rowgroup trimming occurred because there was at least one string column with wide and/or high cardinality strings. 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. This query shows the size of the memory grant that a rowgroup requires for compression in to the columnstore.

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

• 72 MB +
• #rows * #columns * 8 bytes +
• #rows * #short-string-columns * 32 bytes +
• #long-string-columns * 16 MB for compression dictionary

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. But, it leads to rowgroups that do not achieve the best columnstore query performance.

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. When a load query has complex sorts and joins, the memory available for compression is reduced.

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.

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.