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

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

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

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 naar alle rekenknooppunten. Zie Ontwerp richtlijnen voor gerepliceerde tabellen voor meer informatie. Zie Gedistribueerde tabellen in het overzicht van tabellen om snel een keuze te maken uit de drie opties.

Als onderdeel van het tabelontwerp moet u zoveel mogelijk inzicht krijgen in uw gegevens en hoe de gegevens worden opgevraagd. Denk bijvoorbeeld aan de volgende 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 hashfunctie om elke rij toe te wijzen aan één distributie.

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

Met hash gedistribueerde tabellen werken goed voor grote feitentabellen in een sterschema. Ze kunnen een groot aantal rijen hebben en nog steeds hoge prestaties leveren. Er zijn enkele ontwerpoverwegingen die u helpen de prestaties te krijgen die het gedistribueerde systeem biedt. Het kiezen van een goede distributiekolom is een van deze overwegingen die in dit artikel worden beschreven.

Overweeg het gebruik van een met hash gedistribueerde tabel wanneer:

• De tabelgrootte op schijf is meer dan 2 GB.
• De tabel heeft frequente invoeg-, update- en verwijderbewerkingen.

Round robin gedistribueerd

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

Als gevolg hiervan moet het systeem soms een bewerking voor gegevens movement aanroepen om uw gegevens beter te organiseren voordat een query kan worden opgelost. Deze extra stap kan uw query's vertragen. Als u bijvoorbeeld een round robin-tabel wilt samenvoegen, moeten de rijen doorgaans opnieuw worden geseed. Dit is een prestatieverbetering.

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 startpunt, omdat dit de standaardinstelling is
• Als er geen duidelijke sameningssleutel is
• Als er geen goede kandidaatkolom is voor hash-distributie van de tabel
• Als de tabel geen gemeenschappelijke joinsleutel deelt met andere tabellen
• Als de join minder belangrijk is dan andere joins in de query
• Wanneer de tabel een tijdelijke faseringstabel is

De zelfstudie Load New York taxicab data (New York taxicab-gegevens laden) geeft een voorbeeld van het laden van gegevens in een round robin-faseringstabel.

Een distributiekolom kiezen

Een met hash gedistribueerde tabel heeft een distributiekolom die de hash-sleutel is. Met de volgende code wordt bijvoorbeeld een met 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. Updates van gegevens in de distributiekolom kunnen leiden tot een willekeurige gegevensbewerking.

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

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

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 deel van een parallelle query vóór andere. Omdat de query niet kan worden voltooid totdat alle distributies zijn voltooid, is elke query alleen zo snel als de langzaamste distributie.

• Scheefheid van gegevens betekent dat de gegevens niet gelijkmatig over de distributies worden verdeeld
• Scheef verwerken 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 NULL's of slechts enkele NULL's. Als alle waarden in de kolom bijvoorbeeld NULL zijn, worden alle rijen toegewezen aan dezelfde verdeling. Als gevolg hiervan is de queryverwerking vertakking tot één distributie en profiteert niet van parallelle verwerking.
• Is geen datumkolom. Alle gegevens voor dezelfde datum komen in dezelfde verdeling terecht. Als meerdere gebruikers allemaal op dezelfde datum filteren, doen slechts 1 van de 60 distributies al het verwerkingswerk.

Een distributiekolom kiezen die de verplaatsing van gegevens minimaliseert

Om de juiste queryresultaatquery's te krijgen, kunnen gegevens van het ene reken knooppunt naar het andere worden verplaatst. Gegevens verplaatsen gebeurt meestal wanneer query's joins en aggregaties hebben op gedistribueerde tabellen. Het kiezen van een distributiekolom die helpt de verplaatsing van gegevens te minimaliseren, is een van de belangrijkste strategieën voor het optimaliseren van de prestaties van uw toegewezen SQL groep.

Als u de verplaatsing van gegevens wilt minimaliseren, selecteert u een distributiekolom die:

• Wordt gebruikt in JOIN GROUP BY de -, DISTINCT OVER -, -, - HAVING en -component. Wanneer twee grote feitentabellen regelmatig worden samen gebruikt, 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 naar een kolom die vaak in de -component GROUP BY staat.
• Wordt niet gebruikt in WHERE -component. Dit kan de query beperken tot niet alle distributies.
• Is geen datumkolom. WHERE -component wordt vaak gefilterd op datum. Als dit gebeurt, kan alle verwerking worden uitgevoerd op slechts een paar distributies.

Wat te 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 gegevensver movement wilt voorkomen tijdens het uitvoeren van query's, gebruikt u de samengestelde distributiekolom als een join-kolom in query's.

Wanneer u een met hash gedistribueerde tabel ontwerpt, bestaat de volgende stap uit het laden van gegevens in de tabel. Zie Overzicht van laden voor hulp bij het laden.

Zien of uw distributiekolom een goede keuze is

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

Bepalen of de tabel gegevensverschil heeft

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

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

Identificeren welke tabellen meer dan 10% gegevensverschil hebben:

1. Maak de weergave dbo.vTableSizes die wordt weergegeven in het artikel Overzicht van 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 voor gegevensverkeer controleren

Met een goede distributiekolom kunnen joins en aggregaties minimale gegevensver movement hebben. Dit is van invloed op de manier waarop joins moeten worden geschreven. Als u minimale verplaatsing van gegevens wilt voor een join op twee met hash gedistribueerde tabellen, moet een van de join-kolommen de distributiekolom zijn. Wanneer twee met hash gedistribueerde tabellen worden samengedeeld in een distributiekolom van hetzelfde gegevenstype, vereist de join geen verplaatsing van gegevens. Joins kunnen extra kolommen gebruiken zonder gegevens te verplaatsen.

Ga als volg te werk om gegevensverkeer tijdens een join te voorkomen:

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

Als u wilt zien of query's gegevens verplaatsen, kunt u het queryplan bekijken.

Een probleem met een distributiekolom oplossen

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

Als u wilt bepalen of u gegevensverschil in een tabel moet oplossen, moet u zo veel mogelijk inzicht krijgen in de gegevensvolumes en query's in uw workload. U kunt de stappen in het artikel Querybewaking gebruiken om de impact van scheefheid op queryprestaties te bewaken. Kijk met name hoe lang het duurt voordat grote query's zijn voltooid op afzonderlijke distributies.

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

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

Gebruik een van de volgende instructies om een gedistribueerde tabel te maken:

• CREATE TABLE (toegewezen SQL pool)
• CREATE TABLE AS SELECT (toegewezen SQL-pool)