Prestatieoverwegingen in PolyBase voor SQL Server

Van toepassing op: SQL Server 2016 (13.x) - Windows en latere versies SQL Server 2017 (14.x) - Linux en latere versies Azure Synapse Analytics

In PolyBase voor SQL Server is er geen vaste limiet voor het aantal bestanden of de hoeveelheid gegevens waarop query's kunnen worden uitgevoerd. Queryprestaties zijn afhankelijk van de hoeveelheid gegevens, gegevensindeling, de manier waarop gegevens worden georganiseerd en complexiteit van query's en joins.

In dit artikel worden belangrijke prestatieonderwerpen en richtlijnen behandeld.

Statistiek

Het verzamelen van statistieken over uw externe gegevens is een van de belangrijkste dingen die u kunt doen voor queryoptimalisatie. Hoe meer het exemplaar weet over uw gegevens, hoe sneller het query's kan uitvoeren. De optimalisatiefunctie voor SQL Engine-query's is een optimalisatie op basis van kosten. Het vergelijkt de kosten van verschillende queryplannen en kiest vervolgens het plan met de laagste kosten. In de meeste gevallen wordt het plan gekozen dat het snelst wordt uitgevoerd.

Automatisch statistieken maken

Vanaf SQL Server 2022 analyseert de database-engine binnenkomende gebruikersquery's voor ontbrekende statistieken. Als er statistieken ontbreken, worden met de queryoptimalisatie automatisch statistieken gemaakt voor afzonderlijke kolommen in het querypredicaat of joinvoorwaarde om de kardinaliteitschattingen voor het queryplan te verbeteren. Het automatisch maken van statistieken wordt synchroon uitgevoerd, zodat u mogelijk enigszins verslechterde queryprestaties kunt observeren als uw kolommen statistieken missen. De tijd die nodig is om statistieken voor één kolom te maken, is afhankelijk van de grootte van de beoogde bestanden.

Handmatige statistieken voor OPENROWSET maken

Statistieken met één kolom voor het OPENROWSET-pad kunnen worden gemaakt met behulp van de sys.sp_create_openrowset_statistics opgeslagen procedure door de selectiequery door te geven met één kolom als parameter:

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';

Het exemplaar maakt standaard gebruik van 100% van de gegevens die in de dataset zijn opgegeven om statistieken te maken. U kunt desgewenst de grootte van de steekproef opgeven als een percentage met behulp van de opties TABLESAMPLE. Als u statistieken met één kolom voor meerdere kolommen wilt maken, voert u deze uit sys.sp_create_openrowset_statistics voor elk van de kolommen. U kunt geen statistieken met meerdere kolommen maken voor het OPENROWSET-pad.

Als u bestaande statistieken wilt bijwerken, verwijder ze eerst met behulp van de sys.sp_drop_openrowset_statistics opgeslagen procedure en maak ze vervolgens opnieuw aan met behulp van de sys.sp_create_openrowset_statistics.

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 
';

Handmatige statistieken voor externe tabellen maken

De syntaxis voor het maken van statistieken voor externe tabellen lijkt op de syntaxis die wordt gebruikt voor gewone gebruikerstabellen. Als u statistieken voor een kolom wilt maken, geeft u een naam op voor het statistiekenobject en de naam van de kolom:

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

De WITH opties zijn verplicht en voor de steekproefgrootte zijn FULLSCAN de toegestane opties en SAMPLE n PERCENT.

• Als u statistieken met één kolom voor meerdere kolommen wilt maken, voert u deze uit CREATE STATISTICS voor elk van de kolommen.
• Statistieken met meerdere kolommen worden niet ondersteund.

Gepartitioneerde gegevens opvragen

Van toepassing op Azure SQL Managed Instance en Azure Synapse Analytics.

Wanneer gegevens zijn ingedeeld in mappen of bestanden (ook wel partities genoemd), gebruikt u partitie-verwijdering om alleen specifieke mappen en bestanden op te vragen. Partitieuitschakeling vermindert het aantal bestanden en de hoeveelheid gegevens die de query moet lezen en verwerken, wat resulteert in betere prestaties.

Als u partities wilt elimineren bij het uitvoeren van query's, gebruikt u de metagegevensfunctie filepath() in de WHERE component van de query.

Maak eerst een externe gegevensbron:

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

De volgende voorbeeldquery leest nyC Yellow Taxi-gegevensbestanden alleen voor de afgelopen drie maanden van 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;

Als uw opgeslagen gegevens niet zijn gepartitioneerd, kunt u overwegen deze te partitioneren om de queryprestaties te verbeteren.

Als u externe tabellen gebruikt, worden de functies filepath() en filename() ondersteund, maar niet in de WHERE clausule. U kunt nog steeds filteren op filename of filepath als u deze gebruikt in berekende kolommen. In het volgende voorbeeld ziet u dit:

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

Als uw opgeslagen gegevens niet zijn gepartitioneerd, kunt u overwegen deze te partitioneren om de queryprestaties te verbeteren.

Berekening naar Hadoop pushen

Van toepassing op ALLEEN SQL Server 2016 (13.x), SQL Server 2017 (14.x) en SQL Server 2019 (15.x)

PolyBase pusht enkele berekeningen naar de externe bron om de algehele query te optimaliseren. Met de queryoptimalisatie wordt een kostengebaseerde beslissing genomen om berekeningen naar Hadoop te pushen, als dat de queryprestaties verbetert. De queryoptimalisatie gebruikt statistieken voor externe tabellen om de kostengebaseerde beslissing te nemen. Door berekeningen te pushen worden MapReduce-taken gemaakt en worden de gedistribueerde rekenresources van Hadoop gebruikt. Zie Pushdown-berekeningen in PolyBase voor meer informatie.

Rekenkracht aanpassen

Van toepassing op ALLEEN SQL Server 2016 (13.x), SQL Server 2017 (14.x) en SQL Server 2019 (15.x)

Als u de queryprestaties wilt verbeteren, kunt u scale-outgroepen van SQL Server PolyBase gebruiken. Dit maakt parallelle gegevensoverdracht mogelijk tussen SQL Server-exemplaren en Hadoop-knooppunten en voegt rekenresources toe voor gebruik op de externe gegevens.

Verwante inhoud

• Veelgestelde vragen in PolyBase
• Gegevensvirtualisatie met PolyBase in SQL Server