Überlegungen zur Leistung 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 Von 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, sodass Sie die Leistung von Abfragen leicht beeinträchtigt sehen können, wenn Ihre Spalten Statistiken fehlen. Die Zeit zum Erstellen von Statistiken für eine einzelne Spalte hängt von der Größe der Zieldateien ab.
Erstellen manueller OPENROWSET-Statistiken
Einzelspaltige Statistiken für den OPENROWSET-Pfad können mithilfe der sys.sp_create_openrowset_statistics gespeicherten Prozedur erstellt werden, indem die Auswahlabfrage 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 Beispielgröße mithilfe der TABLESAMPLE-Optionen als Prozentsatz angeben. Führen Sie zum Erstellen von Einzelspaltenstatistiken für mehrere Spalten sys.sp_create_openrowset_statistics für jede der Spalten aus. Sie können keine mehrspaltigen Statistiken für den OPENROWSET-Pfad erstellen.
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 für externe 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 sind obligatorisch, und für die Beispielgröße sind FULLSCAN die zulässigen Optionen und SAMPLE n PERCENT.
- Führen Sie zum Erstellen von Einzelspaltenstatistiken für mehrere Spalten
CREATE STATISTICSfür jede der Spalten aus. - Mehrspaltenstatistiken werden nicht unterstützt.
Abfragen von partitionierten Daten
Daten werden häufig in Unterordnern organisiert, die auch Partitionen genannt werden. Sie können die SQL Server-Instanz anweisen, nur bestimmte Ordner und Dateien abzufragen. Dies reduziert die Anzahl der Dateien und die Datenmenge, die die Abfrage lesen und verarbeiten muss, was zu einer besseren Leistung führt. Diese Art der Abfrageoptimierung wird als Partitionslöschung oder Partitionslöschung bezeichnet. Sie können Partitionen aus der Abfrageausführung entfernen, indem Sie die Metadatenfunktion filepath() in der WHERE Klausel der Abfrage verwenden.
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, und filename() Funktionen werden unterstützt, filepath() 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.
Pushberechnung auf 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.
Wichtig
Die Microsoft SQL Server PolyBase-Erweiterungsgruppen werden eingestellt. Die Erweiterungsgruppen-Funktionalität wird in SQL Server 2022 (16.x) aus dem Produkt entfernt. PolyBase-Datenvirtualisierung wird in SQL Server weiterhin als Aufskalierungsgruppen-Feature vollständig unterstützt. Weitere Informationen finden Sie unter Big Data-Optionen auf der Microsoft SQL Server-Plattform.
Zugehöriger Inhalt
Feedback
Bald verfügbar: Im Laufe des Jahres 2024 werden wir GitHub-Tickets als Feedbackmechanismus für Inhalte auslaufen lassen und es durch ein neues Feedbacksystem ersetzen. Weitere Informationen finden Sie unter: https://aka.ms/ContentUserFeedback.
Einreichen und Feedback anzeigen für