Bagikan melalui

Menentukan Jumlah Bucket yang Tepat untuk Indeks Hash

Anda harus menentukan nilai untuk BUCKET_COUNT parameter saat membuat tabel yang dioptimalkan memori. Topik ini membuat rekomendasi untuk menentukan nilai yang sesuai untuk BUCKET_COUNT parameter. Jika Anda tidak dapat menentukan jumlah wadah yang benar, gunakan indeks non-kluster sebagai gantinya. Nilai yang salah BUCKET_COUNT , terutama yang terlalu rendah, dapat berdampak signifikan pada performa beban kerja, serta waktu pemulihan database. Lebih baik melebih-lebihkan jumlah ember.

Kunci indeks duplikat dapat mengurangi performa dengan indeks hash karena kunci di-hash ke wadah yang sama, menyebabkan rantai wadah tersebut meningkat.

Untuk informasi selengkapnya mengenai indeks hash nonclustered, lihat Indeks Hash dan Panduan untuk Menggunakan Indeks pada Tabel Memory-Optimized.

Satu tabel hash dialokasikan untuk setiap indeks hash pada tabel yang dioptimalkan memori. Ukuran tabel hash yang dialokasikan untuk indeks ditentukan oleh BUCKET_COUNT parameter dalam CREATE TABLE (Transact-SQL) atau CREATE TYPE (Transact-SQL). Jumlah wadah akan dibulatkan secara internal ke kekuatan dua berikutnya. Misalnya, menentukan jumlah wadah 300.000 akan menghasilkan jumlah wadah aktual 524.288.

Untuk tautan ke artikel dan video tentang jumlah bucket, lihat Cara menentukan jumlah bucket yang tepat untuk indeks hash (In-Memory OLTP).

Rekomendasi

Dalam kebanyakan kasus, jumlah wadah harus antara 1 dan 2 kali jumlah nilai yang berbeda dalam kunci indeks. Jika kunci indeks berisi banyak nilai duplikat, rata-rata ada lebih dari 10 baris untuk setiap nilai kunci indeks, gunakan indeks nonclustered sebagai gantinya

Anda mungkin tidak selalu dapat memprediksi berapa banyak nilai yang mungkin dimiliki atau akan dimiliki kunci indeks tertentu. Performa harus dapat diterima jika BUCKET_COUNT nilainya tidak lebih dari 5 kali jumlah nilai kunci yang sebenarnya.

Untuk menentukan jumlah kunci indeks unik dalam data yang ada, gunakan kueri yang mirip dengan contoh berikut:

Kunci Primer dan Indeks Unik

Karena indeks kunci utama unik, jumlah nilai berbeda dalam kunci sesuai dengan jumlah baris dalam tabel. Untuk contoh kunci primer pada (SalesOrderID, SalesOrderDetailID) dalam tabel Sales.SalesOrderDetail dalam database AdventureWorks, terbitkan kueri berikut untuk menghitung jumlah nilai kunci utama yang berbeda, yang sesuai dengan jumlah baris dalam tabel:

SELECT COUNT(*) AS [row count]   
FROM Sales.SalesOrderDetail

Kueri ini memperlihatkan jumlah baris 121.317. Gunakan jumlah bucket 240.000 jika jumlah baris tidak akan berubah secara signifikan. Gunakan jumlah wadah sebanyak 480.000 jika jumlah pesanan penjualan dalam tabel diperkirakan akan menjadi empat kali lipat.

Indeks Non-Unik

Untuk indeks lain, misalnya indeks multi-kolom pada (SpecialOfferID, ProductID), terbitkan kueri berikut untuk menentukan jumlah nilai kunci indeks unik:

SELECT COUNT(*) AS [SpecialOfferID_ProductID index key count]  
FROM   
   (SELECT DISTINCT SpecialOfferID, ProductID   
    FROM Sales.SalesOrderDetail) t

Kueri ini mengembalikan jumlah kunci indeks untuk (SpecialOfferID, ProductID) sebesar 484, menunjukkan bahwa indeks non-kluster harus digunakan daripada indeks hash non-kluster.

Menentukan Jumlah Duplikat

Untuk menentukan jumlah rata-rata nilai duplikat untuk nilai kunci indeks, bagi jumlah total baris dengan jumlah kunci indeks unik.

Untuk contoh indeks pada (SpecialOfferID, ProductID), ini mengarah ke 121317 / 484 = 251. Ini berarti nilai kunci indeks memiliki rata-rata 251, dan dengan demikian ini harus menjadi indeks non-kluster.

Pemecahan Masalah Jumlah Bucket

Untuk memecahkan masalah jumlah wadah dalam tabel yang dioptimalkan memori, gunakan sys.dm_db_xtp_hash_index_stats (Transact-SQL) untuk mendapatkan statistik tentang wadah kosong dan panjang rantai baris. Kueri berikut dapat digunakan untuk mendapatkan statistik tentang semua indeks hash dalam database saat ini. Kueri bisa memakan waktu beberapa menit untuk dijalankan jika ada tabel besar dalam database.

SELECT   
   object_name(hs.object_id) AS 'object name',   
   i.name as 'index name',   
   hs.total_bucket_count,  
   hs.empty_bucket_count,  
   floor((cast(empty_bucket_count as float)/total_bucket_count) * 100) AS 'empty_bucket_percent',  
   hs.avg_chain_length,   
   hs.max_chain_length  
FROM sys.dm_db_xtp_hash_index_stats AS hs   
   JOIN sys.indexes AS i   
   ON hs.object_id=i.object_id AND hs.index_id=i.index_id

Dua indikator utama kesehatan indeks hash adalah:

empty_bucket_percent
empty_bucket_percent menunjukkan jumlah wadah kosong dalam indeks hash.

Jika empty_bucket_percent kurang dari 10 persen, jumlah wadah kemungkinan terlalu rendah. Idealnya, empty_bucket_percent harus 33 persen atau lebih besar. Jika jumlah wadah cocok dengan jumlah nilai kunci indeks, sekitar 1/3 dari wadah tersebut kosong lantaran distribusi hash.

rata-rata_panjang_rantai
avg_chain_length menunjukkan panjang rata-rata rantai baris dalam bucket hash.

Jika avg_chain_length lebih besar dari 10 dan empty_bucket_percent lebih besar dari 10 persen, kemungkinan ada banyak nilai kunci indeks duplikat dan indeks nonclustered akan lebih tepat. Panjang rantai rata-rata 1 sangat ideal.

Ada dua faktor yang berdampak pada panjang rantai:

  1. Duplikat; semua baris duplikat adalah bagian dari rantai yang sama dalam indeks hash.

  2. Beberapa nilai kunci dipetakan ke wadah yang sama. Semakin sedikit jumlah wadah, semakin banyak wadah yang akan memiliki beberapa nilai yang dipetakan ke dalamnya.

Sebagai contoh, pertimbangkan tabel dan skrip berikut untuk menyisipkan baris sampel dalam tabel:

CREATE TABLE [Sales].[SalesOrderHeader_test]  
(  
   [SalesOrderID] [uniqueidentifier] NOT NULL DEFAULT (newid()),  
   [OrderSequence] int NOT NULL,  
   [OrderDate] [datetime2](7) NOT NULL,  
   [Status] [tinyint] NOT NULL,  
  
PRIMARY KEY NONCLUSTERED HASH ([SalesOrderID]) WITH ( BUCKET_COUNT = 262144 ),  
INDEX IX_OrderSequence HASH (OrderSequence) WITH ( BUCKET_COUNT = 20000),  
INDEX IX_Status HASH ([Status]) WITH ( BUCKET_COUNT = 8),  
INDEX IX_OrderDate NONCLUSTERED ([OrderDate] ASC),  
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )  
GO  
  
DECLARE @i int = 0  
BEGIN TRAN  
WHILE @i < 262144  
BEGIN  
   INSERT Sales.SalesOrderHeader_test (OrderSequence, OrderDate, [Status]) VALUES (@i, sysdatetime(), @i % 8)  
   SET @i += 1  
END  
COMMIT  
GO

Skrip menyisipkan 262.144 baris dalam tabel. Ini menyisipkan nilai unik ke dalam indeks kunci utama dan di IX_OrderSequence. Ini menyisipkan banyak nilai duplikat dalam indeks IX_Status: skrip hanya menghasilkan 8 nilai yang berbeda.

Output kueri pemecahan masalah BUCKET_COUNT adalah sebagai berikut:

nama indeks total_bucket_count jumlah_ember_kosong persen_bucket_kosong panjang_rantai_rata-rata panjang_rantai_maksimal
IX_Status 8 4 50 65536 65536
IX_OrderSequence 32768 13 0 8 26
PK_SalesOrd_B14003C3F8FB3364 262144 96319 36 1 8

Pertimbangkan tiga indeks hash pada tabel ini:

  • IX_Status: 50 persen ember sudah kosong, ini merupakan hal yang baik. Namun, panjang rantai rata-rata sangat tinggi (65.536). Ini menunjukkan sejumlah besar nilai duplikat. Oleh karena itu, menggunakan indeks hash non-kluster tidak sesuai dalam kasus ini. Indeks non-kluster harus digunakan sebagai gantinya.

  • IX_OrderSequence: 0 persen wadah kosong, yang terlalu rendah. Selain itu, panjang rantai rata-rata adalah 8. Karena nilai dalam indeks ini unik, ini berarti rata-rata 8 nilai dipetakan ke setiap wadah. Jumlah wadah harus ditingkatkan. Karena kunci indeks memiliki 262.144 nilai unik, jumlah wadah harus setidaknya 262.144. Jika pertumbuhan di masa depan diharapkan, jumlahnya harus lebih tinggi.

  • Indeks kunci primer (PK__SalesOrder...): 36 persen dari wadah-wadah yang kosong, yang merupakan hal yang baik. Selain itu panjang rantai rata-rata adalah 1, yang juga baik. Tidak ada perubahan yang diperlukan.

Untuk informasi selengkapnya tentang pemecahan masalah dengan indeks hash yang dioptimalkan memori Anda, lihat Memecahkan Masalah Performa Umum dengan Indeks Hash Memory-Optimized.

Pertimbangan Terperinci untuk Pengoptimalan Lebih Lanjut

Bagian ini menguraikan pertimbangan lebih lanjut untuk mengoptimalkan jumlah wadah.

Untuk mencapai performa terbaik untuk indeks hash, seimbangkan jumlah memori yang dialokasikan ke tabel hash dan jumlah nilai yang berbeda dalam kunci indeks. Ada juga keseimbangan antara performa pencarian titik dan pemindaian tabel:

  • Semakin tinggi nilai jumlah wadah, semakin banyak wadah kosong yang ada dalam indeks. Ini berdampak pada penggunaan memori (8 byte per wadah) dan performa pemindaian tabel, karena setiap wadah dipindai sebagai bagian dari pemindaian tabel.

  • Semakin rendah jumlah wadah, semakin banyak nilai yang ditetapkan ke satu wadah. Ini mengurangi performa untuk pencarian titik dan operasi sisipan, karena SQL Server mungkin perlu menelusuri beberapa nilai dalam satu bucket untuk menemukan nilai yang ditentukan oleh predikat pencarian.

Jika jumlah wadah jauh lebih rendah daripada jumlah kunci indeks unik, banyak nilai akan dipetakan ke setiap wadah. Ini menurunkan performa sebagian besar operasi DML, terutama pencarian titik (pencarian kunci indeks individual) dan operasi penyisipan. Misalnya, Anda mungkin melihat performa kueri SELECT yang buruk serta operasi perbarui dan hapus dengan predikat kesetaraan yang cocok dengan kolom kunci indeks dalam klausa WHERE. Jumlah wadah yang rendah juga akan memengaruhi waktu pemulihan database, karena indeks dibuat ulang pada startup database.

Nilai Kunci Indeks Duplikat

Nilai duplikat dapat memperbesar dampak kinerja dari tabrakan hash. Ini biasanya tidak menjadi masalah jika setiap kunci indeks memiliki jumlah duplikat yang rendah. Tetapi ini bisa menjadi masalah jika perbedaan antara jumlah kunci indeks unik dan jumlah baris dalam tabel menjadi sangat besar.

Semua baris dengan kunci indeks yang sama akan masuk ke rantai duplikat yang sama. Jika beberapa kunci indeks berada dalam wadah yang sama karena tabrakan hash, pemindai indeks selalu perlu memindai rantai duplikat penuh untuk nilai pertama sebelum mereka dapat menemukan baris pertama yang sesuai dengan nilai kedua. Kunci duplikat juga mempersulit pengumpulan sampah untuk menemukan baris. Misalnya, jika ada 1.000 duplikat untuk kunci apa pun dan salah satu baris dihapus, sistem pengumpul sampah perlu memindai rantai 1.000 duplikat untuk melepaskan tautan baris dari indeks. Ini benar meskipun kueri yang menemukan operasi penghapusan menggunakan indeks yang lebih efisien (indeks kunci utama) untuk menentukan baris, karena pengumpul sampah perlu melepaskan tautan dari setiap indeks

Untuk indeks hash, ada dua cara untuk mengurangi pekerjaan yang disebabkan oleh nilai kunci indeks duplikat:

  • Gunakan indeks nonclustered sebagai gantinya. Anda dapat mengurangi duplikat dengan menambahkan kolom ke kunci indeks tanpa memerlukan perubahan apa pun pada aplikasi.

  • Tentukan jumlah wadah yang sangat tinggi untuk indeks. Misalnya, 20 hingga 100 kali jumlah kunci indeks unik. Ini akan mengurangi tabrakan hash.

Tabel Kecil

Untuk tabel yang lebih kecil, pemanfaatan memori biasanya tidak menjadi perhatian, karena ukuran indeks akan kecil dibandingkan dengan ukuran database secara keseluruhan.

Anda sekarang harus membuat pilihan berdasarkan jenis performa yang Anda inginkan:

  • Jika operasi yang bersifat kritis bagi performa pada indeks sebagian besar adalah pencarian titik dan/atau operasi penyisipan, jumlah bucket yang lebih tinggi akan lebih tepat untuk mengurangi kemungkinan benturan hash. Tiga kali jumlah baris atau bahkan lebih akan menjadi opsi terbaik.

  • Jika pemindaian indeks penuh adalah operasi penting performa yang dominan, gunakan jumlah bucket yang mendekati jumlah aktual nilai kunci indeks.

Tabel Besar

Untuk tabel besar, pemanfaatan memori bisa menjadi perhatian. Misalnya, dengan tabel yang memiliki 250 juta baris dan 4 indeks hash, masing-masing dengan jumlah bucket satu miliar, overhead untuk tabel hash adalah 4 indeks * 1 miliar bucket * 8 byte = 32 GB penggunaan memori. Saat memilih jumlah wadah sebanyak 250 juta pada setiap indeks, total overhead untuk tabel hash akan menjadi 8 gigabyte. Perhatikan bahwa ini di samping penggunaan memori sebesar 8 byte yang ditambahkan setiap indeks ke setiap baris individu, yang totalnya adalah 8 gigabyte dalam skenario ini (4 indeks * 8 byte * 250 juta baris).

Pemindaian tabel penuh biasanya tidak berada di jalur kritis performa untuk beban kerja OLTP. Oleh karena itu, pilihannya adalah antara pemanfaatan memori versus performa pencarian titik dan operasi sisipan:

  • Jika pemanfaatan memori menjadi perhatian, pilih jumlah wadah yang dekat dengan jumlah nilai kunci indeks. Jumlah wadah tidak boleh jauh lebih rendah dari jumlah nilai kunci indeks, karena ini berdampak pada sebagian besar operasi DML serta waktu yang diperlukan untuk memulihkan database setelah server dimulai ulang.

  • Saat mengoptimalkan performa untuk pencarian titik, jumlah wadah yang lebih tinggi dua atau bahkan tiga kali jumlah nilai indeks unik akan sesuai. Jumlah wadah yang lebih besar akan menyebabkan lebih banyak pemanfaatan memori dan meningkatkan waktu yang diperlukan untuk melakukan pemindaian indeks secara menyeluruh.

Lihat Juga

Indeks pada Tabel Memory-Optimized

  • Last updated on