Pertimbangan performa di PolyBase untuk SQL Server

  • Artikel

Berlaku untuk: SQL Server 2016 (13.x) - Windows dan versi yang lebih baru SQL Server 2017 (14.x) - Linux dan versi yang lebih baru Azure Synapse Analytics

Di PolyBase untuk SQL Server, tidak ada batasan keras untuk jumlah file atau jumlah data yang dapat dikueri. Performa kueri bergantung pada jumlah data, format data, cara data diatur, dan kompleksitas kueri dan gabungan.

Artikel ini membahas topik dan panduan performa penting.

Statistik

Mengumpulkan statistik pada data Anda merupakan salah satu hal paling penting yang dapat Anda lakukan untuk optimasi kueri. Semakin banyak yang diketahui instans tentang data Anda, semakin cepat ia dapat menjalankan kueri. Pengoptimal kueri mesin SQL adalah pengoptimal berbasis biaya. Pengoptimal membandingkan biaya paket kueri yang bermacam, dan kemudian memilih paket dengan biaya terendah. Dalam kebanyakan kasus, ia memilih rencana yang menjalankan yang tercepat.

Pembuatan statistik otomatis

Mulai SQL Server 2022, Mesin Database menganalisis kueri pengguna masuk untuk statistik yang hilang. Jika statistik hilang, pengoptimal kueri secara otomatis membuat statistik pada kolom individual dalam predikat kueri atau kondisi gabungan untuk meningkatkan perkiraan kardinalitas untuk rencana kueri. Pembuatan statistik otomatis dilakukan secara sinkron sehingga Anda mungkin mengamati performa kueri yang sedikit terdegradasi jika kolom Anda kehilangan statistik. Waktu untuk membuat statistik untuk satu kolom tergantung pada ukuran file yang ditargetkan.

Membuat statistik manual OPENROWSET

Statistik kolom tunggal untuk jalur OPENROWSET dapat dibuat menggunakan sys.sp_create_openrowset_statistics prosedur tersimpan, dengan meneruskan kueri pemilihan dengan satu kolom sebagai 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';

Secara default, instans tersebut menggunakan 100% data yang disediakan dalam himpunan data untuk membuat statistik. Anda dapat secara opsional menentukan ukuran sampel sebagai persentase menggunakan opsi TABLESAMPLE. Untuk membuat statistik kolom tunggal untuk beberapa kolom, jalankan sys.sp_create_openrowset_statistics untuk setiap kolom. Anda tidak dapat membuat statistik multikolom untuk jalur OPENROWSET.

Untuk memperbarui statistik yang ada, letakkan terlebih dahulu menggunakan prosedur sys.sp_drop_openrowset_statistics yang disimpan, lalu buat ulang menggunakan 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 
';

Membuat statistik manual tabel eksternal

Sintaks untuk membuat statistik pada tabel eksternal menyerupai yang digunakan untuk tabel pengguna biasa. Untuk membuat statistik pada kolom, berikan nama untuk objek statistik dan nama kolom:

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

Opsinya WITH wajib, dan untuk ukuran sampel, opsi yang diizinkan adalah FULLSCAN dan SAMPLE n PERCENT.

  • Untuk membuat statistik kolom tunggal untuk beberapa kolom, jalankan CREATE STATISTICS untuk setiap kolom.
  • Statistik multi-kolom tidak didukung.

Data yang dipartisi kueri

Data sering diatur dalam subfolder yang juga disebut partisi. Anda dapat menginstruksikan instans SQL Server untuk mengkueri hanya folder dan file tertentu. Melakukannya mengurangi jumlah file dan jumlah data yang perlu dibaca dan diproses kueri, sehingga menghasilkan performa yang lebih baik. Jenis pengoptimalan kueri ini dikenal sebagai pemangkasan partisi atau eliminasi partisi. Anda dapat menghilangkan partisi dari eksekusi kueri dengan menggunakan fungsi filepath() metadata dalam WHERE klausa kueri.

Pertama, buat sumber data eksternal:

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

Contoh kueri berikut membaca file data Taksi Kuning NYC hanya selama tiga bulan terakhir 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;

Jika data tersimpan Anda tidak dipartisi, pertimbangkan untuk mempartisinya untuk meningkatkan performa kueri.

Jika Anda menggunakan tabel eksternal, filepath() dan filename() fungsi didukung tetapi tidak dalam WHERE klausa. Anda masih dapat memfilter menurut filename atau filepath jika Anda menggunakannya dalam kolom komputasi. Contoh berikut menunjukkan hal berikut:

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

Jika data tersimpan Anda tidak dipartisi, pertimbangkan untuk mempartisinya untuk meningkatkan performa kueri.

Dorong komputasi ke Hadoop

Berlaku untuk SQL Server 2016 (13.x), SQL Server 2017 (14.x), dan SQL Server 2019 (15.x) saja

PolyBase mendorong beberapa komputasi ke sumber eksternal untuk mengoptimalkan kueri keseluruhan. Pengoptimal kueri membuat keputusan berbasis biaya untuk mendorong komputasi ke Hadoop, jika itu akan meningkatkan performa kueri. Pengoptimal kueri menggunakan statistik pada tabel eksternal untuk membuat keputusan berbasis biaya. Mendorong komputasi membuat pekerjaan MapReduce dan memanfaatkan sumber daya komputasi terdistribusi Hadoop. Untuk informasi selengkapnya, lihat Komputasi pushdown di PolyBase.

Menskalakan sumber daya komputasi

Berlaku untuk SQL Server 2016 (13.x), SQL Server 2017 (14.x), dan SQL Server 2019 (15.x) saja

Untuk meningkatkan performa kueri, Anda bisa menggunakan grup peluasan skala SQL Server PolyBase. Ini memungkinkan transfer data paralel antara instans SQL Server dan simpul Hadoop, dan menambahkan sumber daya komputasi untuk beroperasi pada data eksternal.

Penting

Grup peluasan skala Microsoft SQL Server PolyBase akan dihentikan. Fungsionalitas grup peluasan skala akan dihapus dari produk di SQL Server 2022 (16.x). Virtualisasi data PolyBase akan terus didukung sepenuhnya sebagai fitur peningkatan skala di SQL Server. Untuk informasi selengkapnya, lihat Opsi big data di platform Microsoft SQL Server.

Konten terkait