Memperkirakan Persyaratan Memori untuk Tabel yang Dioptimalkan Memori

Artikel
05/30/2023

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

Tabel yang dioptimalkan memori mengharuskan memori yang memadai ada untuk menyimpan semua baris dan indeks dalam memori. Karena memori adalah sumber daya terbatas, penting agar Anda memahami dan mengelola penggunaan memori pada sistem Anda. Topik di bagian ini mencakup penggunaan memori umum dan skenario manajemen.

Baik Anda membuat tabel baru yang dioptimalkan memori atau memigrasikan tabel berbasis disk yang ada ke tabel yang dioptimalkan memori OLTP Dalam Memori, penting untuk memiliki perkiraan yang wajar dari setiap kebutuhan memori tabel sehingga Anda dapat memprovisikan server dengan memori yang memadai. Bagian ini menjelaskan cara memperkirakan jumlah memori yang Anda butuhkan untuk menyimpan data untuk tabel yang dioptimalkan memori.

Jika Anda merenungkan migrasi dari tabel berbasis disk ke tabel yang dioptimalkan memori, sebelum Anda melanjutkan dalam topik ini, lihat topik Menentukan apakah Tabel atau Prosedur Tersimpan Harus Di-Port ke OLTP Dalam Memori untuk panduan tentang tabel mana yang terbaik untuk dimigrasikan. Semua topik di bawah Migrasi ke OLTP Dalam Memori memberikan panduan tentang migrasi dari tabel berbasis disk ke memori yang dioptimalkan.

Panduan Dasar untuk Memperkirakan Persyaratan Memori

Dimulai dengan SQL Server 2016 (13.x), tidak ada batasan pada ukuran tabel yang dioptimalkan memori, meskipun tabel harus pas dalam memori. Di SQL Server 2014 (12.x) ukuran data yang didukung adalah 256 GB untuk tabel SCHEMA_AND_DATA.

Ukuran tabel yang dioptimalkan memori sesuai dengan ukuran data ditambah beberapa overhead untuk header baris. Saat memigrasikan tabel berbasis disk ke memori yang dioptimalkan, ukuran tabel yang dioptimalkan memori akan kira-kira sesuai dengan ukuran indeks berkluster atau tumpukan tabel berbasis disk asli.

Indeks pada tabel yang dioptimalkan memori cenderung lebih kecil dari indeks nonclustered pada tabel berbasis disk. Ukuran indeks non-kluster berada dalam urutan [primary key size] * [row count]. Ukuran indeks hash adalah [bucket count] * 8 bytes.

Ketika ada beban kerja aktif, memori tambahan diperlukan untuk memperhitungkan penerapan versi baris dan berbagai operasi. Berapa banyak memori yang diperlukan dalam praktiknya tergantung pada beban kerja, tetapi untuk menjadi aman, rekomendasinya adalah memulai dengan dua kali ukuran tabel dan indeks yang dioptimalkan memori yang diharapkan, dan mengamati apa persyaratan memori dalam praktiknya. Overhead untuk penerapan versi baris selalu tergantung pada karakteristik beban kerja - terutama transaksi yang berjalan lama meningkatkan overhead. Untuk sebagian besar beban kerja yang menggunakan database yang lebih besar (misalnya, >100 GB), overhead cenderung terbatas (25% atau kurang).

Komputasi Persyaratan Memori Terperinci

Contoh tabel yang dioptimalkan memori

Pertimbangkan skema tabel yang dioptimalkan memori berikut:

CREATE TABLE t_hk
(  
  col1 int NOT NULL  PRIMARY KEY NONCLUSTERED,  

  col2 int NOT NULL  INDEX t1c2_index   
      HASH WITH (bucket_count = 5000000),  

  col3 int NOT NULL  INDEX t1c3_index   
      HASH WITH (bucket_count = 5000000),  

  col4 int NOT NULL  INDEX t1c4_index   
      HASH WITH (bucket_count = 5000000),  

  col5 int NOT NULL  INDEX t1c5_index NONCLUSTERED,  

  col6 char (50) NOT NULL,  
  col7 char (50) NOT NULL,   
  col8 char (30) NOT NULL,   
  col9 char (50) NOT NULL  

)   WITH (memory_optimized = on)  ;
GO

Menggunakan skema ini kita akan menentukan memori minimum yang diperlukan untuk tabel yang dioptimalkan memori ini.

Memori untuk tabel

Baris tabel yang dioptimalkan memori terdiri dari tiga bagian:

Stempel waktu
Header baris/tanda waktu = 24 byte.
Penunjuk indeks
Untuk setiap indeks hash dalam tabel, setiap baris memiliki penunjuk alamat 8 byte ke baris berikutnya dalam indeks. Karena ada empat indeks, setiap baris akan mengalokasikan 32 byte untuk penunjuk indeks (penunjuk 8 byte untuk setiap indeks).
Data
Ukuran bagian data baris ditentukan dengan menjumlahkan ukuran jenis untuk setiap kolom data. Dalam tabel kami, kami memiliki lima bilangan bulat 4-byte, tiga kolom karakter 50 byte, dan satu kolom karakter 30 byte. Oleh karena itu bagian data dari setiap baris adalah 4 + 4 + 4 + 4 + 4 + 50 + 50 + 30 + 50 atau 200 byte.

Berikut ini adalah komputasi ukuran untuk 5.000.000 (5 juta) baris dalam tabel yang dioptimalkan memori. Total memori yang digunakan oleh baris data diperkirakan sebagai berikut:

Memori untuk baris tabel

Dari perhitungan di atas, ukuran setiap baris dalam tabel yang dioptimalkan memori adalah 24 + 32 + 200, atau 256 byte. Karena kita memiliki 5 juta baris, tabel akan mengonsumsi 5.000.000 * 256 byte, atau 1.280.000.000 byte - sekitar 1,28 GB.

Memori untuk indeks

Memori untuk setiap indeks hash

Setiap indeks hash adalah array hash dari penunjuk alamat 8-byte. Ukuran array paling baik ditentukan oleh jumlah nilai indeks unik untuk indeks tersebut - misalnya, jumlah nilai Col2 unik adalah titik awal yang baik untuk ukuran array untuk t1c2_index. Array hash yang terlalu besar membuang memori. Array hash yang terlalu kecil memperlambat performa karena akan ada terlalu banyak tabrakan berdasarkan nilai indeks yang hash ke indeks yang sama.

Indeks hash mencapai pencarian kesetaraan yang sangat cepat seperti:

SELECT * FROM t_hk  
   WHERE Col2 = 3;

Indeks nonclustered lebih cepat untuk pencarian rentang seperti:

SELECT * FROM t_hk  
   WHERE Col2 >= 3;

Jika Anda memigrasikan tabel berbasis disk, Anda bisa menggunakan yang berikut ini untuk menentukan jumlah nilai unik untuk indeks t1c2_index.

SELECT COUNT(DISTINCT [Col2])  
  FROM t_hk;

Jika Anda membuat tabel baru, Anda harus memperkirakan ukuran array atau mengumpulkan data dari pengujian anda sebelum penyebaran.

Untuk informasi tentang cara kerja indeks hash dalam tabel yang dioptimalkan memori OLTP Dalam Memori, lihat Indeks Hash.

Mengatur ukuran array indeks hash

Ukuran array hash diatur oleh (bucket_count= value) di mana value adalah nilai bilangan bulat yang lebih besar dari nol. Jika value bukan kekuatan 2, bucket_count aktual dibulatkan ke atas ke daya terdekat berikutnya 2. Dalam tabel contoh kami, (bucket_count = 5000000), karena 5.000.000 bukan kekuatan 2, jumlah wadah aktual dibulatkan hingga 8.388.608 (2^23). Anda harus menggunakan angka ini, bukan 5.000.000 saat menghitung memori yang diperlukan oleh array hash.

Dengan demikian, dalam contoh kita, memori yang diperlukan untuk setiap array hash adalah:

8.388.608 * 8 = 2^23 * 8 = 2^23 * 2^3 = 2^26 = 67.108.864 atau sekitar 64 MB.

Karena kita memiliki tiga indeks hash, memori yang diperlukan untuk indeks hash adalah 3 * 64 MB = 192 MB.

Memori untuk indeks nonclustered

Indeks nonclustered diimplementasikan sebagai pohon Bw dengan simpul dalam yang berisi nilai indeks dan penunjuk ke simpul berikutnya. Simpul daun berisi nilai indeks dan penunjuk ke baris tabel dalam memori.

Tidak seperti indeks hash, indeks nonclustered tidak memiliki ukuran wadah tetap. Indeks tumbuh dan menyusut secara dinamis dengan data.

Memori yang diperlukan oleh indeks non-kluster dapat dihitung sebagai berikut:

Memori yang dialokasikan untuk node non-daun
Untuk konfigurasi umum, memori yang dialokasikan untuk node non-daun adalah persentase kecil dari keseluruhan memori yang diambil oleh indeks. Ini sangat kecil sehingga dapat diabaikan dengan aman.
Memori untuk simpul daun
Simpul daun memiliki satu baris untuk setiap kunci unik dalam tabel yang menunjuk ke baris data dengan kunci unik tersebut. Jika Anda memiliki beberapa baris dengan kunci yang sama (yaitu, Anda memiliki indeks non-unik berkluster), hanya ada satu baris dalam simpul daun indeks yang menunjuk ke salah satu baris dengan baris lain yang ditautkan satu sama lain. Dengan demikian, total memori yang diperlukan dapat ditempuh dengan:
- memoryForNonClusteredIndex = (pointerSize + sum(keyColumnDataTypeSizes)) * rowsWithUniqueKeys

Indeks nonclustered paling baik saat digunakan untuk pencarian rentang, seperti yang dicontohkan oleh kueri berikut:

SELECT * FROM t_hk  
   WHERE c2 > 5;

Memori untuk penerapan versi baris

Untuk menghindari kunci, OLTP Dalam Memori menggunakan konkurensi optimis saat memperbarui atau menghapus baris. Ini berarti bahwa ketika baris diperbarui, versi lain dari baris dibuat. Selain itu, penghapusan logis - baris yang ada ditandai sebagai dihapus, tetapi tidak segera dihapus. Sistem menyimpan versi baris lama (termasuk baris yang dihapus) tersedia sampai semua transaksi yang mungkin dapat menggunakan versi telah selesai dieksekusi.

Karena mungkin ada lebih banyak baris dalam memori kapan saja menunggu siklus pengumpulan sampah untuk melepaskan memori mereka, Anda harus memiliki memori yang cukup untuk mengakomodasi baris lain ini.

Jumlah baris tambahan dapat diperkirakan dengan menghitung jumlah puncak pembaruan dan penghapusan baris per detik, lalu mengalikannya dengan jumlah detik yang dibutuhkan transaksi terpanjang (minimal 1).

Nilai tersebut kemudian dikalikan dengan ukuran baris untuk mendapatkan jumlah byte yang Anda butuhkan untuk penerapan versi baris.

rowVersions = durationOfLongestTransactionInSeconds * peakNumberOfRowUpdatesOrDeletesPerSecond

Kebutuhan memori untuk baris basi kemudian diperkirakan dengan mengalikan jumlah baris basi dengan ukuran baris tabel yang dioptimalkan memori (Lihat Memori untuk tabel di atas).

memoryForRowVersions = rowVersions * rowSize

Memori untuk variabel tabel

Memori yang digunakan untuk variabel tabel hanya dirilis ketika variabel tabel keluar dari cakupan. Baris yang dihapus, termasuk baris yang dihapus sebagai bagian dari pembaruan, dari variabel tabel tidak tunduk pada pengumpulan sampah. Tidak ada memori yang dirilis sampai variabel tabel keluar dari cakupan.

Variabel tabel yang ditentukan dalam batch SQL besar, dibandingkan dengan cakupan prosedur, yang digunakan dalam banyak transaksi, dapat mengonsumsi banyak memori. Karena mereka bukan sampah yang dikumpulkan, baris yang dihapus dalam variabel tabel dapat mengonsumsi banyak memori dan menurunkan performa karena operasi baca perlu memindai melewati baris yang dihapus.

Memori untuk pertumbuhan

Perhitungan di atas memperkirakan kebutuhan memori Anda untuk tabel karena saat ini ada. Selain memori ini, Anda perlu memperkirakan pertumbuhan tabel dan memberikan memori yang cukup untuk mengakomodasi pertumbuhan tersebut. Misalnya, jika Anda mengantisipasi pertumbuhan 10% maka Anda perlu beberapa hasil dari atas dengan 1,1 untuk mendapatkan total memori yang diperlukan untuk tabel Anda.

Bagikan melalui