Leistungsüberlegungen in PolyBase für SQL Server

Gilt für: SQL Server 2016 (13.x) Windows und höhere Versionen SQL Server 2017 (14.x) – Linux und höhere Versionen Azure Synapse Analytics

In PolyBase für SQL Server gibt es keine harte Beschränkung auf die Anzahl der Dateien oder die Datenmenge, die abgefragt werden kann. Die Abfrageleistung hängt von der Datenmenge, dem Datenformat, der Organisation von Daten und der Komplexität von Abfragen und Verknüpfungen ab.

In diesem Artikel werden wichtige Leistungsthemen und Anleitungen behandelt.

Statistik

Das Erfassen von Statistiken zu externen Daten ist eine der wichtigsten Maßnahmen für die Abfrageoptimierung. Je mehr Informationen der Instanz über Ihre Daten vorliegen, desto schneller kann sie Abfragen ausführen. Der Abfrageoptimierer der SQL-Engine arbeitet kostenorientiert. Die Kosten der verschiedenen Abfragepläne werden verglichen, und dann wird der Plan mit den geringsten Kosten gewählt. In den meisten Fällen wird der Plan gewählt, der am schnellsten ausgeführt wird.

Automatische Erstellung von Statistiken

Ab SQL Server 2022 analysiert die Datenbank-Engine eingehende Benutzerabfragen nach fehlenden Statistiken. Wenn Statistiken fehlen, erstellt der Abfrageoptimierer automatisch Statistiken für einzelne Spalten im Abfrageprädikat oder der Verknüpfungsbedingung, um Kardinalitätsschätzungen für den Abfrageplan zu verbessern. Die automatische Erstellung von Statistiken erfolgt synchron, so dass sich die Abfrageleistung geringfügig verschlechtern kann, wenn für Ihre Spalten keine Statistiken vorliegen. Die Zeit zum Erstellen von Statistiken für eine einzelne Spalte hängt von der Größe der Zieldateien ab.

Manuelle OPENROWSET-Statistiken erstellen

Statistiken zu einzelnen Spalten für den Pfad OPENROWSET können mit der gespeicherten Prozedur sys.sp_create_openrowset_statistics erstellt werden, indem die SELECT-Abfrage mit einer einzelnen Spalte als Parameter übergeben wird:

EXEC sys.sp_create_openrowset_statistics N' 
SELECT pickup_datetime 
FROM OPENROWSET( 
 BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'', 
 FORMAT = ''parquet'') AS filerows';

Standardmäßig verwendet die Instanz 100 % der im Dataset bereitgestellten Daten, um Statistiken zu erstellen. Optional können Sie die Stichprobengröße mithilfe der TABLESAMPLE-Optionen als Prozentsatz angeben. Um Einzelspaltenstatistiken für mehrere Spalten zu erstellen, führen Sie sys.sp_create_openrowset_statistics für jede der Spalten aus. Für den Pfad OPENROWSET können keine Mehrspaltenstatistiken erstellt werden.

Um vorhandene Statistiken zu aktualisieren, löschen Sie diese zunächst mit der gespeicherten Prozedur sys.sp_drop_openrowset_statistics, und erstellen Sie sie dann mithilfe von sys.sp_create_openrowset_statistics neu:

EXEC sys.sp_drop_openrowset_statistics 
N'SELECT pickup_datetime 
FROM OPENROWSET( 
 BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'', 
 FORMAT = ''parquet'') AS filerows 
';

Manuelle Statistiken zu externen Tabellen erstellen

Die Syntax für die Erstellung von Statistiken zu externen Tabellen ähnelt der Syntax für reguläre Benutzertabellen. Zum Erstellen von Statistiken zu einer Spalte geben Sie einen Namen für das Statistikobjekt und den Namen der Spalte an:

CREATE STATISTICS sVendor 
ON tbl_TaxiRides (vendorID) 
WITH FULLSCAN, NORECOMPUTE; 

Die WITH-Optionen müssen angegeben werden, und für die Stichprobengröße sind die Optionen FULLSCAN und SAMPLE n PERCENT zulässig.

• Um Einzelspaltenstatistiken für mehrere Spalten zu erstellen, führen Sie CREATE STATISTICS für jede der Spalten aus.
• Mehrspaltenstatistiken werden nicht unterstützt.

Abfragen von partitionierten Daten

Gilt für Azure SQL Managed Instance und Azure Synapse Analytics.

Wenn Daten in Ordnern oder Dateien (auch als Partitionen bezeichnet) organisiert sind, verwenden Sie die Partitionslöschung, um nur bestimmte Ordner und Dateien abzufragen. Partitionslöschung reduziert die Anzahl der Dateien und die Datenmenge, die die Abfrage lesen und verarbeiten muss, was zu einer besseren Leistung führt.

Um Partitionen aus der Abfrageausführung zu entfernen, verwenden Sie die Metadatenfunktion filepath() in der WHERE-Klausel der Abfrage.

Erstellen Sie zunächst eine externe Datenquelle:

CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net'
);
GO

Die folgende Beispielabfrage liest nur die Datendateien von „NYC Yellow Taxi“ für die letzten drei Monate des Jahres 2017:

SELECT 
    r.filepath() AS filepath 
    ,r.filepath(1) AS [year] 
    ,r.filepath(2) AS [month] 
    ,COUNT_BIG(*) AS [rows] 
FROM OPENROWSET( 
        BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
        DATA_SOURCE = 'NYCTaxiExternalDataSource', 
        FORMAT = 'parquet' 
    ) 
WITH ( 
    vendorID INT 
) AS [r] 
WHERE 
    r.filepath(1) IN ('2017') 
    AND r.filepath(2) IN ('10', '11', '12') 
GROUP BY 
    r.filepath() 
    ,r.filepath(1) 
    ,r.filepath(2) 
ORDER BY filepath;

Wenn Ihre gespeicherten Daten nicht partitioniert sind, sollten Sie sie für eine verbesserte Abfrageleistung partitionieren.

Wenn Sie externe Tabellen verwenden, werden die Funktionen filepath() und filename() unterstützt, aber nicht in der WHERE-Klausel. Sie können weiterhin nach filename oder filepath filtern, wenn Sie diese in berechneten Spalten verwenden. Dies wird im folgenden Beispiel veranschaulicht:

CREATE EXTERNAL TABLE tbl_TaxiRides ( 
 vendorID VARCHAR(100) COLLATE Latin1_General_BIN2, 
 tpepPickupDateTime DATETIME2, 
 tpepDropoffDateTime DATETIME2, 
 passengerCount INT, 
 tripDistance FLOAT, 
 puLocationId VARCHAR(8000), 
 doLocationId VARCHAR(8000), 
 startLon FLOAT, 
 startLat FLOAT, 
 endLon FLOAT, 
 endLat FLOAT, 
 rateCodeId SMALLINT, 
 storeAndFwdFlag VARCHAR(8000), 
 paymentType VARCHAR(8000), 
 fareAmount FLOAT, 
 extra FLOAT, 
 mtaTax FLOAT, 
 improvementSurcharge VARCHAR(8000), 
 tipAmount FLOAT, 
 tollsAmount FLOAT, 
 totalAmount FLOAT, 
 [Year]  AS CAST(filepath(1) AS INT), --use filepath() for partitioning 
 [Month]  AS CAST(filepath(2) AS INT) --use filepath() for partitioning 
) 
WITH ( 
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = NYCTaxiExternalDataSource, 
 FILE_FORMAT = DemoFileFormat 
); 
GO 
 
SELECT * 
      FROM tbl_TaxiRides 
WHERE 
      [year]=2017             
      AND [month] in (10,11,12); 

Wenn Ihre gespeicherten Daten nicht partitioniert sind, sollten Sie sie für eine verbesserte Abfrageleistung partitionieren.

Übertragen von Berechnungen an Hadoop

Gilt nur für SQL Server 2016 (13.x), SQL Server 2017 (14.x) und SQL Server 2019 (15.x)

PolyBase überträgt einige Berechnungen per Push an die externe Quelle, um die Abfrage insgesamt zu optimieren. Der Abfrageoptimierer trifft eine kostenbasierte Entscheidung darüber, ob die Berechnung an Hadoop übertragen wird, wenn die Abfrageleistung dadurch verbessert wird. Für diese kostenbasierte Entscheidung verwendet der Abfrageoptimierer Statistiken in externen Tabellen. Bei der Übertragung der Berechnung werden MapReduce-Aufträge erstellt und die verteilten Berechnungsressourcen von Hadoop genutzt. Weitere Informationen finden Sie unter Weitergabeberechnungen in PolyBase.

Skalieren von Computeressourcen

Gilt nur für SQL Server 2016 (13.x), SQL Server 2017 (14.x) und SQL Server 2019 (15.x)

Um die Abfrageleistung zu verbessern, können Sie PolyBase-Erweiterungsgruppenvon SQL Server verwenden. Die ermöglicht eine parallele Datenübertragung zwischen SQL Server-Instanzen und Hadoop-Knoten und fügt Berechnungsressourcen für die Verarbeitung der externen Daten hinzu.

Zugehöriger Inhalt

• Häufig gestellte Fragen in PolyBase
• Datenvirtualisierung mit PolyBase in SQL Server