Cara mengetahui apakah pushdown eksternal terjadi

Artikel ini merinci cara menentukan apakah kueri PolyBase mendapat manfaat dari pushdown ke sumber data eksternal. Untuk informasi lebih lanjut mengenai pushdown eksternal, lihat perhitungan pushdown di PolyBase.

Apakah kueri saya mendapat keuntungan dari pushdown eksternal?

Komputasi pushdown meningkatkan performansi kueri pada sumber data eksternal. Tugas komputasi tertentu didelegasikan ke sumber data eksternal alih-alih dibawa ke SQL Server. Terutama dalam kasus pemfilteran dan pendorongan gabungan, beban kerja pada instans SQL Server dapat sangat berkurang.

Komputasi pushdown PolyBase dapat secara signifikan meningkatkan performa kueri. Jika kueri PolyBase berjalan lambat, Anda harus menentukan apakah pushdown dari kueri PolyBase Anda sedang terjadi.

Ada tiga skenario berbeda di mana pushdown dapat diamati dalam rencana eksekusi:

• Pendorongan predikat filter
• Bergabung dengan pushdown
• Pendorongan agregasi

Nota

Ada batasan tentang apa yang dapat didorong ke sumber data eksternal dengan komputasi pushdown PolyBase:

• Beberapa fungsi T-SQL dapat mencegah terjadinya pushdown. Untuk informasi lebih lanjut, silakan lihat fitur dan batasan PolyBase.
• Untuk daftar fungsi T-SQL yang dapat didorong ke bawah, lihat komputasi Pushdown di PolyBase.

Dua fitur baru SQL Server 2019 (15.x) telah diperkenalkan untuk memungkinkan administrator menentukan apakah kueri PolyBase sedang didorong ke sumber data eksternal:

• Lihat Perkiraan Rencana Eksekusi dengan bendera pelacakan 6408
• Menampilkan read_command dalam tampilan manajemen dinamis sys.dm_exec_external_work

Artikel ini menyediakan detail tentang cara menggunakan masing-masing dari dua kasus penggunaan ini, untuk masing-masing dari tiga skenario pushdown.

Menggunakan TF6408

Secara default, perkiraan rencana eksekusi tidak mengekspos rencana kueri jarak jauh, dan Anda hanya melihat objek operator kueri jarak jauh. Misalnya, perkiraan rencana eksekusi dari SQL Server Management Studio (SSMS):

Atau, di Azure Data Studio:

Mulai SQL Server 2019 (15.x), Anda dapat mengaktifkan bendera pelacakan baru 6408 secara global menggunakan DBCC TRACEON. Misalnya:

DBCC TRACEON (6408, -1);  

Bendera pelacakan ini hanya berfungsi dengan perkiraan rencana eksekusi dan tidak berpengaruh pada rencana eksekusi aktual. Bendera pelacakan ini memaparkan informasi tentang operator Kueri Jarak Jauh yang memperlihatkan apa yang terjadi selama fase Kueri Jarak Jauh.

Rencana Eksekusi dibaca dari kanan-ke-kiri, seperti yang ditunjukkan oleh arah panah. Jika sebuah operator berada di sebelah kanan operator lain, dikatakan bahwa operator tersebut "mendahului" operator yang lain. Jika operator berada di sebelah kiri operator lain, operator tersebut dikatakan berada "setelah" operator yang lain.

• Di SSMS, sorot kueri dan pilih Tampilkan Perkiraan Rencana Eksekusi dari toolbar atau gunakan Ctrl+L.
• Di Azure Data Studio, sorot kueri dan pilih Explain. Kemudian pertimbangkan skenario berikut untuk menentukan apakah pushdown terjadi.

Masing-masing contoh di bawah ini mencakup output dari SSMS dan Azure Data Studio.

Penurunan predikat filter (tampilan dengan rencana eksekusi)

Pertimbangkan kueri berikut, yang menggunakan predikat filter dalam klausa WHERE:

SELECT *
FROM [Person].[BusinessEntity] AS be
WHERE be.BusinessEntityID = 17907;

Jika pushdown dari predikat filter terjadi, operator filter berada sebelum operator eksternal. Ketika operator filter sebelum operator eksternal, pemfilteran terjadi sebelum dipilih kembali dari sumber data eksternal, menunjukkan predikat filter didorong ke bawah.

Dengan penerapan predikat filter secara langsung (lihat dengan rencana eksekusi)

Dengan bendera pelacakan 6408 diaktifkan, Anda sekarang melihat informasi tambahan dalam perkiraan output rencana eksekusi. Output bervariasi antara SSMS dan Azure Data Studio.

Di SSMS, rencana kueri jarak jauh ditampilkan dalam perkiraan rencana eksekusi sebagai Kueri 2 (sp_execute_memo_node_1) dan sesuai dengan operator Kueri Jarak Jauh di Kueri 1. Misalnya:

Di Azure Data Studio, eksekusi kueri jarak jauh diwakili sebagai rencana kueri tunggal. Misalnya:

Tanpa pushdown predikat filter (lihat rencana eksekusi)

Jika pushdown predikat filter tidak terjadi, filter akan diletakkan setelah operator eksternal.

Perkiraan rencana eksekusi dari SSMS:

Perkiraan rencana eksekusi dari Azure Data Studio:

Pembebanan JOIN

Pertimbangkan kueri berikut yang menggunakan operator JOIN untuk dua tabel eksternal pada sumber data eksternal yang sama:

SELECT be.BusinessEntityID, bea.AddressID
FROM [Person].[BusinessEntity] AS be
INNER JOIN [Person].[BusinessEntityAddress] AS bea
ON be.BusinessEntityID = bea.BusinessEntityID;

Jika JOIN didorong ke sumber data eksternal, operator Join akan berada di depan operator eksternal. Dalam contoh ini, [BusinessEntity] dan [BusinessEntityAddress] adalah tabel eksternal.

Dengan pushdown gabungan (tampilan dengan rencana eksekusi)

Perkiraan rencana eksekusi dari SSMS:

Perkiraan rencana eksekusi dari Azure Data Studio:

Tanpa penurunan dari penggabungan (tampilan dengan rencana eksekusi)

Jika JOIN tidak diturunkan ke sumber data eksternal, operator Join akan berada setelah operator eksternal. Di SSMS, operator eksternal berada dalam rencana kueri untuk sp_execute_memo_node, yang berada di operator Kueri Jarak Jauh di Kueri 1. Di Azure Data Studio, operator Gabung berada setelah operator eksternal.

Perkiraan rencana eksekusi dari SSMS:

Perkiraan rencana eksekusi dari Azure Data Studio:

Pushdown agregasi (tampilan dengan rencana eksekusi)

Pertimbangkan kueri berikut, yang menggunakan fungsi agregat:

SELECT SUM([Quantity]) as Quant
FROM [AdventureWorks2022].[Production].[ProductInventory];

Dengan penerapan pushdown pada agregasi (lihat dengan rencana eksekusi)

Jika terjadi pushdown agregasi, operator agregasi akan berada sebelum operator eksternal. Ketika operator agregasi sebelum operator eksternal, agregasi terjadi sebelum dipilih kembali dari sumber data eksternal, menunjukkan agregasi didorong ke bawah.

Perkiraan rencana eksekusi dari SSMS:

Perkiraan rencana eksekusi dari Azure Data Studio:

Tanpa pushdown agregasi (dengan melihat rencana eksekusi)

Jika pushdown agregasi tidak terjadi, maka operator agregasi akan berada setelah operator eksternal.

Perkiraan rencana eksekusi dari SSMS:

Perkiraan rencana eksekusi dari Azure Data Studio:

Menggunakan DMV

Mulai dengan SQL Server 2019 (15.x) dan versi yang lebih baru, kolom read_command dari DMV sys.dm_exec_external_work memperlihatkan kueri yang dikirim ke sumber data eksternal. Ini memungkinkan Anda untuk menentukan apakah pushdown terjadi, tetapi tidak mengekspos pola eksekusi. Menampilkan kueri jarak jauh tidak memerlukan TF6408.

Nota

Untuk penyimpanan Hadoop dan Azure, read_command selalu mengembalikan NULL.

Anda dapat menjalankan kueri berikut dan menggunakan start_time/end_time dan read_command untuk mengidentifikasi kueri yang sedang diselidiki:

SELECT execution_id, start_time, end_time, read_command
FROM sys.dm_exec_external_work
ORDER BY execution_id desc;

Nota

Salah satu batasan metode sys.dm_exec_external_work adalah bidang read_command di DMV dibatasi hingga 4000 karakter. Jika kueri cukup panjang, read_command kemungkinan akan terpotong sebelum Anda dapat melihat fungsi WHERE/JOIN/agregasi di read_command.

Pemrosesan predikat filter lebih awal (tampilan dengan DMV)

Pertimbangkan kueri yang digunakan dalam contoh predikat filter sebelumnya:

SELECT *
FROM [Person].[BusinessEntity] be
WHERE be.BusinessEntityID = 17907;

Dengan pushdown filter (tampilan menggunakan DMV)

Anda dapat mengetahui apakah pushdown predikat filter terjadi dengan memeriksa read_command di DMV. Anda akan melihat sesuatu seperti sampel ini:

SELECT [T1_1].[BusinessEntityID] AS [BusinessEntityID], [T1_1].[rowguid] AS [rowguid], 
  [T1_1].[ModifiedDate] AS [ModifiedDate] FROM 
  (SELECT [T2_1].[BusinessEntityID] AS [BusinessEntityID], [T2_1].[rowguid] AS [rowguid], 
    [T2_1].[ModifiedDate] AS [ModifiedDate] 
FROM [AdventureWorks2022].[Person].[BusinessEntity] AS T2_1 
WHERE ([T2_1].[BusinessEntityID] = CAST ((17907) AS INT))) AS T1_1;

Klausa WHERE berada dalam perintah yang dikirim ke sumber data eksternal, yang berarti predikat filter sedang dievaluasi di sumber data eksternal. Pemfilteran pada himpunan data terjadi di sumber data eksternal, dan hanya himpunan data yang difilter yang diambil oleh PolyBase.

Tanpa pushdown filter (tampilan dengan DMV)

Jika pushdown tidak terjadi, Anda mungkin melihat sesuatu seperti:

SELECT "BusinessEntityID","rowguid","ModifiedDate" FROM "AdventureWorks2022"."Person"."BusinessEntity"

Tidak ada klausa WHERE dalam perintah yang dikirim ke sumber data eksternal, sehingga predikat filter tidak didorong ke bawah. Pemfilteran pada seluruh himpunan data terjadi di sisi SQL Server, setelah himpunan data diambil oleh PolyBase.

Penurunan JOIN (melihat dengan DMV)

Pertimbangkan kueri yang digunakan dalam contoh JOIN sebelumnya:

SELECT be.BusinessEntityID, bea.AddressID
FROM [Person].[BusinessEntity] be
INNER JOIN [Person].[BusinessEntityAddress] bea ON be.BusinessEntityID = bea.BusinessEntityID;

Dengan pushdown dari join (tampilan menggunakan DMV)

Jika JOIN didorong ke sumber data eksternal, Anda akan melihat sesuatu seperti:

SELECT [T1_1].[BusinessEntityID] AS [BusinessEntityID], [T1_1].[AddressID] AS [AddressID] 
FROM (SELECT [T2_2].[BusinessEntityID] AS [BusinessEntityID], [T2_1].[AddressID] AS [AddressID] 
FROM [AdventureWorks2022].[Person].[BusinessEntityAddress] AS T2_1 
INNER JOIN  [AdventureWorks2022].[Person].[BusinessEntity] AS T2_2  
ON ([T2_1].[BusinessEntityID] = [T2_2].[BusinessEntityID])) AS T1_1;

Klausa JOIN ada dalam perintah yang dikirim ke sumber data eksternal, sehingga JOIN didorong ke bawah. Gabungan pada himpunan data terjadi di sumber data eksternal, dan hanya himpunan data yang cocok dengan kondisi gabungan yang diambil oleh PolyBase.

Tanpa pemindahan gabungan (tampilan dengan DMV)

Jika operasi pushdown gabungan tidak terjadi, Anda akan melihat ada dua kueri berbeda yang dijalankan pada sumber data eksternal.

SELECT [T1_1].[BusinessEntityID] AS [BusinessEntityID], [T1_1].[AddressID] AS [AddressID] 
FROM [AdventureWorks2022].[Person].[BusinessEntityAddress] AS T1_1;

SELECT [T1_1].[BusinessEntityID] AS [BusinessEntityID] FROM [AdventureWorks2022].[Person].[BusinessEntity] AS T1_1;

Gabungan dua himpunan data terjadi di sisi SQL Server, setelah kedua himpunan data diambil oleh PolyBase.

Pushdown agregasi (tampilan dengan DMV)

Pertimbangkan kueri berikut, yang menggunakan fungsi agregat:

SELECT SUM([Quantity]) as Quant
FROM [AdventureWorks2022].[Production].[ProductInventory];

Dengan Penurunan agregasi (lihat dengan DMV)

Ketika pushdown agregasi terjadi, Anda dapat melihat fungsi agregasi di read_command. Misalnya:

SELECT [T1_1].[col] AS [col] FROM (SELECT SUM([T2_1].[Quantity]) AS [col] 
FROM [AdventureWorks2022].[Production].[ProductInventory] AS T2_1) AS T1_1

Fungsi agregasi ada dalam perintah yang dikirim ke sumber data eksternal, sehingga agregasi didorong ke bawah. Agregasi terjadi di sumber data eksternal, dan hanya himpunan data agregat yang diambil oleh PolyBase.

Tanpa pushdown agregasi (lihat melalui DMV)

Jika agregasi tidak didorong ke bawah, Anda tidak akan melihat fungsi penggabungan di read_command. Misalnya:

SELECT "Quantity" FROM "AdventureWorks2022"."Production"."ProductInventory"

Agregasi dilakukan di SQL Server, setelah himpunan data yang tidak diagregasi diambil oleh PolyBase.

Langkah berikutnya

• pemecahan masalah PolyBase
• kesalahan PolyBase dan kemungkinan solusi