Richtlijnen voor het ontwerpen van gedistribueerde tabellen met behulp van een toegewezen 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-pools.

In dit artikel wordt ervan uitgegaan dat u bekend bent met de concepten voor gegevensdistributie en gegevensverplaatsing in een 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 in feite opgeslagen in 60 distributies. De rijen worden gedistribueerd met een hash- of round robin-algoritme.

Hash-distributie verbetert de queryprestaties op 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 de prestaties van query's en laden.

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 een keuze te maken uit de drie opties.

Als onderdeel van het tabelontwerp moet u zoveel mogelijk inzicht hebben in uw gegevens en hoe de gegevens worden opgevraagd.  Neem bijvoorbeeld 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 verdeelt 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 een toegewezen SQL-pool wordt deze kennis gebruikt om gegevensverplaatsing tijdens query's te minimaliseren, waardoor de queryprestaties worden verbeterd.

Met hash gedistribueerde tabellen werken goed voor grote feitentabellen in een stervormig schema. Ze kunnen een zeer groot aantal rijen hebben en toch hoge prestaties leveren. Er zijn enkele ontwerpoverwegingen die u helpen bij het verkrijgen van de prestaties die het gedistribueerde systeem moet bieden. Het kiezen van een goede distributiekolom of -kolommen is een van deze overwegingen die in dit artikel worden beschreven.

Overweeg het gebruik van een met hash gedistribueerde tabel in de volgende gevallen:

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

Round robin gedistribueerd

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

Als gevolg hiervan moet het systeem soms een bewerking voor gegevensverplaatsing aanroepen om uw gegevens beter te organiseren voordat een query kan worden opgelost. Deze extra stap kan uw query's vertragen. Als u bijvoorbeeld lid wordt van een round robin-tabel, moet u meestal de rijen opnieuw wijzigen. 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 uitgangspunt, omdat dit de standaardinstelling is
• Als er geen duidelijke samenvoegsleutel is
• Als er geen goede kandidaatkolom is voor hashdistributie 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 Taxicab-gegevens in New York 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 of set kolommen die de hashsleutel is. Met de volgende code maakt u bijvoorbeeld een met hash gedistribueerde tabel 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])
);

Hash-distributie kan worden toegepast op meerdere kolommen voor een gelijkmatigere verdeling van de basistabel. Met distributie met meerdere kolommen kunt u maximaal acht kolommen kiezen voor distributie. Dit vermindert niet alleen het scheeftrekken van gegevens in de loop van de tijd, maar verbetert ook de queryprestaties. Bijvoorbeeld:

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],   [OrderDateKey],   [CustomerKey] ,   [PromotionKey])
);

Notitie

Distributie met meerdere kolommen in Azure Synapse Analytics kan worden ingeschakeld door het compatibiliteitsniveau van de database te 50 wijzigen in met deze opdracht. ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50; Zie ALTER DATABASE SCOPED CONFIGURATION (ALTER DATABASE SCOPED CONFIGURATION) voor meer informatie over het instellen van het databasecompatibiliteitsniveau. Zie CREATE MATERIALIZED VIEW, CREATE TABLE of CREATE TABLE AS SELECT voor meer informatie over distributies met meerdere kolommen.

Gegevens die zijn opgeslagen in de distributiekolom(s) kunnen worden bijgewerkt. Updates gegevens in een of meer distributiekolommen kan leiden tot een willekeurige bewerking van de gegevens.

Het kiezen van een of meer distributiekolom(s) is een belangrijke ontwerpbeslissing, omdat de waarden in de hashkolom(s) bepalen hoe de rijen worden verdeeld. De beste keuze is afhankelijk van verschillende factoren en omvat meestal compromissen. Zodra een distributiekolom of kolomset is gekozen, kunt u deze niet meer wijzigen. Als u de eerste keer niet de beste kolom(en) hebt gekozen, kunt u CREATE TABLE AS SELECT (CTAS) gebruiken om de tabel opnieuw te maken met de gewenste distributie-hashsleutel.

Kies een distributiekolom 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 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 scheeftrekken 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 of set kolommen die:

• Heeft veel unieke waarden. De distributiekolom(s) kunnen 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 met nulwaarden kunnen eindigen.
• Heeft geen NULL's of heeft slechts een paar NULL's. Als bijvoorbeeld alle waarden in de distributiekolom(s) NULL zijn, worden alle rijen toegewezen aan dezelfde distributie. Als gevolg hiervan is queryverwerking scheef ten opzichte van één distributie en profiteert niet van parallelle verwerking.
• Is geen datumkolom. Alle gegevens voor dezelfde datum landen in dezelfde distributie of clusteren records op datum. Als meerdere gebruikers allemaal filteren op dezelfde datum (zoals de datum van vandaag), doet slechts 1 van de 60 distributies alle verwerkingstaken.

Kies een distributiekolom die gegevensverplaatsing minimaliseert

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

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

• Wordt gebruikt in JOINcomponenten , GROUP BY, DISTINCT, OVER, en HAVING . Wanneer twee grote feitentabellen vaak joins hebben, verbeteren de queryprestaties wanneer u beide tabellen distribueert over een van de joinkolommen. Wanneer een tabel niet wordt gebruikt in joins, kunt u overwegen de tabel te distribueren over een kolom of kolomset die vaak in de GROUP BY component voorkomt.
• Wordt niet gebruikt in WHERE componenten. Wanneer de component van WHERE een query en de distributiekolommen van de tabel zich in dezelfde kolom bevinden, kan de query te maken krijgen met een hoge gegevensscheefheid, waardoor de verwerkingsbelasting op slechts enkele distributies valt. Dit is van invloed op de queryprestaties. Idealiter delen veel distributies de verwerkingsbelasting.
• Is geen datumkolom. WHERE -componenten filteren vaak op datum. Als dit gebeurt, kan alle verwerking worden uitgevoerd op slechts enkele distributies die van invloed zijn op de queryprestaties. In het ideale gevallen delen veel distributies de verwerkingsbelasting.

Zodra u een met hash gedistribueerde tabel hebt ontworpen, is de volgende stap het laden van gegevens in de tabel. Zie Overzicht van laden voor hulp bij het laden.

Hoe u kunt zien of uw distributie een goede keuze is

Nadat de gegevens in een met hash gedistribueerde tabel zijn geladen, controleert u hoe de rijen gelijkmatig worden verdeeld over de 60 distributies. De rijen per distributie kunnen variëren tot 10% zonder merkbare invloed op de prestaties. Houd rekening met de volgende onderwerpen om uw distributiekolom(s) te evalueren.

Bepalen of de tabel gegevens scheeftrekken heeft

Een snelle manier om te controleren op gegevensverschil, is door DBCC-PDW_SHOWSPACEUSED te gebruiken. De volgende SQL-code retourneert het aantal tabelrijen dat is opgeslagen in elk van de 60 distributies. 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');

Ga als volgende te werk om te bepalen welke tabellen meer dan 10% gegevensscheefheid hebben:

1. Maak de weergave dbo.vTableSizes die wordt weergegeven in het artikel Tabellenoverzicht .
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 gegevensverplaatsing controleren

Een goede distributiekolomset 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 met hash gedistribueerde tabellen, moet een van de join-kolommen zich in een distributiekolom of -kolom(en) bevinden. Wanneer twee met hash gedistribueerde tabellen worden samengevoegd in een distributiekolom van hetzelfde gegevenstype, is voor de join geen gegevensverplaatsing vereist. Joins kunnen extra kolommen gebruiken zonder gegevensverplaatsing.

Gegevensverplaatsing tijdens een join voorkomen:

• De tabellen die betrokken zijn bij de join moeten hash-gedistribueerd zijn 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 operator equals.
• Het jointype mag geen zijn CROSS JOIN.

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 gegevensverschil op te lossen. Het distribueren van gegevens is een kwestie van het vinden van de juiste balans tussen het minimaliseren van scheeftrekken van gegevens en het verplaatsen van gegevens. Het is niet altijd mogelijk om zowel gegevensscheefheid als gegevensverplaatsing te minimaliseren. Soms kan het voordeel van de minimale gegevensverplaatsing zwaarder wegen dan de impact van het hebben van gegevensverschil.

Als u wilt beslissen of u het scheeftrekken van gegevens 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 scheeftrekken op queryprestaties te controleren. Kijk in het bijzonder hoe lang het duurt om grote query's te voltooien op afzonderlijke distributies.

Omdat u de distributiekolom(s) in een bestaande tabel niet kunt wijzigen, is de gebruikelijke manier om gegevensverschil op te lossen door de tabel opnieuw te maken met een of meer andere distributiekolom(s).

De tabel opnieuw maken met een nieuwe distributiekolomset

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

CREATE TABLE AS SELECT Gebruik eerst (CTAS) de nieuwe tabel met de nieuwe sleutel. Maak vervolgens de statistieken opnieuw en verwissel de tabellen door ze een andere naam te geven.

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 deze instructies om een gedistribueerde tabel te maken:

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