Delen via

Prestaties afstemmen met geordende geclusterde columnstore-index in Azure Synapse Analytics

  • Artikel

Van toepassing op: toegewezen SQL-pools van Azure Synapse Analytics

Wanneer gebruikers een query uitvoeren op een columnstore-tabel in een toegewezen SQL-pool, controleert de optimizer de minimum- en maximumwaarden die in elk segment zijn opgeslagen. Segmenten die buiten de grenzen van het querypredicaat vallen, worden niet gelezen van schijf naar geheugen. Een query kan sneller worden voltooid als het aantal segmenten dat moet worden gelezen en de totale grootte ervan klein is.

Notitie

Dit artikel is van toepassing op toegewezen SQL-pools van Azure Synapse Analytics. Zie Prestaties afstemmen met geordende columnstore-indexen in SQL Server en andere SQL-platforms voor meer informatie over geordende columnstore-indexen.

Geordende versus niet-geordende geclusterde columnstore-index

Voor elke tabel die zonder indexoptie is gemaakt, maakt een intern onderdeel (opbouwfunctie voor indexen) standaard een niet-geordende geclusterde columnstore-index (CCI). Gegevens in elke kolom worden gecomprimeerd in een afzonderlijk CCI-rijgroepsegment. Er zijn metagegevens in het waardebereik van elk segment, zodat segmenten buiten de grenzen van het querypredicaat niet worden gelezen vanaf schijf tijdens het uitvoeren van de query. CCI biedt het hoogste niveau van gegevenscompressie en vermindert de grootte van segmenten om te lezen, zodat query's sneller kunnen worden uitgevoerd. Omdat de opbouwfunctie voor indexen echter geen gegevens sorteert voordat ze in segmenten worden gecomprimeerd, kunnen segmenten met overlappende waardebereiken optreden, waardoor query's meer segmenten van schijf lezen en langer duren om te voltooien.

Geordende geclusterde columnstore-indexen door efficiënte segmentverwijdering mogelijk te maken, wat resulteert in veel snellere prestaties door grote hoeveelheden geordende gegevens over te slaan die niet overeenkomen met het querypredicaat. Bij het maken van een geordende CCI sorteert de toegewezen SQL-poolengine de bestaande gegevens in het geheugen op basis van de ordersleutel(s) voordat de opbouwfunctie voor indexen deze in indexsegmenten comprimeert. Met gesorteerde gegevens wordt segmentovername verminderd, waardoor query's efficiënter kunnen worden verwijderd en dus sneller presteren omdat het aantal segmenten dat van de schijf moet worden gelezen kleiner is. Als alle gegevens tegelijk in het geheugen kunnen worden gesorteerd, kan overlappend segment worden vermeden. Vanwege grote tabellen in datawarehouses gebeurt dit scenario niet vaak.

Als u de segmentbereiken voor een kolom wilt controleren, voert u de volgende opdracht uit met de tabelnaam en kolomnaam:

SELECT o.name, pnp.index_id,
cls.row_count, pnp.data_compression_desc,
pnp.pdw_node_id, pnp.distribution_id, cls.segment_id,
cls.column_id,
cls.min_data_id, cls.max_data_id,
cls.max_data_id-cls.min_data_id as difference
FROM sys.pdw_nodes_partitions AS pnp
   JOIN sys.pdw_nodes_tables AS Ntables ON pnp.object_id = NTables.object_id AND pnp.pdw_node_id = NTables.pdw_node_id
   JOIN sys.pdw_table_mappings AS Tmap  ON NTables.name = TMap.physical_name AND substring(TMap.physical_name,40, 10) = pnp.distribution_id
   JOIN sys.objects AS o ON TMap.object_id = o.object_id
   JOIN sys.pdw_nodes_column_store_segments AS cls ON pnp.partition_id = cls.partition_id AND pnp.distribution_id  = cls.distribution_id
JOIN sys.columns as cols ON o.object_id = cols.object_id AND cls.column_id = cols.column_id
WHERE o.name = '<Table Name>' and cols.name = '<Column Name>'  and TMap.physical_name  not like '%HdTable%'
ORDER BY o.name, pnp.distribution_id, cls.min_data_id;

Notitie

In een geordende CCI-tabel worden de nieuwe gegevens die voortvloeien uit dezelfde batch DML- of gegevenslaadbewerkingen in die batch gesorteerd, er is geen globale sortering voor alle gegevens in de tabel. Gebruikers kunnen de geordende CCI herbouwen om alle gegevens in de tabel te sorteren. In een toegewezen SQL-pool is de columnstore-index REBUILD een offlinebewerking. Voor een gepartitioneerde tabel wordt de REBUILD één partitie tegelijk uitgevoerd. Gegevens in de partitie die opnieuw worden opgebouwd, zijn offline en niet beschikbaar totdat de REBUILD voor die partitie is voltooid.

Queryprestaties

De prestatiewinst van een query van een geordende CCI is afhankelijk van de querypatronen, de grootte van de gegevens, hoe goed de gegevens worden gesorteerd, de fysieke structuur van segmenten en de DWU en resourceklasse die is gekozen voor de queryuitvoering. Gebruikers moeten al deze factoren controleren voordat ze de volgordekolommen kiezen bij het ontwerpen van een geordende CCI-tabel.

Query's met al deze patronen worden doorgaans sneller uitgevoerd met geordende CCI.

  1. De query's hebben gelijkheid, ongelijkheid of bereikpredicaten
  2. De predicaatkolommen en de geordende CCI-kolommen zijn hetzelfde.

In dit voorbeeld heeft tabel T1 een geclusterde columnstore-index geordend in de volgorde van Col_C, Col_B en Col_A.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON  T1
ORDER (Col_C, Col_B, Col_A);

De prestaties van query 1 en query 2 kunnen meer profiteren van geordende CCI dan de andere query's, omdat ze verwijzen naar alle geordende CCI-kolommen.

-- Query #1:

SELECT * FROM T1 WHERE Col_C = 'c' AND Col_B = 'b' AND Col_A = 'a';

-- Query #2

SELECT * FROM T1 WHERE Col_B = 'b' AND Col_C = 'c' AND Col_A = 'a';

-- Query #3
SELECT * FROM T1 WHERE Col_B = 'b' AND Col_A = 'a';

-- Query #4
SELECT * FROM T1 WHERE Col_A = 'a' AND Col_C = 'c';

Prestaties van het laden van gegevens

De prestaties van het laden van gegevens in een geordende CCI-tabel zijn vergelijkbaar met een gepartitioneerde tabel. Het laden van gegevens in een geordende CCI-tabel kan langer duren dan een niet-geordende CCI-tabel vanwege de sorteerbewerking voor gegevens, maar query's kunnen later sneller worden uitgevoerd met geordende CCI.

Hier volgt een voorbeeld van een prestatievergelijking van het laden van gegevens in tabellen met verschillende schema's.

Staafdiagram met de prestatievergelijking van het laden van gegevens in tabellen met verschillende schema's.

Hier volgt een voorbeeld van een vergelijking van queryprestaties tussen CCI en geordende CCI.

Staafdiagram vergelijkt de prestaties tijdens data_loading. Een geordende geclusterde columnstore-index heeft een lagere duur.

Segmentover overlapping verminderen

Het aantal overlappende segmenten is afhankelijk van de grootte van de gegevens die moeten worden gesorteerd, het beschikbare geheugen en de maximale mate van parallelle uitvoering (MAXDOP) tijdens het maken van geordende CCI. De volgende strategieën verminderen het overlappen van segmenten bij het maken van geordende CCI.

  • Gebruik xlargerc resourceklasse in een hogere DWU om meer geheugen toe te staan voor het sorteren van gegevens voordat de opbouwfunctie voor indexen de gegevens in segmenten comprimeert. Eenmaal in een indexsegment kan de fysieke locatie van de gegevens niet worden gewijzigd. Er zijn geen gegevenssortering binnen een segment of in verschillende segmenten.

  • Maak geordende CCI met OPTION (MAXDOP = 1). Elke thread die wordt gebruikt voor geordende CCI-creatie werkt op een subset van gegevens en sorteert deze lokaal. Er is geen wereldwijde sortering voor gegevens gesorteerd op verschillende threads. Het gebruik van parallelle threads kan de tijd verminderen om een geordende CCI te maken, maar genereert meer overlappende segmenten dan het gebruik van één thread. Het gebruik van één threaded bewerking levert de hoogste compressiekwaliteit. Bijvoorbeeld:

CREATE TABLE Table1 WITH (DISTRIBUTION = HASH(c1), CLUSTERED COLUMNSTORE INDEX ORDER(c1) )
AS SELECT * FROM ExampleTable
OPTION (MAXDOP 1);

Notitie

Momenteel wordt in toegewezen SQL-pools in Azure Synapse Analytics de optie MAXDOP alleen ondersteund bij het maken van een geordende CCI-tabel met behulp van CREATE TABLE AS SELECT de opdracht. Het maken van een geordende CCI via CREATE INDEX of CREATE TABLE opdrachten biedt geen ondersteuning voor de MAXDOP-optie. Deze beperking geldt niet voor SQL Server 2022 en latere versies, waar u MAXDOP kunt opgeven met de CREATE INDEX of CREATE TABLE opdrachten.

  • Sorteer de gegevens vooraf op de sorteersleutel(s) voordat ze in tabellen worden geladen.

Hier volgt een voorbeeld van een geordende CCI-tabeldistributie die geen segment overlapt na bovenstaande aanbevelingen. De geordende CCI-tabel wordt gemaakt in een DWU1000c-database via CTAS van een heaptabel van 20 GB met MAXDOP 1 en xlargerc. De CCI wordt geordend op een BIGINT-kolom zonder duplicaten.

Een schermopname van tekstgegevens met geen segmentover overlapping.

Geordende CCI maken voor grote tabellen

Het maken van een geordende CCI is een offlinebewerking. Voor tabellen zonder partities zijn de gegevens pas toegankelijk voor gebruikers als het geordende CCI-aanmaakproces is voltooid. Voor gepartitioneerde tabellen, omdat de engine de geordende CCI-partitie per partitie maakt, hebben gebruikers nog steeds toegang tot de gegevens in partities waar geordende CCI-creatie niet wordt verwerkt. U kunt deze optie gebruiken om de downtime tijdens het maken van geordende CCI's op grote tabellen te minimaliseren:

  1. Maak partities op de grote doeltabel (aangeroepen Table_A).
  2. Maak een lege geordende CCI-tabel (aangeroepen Table_B) met dezelfde tabel en hetzelfde partitieschema als Table_A.
  3. Overschakelen van Table_A één partitie naar Table_B.
  4. Voer uit ALTER INDEX <Ordered_CCI_Index> ON <Table_B> REBUILD PARTITION = <Partition_ID> om de overgeschakelde partitie opnieuw op Table_Bte bouwen.
  5. Herhaal stap 3 en 4 voor elke partitie in Table_A.
  6. Zodra alle partities zijn overgeschakeld naar Table_A Table_B en opnieuw zijn opgebouwd, neerzetten Table_Aen hernoemen Table_B naar Table_A.

Tip

Voor een toegewezen SQL-pooltabel met een geordende CCI sorteert ALTER INDEX REBUILD de gegevens opnieuw met behulp van tempdb. Bewaken tempdb tijdens herbouwbewerkingen. Als u meer tempdb ruimte nodig hebt, schaalt u de pool omhoog. Terugschalen zodra het opnieuw opbouwen van de index is voltooid.

Voor een toegewezen SQL-pooltabel met een geordende CCI sorteert ALTER INDEX REORGANIZE de gegevens niet opnieuw. Als u gegevens wilt gebruiken, gebruikt u ALTER INDEX REBUILD.

Zie Geclusterde columnstore-indexen optimaliseren voor meer informatie over geordende CCI-onderhoud.

Functieverschillen in SQL Server 2022-mogelijkheden

SQL Server 2022 (16.x) heeft geordende geclusterde columnstore-indexen geïntroduceerd die vergelijkbaar zijn met de functie in toegewezen SQL-pools van Azure Synapse.

  • Momenteel bieden alleen SQL Server 2022 (16.x) en latere versies ondersteuning voor geclusterde columnstore uitgebreide segmentverwijderingsmogelijkheden voor tekenreeks-, binaire en guid-gegevenstypen, en het gegevenstype datetimeoffset voor schaal groter dan twee. Voorheen is deze segmentuitschakeling van toepassing op numerieke gegevenstypen, datum- en tijdgegevenstypen en het gegevenstype datetimeoffset met een schaal kleiner dan of gelijk aan twee.
  • Momenteel ondersteunen alleen SQL Server 2022 (16.x) en latere versies geclusterde columnstore-rijgroepverwijdering voor het voorvoegsel van LIKE predicaten, bijvoorbeeld column LIKE 'string%'. Segmentverwijdering wordt niet ondersteund voor het niet-voorvoegselgebruik van LIKE, zoals column LIKE '%string'.

Zie Wat is er nieuw in Columnstore-indexen voor meer informatie.

Voorbeelden

A. Controleren op geordende kolommen en rangschikken:

SELECT object_name(c.object_id) table_name, c.name column_name, i.column_store_order_ordinal
FROM sys.index_columns i
JOIN sys.columns c ON i.object_id = c.object_id AND c.column_id = i.column_id
WHERE column_store_order_ordinal <>0;

B. Als u kolomordinaal wilt wijzigen, kolommen wilt toevoegen aan of verwijderen uit de bestellijst of wilt wijzigen van CCI in geordende CCI:

CREATE CLUSTERED COLUMNSTORE INDEX InternetSales ON dbo.InternetSales
ORDER (ProductKey, SalesAmount)
WITH (DROP_EXISTING = ON);

Volgende stappen