Leitfaden zum Entwerfen von verteilten Tabellen mithilfe eines dedizierten SQL-Pools in Azure Synapse Analytics

Dieser Artikel enthält Empfehlungen für das Entwerfen von Tabellen mit Hashverteilung und verteilten Roundrobintabellen in dedizierten SQL-Pools

In diesem Artikel wird davon ausgegangen, dass Sie mit den Konzepten der Datenverteilung und -verschiebung im dedizierten SQL-Pool vertraut sind. Weitere Informationen finden Sie unter Azure Synapse Analytics-Architektur.

Was ist eine verteilte Tabelle?

Eine verteilte Tabelle wird zwar als einzelne Tabelle dargestellt, tatsächlich sind die Zeilen jedoch in 60 Verteilungen gespeichert. Die Zeilen werden mit einem Hash- oder Roundrobinalgorithmus verteilt.

Die Hashverteilung verbessert die Abfrageleistung bei umfangreichen Faktentabellen und bildet den Schwerpunkt dieses Artikels. Roundrobinverteilung ermöglicht die Verbesserung der Ladegeschwindigkeit. Durch diese Entwurfsentscheidungen lassen sich Abfrageleistung und Ladegeschwindigkeit erheblich verbessern.

Eine andere Tabellenspeicheroption besteht darin, eine kleine Tabelle auf allen Computeknoten zu replizieren. Weitere Informationen finden Sie unter Entwurfsleitfaden für replizierte Tabellen. Eine praktische Entscheidungshilfe für die Wahl einer der drei Optionen finden Sie in der Tabellenübersicht unter „Verteilte Tabellen“.

Für den Tabellenentwurf sollten Sie möglichst umfassende Kenntnisse zu Ihren Daten und über das Abfragen der Daten besitzen.    Stellen Sie sich beispielsweise die folgenden Fragen:

• Wie groß ist die Tabelle?
• Wie oft wird die Tabelle aktualisiert?
• Verfüge ich in einem dedizierten SQL-Pool über Fakten- und Dimensionstabellen?

Hashverteilung

Bei einer Tabelle mit Hashverteilung werden Tabellenzeilen mithilfe einer deterministischen Hashfunktion, die jede Zeile einer Verteilung zuweist, auf die Computeknoten verteilt.

Da identische Werte per Hash immer der gleichen Verteilung zugewiesen werden, sind die Zeilenpositionen in SQL Analytics bekannt. Der dedizierte SQL-Pool nutzt dieses Wissen, um Datenverschiebungen während Abfragen zu minimieren, was die Abfrageleistung verbessert.

Tabellen mit Hashverteilung eignen sich gut für umfangreiche Faktentabellen in einem Sternschema. Sie können sehr viele Zeilen umfassen und trotzdem eine hohe Leistung bieten. Es gibt natürlich einige Konzeptionsüberlegungen, die dazu beitragen, die Leistung zu erreichen, für die das verteilte System ausgelegt ist. Einer dieser Aspekte, der auch in diesem Artikel beschrieben wird, ist die Wahl einer oder mehrerer geeigneter Verteilungsspalten.

Geeignete Szenarien für die Verwendung einer Tabelle mit Hashverteilung:

• Die Tabellengröße auf dem Datenträger beträgt mehr als 2 GB.
• In der Tabelle erfolgen häufig Einfüge-, Aktualisierungs- und Löschvorgänge.

Roundrobinverteilung

Bei einer Tabelle mit Roundrobinverteilung werden die Tabellenzeilen gleichmäßig auf alle Verteilungen aufgeteilt. Die Zeilen werden den Verteilungen nach dem Zufallsprinzip zugewiesen. Anders als bei Tabellen mit Hashverteilung werden Zeilen mit identischen Werten nicht unbedingt der gleichen Verteilung zugewiesen.

Aus diesem Grund muss das System in einigen Fällen zunächst einen Datenverschiebungsvorgang auslösen, um die Daten besser zu organisieren, bevor die Aufgabenstellung einer Abfrage gelöst werden kann. Aufgrund dieses zusätzlichen Schritts können die Abfragen verlangsamt werden. Zur Verknüpfung einer Roundrobintabelle ist in der Regel eine Umstrukturierung der Zeilen erforderlich, was sich negativ auf die Leistung auswirkt.

Geeignete Szenarien für die Verwendung der Roundrobinverteilung in einer Tabelle:

• Als einfacher Ausgangspunkt, da es sich hierbei um die Standardkonfiguration handelt
• Wenn kein offensichtlicher Joinschlüssel vorhanden ist
• Wenn keine gute Kandidatenspalte für die Hashverteilung der Tabelle vorhanden ist
• Wenn die Tabelle nicht über einen gemeinsamen Joinschlüssel mit anderen Tabellen verfügt
• Wenn das Join weniger signifikant als andere Joins in der Abfrage ist
• Wenn die Tabelle eine temporäre Stagingtabelle ist

Im Tutorial Laden von Daten zu New Yorker Taxis wird anhand eines Beispiels das Laden von Daten in eine Roundrobin-Stagingtabelle gezeigt.

Auswählen einer Verteilungsspalte

Eine Tabelle mit Hashverteilung besitzt eine Verteilungsspalte oder Gruppe von Spalten, bei denen es sich um den Hashschlüssel handelt. Der folgende Code erstellt beispielsweise eine Tabelle mit Hashverteilung, bei der ProductKey als Verteilungsspalte fungiert.

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

Die Hash-Verteilung kann für eine gleichmäßigere Verteilung der Basistabelle auf mehrere Spalten angewendet werden. Bei der mehrspaltigen Verteilung können Sie bis zu acht Spalten für die Verteilung auswählen. Dies reduziert nicht nur die Datenverzerrung im Laufe der Zeit, sondern verbessert auch die Abfrageleistung. Beispiel:

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

Hinweis

Die mehrspaltige Verteilung in Azure Synapse Analytics kann durch Ändern des Kompatibilitätsgrads der Datenbank in 50 mit diesem Befehl aktiviert werden. ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;Weitere Informationen zum Festlegen des Datenbank-Kompatibilitätsgrads finden Sie unter ALTER DATABSE SCOPED CONFIGURATION. Weitere Informationen zur mehrspaltigen Verteilung finden Sie unter CREATE MATERIALIZED VIEW, CREATE TABLE oder CREATE TABLE AS SELECT.

Die in Verteilungsspalten gespeicherten Daten können aktualisiert werden. Aktualisierungen von Daten in Verteilungsspalten können zu einer Zufallswiedergabe von Daten führen.

Die Auswahl von Verteilungsspalten ist eine wichtige Entwurfsentscheidung, da die Zeilenverteilung von den Werten in den Hashspalten bestimmt wird. Die beste Wahl hängt von mehreren Faktoren ab und erfordert häufig Kompromisse. Nachdem eine Verteilungsspalte oder eine Spaltengruppe ausgewählt wurde, kann dies nicht mehr geändert werden. Sollten Sie sich beim ersten Mal nicht für die optimale(n) Spalte(n) entschieden haben, können Sie die Tabelle mithilfe von CREATE TABLE AS SELECT (CTAS) mit dem gewünschten Verteilungs-Hashschlüssel neu erstellen.

Wählen Sie eine Verteilungsspalte mit Daten, die sich gleichmäßig verteilen lassen.

Aus Leistungsgründen sollten alle Verteilungen ungefähr die gleiche Anzahl von Zeilen besitzen. Bei einem Missverhältnis der Zeilenanzahl schließen einige Verteilungen ihren Teil einer parallelen Abfrage schneller ab als andere. Da die Abfrage erst abgeschlossen werden kann, wenn die Verarbeitung aller Verteilungen abgeschlossen ist, ist die Abfrage nur so schnell wie die langsamste Verteilung.

• Datenschiefe bedeutet, dass die Daten nicht gleichmäßig auf die Verteilungen aufgeteilt sind.
• Verarbeitungsschiefe bedeutet, dass bei parallelen Abfragen einige Verteilungen mehr Zeit benötigen als andere. Dieser Fall kann eintreten, wenn eine Datenschiefe vorliegt.

Wählen Sie zur Erzielung einer ausgeglichenen parallelen Verarbeitung eine Verteilungsspalte oder Gruppe von Spalten aus, die folgende Kriterien erfüllt:

• Viele eindeutige Werte: Die Verteilungsspalten können Duplikatwerte enthalten. Alle Zeilen mit gleichem Wert werden der gleichen Verteilung zugewiesen. Da es 60 Verteilungen gibt, können einige Verteilungen über mehrere eindeutige Werte verfügen, andere hingegen haben gar keine Werte.
• Keine oder nur wenige NULL-Werte: Stellen Sie sich als Extrembeispiel vor, alle Werte in den Verteilungsspalten wären NULL-Werte. In diesem Fall würden alle Zeilen der gleichen Verteilung zugewiesen. Dadurch würde die gesamte Abfrageverarbeitung in einer einzelnen Verteilung stattfinden und nicht von der parallelen Verarbeitung profitieren.
• Keine Datumsspalte: Alle Daten für das gleiche Datum landen in derselben Verteilung oder gruppieren Datensätze nach Datum. Wenn mehrere Benutzer nach dem gleichen Datum filtern (z. B. das heutige Datum), findet die Verarbeitung nur in einer der 60 Verteilungen statt.

Wählen Sie eine Verteilungsspalte, die Datenverschiebungen minimiert.

Zur Ermittlung des korrekten Abfrageergebnisses werden Daten ggf. zwischen Computeknoten verschoben. Zu Datenverschiebungen kommt es in der Regel, wenn Abfragen über Joins und Aggregationen für verteilte Tabellen verfügen. Die Wahl einer Verteilungsspalte oder Gruppe von Spalten, die zur Minimierung von Datenverschiebungen beitragen, ist eine der wichtigsten Strategien zum Optimieren der Leistung Ihres dedizierten SQL-Pools.

Zur Minimierung von Datenverschiebungen muss die Verteilungsspalte oder Gruppe von Spalten folgende Kriterien erfüllen:

• Sie wird in Klauseln vom Typ JOIN, GROUP BY, DISTINCT, OVER und HAVING verwendet. Wenn zwei umfangreiche Faktentabellen über häufige Joins verfügen, verbessert sich die Abfrageleistung, wenn die Verteilung der beiden Tabellen auf einer der Verknüpfungsspalten basiert. Wenn eine Tabelle nicht in Verknüpfungen (Joins) verwendet wird, können Sie die Tabelle ggf. auf Grundlage einer Spalte oder Spaltengruppe verteilen, die häufig in der GROUP BY-Klausel vorkommt.
• Sie wird nicht in WHERE-Klauseln verwendet. Wenn sich die WHERE-Klausel einer Abfrage und die Verteilungsspalten der Tabelle auf dieselbe Spalte beziehen, kann in der Abfrage eine hohe Datenschiefe auftreten, sodass die Verarbeitungslast nur auf wenige Verteilungen fällt. Dies wirkt sich auf die Abfrageleistung aus. Im Idealfall teilen sich viele Verteilungen die Verarbeitungslast.
• Sie ist keine Datumsspalte. WHERE Klauseln werden häufig zum Filtern nach Datum verwendet. In diesem Fall findet die gesamte Verarbeitung unter Umständen nur in einigen wenigen Verteilungen statt, wodurch die Abfrageleistung beeinträchtigt wird. Im Idealfall teilen sich viele Verteilungen die Verarbeitungslast.

Nach dem Entwerfen einer Tabelle mit Hashverteilung müssen Daten in die Tabelle geladen werden. Eine entsprechende Anleitung finden Sie in der Übersicht über das Laden.

Wie lässt sich feststellen, ob Ihre Verteilung eine gute Wahl ist?

Überprüfen Sie, wie gleichmäßig die Zeilen auf die 60 Verteilungen aufgeteilt sind, nachdem Sie Daten in eine Tabelle mit Hashverteilung geladen haben. Die Zeilen pro Verteilung können um bis zu zehn Prozent variieren, ohne dass dies merkliche Auswirkungen auf die Leistung hat. Berücksichtigen Sie die folgenden Themen, um Ihre Verteilungsspalten zu evaluieren.

Ermitteln, ob die Tabelle von Datenschiefe betroffen ist

Mit DBCC PDW_SHOWSPACEUSED können Sie schnell feststellen, ob eine Datenschiefe vorliegt. Der folgende SQL-Code gibt die Anzahl von Tabellenzeilen zurück, die in jeder der 60 Verteilungen gespeichert sind. Um eine ausgeglichene Leistung zu erzielen, müssen die Zeilen in Ihrer verteilten Tabelle gleichmäßig auf alle Verteilungen aufgeteilt sein.

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

So ermitteln Sie Tabellen mit einer Datenschiefe von mehr als zehn Prozent:

1. Erstellen Sie die Ansicht dbo.vTableSizes, die im Artikel Tabellenübersicht gezeigt wird.
2. Führen Sie die folgende Abfrage aus:

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;

Überprüfen von Abfrageplänen auf Datenverschiebungen

Eine gute Gruppe von Verteilungsspalten ermöglicht Verknüpfungen (Joins) und Aggregationen mit geringer Datenverschiebung. Aus diesem Grund müssen Joins auf eine bestimmte Weise erstellt werden. Um bei einem Join zweier Tabellen mit Hashverteilung eine möglichst geringe Datenverschiebung zu erzielen, muss sich eine der Verknüpfungsspalten in der oder den Verteilungsspalten befinden. Wenn zwei Tabellen mit Hashverteilung auf der Grundlage einer Verteilungsspalte gleichen Datentyps verknüpft sind, sind für das Join keine Datenverschiebungen erforderlich. Joins können zusätzliche Spalten verwenden, ohne Datenverschiebungen zu verursachen.

So vermeiden Sie Datenverschiebungen während eines Joins:

• Die an dem Join beteiligten Tabellen müssen per Hashverteilung auf der Grundlage einer der an dem Join beteiligten Spalten verteilt werden.
• Die Datentypen der Verknüpfungsspalten müssen in beiden Tabellen übereinstimmen.
• Die Spalten müssen mit dem Operator „Equals“ (Gleich) verknüpft werden.
• Der Jointyp darf nicht CROSS JOIN sein.

Ob es bei Abfragen zu Datenverschiebungen kommt, können Sie anhand des Abfrageplans ermitteln.

Lösen eines Problems mit der Verteilungsspalte

Nicht alle Fälle von Datenschiefe müssen behoben werden. Verteilen von Daten bedeutet, das richtige Gleichgewicht zwischen der Minimierung der Datenschiefe und der Minimierung von Datenverschiebungen zu finden. Es ist nicht immer möglich, beides zu minimieren. Manchmal überwiegen die Vorteile einer Minimierung von Datenverschiebungen unter Umständen die Nachteile der Datenschiefe.

Um zu entscheiden, ob die Datenschiefe in einer Tabelle beseitigt werden sollte, müssen Sie die Datenmenge und die Abfragen in Ihrer Workload möglichst umfassend verstehen. Wie Sie die Auswirkungen der Datenschiefe auf die Abfrageleistung überwachen, erfahren Sie im Artikel zur Abfrageüberwachung. Achten Sie insbesondere darauf, wie lange die Ausführung großer Abfragen für die einzelnen Verteilungen dauert.

Da die Verteilungsspalten für eine vorhandene Tabelle nicht geändert werden können, muss die Tabelle zur Behebung der Datenschiefe in der Regel mit anderen Verteilungsspalten neu erstellt werden.

Neuerstellen der Tabelle mit einer neuen Gruppe von Verteilungsspalten

In diesem Beispiel wird CREATE TABLE AS SELECT verwendet, um eine Tabelle mit einer anderen Hashverteilungsspalte oder Gruppe von Spalten neu zu erstellen.

Verwenden Sie zuerst CREATE TABLE AS SELECT (CTAS) für die neue Tabelle mit dem neuen Schlüssel. Erstellen Sie dann die Statistiken neu, und tauschen Sie schließlich die Tabellen gegeneinander aus, indem Sie sie umbenennen.

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ächste Schritte

Verwenden Sie zum Erstellen einer verteilten Tabelle eine der folgenden Anweisungen:

• CREATE TABLE (dedizierter SQL-Pool)
• CREATE TABLE AS SELECT (dedizierter SQL-Pool)