Posting ini adalah bagian ketiga dari seri pada operator bergabung (pastikan untuk membaca bagian 1 - loop bersatu bergabung , dan bagian 2 - gabung bergabung ). Terjemahan artikel disiapkan khusus untuk siswa kursus "Pengembang MS SQL Server" .
Hash Match Joins adalah alat kerja andal dari operator koneksi fisik.
Sementara Nested Loops Join akan gagal jika ada terlalu banyak data untuk dimasukkan ke dalam memori, dan Gabung Bergabung akan meminta input untuk diurutkan, Hash Match akan menghubungkan data apa pun yang Anda kirim (asalkan predikat kesetaraan dieksekusi untuk koneksi, dan sejauh ini ada cukup ruang kosong di tempdb Anda).
Tonton video terkait YouTube
Algoritma pencocokan hash terdiri dari dua tahap, yang berfungsi sebagai berikut:
Selama fase build pertama, SQL Server membuat tabel hash di memori dari salah satu tabel input (biasanya yang terkecil dari keduanya). Hash dihitung berdasarkan tombol input dan kemudian disimpan bersama dengan garis dalam tabel hash di blok yang sesuai. Dalam kebanyakan kasus, hanya ada satu baris data di setiap blok, kecuali ketika:
- Ada garis dengan kunci duplikat.
- Fungsi hash menciptakan tabrakan, dan kunci yang sama sekali berbeda menerima hash yang sama (ini jarang terjadi, tetapi mungkin).
Setelah membuat tabel hash, tahap "Probe" (verifikasi) dimulai. Pada langkah kedua, SQL Server menghitung hash kunci untuk setiap baris di tabel input kedua dan memeriksa apakah ada di tabel hash yang dibuat di langkah pertama. Jika ada kecocokan untuk hash ini, maka akan diperiksa apakah kunci dari baris dalam tabel hash dan baris dari tabel kedua benar-benar cocok (pemeriksaan ini harus dilakukan karena kemungkinan tabrakan).
Versi umum dari algoritma hash match terjadi ketika, pada tahap konstruksi, tidak mungkin untuk membuat tabel hash yang dapat sepenuhnya disimpan dalam memori:
Ini terjadi ketika ada lebih banyak data daripada yang dapat disimpan dalam memori, atau ketika SQL Server menyediakan memori tidak cukup untuk koneksi hash cocok .
Ketika SQL Server tidak memiliki cukup memori untuk menyimpan tabel hash selama fase build, ia terus bekerja, menyimpan beberapa blok dalam memori dan menempatkan blok lain di tempdb.
Dalam fase validasi, SQL Server menggabungkan deretan data dari tabel kedua menjadi blok dari fase build di memori. Jika blok yang sesuai dengan baris ini saat ini kehabisan memori, SQL Server menulis baris ini ke tempdb untuk perbandingan nanti.
Ketika pencocokan untuk satu blok selesai, SQL Server membersihkan data ini dari memori dan memuat blok berikut ke dalam memori. Kemudian membandingkan baris dari tabel kedua (saat ini terletak di tempdb) dengan blok baru di memori.
Seperti halnya setiap pernyataan bergabung secara fisik dalam seri ini, detail tentang pernyataan pertandingan hash dapat ditemukan di Hugo Kornelis bantuan pada pertandingan hash .
Apa yang ditampilkan dengan Hash Match Join?
Mengetahui fitur internal tentang cara kerja hash match join , memungkinkan kami menentukan apa yang dipikirkan pengoptimal tentang data kami dan operator koneksi hulu, membantu kami fokus pada penyempurnaan kinerja.
Berikut adalah beberapa skenario untuk dipertimbangkan saat berikutnya Anda melihat bahwa hash match join digunakan dalam rencana eksekusi Anda:
- Sementara hash match join dapat menggabungkan kumpulan data besar, membangun tabel hash dari tabel input pertama adalah operasi pemblokiran yang mencegah eksekusi pernyataan berikutnya. Dalam hal ini, saya selalu memeriksa apakah ada cara mudah untuk mengkonversi pencocokan hash ke loop bersarang atau menggabungkan gabung. Kadang-kadang ini tidak mungkin (terlalu banyak baris untuk loop bersarang atau data yang tidak disortir untuk digabungkan), tetapi selalu layak untuk memeriksa apakah indeks sederhana atau estimasi yang ditingkatkan akan dihasilkan dari memperbarui statistik ke fakta bahwa SQL Server memilih pernyataan bergabung dengan hash non-blocking.
- Gabungan hash cocok untuk koneksi besar karena mereka dapat ditransfer ke tempdb, yang memungkinkan mereka untuk membuat koneksi ke dataset besar, yang dapat menyebabkan koneksi gagal dalam memori menggunakan loop bersarang atau menggabungkan pernyataan bergabung.
- Jika Anda melihat pernyataan bergabung hash cocok , itu berarti bahwa SQL Server menganggap input terlalu besar. Jika kita tahu bahwa data input kita seharusnya tidak begitu besar, maka ada baiknya memeriksa jika ada masalah dengan statistik atau estimasi, karena SQL Server yang salah memilih hash match join .
- Ketika dieksekusi di memori, hash match join cukup efisien. Masalah muncul ketika fase build ke tempdb.
- Jika saya melihat segitiga kuning kecil yang menunjukkan bahwa koneksi menuju ke tempdb, saya mengerti mengapa ini terjadi: jika ada lebih banyak data daripada memori yang tersedia, ada sedikit yang dapat dilakukan, tetapi jika memori yang dialokasikan tampaknya kecil, ini mungkin berarti bahwa kami mungkin memiliki satu masalah lagi dengan statistik yang mengarah ke perkiraan pengoptimal SQL Server yang terlalu rendah.
Terima kasih telah membaca artikel ini. Anda mungkin juga menyukai Twitter saya .
Kami membahas topik ini dalam pelajaran terbuka sebelumnya. Menunggu komentar Anda!