Vägledning för att utforma distribuerade tabeller med dedikerad SQL pool i Azure Synapse Analytics

  • Artikel
  • 8 minuter för att läsa

Den här artikeln innehåller rekommendationer för att utforma hash-distribuerade och distribuerade tabeller med resursallokering i dedikerade SQL pooler.

Den här artikeln förutsätter att du är bekant med begreppen datadistribution och dataförflyttning i dedikerad SQL pool. Mer information finns i Azure Synapse Analytics arkitektur.

Vad är en distribuerad tabell?

En distribuerad tabell visas som en enda tabell, men raderna lagras faktiskt över 60 distributioner. Raderna distribueras med en hash- eller resursallokeringsalgoritm.

Hash-distribution förbättrar frågeprestanda för stora faktatabeller och är fokus i den här artikeln. Resursallokeringsdistribution är användbart för att förbättra inläsningshastigheten. Dessa designval har en betydande inverkan på att förbättra fråge- och inläsningsprestanda.

Ett annat alternativ för tabellagring är att replikera en liten tabell över alla beräkningsnoder. Mer information finns i Designvägledning för replikerade tabeller. Om du snabbt vill välja bland de tre alternativen kan du gå till Distribuerade tabeller i tabellöversikten.

Som en del av tabelldesignen bör du förstå så mycket som möjligt om dina data och hur data efterfrågas. Tänk dig till exempel följande frågor:

  • Hur stor är tabellen?
  • Hur ofta uppdateras tabellen?
  • Har jag fakta- och dimensionstabeller i en dedikerad SQL pool?

Hash-distribuerad

En hash-distribuerad tabell distribuerar tabellrader över beräkningsnoderna med hjälp av en deterministisk hash-funktion för att tilldela varje rad till en distribution.

Distribuerad tabell

Eftersom identiska värden alltid hash-kodas till SQL har SQL Analytics inbyggd kunskap om radplatserna. I dedikerade SQL används den här kunskapen för att minimera dataförflyttning under frågor, vilket förbättrar frågeprestanda.

Hash-distribuerade tabeller fungerar bra för stora faktatabeller i ett star-schema. De kan ha ett mycket stort antal rader och ändå uppnå höga prestanda. Det finns några designöverväganden som hjälper dig att få den prestanda som det distribuerade systemet har utformats för att ge. Att välja en bra distributionskolumn är ett sådant övervägande som beskrivs i den här artikeln.

Överväg att använda en hash-distribuerad tabell när:

  • Tabellstorleken på disken är mer än 2 GB.
  • Tabellen innehåller ofta infognings-, uppdaterings- och borttagningsåtgärder.

Distribuerad resursallokering

En distribuerad tabell med resursallokering distribuerar tabellrader jämnt över alla distributioner. Tilldelningen av rader till distributioner är slumpmässig. Till skillnad från hash-distribuerade tabeller garanteras inte att rader med lika värden tilldelas till samma distribution.

Därför måste systemet ibland anropa en dataförflyttningsåtgärd för att bättre organisera dina data innan den kan lösa en fråga. Det här extra steget kan göra dina frågor långsammare. Om du till exempel vill ansluta till en resursallokeringstabell måste du vanligtvis omsuffra raderna, vilket är en prestandaträff.

Överväg att använda resursallokeringsdistributionen för tabellen i följande scenarier:

  • När du kommer igång som en enkel startpunkt eftersom det är standardinställningen
  • Om det inte finns någon uppenbar sammanfogningsnyckel
  • Om det inte finns någon lämplig kandidatkolumn för hash-distribution av tabellen
  • Om tabellen inte delar en gemensam kopplingsnyckel med andra tabeller
  • Om koppling är mindre viktig än andra kopplingar i frågan
  • När tabellen är en tillfällig mellanlagringstabell

Självstudien Load New York taxicab data (Load New York taxicab data) ger ett exempel på inläsning av data till en mellanlagringstabell med resursallokering.

Välja en distributionskolumn

En hash-distribuerad tabell har en distributionskolumn som är hash-nyckeln. Följande kod skapar till exempel en hash-distribuerad tabell med ProductKey som distributionskolumn.

CREATE TABLE [dbo].[FactInternetSales]
(   [ProductKey]            int          NOT NULL
,   [OrderDateKey]          int          NOT NULL
,   [CustomerKey]           int          NOT NULL
,   [PromotionKey]          int          NOT NULL
,   [SalesOrderNumber]      nvarchar(20) NOT NULL
,   [OrderQuantity]         smallint     NOT NULL
,   [UnitPrice]             money        NOT NULL
,   [SalesAmount]           money        NOT NULL
)
WITH
(   CLUSTERED COLUMNSTORE INDEX
,  DISTRIBUTION = HASH([ProductKey])
);

Data som lagras i distributionskolumnen kan uppdateras. Uppdateringar av data i distributionskolumnen kan resultera i en datablandning.

Att välja en distributionskolumn är ett viktigt designbeslut eftersom värdena i den här kolumnen avgör hur raderna distribueras. Det bästa valet beror på flera faktorer och omfattar vanligtvis kompromisser. När du har valt en distributionskolumn kan du inte ändra den.

Om du inte valde den bästa kolumnen första gången kan du använda CREATE TABLE AS SELECT (CTAS) för att skapa tabellen på nytt med en annan distributionskolumn.

Välj en distributionskolumn med data som distribueras jämnt

För bästa prestanda bör alla distributioner ha ungefär samma antal rader. När en eller flera distributioner har ett oproportionerlig antal rader slutför vissa distributioner sin del av en parallell fråga före andra. Eftersom frågan inte kan slutföras förrän alla distributioner har slutfört bearbetningen är varje fråga bara lika snabb som den långsammaste distributionen.

  • Dataskevning innebär att data inte distribueras jämnt mellan fördelningarna
  • Bearbetningsskevitet innebär att vissa distributioner tar längre tid än andra när du kör parallella frågor. Detta kan inträffa när data är skeva.

Om du vill balansera den parallella bearbetningen väljer du en distributionskolumn som:

  • Har många unika värden. Kolumnen kan ha dubblettvärden. Alla rader med samma värde tilldelas till samma distribution. Eftersom det finns 60 distributioner kan vissa distributioner > 1 unika värden medan andra slutar med noll värden.
  • Har inte NULL:er eller bara några få NUL:er. Om alla värden i kolumnen är NULL tilldelas alla rader till samma distribution. Därför är frågebearbetning skev till en distribution och drar inte nytta av parallell bearbetning.
  • Är inte en datumkolumn. Alla data för samma datum hamnar i samma distribution. Om flera användare filtrerar på samma datum, gör bara 1 av de 60 distributionerna allt bearbetningsarbete.

Välj en distributionskolumn som minimerar dataflyttningen

För att få rätt frågeresultat kan frågor flytta data från en beräkningsnod till en annan. Dataförflyttning sker vanligtvis när frågor har kopplingar och aggregeringar i distribuerade tabeller. Att välja en distributionskolumn som hjälper till att minimera dataflyttning är en av de viktigaste strategierna för att optimera prestanda för din dedikerade SQL pool.

Om du vill minimera dataflyttningen väljer du en distributionskolumn som:

  • Används i JOIN GROUP BY satserna , DISTINCT , , OVER och HAVING . När två stora faktatabeller har frekventa kopplingar förbättras frågeprestanda när du distribuerar båda tabellerna i en av kopplingskolumnerna. När en tabell inte används i kopplingar kan du distribuera tabellen på en kolumn som ofta finns i GROUP BY -satsen.
  • Används inte i WHERE satser. Detta kan begränsa frågan så att den inte körs på alla distributioner.
  • Är inte en datumkolumn. WHERE -satser filtrerar ofta efter datum. När detta inträffar kan all bearbetning bara köras på ett fåtal distributioner.

Vad du gör när ingen av kolumnerna är en bra distributionskolumn

Om ingen av dina kolumner har tillräckligt med distinkta värden för en distributionskolumn kan du skapa en ny kolumn som en sammansatt kolumn med ett eller flera värden. Använd den sammansatta distributionskolumnen som en kopplingskolumn i frågor för att undvika dataförflyttning under frågekörning.

När du utformar en hash-distribuerad tabell är nästa steg att läsa in data i tabellen. Vägledning för inläsning finns i Översikt över inläsning.

Så här ser du om distributionskolumnen är ett bra alternativ

När data har lästs in i en hash-distribuerad tabell kontrollerar du hur jämnt raderna fördelas mellan de 60 distributionerna. Raderna per distribution kan variera upp till 10 % utan märkbar inverkan på prestandan.

Kontrollera om tabellen har dataskevnad

Ett snabbt sätt att söka efter dataskevnad är att använda DBCC PDW_SHOWSPACEUSED. Följande kod SQL returnerar antalet tabellrader som lagras i var och en av de 60 distributionerna. För balanserad prestanda bör raderna i den distribuerade tabellen vara jämnt fördelade över alla distributioner.

-- Find data skew for a distributed table
DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');

Så här identifierar du vilka tabeller som har mer än 10 % dataskevning:

  1. Skapa vyn dbo.vTableSizes som visas i översiktsartikeln Tabeller.
  2. Kör följande fråga:
select *
from dbo.vTableSizes
where two_part_name in
    (
    select two_part_name
    from dbo.vTableSizes
    where row_count > 0
    group by two_part_name
    having (max(row_count * 1.000) - min(row_count * 1.000))/max(row_count * 1.000) >= .10
    )
order by two_part_name, row_count
;

Kontrollera frågeplaner för dataförflyttning

En bra distributionskolumn gör att kopplingar och aggregeringar kan ha minimal dataförflyttning. Detta påverkar hur kopplingar ska skrivas. För att få minimal dataförflyttning för en koppling i två hash-distribuerade tabeller måste en av kopplingskolumnerna vara distributionskolumnen. När två hash-distribuerade tabeller ansluts till en distributionskolumn av samma datatyp kräver inte koppling dataförflyttning. Kopplingar kan använda ytterligare kolumner utan att medföra dataförflyttning.

Så här undviker du dataflyttning under en koppling:

  • Tabellerna som ingår i koppling måste hash-distribueras på en av kolumnerna som deltar i koppling.
  • Datatyperna för kopplingskolumnerna måste matcha mellan båda tabellerna.
  • Kolumnerna måste vara sammanfogade med en equals-operator.
  • Kopplingstypen kanske inte är CROSS JOIN en .

Om du vill se om dataförflyttning uppstår i frågor kan du titta på frågeplanen.

Lösa problem med distributionskolumner

Det är inte nödvändigt att lösa alla fall av dataskev. Att distribuera data handlar om att hitta rätt balans mellan att minimera dataskevnad och dataförflyttning. Det går inte alltid att minimera både dataskev och dataförflyttning. Ibland kan fördelen med att ha den minimala dataförflyttningen uppväga effekten av att ha dataskev.

För att avgöra om du ska lösa dataskevningar i en tabell bör du förstå så mycket som möjligt om datavolymerna och frågorna i din arbetsbelastning. Du kan använda stegen i artikeln Frågeövervakning för att övervaka effekten av snedställning på frågeprestanda. Mer specifikt kan du titta efter hur lång tid det tar för stora frågor att slutföras på enskilda distributioner.

Eftersom du inte kan ändra distributionskolumnen i en befintlig tabell är det vanliga sättet att lösa datasnedställning att skapa om tabellen med en annan distributionskolumn.

Skapa tabellen på nytt med en ny distributionskolumn

Det här exemplet använder CREATE TABLE AS SELECT för att skapa en tabell med en annan hash-distributionskolumn.

CREATE TABLE [dbo].[FactInternetSales_CustomerKey]
WITH (  CLUSTERED COLUMNSTORE INDEX
     ,  DISTRIBUTION =  HASH([CustomerKey])
     ,  PARTITION       ( [OrderDateKey] RANGE RIGHT FOR VALUES (   20000101, 20010101, 20020101, 20030101
                                                                ,   20040101, 20050101, 20060101, 20070101
                                                                ,   20080101, 20090101, 20100101, 20110101
                                                                ,   20120101, 20130101, 20140101, 20150101
                                                                ,   20160101, 20170101, 20180101, 20190101
                                                                ,   20200101, 20210101, 20220101, 20230101
                                                                ,   20240101, 20250101, 20260101, 20270101
                                                                ,   20280101, 20290101
                                                                )
                        )
    )
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
OPTION  (LABEL  = 'CTAS : FactInternetSales_CustomerKey')
;

--Create statistics on new table
CREATE STATISTICS [ProductKey] ON [FactInternetSales_CustomerKey] ([ProductKey]);
CREATE STATISTICS [OrderDateKey] ON [FactInternetSales_CustomerKey] ([OrderDateKey]);
CREATE STATISTICS [CustomerKey] ON [FactInternetSales_CustomerKey] ([CustomerKey]);
CREATE STATISTICS [PromotionKey] ON [FactInternetSales_CustomerKey] ([PromotionKey]);
CREATE STATISTICS [SalesOrderNumber] ON [FactInternetSales_CustomerKey] ([SalesOrderNumber]);
CREATE STATISTICS [OrderQuantity] ON [FactInternetSales_CustomerKey] ([OrderQuantity]);
CREATE STATISTICS [UnitPrice] ON [FactInternetSales_CustomerKey] ([UnitPrice]);
CREATE STATISTICS [SalesAmount] ON [FactInternetSales_CustomerKey] ([SalesAmount]);

--Rename the tables
RENAME OBJECT [dbo].[FactInternetSales] TO [FactInternetSales_ProductKey];
RENAME OBJECT [dbo].[FactInternetSales_CustomerKey] TO [FactInternetSales];

Nästa steg

Om du vill skapa en distribuerad tabell använder du något av följande uttryck: