Pemecahan Masalah Indeks Hash untuk Tabel yang Dioptimalkan Memori
Berlaku untuk: SQL ServerAzure 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