Pemecahan Masalah Indeks Hash untuk Tabel yang Dioptimalkan Memori

Artikel
08/21/2023

Berlaku untuk:SQL Server Azure SQL Database Azure SQL Managed Instance

Prasyarat

Informasi konteks penting untuk memahami artikel ini tersedia di:

Angka praktis

Saat membuat indeks hash untuk tabel yang dioptimalkan memori, jumlah wadah perlu ditentukan pada waktu pembuatan. Dalam kebanyakan kasus, jumlah wadah idealnya adalah antara 1 dan 2 kali jumlah nilai yang berbeda dalam kunci indeks.

Namun, bahkan jika BUCKET_COUNT berada di bawah atau di atas rentang pilihan, performa indeks hash Anda cenderung dapat ditoleransi atau diterima. Minimal, pertimbangkan untuk memberikan indeks hash Anda BUCKET_COUNT kira-kira sama dengan jumlah baris yang Anda prediksi tabel yang dioptimalkan memori Anda akan tumbuh.
Misalkan tabel Anda yang berkembang memiliki 2.000.000 baris, tetapi prediksinya adalah akan tumbuh 10 kali lipat menjadi 20.000.000 baris. Mulai dengan jumlah wadah yang 10 kali jumlah baris dalam tabel. Ini memberi Anda ruang untuk peningkatan jumlah baris.

Idealnya Anda akan meningkatkan jumlah wadah ketika kuantitas baris mencapai jumlah wadah awal.
Bahkan jika kuantitas baris tumbuh hingga lima kali lebih besar dari jumlah wadah, performanya masih baik di sebagian besar situasi.

Misalkan indeks hash memiliki 10.000.000 nilai kunci yang berbeda.

Jumlah bucket 2.000.000 akan serendah yang bisa Anda terima. Tingkat penurunan performa dapat ditoleransi.

Terlalu banyak nilai duplikat dalam indeks?

Jika nilai terindeks hash memiliki tingkat duplikat yang tinggi, wadah hash akan mengalami rantai yang lebih panjang.

Asumsikan Anda memiliki tabel SupportEvent yang sama dari blok kode sintaks T-SQL sebelumnya. Kode T-SQL berikut menunjukkan bagaimana Anda dapat menemukan dan menampilkan rasio semua nilai terhadap nilai unik:

-- Calculate ratio of:  Rows / Unique_Values.  
DECLARE @allValues float(8) = 0.0, @uniqueVals float(8) = 0.0;  
  
SELECT @allValues = Count(*) FROM SupportEvent;  
  
SELECT @uniqueVals = Count(*) FROM  
  (SELECT DISTINCT SupportEngineerName  
      FROM SupportEvent) as d;  
  
    -- If (All / Unique) >= 10.0, use a nonclustered index, not a hash.   
SELECT Cast((@allValues / @uniqueVals) as float) as [All_divby_Unique];  
go

Rasio 10,0 atau lebih tinggi berarti hash akan menjadi jenis indeks yang buruk. Pertimbangkan untuk menggunakan indeks nonclustered sebagai gantinya,

Pemecahan masalah jumlah wadah indeks hash

Bagian ini membahas cara memecahkan masalah jumlah wadah untuk indeks hash Anda.

Memantau statistik untuk rantai dan wadah kosong

Anda dapat memantau kesehatan statistik indeks hash Anda dengan menjalankan T-SQL SELECT berikut. SELECT menggunakan tampilan manajemen data (DMV) bernama sys.dm_db_xtp_hash_index_stats.

SELECT  
  QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(OBJECT_NAME(h.object_id)) as [table],   
  i.name                   as [index],   
  h.total_bucket_count,  
  h.empty_bucket_count,  
    
  FLOOR((  
    CAST(h.empty_bucket_count as float) /  
      h.total_bucket_count) * 100)  
                            as [empty_bucket_percent],  
  h.avg_chain_length,   
  h.max_chain_length  
FROM  
        sys.dm_db_xtp_hash_index_stats  as h   
  JOIN sys.indexes                     as i  
          ON h.object_id = i.object_id  
          AND h.index_id  = i.index_id  
JOIN sys.memory_optimized_tables_internal_attributes ia ON h.xtp_object_id=ia.xtp_object_id
JOIN sys.tables t on h.object_id=t.object_id
WHERE ia.type=1
ORDER BY [table], [index];

Bandingkan hasil SELECT dengan panduan statistik berikut:

Wadah kosong:
- 33% adalah nilai target yang baik, tetapi persentase yang lebih besar (bahkan 90%) biasanya baik-baik saja.
- Ketika jumlah wadah sama dengan jumlah nilai kunci yang berbeda, sekitar 33% dari wadah kosong.
- Nilai di bawah 10% terlalu rendah.
Rantai dalam wadah:
- Panjang rantai rata-rata 1 ideal jika tidak ada nilai kunci indeks duplikat. Panjang rantai hingga 10 biasanya dapat diterima.
- Jika panjang rantai rata-rata lebih besar dari 10, dan persentase wadah kosong lebih besar dari 10%, data memiliki begitu banyak duplikat sehingga indeks hash mungkin bukan jenis yang paling tepat.

Demonstrasi rantai dan wadah kosong

Blok kode T-SQL berikut memberi Anda cara mudah untuk menguji SELECT * FROM sys.dm_db_xtp_hash_index_stats;. Blok kode selesai dalam 1 menit. Berikut adalah fase blok kode berikut:

Membuat tabel yang dioptimalkan memori yang memiliki beberapa indeks hash.
Mengisi tabel dengan ribuan baris.
a. Operator modulo digunakan untuk mengonfigurasi laju nilai duplikat di kolom StatusCode.
b. Perulangan menyisipkan 262.144 baris dalam waktu sekitar 1 menit.
PRINTs pesan yang meminta Anda untuk menjalankan SELECT sebelumnya dari sys.dm_db_xtp_hash_index_stats.

DROP TABLE IF EXISTS SalesOrder_Mem;  
go  
  
  
CREATE TABLE SalesOrder_Mem  
(  
  SalesOrderId   uniqueidentifier  NOT NULL  DEFAULT newid(),  
  OrderSequence  int               NOT NULL,  
  OrderDate      datetime2(3)      NOT NULL,  
  StatusCode     tinyint           NOT NULL,  
  
  PRIMARY KEY NONCLUSTERED  
      HASH (SalesOrderId)  WITH (BUCKET_COUNT = 262144),  
  
  INDEX ix_OrderSequence  
      HASH (OrderSequence) WITH (BUCKET_COUNT = 20000),  
  
  INDEX ix_StatusCode  
      HASH (StatusCode)    WITH (BUCKET_COUNT = 8),  
  
  INDEX ix_OrderDate       NONCLUSTERED (OrderDate DESC)  
)  
  WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)  
go  
  
--------------------  
  
SET NOCOUNT ON;  
  
-- Same as PK bucket_count.  68 seconds to complete.  
DECLARE @i int = 262144;  
  
BEGIN TRANSACTION;  
  
WHILE @i > 0  
BEGIN  
  
  INSERT SalesOrder_Mem  
      (OrderSequence, OrderDate, StatusCode)  
    Values  
      (@i, GetUtcDate(), @i % 8);  -- Modulo technique.  
  
  SET @i -= 1;  
END  
COMMIT TRANSACTION;  
  
PRINT 'Next, you should query:  sys.dm_db_xtp_hash_index_stats .';  
go

Perulangan INSERT sebelumnya melakukan hal berikut:

Menyisipkan nilai unik untuk indeks kunci utama, dan untuk ix_OrderSequence.
Menyisipkan beberapa ratus ribu baris yang hanya mewakili delapan nilai berbeda untuk StatusCode. Oleh karena itu ada tingkat duplikasi nilai yang tinggi dalam ix_StatusCode indeks.

Untuk pemecahan masalah saat jumlah wadah tidak optimal, periksa output SELECT berikut dari sys.dm_db_xtp_hash_index_stats. Untuk hasil ini, kami menambahkan WHERE Object_Name(h.object_id) = 'SalesOrder_Mem' ke SELECT yang disalin dari bagian D.1.

Hasil kami SELECT ditampilkan setelah kode, dibagi secara buatan menjadi dua tabel hasil yang lebih sempit untuk tampilan yang lebih baik.

Berikut adalah hasil untuk jumlah wadah.

IndexName	total_bucket_count	empty_bucket_count	EmptyBucketPercent
ix_OrderSequence	32768	13	0
ix_StatusCode	8	4	50
PK_SalesOrd_B14003...	262144	96525	36

Selanjutnya adalah hasil untuk panjang rantai.

IndexName	avg_chain_length	max_chain_length
ix_OrderSequence	8	26
ix_StatusCode	65536	65536
PK_SalesOrd_B14003...	1	8

Mari kita menginterpretasikan tabel hasil sebelumnya untuk tiga indeks hash:

ix_StatusCode:

50% dari ember kosong, yang bagus.
Namun, panjang rantai rata-rata sangat tinggi pada 65536.
- Ini menunjukkan tingkat nilai duplikat yang tinggi.
- Oleh karena itu, menggunakan indeks hash tidak sesuai dalam hal ini. Indeks non-kluster harus digunakan sebagai gantinya.

ix_OrderSequence:

0% dari ember kosong, yang terlalu rendah.
Panjang rantai rata-rata adalah 8, meskipun semua nilai dalam indeks ini unik.
- Oleh karena itu jumlah wadah harus ditingkatkan, untuk mengurangi panjang rantai rata-rata lebih dekat ke 2 atau 3.
Karena kunci indeks memiliki nilai unik 262144, jumlah wadah harus setidaknya 262144.
- Jika pertumbuhan di masa depan diharapkan, jumlah wadah harus lebih tinggi.

Indeks kunci primer (PK_SalesOrd_...):

36% dari ember kosong, yang bagus.
Panjang rantai rata-rata adalah 1, yang juga baik. Tidak diperlukan perubahan.

Menyeimbangkan trade-off

Beban kerja OLTP berfokus pada baris individual. Pemindaian tabel penuh biasanya tidak berada di jalur kritis performa untuk beban kerja OLTP. Oleh karena itu, trade-off yang harus Anda seimbangkan adalah antara kuantitas pemanfaatan memori versus performa pengujian kesetaraan dan operasi sisipan.

Jika pemanfaatan memori adalah kekhawatiran yang lebih besar:

Pilih jumlah wadah yang dekat dengan jumlah nilai kunci indeks unik.
Jumlah wadah tidak boleh jauh lebih rendah daripada jumlah nilai kunci indeks unik, karena ini berdampak pada sebagian besar operasi DML serta waktu yang diperlukan untuk memulihkan database setelah server dimulai ulang.

Jika performa pengujian kesetaraan adalah kekhawatiran yang lebih besar:

Jumlah wadah yang lebih tinggi, dari dua atau tiga kali jumlah nilai indeks unik, sesuai. Jumlah yang lebih tinggi berarti:
- Pengambilan yang lebih cepat saat mencari satu nilai tertentu.
- Peningkatan pemanfaatan memori.
- Peningkatan waktu yang diperlukan untuk pemindaian penuh indeks hash.

Pembacaan tambahan

Indeks Hash untuk Tabel yang Dioptimalkan Memori
Indeks Nonclustered untuk Tabel yang Dioptimalkan Memori

Bagikan melalui