Pemecahan Masalah Performa Umum dengan Indeks Hash Memory-Optimized

Topik ini akan berfokus pada pemecahan masalah dan mengatasi masalah umum dengan indeks hash.

Pencarian Membutuhkan Himpunan Bagian Kolom Kunci Indeks Hash

Masalah: Indeks hash memerlukan nilai untuk semua kolom kunci indeks untuk menghitung nilai hash, dan menemukan baris yang sesuai dalam tabel hash. Oleh karena itu, jika kueri menyertakan predikat kesetaraan hanya untuk subset kunci indeks dalam klausa WHERE, SQL Server tidak dapat menggunakan pencarian indeks untuk menemukan baris yang sesuai dengan predikat dalam klausa WHERE.

Sebaliknya, indeks yang diurutkan seperti indeks non-kluster berbasis disk dan indeks non-kluster yang dioptimalkan memori mendukung pencarian indeks pada subset kolom kunci indeks, selama mereka adalah kolom utama dalam indeks.

Gejala: Hal ini mengakibatkan penurunan performa, karena SQL Server perlu menjalankan pemindaian tabel penuh daripada pencarian indeks, yang biasanya merupakan operasi yang lebih cepat.

Cara memecahkan masalah: Selain penurunan performa, inspeksi rencana kueri akan menampilkan pemindaian alih-alih pencarian indeks. Jika kueri cukup sederhana, inspeksi teks kueri dan definisi indeks juga akan menunjukkan apakah pencarian memerlukan subset kolom kunci indeks.

Pertimbangkan tabel dan kueri berikut:

CREATE TABLE [dbo].[od]  
(  
     o_id INT NOT NULL,  
     od_id INT NOT NULL,  
     p_id INT NOT NULL,  
     CONSTRAINT PK_od PRIMARY KEY NONCLUSTERED HASH (o_id, od_id) WITH (BUCKET_COUNT = 10000)  
)  
WITH (MEMORY_OPTIMIZED = ON)  
  
 SELECT p_id  
 FROM dbo.od  
 WHERE o_id=1  

Tabel memiliki indeks hash pada dua kolom (o_id, od_id), sementara kueri memiliki predikat kesetaraan pada (o_id). Karena kueri memiliki predikat kesetaraan hanya pada subset kolom kunci indeks, SQL Server tidak dapat melakukan operasi pencarian indeks menggunakan PK_od; sebagai gantinya, SQL Server harus kembali ke pemindaian indeks penuh.

Solusi sementara: Ada sejumlah kemungkinan solusi. Contohnya:

• Buat ulang indeks dengan tipe nonclustered, bukan hash nonclustered. Indeks non-kluster yang dioptimalkan memori diurutkan, dan dengan demikian SQL Server dapat melakukan pencarian indeks pada kolom kunci indeks terkemuka. Definisi kunci primer yang dihasilkan untuk contohnya adalah constraint PK_od primary key nonclustered.

• Ubah kunci indeks saat ini agar sesuai dengan kolom dalam klausa WHERE.

• Tambahkan indeks hash baru yang cocok dengan kolom dalam klausa WHERE kueri. Dalam contoh, definisi tabel yang dihasilkan akan terlihat sebagai berikut:

  CREATE TABLE dbo.od  
   ( o_id INT NOT NULL,  
   od_id INT NOT NULL,  
   p_id INT NOT NULL,  
  
   CONSTRAINT PK_od PRIMARY KEY   
   NONCLUSTERED HASH (o_id,od_id) WITH (BUCKET_COUNT=10000),  
  
   INDEX ix_o_id NONCLUSTERED HASH (o_id) WITH (BUCKET_COUNT=10000)  
  
   ) WITH (MEMORY_OPTIMIZED=ON)  
  

Perhatikan bahwa indeks hash yang dioptimalkan memori tidak berkinerja optimal jika ada banyak baris duplikat untuk nilai kunci indeks tertentu: dalam contoh, jika jumlah nilai unik untuk kolom o_id jauh lebih kecil daripada jumlah baris dalam tabel, tidak akan optimal untuk menambahkan indeks pada (o_id); sebaliknya, mengubah jenis indeks PK_od dari hash ke nonclustered akan menjadi solusi yang lebih baik. Untuk informasi selengkapnya, lihat Menentukan Jumlah Bucket yang Benar untuk Indeks Hash.

Lihat Juga

Indeks pada Tabel Memory-Optimized