Indeks pada Tabel yang Dioptimalkan Memori

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Semua tabel yang dioptimalkan memori harus memiliki setidaknya satu indeks, karena merupakan indeks yang menghubungkan baris bersama-sama. Pada tabel yang dioptimalkan memori, setiap indeks juga dioptimalkan memori. Ada beberapa cara di mana indeks pada tabel yang dioptimalkan memori berbeda dari indeks tradisional pada tabel disk-base:

  • Baris data tidak disimpan di halaman, sehingga tidak ada kumpulan halaman atau tingkat, tidak ada partisi atau unit alokasi yang dapat dirujuk untuk mendapatkan semua halaman untuk tabel. Ada konsep halaman indeks untuk salah satu jenis indeks yang tersedia, tetapi disimpan secara berbeda dari indeks untuk tabel berbasis disk. Mereka tidak mengumpulkan jenis fragmentasi tradisional dalam halaman, sehingga mereka tidak memiliki fillfactor.
  • Perubahan yang dilakukan pada indeks pada tabel yang dioptimalkan memori selama manipulasi data tidak pernah ditulis ke disk. Hanya baris data, dan perubahan pada data, yang ditulis ke log transaksi.
  • Indeks yang dioptimalkan memori dibangun kembali ketika database dibawa kembali online.

Semua indeks pada tabel yang dioptimalkan memori dibuat berdasarkan definisi indeks selama pemulihan database.

Indeks harus salah satu dari yang berikut ini:

  • Indeks hash
  • Indeks Nonclustered yang dioptimalkan memori (yang berarti struktur internal default dari pohon B)

Indeks hash dibahas secara lebih rinci dalam Indeks Hash untuk Tabel yang Dioptimalkan Memori.
Indeks nonclustered dibahas secara lebih rinci dalam Indeks Nonclustered untuk Tabel yang Dioptimalkan Memori.
Indeks penyimpan kolom dibahas di artikel lain.

Sintaks untuk indeks yang dioptimalkan memori

Setiap pernyataan CREATE TABLE untuk tabel yang dioptimalkan memori harus menyertakan indeks, baik secara eksplisit melalui INDEKS atau secara implisit melalui KUNCI PRIMAY atau batasan UNIK.

Untuk dideklarasikan dengan DURABILITY default = SCHEMA_AND_DATA, tabel yang dioptimalkan memori harus memiliki kunci primer. Klausul PRIMARY KEY NONCLUSTERED dalam pernyataan CREATE TABLE berikut memenuhi dua persyaratan:

  • Menyediakan indeks untuk memenuhi persyaratan minimum satu indeks dalam pernyataan CREATE TABLE.

  • Menyediakan kunci utama yang diperlukan untuk klausa SCHEMA_AND_DATA.

    CREATE TABLE SupportEvent  
(  
    SupportEventId   int NOT NULL  
        PRIMARY KEY NONCLUSTERED,  
    ...  
)  
    WITH (  
        MEMORY_OPTIMIZED = ON,  
        DURABILITY = SCHEMA_AND_DATA);

Catatan

SQL Server 2014 (12.x) dan SQL Server 2016 (13.x) memiliki batas 8 indeks per tabel atau jenis tabel yang dioptimalkan memori. Dimulai dengan SQL Server 2017 (14.x) dan di Azure SQL Database, tidak ada lagi batasan jumlah indeks khusus untuk tabel dan jenis tabel yang dioptimalkan memori.

Sampel kode untuk sintaks

Sub bagian ini berisi blok kode Transact-SQL yang menunjukkan sintaks untuk membuat berbagai indeks pada tabel yang dioptimalkan memori. Kode menunjukkan hal-hal berikut:

  1. Buat tabel yang dioptimalkan memori.

  2. Gunakan pernyataan ALTER TABLE untuk menambahkan dua indeks.

  3. SISIPKAN beberapa baris data.

    DROP TABLE IF EXISTS SupportEvent;  
go  

CREATE TABLE SupportEvent  
(  
    SupportEventId   int               not null   identity(1,1)  
    PRIMARY KEY NONCLUSTERED,  

    StartDateTime        datetime2     not null,  
    CustomerName         nvarchar(16)  not null,  
    SupportEngineerName  nvarchar(16)      null,  
    Priority             int               null,  
    Description          nvarchar(64)      null  
)  
    WITH (  
    MEMORY_OPTIMIZED = ON,  
    DURABILITY = SCHEMA_AND_DATA);  
go  

    --------------------  

ALTER TABLE SupportEvent  
    ADD CONSTRAINT constraintUnique_SDT_CN  
    UNIQUE NONCLUSTERED (StartDateTime DESC, CustomerName);  
go  

ALTER TABLE SupportEvent  
    ADD INDEX idx_hash_SupportEngineerName  
    HASH (SupportEngineerName) WITH (BUCKET_COUNT = 64);  -- Nonunique.  
go  

    --------------------  

INSERT INTO SupportEvent  
    (StartDateTime, CustomerName, SupportEngineerName, Priority, Description)  
    VALUES  
    ('2016-02-23 13:40:41:123', 'Abby', 'Zeke', 2, 'Display problem.'     ),  
    ('2016-02-24 13:40:41:323', 'Ben' , null  , 1, 'Cannot find help.'    ),  
    ('2016-02-25 13:40:41:523', 'Carl', 'Liz' , 2, 'Button is gray.'      ),  
    ('2016-02-26 13:40:41:723', 'Dave', 'Zeke', 2, 'Cannot unhide column.');  
go

Nilai kunci indeks duplikat

Nilai duplikat untuk kunci indeks dapat mengurangi performa tabel yang dioptimalkan memori. Duplikat bagi sistem untuk melintasi rantai entri untuk sebagian besar operasi baca dan tulis indeks. Ketika rantai entri duplikat melebihi 100 entri, penurunan performa dapat menjadi terukur.

Nilai hash duplikat

Masalah ini lebih terlihat dalam kasus indeks hash. Indeks hash lebih menderita karena pertimbangan berikut:

  • Biaya per operasi yang lebih rendah untuk indeks hash.
  • Gangguan rantai duplikat besar dengan rantai tabrakan hash.

Untuk mengurangi duplikasi dalam indeks, coba penyesuaian berikut:

  • Gunakan indeks nonclustered.
  • Tambahkan kolom tambahan ke akhir kunci indeks, untuk mengurangi jumlah duplikat.
    • Misalnya, Anda dapat menambahkan kolom yang juga ada di kunci utama.

Untuk informasi selengkapnya tentang tabrakan hash, lihat Indeks Hash untuk Tabel yang Dioptimalkan Memori.

Contoh penyempurnaan

Berikut adalah contoh cara menghindari inefisiensi performa dalam indeks Anda.

Customers Pertimbangkan tabel yang memiliki kunci primer pada CustomerId, dan memiliki indeks pada kolom CustomerCategoryID. Biasanya akan ada banyak pelanggan dalam kategori tertentu. Dengan demikian akan ada banyak nilai duplikat untuk CustomerCategoryID di dalam kunci indeks tertentu.

Dalam skenario ini, praktik terbaik adalah menggunakan indeks non-kluster pada (CustomerCategoryID, CustomerId). Indeks ini dapat digunakan untuk kueri yang menggunakan predikat yang melibatkan CustomerCategoryID, namun kunci indeks tidak berisi duplikasi. Oleh karena itu, tidak ada inefisiensi dalam pemeliharaan indeks yang disebabkan oleh nilai CustomerCategoryID duplikat, atau oleh kolom tambahan dalam indeks.

Kueri berikut menunjukkan jumlah rata-rata nilai kunci indeks duplikat untuk indeks dalam CustomerCategoryID tabel Sales.Customers, dalam database sampel WideWorldImporters.

SELECT AVG(row_count) FROM
    (SELECT COUNT(*) AS row_count 
	    FROM Sales.Customers
	    GROUP BY CustomerCategoryID) a

Untuk mengevaluasi jumlah rata-rata duplikat kunci indeks untuk tabel dan indeks Anda sendiri, ganti Sales.Customers dengan nama tabel Anda, dan ganti CustomerCategoryID dengan daftar kolom kunci indeks.

Membandingkan kapan menggunakan setiap jenis indeks

Sifat kueri khusus Anda menentukan jenis indeks mana yang merupakan pilihan terbaik.

Saat menerapkan tabel yang dioptimalkan memori dalam aplikasi yang ada, rekomendasi umumnya adalah memulai dengan indeks non-kluster, karena kemampuannya lebih mirip dengan kemampuan indeks berkluster dan non-kluster tradisional pada tabel berbasis disk.

Rekomendasi untuk penggunaan indeks nonclustered

Indeks nonclustered lebih disukai daripada indeks hash ketika:

  • Kueri memiliki ORDER BY klausa pada kolom terindeks.
  • Kueri di mana hanya kolom di depan indeks multikolom yang diuji.
  • Kueri menguji kolom terindeks dengan menggunakan WHERE klausa dengan:
    • Ketidaksamaan: WHERE StatusCode != 'Done'
    • Pemindaian rentang nilai: WHERE Quantity >= 100

Dalam semua SELECT berikut, indeks nonclustered lebih disukai daripada indeks hash:

SELECT CustomerName, Priority, Description 
FROM SupportEvent  
WHERE StartDateTime > DateAdd(day, -7, GetUtcDate());  

SELECT StartDateTime, CustomerName  
FROM SupportEvent  
ORDER BY StartDateTime DESC; -- ASC would cause a scan.

SELECT CustomerName  
FROM SupportEvent  
WHERE StartDateTime = '2016-02-26';

Rekomendasi untuk penggunaan indeks hash

Indeks hash terutama digunakan untuk pencarian titik dan bukan untuk pemindaian rentang.

Indeks hash lebih disukai daripada indeks nonclustered saat kueri menggunakan predikat kesetaraan, dan WHERE klausa memetakan ke semua kolom kunci indeks, seperti dalam contoh berikut:

SELECT CustomerName 
FROM SupportEvent  
WHERE SupportEngineerName = 'Liz';

Indeks multi-kolom

Indeks multi-kolom bisa menjadi indeks non-klusster atau indeks hash. Misalkan kolom indeks adalah col1 dan col2. Mengingat pernyataan berikut SELECT , hanya indeks nonclustered yang akan berguna bagi pengoptimal kueri:

SELECT col1, col3  
FROM MyTable_memop  
WHERE col1 = 'dn';

Indeks hash memerlukan WHERE klausul untuk menentukan uji kesetaraan untuk setiap kolom di kuncinya. Jika tidak, indeks hash tidak berguna untuk pengoptimal kueri.

Tidak ada jenis indeks yang berguna jika WHERE klausul hanya menentukan kolom kedua dalam kunci indeks.

Tabel ringkasan untuk membandingkan skenario penggunaan indeks

Tabel berikut mencantumkan semua operasi yang didukung oleh berbagai jenis indeks. Ya berarti bahwa indeks dapat secara efisien melayani permintaan, dan Tidak berarti bahwa indeks tidak dapat memenuhi permintaan secara efisien.

Operasi Memori dioptimalkan,
Hash		 Memori dioptimalkan,
nonclustered		 Berbasis disk,
(non)berkluster
Pindai Indeks, ambil semua baris tabel. Ya Ya Ya
Pencarian indeks pada predikat kesetaraan (=). Ya
(Kunci penuh diperlukan.)		 Ya Ya
Pencarian indeks pada ketimpangan dan predikat rentang
(>, <, <=, >=, BETWEEN).		 Tanpa
(Menghasilkan pemindaian indeks.)		 Ya 1 Ya
Ambil baris dalam urutan pengurutan yang cocok dengan definisi indeks. Tidak Ya Ya
Ambil baris dalam urutan pengurutan yang cocok dengan kebalikan definisi indeks. Tidak Tidak Ya

1 Untuk indeks Nonclustered yang dioptimalkan memori, kunci lengkap tidak diperlukan untuk melakukan pencarian indeks.

Manajemen indeks dan statistik otomatis

Manfaatkan solusi seperti Defrag Indeks Adaptif untuk mengelola defragmentasi indeks dan pembaruan statistik secara otomatis untuk satu atau beberapa database. Prosedur ini secara otomatis memilih apakah akan membangun kembali atau mengatur ulang indeks sesuai dengan tingkat fragmentasinya, di antara parameter lainnya, dan memperbarui statistik dengan ambang batas linier.

Lihat Juga

Panduan Desain Indeks SQL Server
Indeks Hash untuk Tabel yang Dioptimalkan Memori
Indeks Nonclustered untuk Tabel yang Dioptimalkan Memori
Defrag Indeks Adaptif