Considerazioni sulle prestazioni in PolyBase per SQL Server

  • Articolo

Si applica a: SQL Server 2016 (13.x) - Windows e versioni successive SQL Server 2017 (14.x) - Linux e versioni successive Azure Synapse Analytics

In PolyBase per SQL Server non esiste alcun limite rigido per il numero di file o la quantità di dati su cui è possibile eseguire query. Le prestazioni delle query dipendono dalla quantità di dati, dal formato dei dati, dalla modalità di organizzazione dei dati e dalla complessità delle query e dei join.

Questo articolo illustra importanti argomenti sulle prestazioni e indicazioni.

Statistiche

Raccogliere le statistiche sui dati esterni è una delle attività più importanti per ottimizzare le query. Più informazioni sui dati sono a disposizione dell’istanza, più rapidamente può eseguire le query. Query Optimizer del motore SQL è un ottimizzatore basato sui costi. Esegue un confronto fra i costi dei vari piani di query e poi sceglie quello che costa meno, che in molti casi è il piano eseguito più velocemente.

Creazione automatica di statistiche

A partire da SQL Server 2022, il motore di database analizza le query utente in ingresso per le statistiche mancanti. Se non sono presenti, Query Optimizer creerà automaticamente statistiche su singole colonne nel predicato della query o nella condizione di join per migliorare le stime di cardinalità del piano di query. La creazione automatica di statistiche viene generata in modo sincrono; se nelle colonne non sono presenti tutte le statistiche, è quindi possibile che si verifichi un leggero peggioramento delle prestazioni delle query. Il tempo necessario per creare le statistiche relative a una colonna dipende dalle dimensioni dei file di destinazione.

Creare statistiche manuali OPENROWSET

È possibile creare statistiche a colonna singola per il percorso OPENROWSET usando la stored procedure sys.sp_create_openrowset_statistics, passando la query select con una singola colonna come parametro:

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

Per impostazione predefinita, l'istanza usa il 100% dei dati forniti nel set di dati per creare statistiche. Facoltativamente, è possibile specificare le dimensioni del campione come percentuale usando le opzioni TABLESAMPLE. Per creare statistiche a colonna singola per più colonne, eseguire sys.sp_create_openrowset_statistics per ognuna delle colonne. Non è possibile creare statistiche su più colonne per il percorso OPENROWSET.

Per aggiornare le statistiche esistenti, eliminarle prima usando la stored procedure sys.sp_drop_openrowset_statistics e quindi ricrearle usando 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 
';

Creare statistiche manuali per una tabella esterna

La sintassi per la creazione di statistiche su tabelle esterne è simile a quella usata per le normali tabelle utente. Per creare statistiche su una colonna, fornire un nome per l'oggetto statistiche e il nome della colonna.

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

Le opzioni WITH sono obbligatorie e, per le dimensioni del campione, le opzioni consentite sono FULLSCAN e SAMPLE n PERCENT.

  • Per creare statistiche a colonna singola per più colonne, eseguire CREATE STATISTICS per ognuna delle colonne.
  • Le statistiche su più colonne non sono supportate.

Eseguire query su dati partizionati

I dati sono spesso organizzati in sottocartelle denominate anche partizioni. È possibile istruire l’istanza di SQL Server affinché esegua una query su cartelle e file specifici. Questa operazione ridurrà il numero di file e la quantità di dati che la query deve leggere ed elaborare, garantendo prestazioni migliori. Questo tipo di ottimizzazione query è noto come eliminazione di partizioni o eliminazione di partizioni. È possibile eliminare le partizioni dall'esecuzione di query usando la funzione di metadati filepath() nella clausola WHERE della query.

Per prima cosa, creare un'origine dati esterna.

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

L'esempio seguente legge i file di dati dei taxi di New York solo per gli ultimi tre mesi del 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;

Se i dati archiviati non sono partizionati, prendere in considerazione di partizionarli al fine di migliorare le prestazioni.

Se si usano tabelle esterne, le funzioni filepath() e filename() sono supportate, ma non nella clausola WHERE. È comunque possibile filtrare in base filename a o filepath se vengono usate nelle colonne calcolate. Questo concetto è illustrato nell'esempio seguente:

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

Se i dati archiviati non sono partizionati, prendere in considerazione di partizionarli al fine di migliorare le prestazioni.

Eseguire il push del calcolo in Hadoop.

Si applica a: solo SQL Server 2016 (13.x), SQL Server 2017 (14.x), e SQL Server 2019 (15.x).

PolyBase esegue il push di alcuni calcoli sull'origine esterna per ottimizzare la query complessiva. Query Optimizer prende una decisione basata sui costi per eseguire il push in Hadoop se in questo modo migliorano le prestazioni della query. Per prendere la decisione basata sui costi, Query Optimizer usa le statistiche sulle tabelle esterne. Il push del calcolo crea processi MapReduce e sfrutta le risorse di calcolo distribuite di Hadoop. Per altre informazioni, vedere Calcoli con distribuzione in PolyBase.

Ridimensionare le prestazioni

Si applica a: solo SQL Server 2016 (13.x), SQL Server 2017 (14.x), e SQL Server 2019 (15.x).

Per migliorare le prestazioni delle query, è possibile usare i gruppi con scalabilità orizzontale di PolyBaseper SQL Server. In questo modo viene abilitato il trasferimento dei dati paralleli tra le istanze di SQL Server e i nodi di Hadoop e vengono aggiunte le risorse di calcolo per operare sui dati esterni.

Importante

I gruppi con scalabilità orizzontale PolyBase di Microsoft SQL Server verranno ritirati. La funzionalità dei gruppi con scalabilità orizzontale verrà rimossa dal prodotto in SQL Server 2022 (16.x). La virtualizzazione dei dati PolyBase continuerà a essere completamente supportata come funzionalità di aumento delle prestazioni in SQL Server. Per altre informazioni, vedere Opzioni per i Big Data nella piattaforma Microsoft SQL Server.

Contenuto correlato