Best Practices für serverlose SQL-Pools in Azure Synapse Analytics

In diesem Artikel finden Sie eine Sammlung von bewährten Methoden für die Verwendung serverloser SQL-Pools. Der serverlose SQL-Pool ist eine Ressource in Azure Synapse Analytics. Wenn Sie mit einem dedizierten SQL-Pool arbeiten, finden Sie unter Best Practices für dedizierte SQL-Pools in Azure Synapse Analytics spezifische Anleitungen.

Ein serverloser SQL-Pool ermöglicht es Ihnen, Dateien in Ihren Azure-Speicherkonten abzufragen. Es verfügt nicht über lokale Speicher- oder Erfassungsfunktionen. Alle Dateien, auf die die Abfrage ausgerichtet ist, sind extern zum serverlosen SQL-Pool. Alles, was mit dem Lesen von Dateien aus dem Speicher zusammenhängt, kann sich auf die Abfrageleistung auswirken.

Einige allgemeine Richtlinien lauten:

• Stellen Sie sicher, dass Ihre Clientanwendungen mit serverlosen SQL-Pool zusammengestellt werden.
  • Wenn Sie Clientanwendungen außerhalb von Azure verwenden, stellen Sie sicher, dass Sie serverlose SQL Pool in einer Region verwenden, die sich in der Nähe Ihres Clientcomputers befindet. Beispiele für Clientanwendungen sind Power BI Desktop, SQL Server Management Studio und Azure Data Studio.
• Stellen Sie sicher, dass sich der Speicher und der serverlose SQL-Pool in der gleichen Region befinden. Beispiele für Storage sind Azure Data Lake Storage und Azure Cosmos DB.
• Versuchen Sie, das Speicherlayout mithilfe der Partitionierung zu optimieren, und halten Sie Ihre Dateien im Bereich zwischen 100 MB und 10 GB.
• Wenn Sie eine große Anzahl von Ergebnissen zurückgeben, stellen Sie sicher, dass Sie SQL Server Management Studio oder Azure Data Studio und nicht Azure Synapse Studio verwenden. Azure Synapse Studio ist ein Webtool, das nicht für große Resultsets konzipiert ist.
• Wenn Sie Ergebnisse nach Zeichenfolgenspalten filtern, versuchen Sie, eine BIN2_UTF8-Sortierung zu verwenden. Weitere Informationen zum Ändern von Sortierungen finden Sie unter Unterstützte Sortierungstypen für Synapse SQL.
• Erwägen Sie, die Ergebnisse auf Clientseite mithilfe des Power BI-Importmodus oder Azure Analysis Services zwischenzuspeichern, und aktualisieren Sie sie in regelmäßigen Abständen. Serverlose SQL-Pools können in Power BI Direct-Query-Modus keine interaktive Benutzeroberfläche bieten, wenn Sie komplexe Abfragen verwenden oder große Datenmengen verarbeiten.
• Die maximale Parallelität ist nicht begrenzt und abhängig von der Komplexität der Abfrage und der Menge der überprüften Daten. Ein serverloser SQL-Pool kann gleichzeitig 1.000 aktive Sitzungen behandeln, die einfache Abfragen ausführen. Die Zahlen sinken, wenn die Abfragen komplexer sind oder eine größere Datenmenge gescannt wird. In diesem Fall sollten Sie daher in Betracht ziehen, die Parallelität zu verringern und Abfragen nach Möglichkeit über einen längeren Zeitraum auszuführen.

Clientanwendungen und Netzwerkverbindungen

Stellen Sie sicher, dass die Clientanwendung mit dem nächstmöglichen Azure Synapse-Arbeitsbereich optimal verbunden ist.

• Platzieren Sie eine Clientanwendung und den Azure Synapse-Arbeitsbereich an demselben Ort. Wenn Sie Anwendungen wie Power BI oder Azure Analysis Services verwenden, stellen Sie sicher, dass sie sich in derselben Region befinden, in der Sie Ihren Azure Synapse-Arbeitsbereich platziert haben. Erstellen Sie bei Bedarf separate Arbeitsbereiche, die mit Ihren Clientanwendungen kombiniert werden. Wenn Sie eine Clientanwendung und den Azure Synapse-Arbeitsbereich in verschiedenen Regionen platzieren, kann dies zu einer größeren Latenz und einem langsameren Streaming von Ergebnissen führen.
• Wenn Sie Daten aus Ihrer lokalen Anwendung lesen, stellen Sie sicher, dass sich der Azure Synapse-Arbeitsbereich in der Region befindet, die in der Nähe Ihres Standorts liegt.
• Stellen Sie sicher, dass beim Lesen einer großen Datenmenge keine Probleme mit der Netzwerkbandbreite auftreten.
• Verwenden Sie nicht Azure Synapse-Studio, um eine große Datenmenge zurückzugeben. Azure Synapse-Studio ist ein Webtool, das zum Übertragen von Daten das HTTPS-Protokoll verwendet. Verwenden Sie Azure Data Studio oder SQL Server Management Studio, um eine große Datenmenge zu lesen.

Speicher- und Inhaltslayout

Im Folgenden finden Sie bewährte Methoden für das Speicher- und Inhaltslayout in serverlosen SQL Pools.

Anordnen Ihres Speichers und des serverlosen SQL-Pools an demselben Ort

Ordnen Sie Ihr Azure Storage-Konto bzw. den Azure Cosmos DB-Analysespeicher und den Endpunkt Ihres serverlosen SQL-Pools an demselben Ort an, um die Latenz zu verringern. Speicherkonten und Endpunkte, die während der Einrichtung des Arbeitsbereichs bereitgestellt werden, befinden sich in derselben Region.

Wenn Sie mit einem serverlosen SQL-Pool auf andere Speicherkonten zugreifen, stellen Sie für eine optimale Leistung sicher, dass sie sich in derselben Region befinden. Wenn sie sich nicht in derselben Region befinden, erhöht sich die Wartezeit für die Netzwerkübertragung der Daten zwischen der Remote- und der Endpunktregion.

Azure Storage-Drosselung

Mehrere Anwendungen und Dienste können auf Ihr Speicherkonto zugreifen. Eine Speicherdrosselung tritt auf, wenn der von Anwendungen, Diensten und die Workloads eines serverlosen SQL-Pools generierte kombinierte IOPS oder Durchsatz die Grenzen des Speicherkontos überschreitet. Als Folge davon werden Sie eine erhebliche negative Auswirkung auf die Abfrageleistung erleben.

Wenn eine Drosselung erkannt wird, verfügt der serverlose SQL-Pool über eine integrierte Behandlung zu deren Behebung. Beim serverlosen SQL-Pool werden Anforderungen an den Speicher langsamer ausgeführt, bis die Drosselung behoben ist.

Tipp

Für eine optimale Abfrageausführung sollten Sie das Speicherkonto während der Abfrageausführung nicht mit anderen Workloads belasten.

Vorbereiten von Dateien für Abfragen

Wenn möglich, können Sie Dateien für eine bessere Leistung vorbereiten:

• Konvertieren Sie umfangreiche CSV- und JSON-Dateien in das Parquet-Format. Bei Parquet handelt es sich um ein Spaltenformat. Da es komprimiert ist, sind die Dateien kleiner als CSV- oder JSON-Dateien mit denselben Daten. Beim Lesen von Parquet-Dateien werden die Spalten und Zeilen, die in der Abfrage nicht benötigt werden, vom serverlosen SQL-Pool übersprungen. Der serverlose SQL-Pool benötigt weniger Zeit und Speicheranforderungen, um es zu lesen.
• Wenn eine Abfrage auf eine einzelne große Datei ausgerichtet ist, lohnt es sich, diese in mehrere kleinere Dateien aufzuteilen.
• Versuchen Sie sicherzustellen, dass die Größe von CSV-Dateien im Bereich zwischen 100 MB und 10 GB liegt.
• Es ist besser, gleich große Dateien für einen einzelnen OPENROWSET-Pfad oder einen Speicherort für die Tabelle zu verwenden.
• Partitionieren Sie Ihre Daten, indem Sie Partitionen in verschiedenen Ordnern oder unter unterschiedlichen Dateinamen speichern. Unter Verwenden von Dateiname und Dateipfadfunktionen zum Ausrichten auf bestimmte Partitionen finden Sie weitere Informationen.

Platzieren Ihres analytischen Azure Cosmos DB-Speichers und des serverlosen SQL-Pools an demselben Ort

Stellen Sie sicher, dass Ihr analytische Azure Cosmos DB-Speicher in derselben Region platziert wird, in dem sich der Azure Synapse-Arbeitsbereich befindet. Regionsübergreifende Abfragen können zu großen Latenzen führen. Verwenden Sie die Regionseigenschaft in der Verbindungszeichenfolge, um explizit den Bereich anzugeben, in dem der analytische Speicher platziert wird (Weitere Informationen finden Sie unter Abfragen von Azure Cosmos DB mit serverlosem SQL): account=<database account name>;database=<database name>;region=<region name>'.

CSV-Optimierungen

Hier finden Sie bewährten Methoden für die Verwendung von CSV-Dateien in serverlosen SQL Pools.

Verwenden von PARSER_VERSION 2.0 zum Abfragen von CSV-Dateien

Beim Abfragen von CSV-Dateien können Sie einen leistungsoptimierten Parser verwenden. Weitere Informationen finden Sie unter PARSER_VERSION.

Manuelles Erstellen von Statistiken für CSV-Dateien

Der serverlose SQL-Pool verwendet Statistiken, um optimale Abfrageausführungspläne zu generieren. Statistiken werden automatisch für Spalten anhand von Stichproben erstellt, und in den meisten Fällen liegt der Prozentsatz für die Stichprobenentnahme unter 100 %. Dieser Flow ist für jedes Dateiformat identisch. Beachten Sie, dass beim Lesen von CSV mit der Parserversion 1.0 die Stichprobenentnahme nicht unterstützt wird und die automatische Erstellung von Statistiken nicht erfolgt, wenn der Stichprobenprozentsatz weniger als 100 % beträgt. Für kleine Tabellen mit geschätzter niedriger Kardinalität (Anzahl der Zeilen) wird die automatische Erstellung von Statistiken mit einem Stichprobenprozentsatz von 100 % ausgelöst. Dies bedeutet, dass ein vollständiger Scan ausgelöst wird und automatische Statistiken auch für CSV mit Parserversion 1.0 erstellt werden. Falls Statistiken nicht automatisch erstellt werden, sollten Sie Statistiken manuell für Spalten erstellen, die Sie in Abfragen verwenden, insbesondere in DISTINCT, JOIN, WHERE, ORDER BY und GROUP BY. Weitere Informationen finden Sie unter Statistiken im serverlosen SQL-Pool.

Datentypen

Hier finden Sie bewährte Methoden für die Verwendung von Datentypen in serverlosen SQL-Pools.

Verwenden geeigneter Datentypen

Die Datentypen, die Sie in Ihrer Abfrage verwenden, wirken sich auf die Leistung und Parallelität aus. Sie können mit folgenden Maßnahmen eine bessere Leistung erzielen:

• Verwenden der kleinsten Datengröße, die den größtmöglichen Wert aufnehmen kann.
  • Wenn die maximale Zeichenlänge des Werts 30 Zeichen beträgt, verwenden Sie den Zeichendatentyp der Länge 30.
  • Wenn Zeichenspaltenwerte eine festgelegte Größe haben, verwenden Sie char oder nchar. Verwenden Sie andernfalls varchar oder nvarchar.
  • Wenn der Höchstwert für eine ganzzahlige (integer) Spalte 500 ist, verwenden Sie smallint, da dies der kleinste Datentyp ist, der diesen Wert aufnehmen kann. Weitere Informationen finden Sie unter Integer-Datentypbereiche.
• Verwenden Sie nach Möglichkeit varchar und char anstelle von nvarchar und nchar.
  • Verwenden Sie den Typ varchar mit UTF8-Sortierung, wenn Sie Daten aus Parquet, Azure Cosmos DB, Delta Lake oder CSV mit UTF-8-Codierung lesen.
  • Verwenden Sie den Typ varchar ohne UTF8-Sortierung, wenn Sie Daten aus CSV lesen, die Unicode-Dateien (z. B. ASCII) nicht unterstützen.
  • Verwenden Sie den Typ nvarchar, wenn Sie Daten aus einer CSV-UTF-16-Datei lesen.
• Verwenden Sie nach Möglichkeit ganzzahlige Datentypen (integer). Die Vorgänge SORT, JOIN und GROUP BY erfolgen schneller mit ganzzahligen Daten als mit Zeichendaten.
• Wenn Sie den Schemarückschluss verwenden, überprüfen Sie abgeleitete Datentypen, und überschreiben Sie sie nach Möglichkeit explizit mit den kleineren Typen.

Abgeleitete Datentypen überprüfen

Mithilfe von Schemarückschlüssen können Sie schnell Abfragen schreiben und Daten erkunden, ohne das Dateischema zu kennen. Bei diesen Vorteilen gibt es allerdings auch den Nachteil, dass abgeleitete Datentypen möglicherweise größer als die tatsächlichen Datentypen sind. Diese Abweichen kommt zustande, wenn nicht genügend Informationen in den Quelldateien vorhanden sind, um sicherzustellen, dass geeignete Datentypen verwendet werden. Beispielsweise enthalten Parquet-Dateien keine Metadaten zur maximalen Länge von Zeichenspalten. Daher leitet sie der serverlose SQL-Pool als „varchar(8000)“ ab.

Denken Sie daran, dass die Situation im Fall der gemeinsam nutzbaren verwalteten und externen Spark-Tabellen im SQL-Modul als externe Tabellen unterschiedlich sein kann. Spark-Tabellen bieten andere Datentypen als die SQL-Engines von Synapse. Zuordnung zwischen Spark-Tabellendatentypen und SQL-Typen finden Sie hier.

Sie können die aus Ihrer Abfrage resultierenden Datentypen mithilfe von sp_describe_first_results_set überprüfen.

Im folgenden Beispiel wird gezeigt, wie Sie abgeleitete Datentypen optimieren können. Die Prozedur wird zum Anzeigen von abgeleiteten Datentypen verwendet:

EXEC sp_describe_first_result_set N'
    SELECT
        vendor_id, pickup_datetime, passenger_count
    FROM  
        OPENROWSET(
            BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/*/*/*'',
            FORMAT=''PARQUET''
        ) AS nyc';

Das Ergebnis lautet wie folgt:

is_hidden	column_ordinal	name	system_type_name	max_length
0	1	vendor_id	varchar(8000)	8.000
0	2	pickup_datetime	datetime2(7)	8
0	3	passenger_count	INT	4

Sobald Sie die abgeleiteten Datentypen für die Abfrage kennen, können Sie geeignete Datentypen angeben:

SELECT
    vendorID, tpepPickupDateTime, passengerCount
FROM  
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=2018/puMonth=*/*.snappy.parquet',
        FORMAT='PARQUET'
    )  
    WITH (
        vendorID varchar(4), -- we used length of 4 instead of the inferred 8000
        tpepPickupDateTime datetime2,
        passengerCount int
    ) AS nyc;

Filteroptimierung

Hier finden Sie bewährte Methoden für die Verwendung von Abfragen in serverlosen SQL Pools.

Pushen von Platzhaltern auf niedrigere Ebenen im Pfad

Sie können Platzhalter in Ihrem Pfad verwenden, um mehrere Dateien und Ordner abzufragen. Der serverlose SQL-Pool listet Dateien in Ihrem Speicherkonto auf und beginnt damit beim ersten Sternchen(*). Dabei kommt die Storage-API zum Einsatz. Dateien, die nicht mit dem angegebenen Pfad übereinstimmen, werden dabei ausgeschlossen. Das Verringern der ursprünglichen Liste mit Dateien kann die Leistung verbessern, wenn viele Dateien vorhanden sind, die mit dem angegebenen Pfad bis zum ersten Platzhalter übereinstimmen.

Verwenden von Dateiname und Dateipfadfunktionen zum Ausrichten auf bestimmte Partitionen

Daten sind oft in Partitionen organisiert. Sie können den serverlosen SQL-Pool anweisen, bestimmte Ordner und Dateien abzufragen. Dies reduziert die Anzahl der Dateien und die Datenmenge, die die Abfrage zum Lesen und Verarbeiten benötigt. Ein weiterer Vorteil ist, dass Sie bessere Leistungen erzielen.

Im Artikel Verwenden von Dateimetadaten in Abfragen finden Sie Beispiele für das Abfragen bestimmter Dateien sowie weitere Informationen zu Dateiname und Dateipfad.

Tipp

Wandeln Sie das Ergebnis der Dateipfad- und Dateinamefunktionen immer in einen geeigneten Datentyp um. Achten Sie, wenn Sie Zeichendatentypen verwenden, unbedingt auf die Verwendung der richtigen Länge.

Funktionen zur Partitionsentfernung (Dateipfad und Dateiname) werden derzeit nicht für externe Tabellen unterstützt, außer denen, die automatisch für jede in Apache Spark für Azure Synapse Analytics erstellte Tabelle erzeugt werden.

Wenn Ihre gespeicherten Daten nicht partitioniert sind, können Sie sie partitionieren. So können Sie diese Funktionen verwenden, um auf diese Dateien ausgerichtete Abfragen zu optimieren. Wenn Sie in einem serverlosen SQL-Pool partitionierte Apache Spark für Azure Synapse-Tabellen abfragen, wird die Abfrage automatisch nur auf die erforderlichen Dateien ausgerichtet.

Verwenden der richtigen Sortierung, um den Prädikatpushdown für Zeichenspalten zu verwenden

In einer Parquetdatei sind Daten in Zeilengruppen angeordnet. Ein serverlose SQL-Pool überspringt Zeilengruppen basierend auf dem angegebenen Prädikat in der WHERE-Klausel, wodurch E/A reduziert wird. Das Ergebnis ist eine höhere Abfrageleistung.

Der Prädikatpushdown für Zeichenspalten werden in Parquetdateien nur für die Latin1_General_100_BIN2_UTF8-Sortierung unterstützt. Sie können die Sortierung für eine bestimmte Spalte mithilfe einer WITH-Klausel angeben. Wenn Sie diese Sortierung nicht mithilfe der WITH-Klausel angeben, wird die Datenbanksortierung verwendet.

Optimieren von wiederholten Abfragen

Hier finden Sie bewährte Methoden für die Verwendung von CETAS in serverlosen SQL Pools.

Verwenden von CETAS zum Verbessern von Abfrageleistung und Verknüpfungen

CETAS ist eine der wichtigsten Funktionen, die in einem serverlosen SQL-Pool verfügbar sind. CETAS ist ein paralleler Vorgang, der externe Tabellenmetadaten erstellt und die Ergebnisse der SELECT-Abfrage in eine Reihe von Dateien in Ihrem Speicherkonto exportiert.

Sie können CETAS verwenden, um häufig verwendete Teile von Abfragen, z. B. verknüpfte Verweistabellen, zu einem neuen Satz von Dateien zu materialisieren. Als Nächstes können Sie eine Verknüpfung zu dieser einzelnen externen Tabelle herstellen, anstatt gemeinsame Verknüpfungen in mehreren Abfragen zu wiederholen.

Da CETAS Parquet-Dateien generiert, werden Statistiken automatisch erstellt, wenn die erste Abfrage auf diese externe Tabelle ausgerichtet ist. Das Ergebnis ist eine verbesserte Leistung für nachfolgende Abfragen, die auf die mit CETAS generierte Tabelle ausgerichtet sind.

Abfragen von Azure-Daten

Serverlose SQL-Pools ermöglichen Ihnen, Daten in Azure Storage oder Azure Cosmos DB mithilfe externer Tabellen und der OPENROWSET-Funktion abzufragen. Stellen Sie sicher, dass für Ihren Speicher die richtigen Berechtigungen eingerichtet wurden.

Abfragen von CSV-Daten

Erfahren Sie, wie Sie einzelne CSV-Dateien oder Ordner und mehrere CSV-Dateien abfragen. Sie können auch partitionierte Dateien abfragen.

Abfragen von Parquet-Daten

Erfahren Sie, wie Sie Parquet-Dateien mit geschachtelten Typen abfragen. Sie können auch partitionierte Dateien abfragen.

Abfragen von Delta Lake

Erfahren Sie, wie Sie Delta Lake-Dateien mit geschachtelten Typen abfragen.

Abfragen von Azure Cosmos DB-Daten

Informieren Sie sich über das Abfragen von Azure Cosmos DB-Analysespeicher. Sie können einen Onlinegenerator verwenden, um die WITH-Klausel basierend auf einem Azure Cosmos DB-Beispieldokument zu generieren. Sie können über Azure Cosmos DB-Container hinweg Sichten erstellen.

Abfragen von JSON-Daten

Erfahren Sie, wie Sie JSON-Dateien abfragen. Sie können auch partitionierte Dateien abfragen.

Erstellen von Sichten, Tabellen und anderen Datenbankobjekten

Erfahren Sie, wie Sie Sichten und externe Tabellen erstellen und verwenden oder Sicherheit auf Zeilenebene einrichten. Wenn Sie über partitionierte Dateien verfügen, verwenden sie unbedingt auch partitionierte Sichten.

Kopieren und Transformieren von Daten (CETAS)

Erfahren Sie, wie Sie mithilfe des CETAS-Befehls Abfrageergebnisse speichern.

Nächste Schritte

• Informationen zu allgemeinen Problemen und entsprechende Lösungen finden Sie im Artikel Problembehandlung bei serverlosen SQL-Pools.
• Wenn Sie nicht mit einem serverlosen SQL-Pool, sondern mit einem dedizierten SQL-Pool arbeiten, finden Sie entsprechende Anleitungen unter Bewährte Methoden für dedizierte SQL-Pools.
• Azure Synapse Analytics: Häufig gestellte Fragen
• Erteilen von Berechtigungen für die verwaltete Identität eines Arbeitsbereichs (Vorschau)