Entwurfsanleitung für die Verwendung replizierter Tabellen im Synapse SQL-Pool

Dieser Artikel enthält Empfehlungen für das Entwerfen replizierter Tabellen in Ihrem Synapse SQL-Poolschema. Verwenden Sie diese Empfehlungen, um die Abfrageleistung zu verbessern, indem Sie die Datenverschiebung und Abfragekomplexität verringern.

Voraussetzungen

In diesem Artikel wird davon ausgegangen, dass Sie mit datenverteilungs- und Datenbewegungskonzepten im SQL-Pool vertraut sind. Weitere Informationen finden Sie im Architekturartikel .

Verstehen Sie im Rahmen des Tabellenentwurfs so viel wie möglich über Ihre Daten und wie die Daten abgefragt werden.  Betrachten Sie beispielsweise die folgenden Fragen:

• Wie groß ist die Tabelle?
• Wie oft wird die Tabelle aktualisiert?
• Habe ich Fakten- und Dimensionstabellen in einem SQL-Pool?

Was ist eine replizierte Tabelle?

Eine replizierte Tabelle verfügt über eine vollständige Kopie der Tabelle, auf die auf jedem Computeknoten zugegriffen werden kann. Durch replizieren einer Tabelle wird die Notwendigkeit zum Übertragen von Daten zwischen Computeknoten vor einer Verknüpfung oder Aggregation entfernt. Da die Tabelle über mehrere Kopien verfügt, funktionieren replizierte Tabellen am besten, wenn die Tabellengröße kleiner als 2 GB komprimiert ist. 2 GB ist kein hartes Limit. Wenn die Daten statisch sind und sich nicht ändern, können Sie größere Tabellen replizieren.

Das folgende Diagramm zeigt eine replizierte Tabelle, auf die auf jedem Computeknoten zugegriffen werden kann. Im SQL-Pool wird die replizierte Tabelle vollständig in eine Verteilungsdatenbank auf jedem Berechnungsknoten kopiert.

Replizierte Tabellen funktionieren gut für Dimensionstabellen in einem Sternschema. Dimensionstabellen werden in der Regel mit Faktentabellen verknüpft, die anders verteilt sind als die Dimensionstabelle. Abmessungen sind in der Regel eine Größe, die es möglich macht, mehrere Kopien zu speichern und zu verwalten. Dimensionen speichern beschreibende Daten, die sich langsam ändern, z. B. Kundenname und Adresse sowie Produktdetails. Die langsam ändernde Art der Daten führt zu einer geringeren Wartung der replizierten Tabelle.

Erwägen Sie die Verwendung einer replizierten Tabelle in folgenden Fällen:

• Die Tabellengröße auf dem Datenträger ist kleiner als 2 GB, unabhängig von der Anzahl der Zeilen. Um die Größe einer Tabelle zu ermitteln, können Sie den Befehl DBCC PDW_SHOWSPACEUSED verwenden: DBCC PDW_SHOWSPACEUSED('ReplTableCandidate').
• Die Tabelle wird in Verknüpfungen verwendet, die andernfalls eine Datenverschiebung erfordern. Beim Verknüpfen von Tabellen, die nicht in derselben Spalte verteilt sind, z.B. einer Tabelle mit Hashverteilung mit einer Roundrobintabelle, ist die Datenverschiebung erforderlich, um die Abfrage abzuschließen. Wenn eine der Tabellen klein ist, sollten Sie eine replizierte Tabelle in Betracht ziehen. Es wird empfohlen, replizierte Tabellen anstelle von Roundrobin-Tabellen in den meisten Fällen zu verwenden. Verwenden Sie sys.dm_pdw_request_steps, um Datenverschiebungsvorgänge in Abfrageplänen anzuzeigen. Die BroadcastMoveOperation ist der typische Datenbewegungsvorgang, der mithilfe einer replizierten Tabelle eliminiert werden kann.

Replizierte Tabellen liefern möglicherweise nicht die beste Abfrageleistung, wenn:

• In der Tabelle erfolgen häufig Einfüge-, Aktualisierungs- und Löschvorgänge. Vorgänge der Datenbearbeitungssprache (Data Manipulation Language, DML) erfordern das erneute Erstellen der replizierten Tabelle. Ein häufiges Neuerstellen kann zu Leistungseinbußen führen.
• Der SQL-Pool wird häufig skaliert. Durch die Skalierung eines SQL-Pools wird die Anzahl der Computeknoten geändert, wodurch die replizierte Tabelle neu erstellt wird.
• Die Tabelle enthält eine große Anzahl von Spalten, die Datenvorgänge greifen jedoch in der Regel nur auf eine kleine Anzahl von Spalten zu. In diesem Szenario ist es möglicherweise effektiver, eine Verteilung der Tabelle und dann einen Index für die Spalten mit häufigem Zugriff zu erstellen, statt die gesamte Tabelle zu replizieren. Wenn eine Abfrage eine Datenverschiebung erfordert, verschiebt der SQL-Pool nur Daten für die angeforderten Spalten.

Verwenden replizierter Tabellen mit einfachen Abfrage-Prädikaten

Bevor Sie eine Tabelle verteilen oder replizieren möchten, überlegen Sie sich die Arten von Abfragen, die Sie für die Tabelle ausführen möchten. Wann immer möglich,

• Verwenden Sie replizierte Tabellen für Abfragen mit einfachen Abfrage-Prädikaten, z. B. Gleichheit oder Ungleichheit.
• Verwenden Sie verteilte Tabellen für Abfragen mit komplexen Abfrage-Prädikaten, z. B. LIKE oder NOT LIKE.

CPU-intensive Abfragen werden am besten ausgeführt, wenn die Arbeit über alle Computeknoten verteilt wird. Beispielsweise führen Abfragen, die Berechnungen für jede Zeile einer Tabelle ausführen, bei verteilten Tabellen besser aus als replizierte Tabellen. Da eine replizierte Tabelle vollständig auf jedem Computeknoten gespeichert wird, wird eine CPU-intensive Abfrage einer replizierten Tabelle für die gesamte Tabelle auf jedem Computeknoten ausgeführt. Die zusätzliche Berechnung kann die Abfrageleistung verlangsamen.

Diese Abfrage verfügt beispielsweise über ein komplexes Prädikat. Sie wird schneller ausgeführt, wenn sich die Daten in einer verteilten Tabelle statt in einer replizierten Tabelle befinden. In diesem Beispiel können die Daten mit Roundrobin verteilt werden.

SELECT EnglishProductName
FROM DimProduct
WHERE EnglishDescription LIKE '%frame%comfortable%';

Konvertieren vorhandener Roundrobin-Tabellen in replizierte Tabellen

Wenn Sie bereits Roundrobin-Tabellen haben, empfehlen wir, sie in replizierte Tabellen zu konvertieren, wenn sie den in diesem Artikel beschriebenen Kriterien entsprechen. Replizierte Tabellen bieten eine bessere Leistung als Roundrobintabellen, da sie nicht das Verschieben von Daten erfordern. Eine Roundrobintabelle erfordert für Joins immer Datenverschiebung.

In diesem Beispiel wird CTAS verwendet, um die DimSalesTerritory Tabelle in eine replizierte Tabelle zu ändern. Dieses Beispiel ist unabhängig davon gültig, ob DimSalesTerritory eine Tabelle mit Hashverteilung oder eine Roundrobintabelle ist.

CREATE TABLE [dbo].[DimSalesTerritory_REPLICATE]
WITH
  (
    HEAP,  
    DISTRIBUTION = REPLICATE  
  )  
AS SELECT * FROM [dbo].[DimSalesTerritory]
OPTION  (LABEL  = 'CTAS : DimSalesTerritory_REPLICATE')

-- Switch table names
RENAME OBJECT [dbo].[DimSalesTerritory] to [DimSalesTerritory_old];
RENAME OBJECT [dbo].[DimSalesTerritory_REPLICATE] TO [DimSalesTerritory];

DROP TABLE [dbo].[DimSalesTerritory_old];

Beispiel für die Abfrageleistung bei Roundrobintabellen und replizierten Tabellen

Eine replizierte Tabelle erfordert keine Datenverschiebung für Verknüpfungen, da die gesamte Tabelle bereits auf jedem Computeknoten vorhanden ist. Wenn die Dimensionstabellen nach dem Roundrobinprinzip verteilt sind, wird für einen Join die Dimensionstabelle vollständig auf jeden Computeknoten kopiert. Um die Daten zu verschieben, enthält der Abfrageplan einen Vorgang namens BroadcastMoveOperation. Diese Art von Datenverschiebungsvorgang verlangsamt die Abfrageleistung und wird durch replizierte Tabellen eliminiert. Verwenden Sie zum Anzeigen der Abfrageplanschritte die sys.dm_pdw_request_steps Systemkatalogansicht.

Beispielsweise ist in der folgenden Abfrage des AdventureWorks-Schemas die Tabelle FactInternetSales eine Tabelle mit Hashverteilung. Die DimDate Tabellen und DimSalesTerritory Tabellen sind kleinere Dimensionstabellen. Diese Abfrage gibt den Gesamtumsatz in Nordamerika für das Geschäftsjahr 2004 zurück:

SELECT [TotalSalesAmount] = SUM(SalesAmount)
FROM dbo.FactInternetSales s
INNER JOIN dbo.DimDate d
  ON d.DateKey = s.OrderDateKey
INNER JOIN dbo.DimSalesTerritory t
  ON t.SalesTerritoryKey = s.SalesTerritoryKey
WHERE d.FiscalYear = 2004
  AND t.SalesTerritoryGroup = 'North America'

Wir haben DimDate und DimSalesTerritory als Round-Robin-Tabellen neu erstellt. Daher hat die Abfrage den folgenden Abfrageplan mit mehreren Übertragungsverschiebungsvorgängen angezeigt:

Wir haben DimDate und DimSalesTerritory als replizierte Tabellen erstellt und die Abfrage erneut ausgeführt. Der resultierende Abfrageplan ist wesentlich kürzer und enthält keinerlei Broadcast-Operationen.

Leistungsüberlegungen zum Ändern replizierter Tabellen

Der SQL-Pool implementiert eine replizierte Tabelle, indem eine Masterversion der Tabelle verwaltet wird. Die Masterversion wird auf jedem Rechenknoten in die erste Verteilungsdatenbank kopiert. Wenn eine Änderung erfolgt, wird die Masterversion zuerst aktualisiert, dann werden die Tabellen auf jedem Computeknoten neu erstellt. Eine Neuerstellung einer replizierten Tabelle umfasst das Kopieren der Tabelle in jeden Computeknoten und das anschließende Erstellen der Indizes. Beispielsweise verfügt eine replizierte Tabelle auf einer DW2000c über fünf Kopien der Daten. Eine Masterkopie und eine vollständige Kopie auf jedem Serverknoten. Alle Daten werden in Verteilungsdatenbanken gespeichert. Der SQL-Pool verwendet dieses Modell, um schnellere Datenänderungsanweisungen und flexible Skalierungsvorgänge zu unterstützen.

Asynchrone Neuerstellungen werden ausgelöst durch die erste Abfrage gegen die replizierte Tabelle nach:

• Daten werden geladen oder geändert.
• Die Synapse SQL-Instanz wird auf eine andere Ebene skaliert.
• Tabellendefinition wird aktualisiert

Nach folgenden Vorgängen ist keine Neuerstellung erforderlich:

• Vorgang anhalten
• Betrieb fortsetzen

Die Neuerstellung erfolgt nicht unmittelbar nach der Änderung der Daten. Stattdessen wird die Neuerstellung ausgelöst, wenn eine Abfrage zum ersten Mal aus der Tabelle auswählt. Die Abfrage, die die Neuerstellung ausgelöst hat, liest sofort aus der Masterversion der Tabelle, während die Daten asynchron auf jeden Serverknoten kopiert werden. Bis die Datenkopie abgeschlossen ist, verwenden nachfolgende Abfragen weiterhin die Masterversion der Tabelle. Wenn eine Aktivität an der replizierten Tabelle ausgeführt wird, die eine weitere Neuerstellung erzwingt, wird das Kopieren der Daten für ungültig erklärt, und die nächste Select-Anweisung löst erneutes Kopieren der Daten aus.

Konservative Verwendung von Indizes

Standardindizierungsmethoden gelten für replizierte Tabellen. Der SQL-Pool erstellt jeden replizierten Tabellenindex im Rahmen der Neuerstellung neu. Verwenden Sie nur Indizes, wenn der Leistungszuwachs die Kosten für den Wiederaufbau der Indizes überwiegt.

Laden von Batchdaten

Wenn Sie Daten in replizierte Tabellen laden, versuchen Sie, die Anzahl der Neuaufbauten zu minimieren, indem Sie Ladevorgänge zusammenfassen. Führen Sie alle Batch-Ladevorgänge aus, bevor Sie SELECT-Anweisungen ausführen.

Dieses Lademuster lädt beispielsweise Daten aus vier Quellen und ruft vier Neuerstellungen auf.

• Aus Quelle 1 laden.
• Select-Anweisung löst Neuerstellung 1 aus.
• Laden aus Quelle 2.
• Die Select-Anweisung löst den Neuaufbau von Version 2 aus.
• Aus Quelle 3 laden.
• Die Select-Anweisung löst die Neuerstellung Nr. 3 aus.
• Aus Quelle 4 laden.
• Die SELECT-Anweisung löst den Neuaufbau 4 aus.

Dieses Lademuster lädt beispielsweise Daten aus vier Quellen, ruft jedoch nur eine Neuerstellung auf.

• Aus Quelle 1 laden.
• Aus Quelle 2 laden.
• Aus Quelle 3 laden.
• Aus Quelle 4 laden.
• Select-Anweisung löst die Neuerstellung aus.

Neuerstellen einer replizierten Tabelle nach dem Laden eines Batches

Um konsistente Abfrageausführungszeiten sicherzustellen, erwägen Sie das Erzwingen des Builds der replizierten Tabellen nach einem Batchladevorgang. Andernfalls wird die erste Abfrage trotzdem eine Datenverschiebung durchführen, um die Abfrage abzuschließen.

Der Vorgang "Replizierter Tabellencache erstellen" kann bis zu zwei Vorgänge gleichzeitig ausführen. Wenn Sie beispielsweise versuchen, den Cache für fünf Tabellen neu zu erstellen, verwendet das System eine staticrc20 (die nicht geändert werden kann), um zwei Tabellen gleichzeitig zu erstellen. Daher wird empfohlen, die Verwendung großer replizierter Tabellen mit einer Größe von mehr als 2 GB zu vermeiden, da dadurch die Cacheneuerstellung über die Knoten hinweg verlangsamt und die Gesamtzeit erhöht wird.

Diese Abfrage verwendet die sys.pdw_replicated_table_cache_state DMV zum Auflisten der replizierten Tabellen, die geändert, aber nicht neu erstellt wurden.

SELECT SchemaName = SCHEMA_NAME(t.schema_id)
 , [ReplicatedTable] = t.[name]
 , [RebuildStatement] = 'SELECT TOP 1 * FROM ' + '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.[name] +']'
FROM sys.tables t 
JOIN sys.pdw_replicated_table_cache_state c 
  ON c.object_id = t.object_id
JOIN sys.pdw_table_distribution_properties p
  ON p.object_id = t.object_id
WHERE c.[state] = 'NotReady'
AND p.[distribution_policy_desc] = 'REPLICATE'

Um eine Neuerstellung auszulösen, führen Sie die folgende Anweisung für jede Tabelle in der vorherigen Ausgabe aus.

SELECT TOP 1 * FROM [ReplicatedTable]

-- Incorrect sequence. Ensure that the rebuild operation is the last statement within the batch.
BEGIN
SELECT TOP 1 * FROM [ReplicatedTable]

UPDATE STATISTICS [ReplicatedTable]
END

-- Correct sequence. Ensure that the rebuild operation is the last statement within the batch.
BEGIN
UPDATE STATISTICS [ReplicatedTable]

SELECT TOP 1 * FROM [ReplicatedTable]
END

Um den Neuerstellungsprozess zu überwachen, können Sie sys.dm_pdw_exec_requests verwenden, wobei der command Vorgang mit "BuildReplicatedTableCache" beginnt. Beispiel:

-- Monitor Build Replicated Cache
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE command like 'BuildReplicatedTableCache%'

Nächste Schritte

Verwenden Sie eine der folgenden Anweisungen, um eine replizierte Tabelle zu erstellen:

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

Eine Übersicht über verteilte Tabellen finden Sie in verteilten Tabellen.