Dela via

Prestandajustering med ordnat grupperat columnstore-index i Azure Synapse Analytics

  • Artikel

Gäller för: Dedikerade SQL-pooler i Azure Synapse Analytics

När användare kör frågor mot en columnstore-tabell i en dedikerad SQL-pool kontrollerar optimeraren de lägsta och högsta värden som lagras i varje segment. Segment som ligger utanför gränserna för frågepredikatet läse inte från disk till minne. En fråga kan slutföras snabbare om antalet segment som ska läsas och deras totala storlek är liten.

Kommentar

Den här artikeln gäller för dedikerade SQL-pooler i Azure Synapse Analytics. Information om sorterade kolumnlagringsindex i SQL Server och andra SQL-plattformar finns i Prestandajustering med ordnade grupperade kolumnlagringsindex.

Ordnat jämfört med icke-ordnat grupperat kolumnlagringsindex

För varje tabell som skapas utan indexalternativ skapar en intern komponent (indexbyggare) som standard ett icke-ordnat grupperat kolumnlagringsindex (CCI) på den. Data i varje kolumn komprimeras till ett separat CCI-radgruppssegment. Det finns metadata för varje segments värdeintervall, så segment som ligger utanför gränserna för frågepredikatet läse inte från disken under frågekörningen. CCI erbjuder den högsta nivån av datakomprimering och minskar storleken på segment att läsa så att frågor kan köras snabbare. Men eftersom indexverktyget inte sorterar data innan de komprimeras till segment kan segment med överlappande värdeintervall inträffa, vilket gör att frågor läser fler segment från disken och tar längre tid att slutföra.

Ordnade grupperade kolumnlagringsindex genom att aktivera effektiv segmenteliminering, vilket resulterar i mycket snabbare prestanda genom att hoppa över stora mängder sorterade data som inte matchar frågepredikatet. När du skapar en ordnad CCI sorterar den dedikerade SQL-poolmotorn befintliga data i minnet efter ordernycklarna innan indexverktyget komprimerar dem till indexsegment. Med sorterade data minskas segmentöverlappningen så att frågor får en effektivare segmenteliminering och därmed snabbare prestanda eftersom antalet segment som ska läsas från disken är mindre. Om alla data kan sorteras i minnet samtidigt kan du undvika segment överlappning. På grund av stora tabeller i informationslager sker det här scenariot inte ofta.

Om du vill kontrollera segmentintervallen för en kolumn kör du följande kommando med tabellnamnet och kolumnnamnet:

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;

Kommentar

I en ordnad CCI-tabell sorteras de nya data som härrör från samma batch med DML- eller datainläsningsåtgärder inom den batchen. Det finns ingen global sortering över alla data i tabellen. Användare kan ÅTERSKAPA den ordnade CCI:en för att sortera alla data i tabellen. I en dedikerad SQL-pool är columnstore-indexet REBUILD en offlineåtgärd. För en partitionerad tabell görs REBUILD en partition i taget. Data i partitionen som återskapas är "offline" och otillgängliga tills REBUILD har slutförts för partitionen.

Frågeprestanda

En frågas prestandavinst från en ordnad CCI beror på frågemönstren, storleken på data, hur väl data sorteras, segmentens fysiska struktur och den DWU- och resursklass som valts för frågekörningen. Användarna bör granska alla dessa faktorer innan de väljer ordningskolumnerna när de utformar en ordnad CCI-tabell.

Frågor med alla dessa mönster körs vanligtvis snabbare med ordnad CCI.

  1. Frågorna har likhet, ojämlikhet eller intervallpredikat
  2. Predikatkolumnerna och de ordnade CCI-kolumnerna är desamma.

I det här exemplet har tabell T1 ett grupperat kolumnlagringsindex ordnat i sekvensen med Col_C, Col_B och Col_A.

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

Prestanda för fråga 1 och fråga 2 kan dra större nytta av ordnad CCI än de andra frågorna, eftersom de refererar till alla ordnade CCI-kolumner.

-- 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';

Datainläsningsprestanda

Prestanda för datainläsning i en ordnad CCI-tabell liknar en partitionerad tabell. Att läsa in data i en ordnad CCI-tabell kan ta längre tid än en icke-ordnad CCI-tabell på grund av datasorteringsåtgärden, men frågor kan köras snabbare efteråt med ordnad CCI.

Här är ett exempel på prestandajämförelse av inläsning av data i tabeller med olika scheman.

Stapeldiagram som visar prestandajämförelsen för att läsa in data i tabeller med olika scheman.

Här är ett exempel på en jämförelse av frågeprestanda mellan CCI och ordnad CCI.

Stapeldiagram som jämför prestanda under data_loading. Ett ordnat grupperat kolumnlagringsindex har lägre varaktighet.

Minska segment som överlappar

Antalet överlappande segment beror på storleken på data som ska sorteras, det tillgängliga minnet och maxdop-inställningen (maximal grad av parallellitet) under ordnad CCI-skapande. Följande strategier minskar segment överlappning när du skapar ordnade CCI.

  • Använd xlargerc resursklassen på en högre DWU för att tillåta mer minne för datasortering innan indexverktyget komprimerar data till segment. En gång i ett indexsegment går det inte att ändra den fysiska platsen för data. Det finns ingen datasortering inom ett segment eller mellan segment.

  • Skapa ordnad CCI med OPTION (MAXDOP = 1). Varje tråd som används för att skapa en ordnad CCI fungerar på en delmängd data och sorterar den lokalt. Det finns ingen global sortering mellan data sorterade efter olika trådar. Att använda parallella trådar kan minska tiden för att skapa en ordnad CCI, men generera fler överlappande segment än att använda en enda tråd. Genom att använda en enda trådad åtgärd får du högsta komprimeringskvalitet. Till exempel:

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

Kommentar

I dedikerade SQL-pooler i Azure Synapse Analytics stöds för närvarande maxdOP-alternativet endast för att skapa en ordnad CCI-tabell med hjälp av CREATE TABLE AS SELECT kommandot . Att skapa en ordnad CCI via CREATE INDEX eller CREATE TABLE kommandon stöder inte MAXDOP-alternativet. Den här begränsningen CREATE INDEX gäller inte för SQL Server 2022 och senare versioner, där du kan ange MAXDOP med kommandona eller CREATE TABLE .

  • Sortera data i förväg efter sorteringsnycklarna innan du läser in dem i tabeller.

Här är ett exempel på en ordnad CCI-tabelldistribution som har noll segment överlappande enligt rekommendationerna ovan. Den ordnade CCI-tabellen skapas i en DWU1000c-databas via CTAS från en heaptabell på 20 GB med MAXDOP 1 och xlargerc. CCI sorteras på en BIGINT-kolumn utan dubbletter.

En skärmbild av textdata som inte visar något segment som överlappar varandra.

Skapa ordnad CCI på stora tabeller

Att skapa en ordnad CCI är en offlineåtgärd. För tabeller utan partitioner är data inte tillgängliga för användare förrän den ordnade CCI-skapandeprocessen har slutförts. Eftersom motorn skapar den ordnade CCI-partitionen efter partition för partitioner kan användarna fortfarande komma åt data i partitioner där ordnat CCI-skapande inte bearbetas. Du kan använda det här alternativet för att minimera stilleståndstiden vid ordnad CCI-skapande i stora tabeller:

  1. Skapa partitioner i den stora måltabellen (kallas Table_A).
  2. Skapa en tom ordnad CCI-tabell (kallas Table_B) med samma tabell och partitionsschema som Table_A.
  3. Växla en partition från Table_A till Table_B.
  4. Kör ALTER INDEX <Ordered_CCI_Index> ON <Table_B> REBUILD PARTITION = <Partition_ID> för att återskapa den inkopplade partitionen på Table_B.
  5. Upprepa steg 3 och 4 för varje partition i Table_A.
  6. När alla partitioner har växlats från Table_A till Table_B och har återskapats släpper Table_Adu och byter Table_B namn till Table_A.

Dricks

För en dedikerad SQL-pooltabell med en ordnad CCI sorterar ALTER INDEX REBUILD om data med .tempdb Övervaka tempdb under återskapandeåtgärder. Om du behöver mer tempdb utrymme skalar du upp poolen. Skala ned igen när indexet har återskapats.

För en dedikerad SQL-pooltabell med en ordnad CCI sorterar INTE ALTER INDEX REORGANIZE om data. Om du vill använda data använder du ALTER INDEX REBUILD.

Mer information om ordnat CCI-underhåll finns i Optimera klustrade kolumnlagringsindex.

Funktionsskillnader i SQL Server 2022-funktioner

SQL Server 2022 (16.x) introducerade ordnade grupperade kolumnlagringsindex som liknar funktionen i azure Synapse-dedikerade SQL-pooler.

  • För närvarande har endast SQL Server 2022 (16.x) och senare versioner stöd för klustrade columnstore-förbättrade segmentelimineringsfunktioner för sträng-, binär- och guid-datatyper och datatypen datetimeoffset för skalning som är större än två. Tidigare gäller detta segment eliminering för numeriska data, datum och tid datatyper och datetimeoffset datatyp med skala mindre än eller lika med två.
  • För närvarande har endast SQL Server 2022 (16.x) och senare versioner stöd för klustrad kolumnlagringsradgruppseliminering för prefixet LIKE för predikat, till exempel column LIKE 'string%'. Segmenteliminering stöds inte för icke-prefixanvändning av LIKE, till exempel column LIKE '%string'.

Mer information finns i Nyheter i Columnstore-index.

Exempel

A. Så här söker du efter ordnade kolumner och ordningsföljd:

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. Om du vill ändra kolumnordning lägger du till eller tar bort kolumner från orderlistan eller ändrar från CCI till ordnad CCI:

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

Nästa steg