Richtlijnen voor het ontwerpen van gedistribueerde tabellen met behulp van toegewezen SQL pool in Azure Synapse Analytics

Dit artikel bevat aanbevelingen voor het ontwerpen van gedistribueerde hash- en round robin gedistribueerde tabellen in toegewezen SQL pools.

In dit artikel wordt ervan uitgegaan dat u bekend bent met concepten voor gegevensdistributie en gegevensverplaatsing in toegewezen SQL pool. Zie Azure Synapse Analytics-architectuur voor meer informatie.

Wat is een gedistribueerde tabel?

Een gedistribueerde tabel wordt weergegeven als één tabel, maar de rijen worden daadwerkelijk opgeslagen in 60 distributies. De rijen worden gedistribueerd met een hash- of round robin-algoritme.

Hash-distributie verbetert de queryprestaties voor grote feitentabellen en is de focus van dit artikel. Round robin-distributie is handig voor het verbeteren van de laadsnelheid. Deze ontwerpkeuzen hebben een aanzienlijke invloed op het verbeteren van query- en laadprestaties.

Een andere optie voor tabelopslag is het repliceren van een kleine tabel op alle rekenknooppunten. Zie Ontwerprichtlijnen voor gerepliceerde tabellen voor meer informatie. Zie Gedistribueerde tabellen in het overzicht van tabellen om snel uit de drie opties te kiezen.

Als onderdeel van het tabelontwerp begrijpt u zoveel mogelijk over uw gegevens en hoe de gegevens worden opgevraagd.  Denk bijvoorbeeld aan deze vragen:

  • Hoe groot is de tabel?
  • Hoe vaak wordt de tabel vernieuwd?
  • Heb ik feiten- en dimensietabellen in een toegewezen SQL pool?

Gedistribueerde hash

Een met hash gedistribueerde tabel distribueert tabelrijen over de rekenknooppunten met behulp van een deterministische hash-functie om elke rij toe te wijzen aan één distributie.

Distributed table

Aangezien identieke waarden altijd hashen naar dezelfde distributie, beschikt SQL Analytics over ingebouwde kennis van de rijlocaties. In toegewezen SQL pool wordt deze kennis gebruikt om gegevensverplaatsing tijdens query's te minimaliseren, waardoor de queryprestaties worden verbeterd.

Hash-gedistribueerde tabellen werken goed voor grote feitentabellen in een stervormig schema. Ze kunnen zeer grote aantallen rijen hebben en nog steeds hoge prestaties bereiken. Er zijn enkele ontwerpoverwegingen waarmee u de prestaties van het gedistribueerde systeem kunt verkrijgen. Het kiezen van een goede distributiekolom is een dergelijke overweging die in dit artikel wordt beschreven.

Overweeg het gebruik van een door hash gedistribueerde tabel wanneer:

  • De tabelgrootte op schijf is meer dan 2 GB.
  • De tabel bevat veelgebruikte bewerkingen voor invoegen, bijwerken en verwijderen.

Round robin gedistribueerd

Met een gedistribueerde tabel round robin worden tabelrijen gelijkmatig verdeeld over alle distributies. De toewijzing van rijen aan distributies is willekeurig. In tegenstelling tot hash-gedistribueerde tabellen worden rijen met gelijke waarden niet gegarandeerd toegewezen aan dezelfde distributie.

Als gevolg hiervan moet het systeem soms een bewerking voor gegevensverplaatsing aanroepen om uw gegevens beter te ordenen voordat een query kan worden omgezet. Deze extra stap kan uw query's vertragen. Als u bijvoorbeeld een round robin-tabel samenvoegt, moeten de rijen meestal opnieuw worden geshuffd. Dit is een prestatietreffer.

Overweeg het gebruik van de round robin-distributie voor uw tabel in de volgende scenario's:

  • Wanneer u aan de slag gaat als een eenvoudig beginpunt, omdat dit de standaardinstelling is
  • Als er geen duidelijke samenvoegsleutel is
  • Als er geen goede kandidaatkolom is voor het distribueren van de tabel met hashs
  • Als de tabel geen gemeenschappelijke joinsleutel met andere tabellen deelt
  • Als de join minder significant is dan andere joins in de query
  • Wanneer de tabel een tijdelijke faseringstabel is

De zelfstudie Taxicab-gegevens laden in New York geeft een voorbeeld van het laden van gegevens in een round robin-faseringstabel.

Een distributiekolom kiezen

Een door hash gedistribueerde tabel heeft een distributiekolom die de hashsleutel is. Met de volgende code wordt bijvoorbeeld een door hash gedistribueerde tabel gemaakt met ProductKey als distributiekolom.

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])
);

Gegevens die zijn opgeslagen in de distributiekolom kunnen worden bijgewerkt. Bijwerken van gegevens in de distributiekolom kan leiden tot een willekeurige bewerking van gegevens.

Het kiezen van een distributiekolom is een belangrijke ontwerpbeslissing omdat de waarden in deze kolom bepalen hoe de rijen worden gedistribueerd. De beste keuze is afhankelijk van verschillende factoren en omvat meestal compromissen. Zodra een distributiekolom is gekozen, kunt u deze niet meer wijzigen.

Als u de beste kolom de eerste keer niet hebt gekozen, kunt u CREATE TABLE AS SELECT (CTAS) gebruiken om de tabel opnieuw te maken met een andere distributiekolom.

Een distributiekolom kiezen met gegevens die gelijkmatig worden verdeeld

Voor de beste prestaties moeten alle distributies ongeveer hetzelfde aantal rijen hebben. Wanneer een of meer distributies een onevenredig aantal rijen hebben, voltooien sommige distributies hun gedeelte van een parallelle query voordat anderen. Omdat de query pas kan worden voltooid als alle distributies zijn verwerkt, is elke query slechts zo snel als de langzaamste distributie.

  • Scheeftrekken van gegevens betekent dat de gegevens niet gelijkmatig over de distributies worden verdeeld
  • Het verwerken van scheefheid betekent dat sommige distributies langer duren dan andere bij het uitvoeren van parallelle query's. Dit kan gebeuren wanneer de gegevens scheef zijn.

Als u de parallelle verwerking wilt verdelen, selecteert u een distributiekolom die:

  • Heeft veel unieke waarden. De kolom kan dubbele waarden bevatten. Alle rijen met dezelfde waarde worden toegewezen aan dezelfde distributie. Omdat er 60 distributies zijn, kunnen sommige distributies 1 unieke waarden hebben > , terwijl andere kunnen eindigen met nulwaarden.
  • Heeft geen NULLs of heeft slechts een paar NULL's. Als alle waarden in de kolom NULL zijn, worden alle rijen toegewezen aan dezelfde distributie. Als gevolg hiervan is queryverwerking scheefgetrokken tot één distributie en profiteert niet van parallelle verwerking.
  • Is geen datumkolom. Alle gegevens voor dezelfde datum komen in dezelfde distributie terecht. Als meerdere gebruikers allemaal op dezelfde datum filteren, doen slechts 1 van de 60 distributies al het verwerkingswerk.

Een distributiekolom kiezen waarmee gegevensverplaatsing wordt geminimaliseerd

Als u de juiste queryresultatenquery's wilt ophalen, kunnen gegevens van het ene rekenknooppunt naar het andere worden verplaatst. Gegevensverplaatsing gebeurt meestal wanneer query's joins en aggregaties hebben voor gedistribueerde tabellen. Het kiezen van een distributiekolom waarmee gegevensverplaatsing wordt geminimaliseerd, is een van de belangrijkste strategieën voor het optimaliseren van de prestaties van uw toegewezen SQL pool.

Als u gegevensverplaatsing wilt minimaliseren, selecteert u een distributiekolom die:

  • Wordt gebruikt in JOIN, GROUP BY, DISTINCT, en OVERHAVING componenten. Wanneer twee grote feitentabellen vaak joins hebben, worden de queryprestaties verbeterd wanneer u beide tabellen distribueert op een van de joinkolommen. Wanneer een tabel niet wordt gebruikt in joins, kunt u overwegen om de tabel te distribueren op een kolom die vaak in de GROUP BY component staat.
  • Wordt niet gebruikt in WHERE componenten. Hierdoor kan de query worden beperkt tot niet op alle distributies worden uitgevoerd.
  • Is geen datumkolom. WHERE componenten filteren vaak op datum. Als dit gebeurt, kan alle verwerking worden uitgevoerd op slechts enkele distributies.

Wat u moet doen wanneer geen van de kolommen een goede distributiekolom is

Als geen van uw kolommen voldoende afzonderlijke waarden voor een distributiekolom heeft, kunt u een nieuwe kolom maken als een samengestelde van een of meer waarden. Als u gegevensverplaatsing tijdens het uitvoeren van query's wilt voorkomen, gebruikt u de samengestelde distributiekolom als joinkolom in query's.

Zodra u een door hash gedistribueerde tabel ontwerpt, is de volgende stap het laden van gegevens in de tabel. Zie Het laadoverzicht voor hulp bij het laden.

Hoe u kunt zien of uw distributiekolom een goede keuze is

Nadat gegevens in een door hash gedistribueerde tabel zijn geladen, controleert u hoe gelijkmatig de rijen worden verdeeld over de 60 distributies. De rijen per distributie kunnen tot 10% variëren zonder merkbare invloed op de prestaties.

Bepalen of de tabel scheeftrekken van gegevens bevat

Een snelle manier om te controleren op scheeftrekken van gegevens is door DBCC-PDW_SHOWSPACEUSED te gebruiken. De volgende SQL code retourneert het aantal tabelrijen dat in elk van de 60 distributies is opgeslagen. Voor evenwichtige prestaties moeten de rijen in de gedistribueerde tabel gelijkmatig worden verdeeld over alle distributies.

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

Om te bepalen welke tabellen meer dan 10% gegevensverschil hebben:

  1. Maak de weergave dbo.vTableSizes die wordt weergegeven in het overzichtsartikel Tabellen .
  2. Voer de volgende query uit.
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
;

Queryplannen controleren op gegevensverplaatsing

Een goede distributiekolom zorgt ervoor dat joins en aggregaties minimale gegevensverplaatsing hebben. Dit is van invloed op de manier waarop joins moeten worden geschreven. Als u een minimale gegevensverplaatsing wilt krijgen voor een join in twee hash-gedistribueerde tabellen, moet een van de joinkolommen de distributiekolom zijn. Wanneer twee hash-gedistribueerde tabellen worden samengevoegd op een distributiekolom van hetzelfde gegevenstype, is voor de join geen gegevensverplaatsing vereist. Joins kunnen extra kolommen gebruiken zonder gegevensverplaatsing te hoeven maken.

Gegevensverplaatsing voorkomen tijdens een join:

  • De tabellen die betrokken zijn bij de join, moeten hash zijn verdeeld over een van de kolommen die deelnemen aan de join.
  • De gegevenstypen van de joinkolommen moeten overeenkomen tussen beide tabellen.
  • De kolommen moeten worden samengevoegd met een gelijk-operator.
  • Het jointype is CROSS JOINmogelijk geen .

Als u wilt zien of query's gegevensverplaatsing ondervinden, kunt u het queryplan bekijken.

Een probleem met een distributiekolom oplossen

Het is niet nodig om alle gevallen van gegevens scheeftrekken op te lossen. Het distribueren van gegevens is een kwestie van het vinden van de juiste balans tussen het minimaliseren van gegevens scheefheid en gegevensverplaatsing. Het is niet altijd mogelijk om zowel gegevens scheeftrekken als gegevensverplaatsing te minimaliseren. Soms kan het voordeel van het hebben van de minimale gegevensverplaatsing opwegen tegen de impact van het scheeftrekken van gegevens.

Als u wilt bepalen of u gegevens scheeftrekken in een tabel moet oplossen, moet u zoveel mogelijk inzicht hebben in de gegevensvolumes en query's in uw workload. U kunt de stappen in het artikel Querybewaking gebruiken om de impact van scheefheid in queryprestaties te bewaken. Zoek met name hoe lang het duurt voordat grote query's zijn voltooid voor afzonderlijke distributies.

Omdat u de distributiekolom in een bestaande tabel niet kunt wijzigen, is de gebruikelijke manier om gegevens scheefheid op te lossen door de tabel opnieuw te maken met een andere distributiekolom.

De tabel opnieuw maken met een nieuwe distributiekolom

In dit voorbeeld wordt CREATE TABLE AS SELECT gebruikt om een tabel met een andere hash-distributiekolom opnieuw te maken.

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

Volgende stappen

Als u een gedistribueerde tabel wilt maken, gebruikt u een van de volgende instructies: