Pengantar Tabel yang Dioptimalkan Memori

Artikel
08/21/2023

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

Tabel yang dioptimalkan memori dibuat menggunakan CREATE TABLE (Transact-SQL).

Tabel yang dioptimalkan memori sepenuhnya tahan lama secara default, dan, seperti transaksi pada tabel berbasis disk (tradisional), transaksi pada tabel yang dioptimalkan memori sepenuhnya atom, konsisten, terisolasi, dan tahan lama (ACID). Tabel yang dioptimalkan memori dan prosedur tersimpan yang dikompilasi secara asli hanya mendukung subset fitur Transact-SQL.

Dimulai dengan SQL Server 2016, dan di Azure SQL Database, tidak ada batasan untuk kolase atau halaman kode yang khusus untuk OLTP Dalam Memori.

Penyimpanan utama untuk tabel yang dioptimalkan memori adalah memori utama. Baris dalam tabel dibaca dari dan ditulis ke memori. Salinan kedua data tabel dipertahankan pada disk, tetapi hanya untuk tujuan durabilitas. Lihat Membuat dan Mengelola Penyimpanan untuk Objek yang Dioptimalkan Memori untuk informasi selengkapnya tentang tabel yang tahan lama. Data dalam tabel yang dioptimalkan memori hanya dibaca dari disk selama pemulihan database (misalnya setelah server dimulai ulang).

Untuk perolehan performa yang lebih besar, OLTP Dalam Memori mendukung tabel tahan lama dengan durabilitas transaksi tertunda. Transaksi tahan lama yang tertunda disimpan ke disk segera setelah transaksi dilakukan dan kontrol dikembalikan ke klien. Sebagai imbalan atas peningkatan performa, transaksi yang diterapkan yang belum disimpan ke disk hilang dalam crash server atau failover.

Selain tabel default yang dioptimalkan memori tahan lama, SQL Server juga mendukung tabel yang dioptimalkan memori yang tidak tahan lama, yang tidak dicatat dan datanya tidak bertahan pada disk. Ini berarti bahwa transaksi pada tabel ini tidak memerlukan IO disk apa pun, tetapi data tidak akan dipulihkan jika ada crash server atau failover.

OLTP Dalam Memori terintegrasi dengan SQL Server untuk memberikan pengalaman yang mulus di semua bidang seperti pengembangan, penyebaran, pengelolaan, dan dukungan. Database dapat berisi dalam memori serta objek berbasis disk.

Baris dalam tabel yang dioptimalkan memori diberi versi. Ini berarti bahwa setiap baris dalam tabel berpotensi memiliki beberapa versi. Semua versi baris dipertahankan dalam struktur data tabel yang sama. Penerapan versi baris digunakan untuk memungkinkan pembacaan dan penulisan bersamaan pada baris yang sama. Untuk informasi selengkapnya tentang baca dan tulis bersamaan pada baris yang sama, lihat Transaksi dengan Tabel yang Dioptimalkan Memori.

Gambar berikut mengilustrasikan multi-penerapan versi. Gambar memperlihatkan tabel dengan tiga baris dan setiap baris memiliki versi yang berbeda.

Multi-versioning.

Tabel memiliki tiga baris: r1, r2, dan r3. r1 memiliki tiga versi, r2 memiliki dua versi, dan r3 memiliki empat versi. Perhatikan bahwa versi yang berbeda dari baris yang sama tidak selalu menempati lokasi memori berturut-turut. Versi baris yang berbeda dapat disebarkan di seluruh struktur data tabel.

Struktur data tabel yang dioptimalkan memori dapat dilihat sebagai kumpulan versi baris. Baris dalam tabel berbasis disk diatur dalam halaman dan jangkauan, dan baris individual yang ditangani menggunakan nomor halaman dan offset halaman, versi baris dalam tabel memori yang dioptimalkan ditangani menggunakan penunjuk memori 8-byte.

Data dalam tabel yang dioptimalkan memori diakses dengan dua cara:

Melalui prosedur tersimpan yang dikompilasi secara asli.
Melalui Transact-SQL yang ditafsirkan, di luar prosedur tersimpan yang dikompilasi secara asli. Pernyataan Transact-SQL ini mungkin berada di dalam prosedur tersimpan yang ditafsirkan atau mungkin pernyataan Transact-SQL ad hoc.

Mengakses Data dalam Tabel yang Dioptimalkan Memori

Tabel yang dioptimalkan memori dapat diakses paling efisien dari prosedur tersimpan yang dikompilasi secara asli (Prosedur Tersimpan yang Dikompilasi Secara Asli). Tabel yang dioptimalkan memori juga dapat diakses dengan Transact-SQL yang ditafsirkan (tradisional). Transact-SQL yang ditafsirkan mengacu pada mengakses tabel yang dioptimalkan memori tanpa prosedur tersimpan yang dikompilasi secara asli. Beberapa contoh akses Transact-SQL yang ditafsirkan termasuk mengakses tabel yang dioptimalkan memori dari pemicu DML, batch Transact-SQL ad hoc, tampilan, dan fungsi bernilai tabel.

Tabel berikut ini meringkas akses Transact-SQL asli dan ditafsirkan untuk berbagai objek.

Fitur	Akses Menggunakan Prosedur Tersimpan yang Dikompilasi Secara Asli	Akses Transact-SQL yang Ditafsirkan	Akses CLR
Tabel yang dioptimalkan memori	Ya	Ya	Tidak*
Jenis tabel yang dioptimalkan memori	Ya	Ya	Tidak
Prosedur tersimpan yang dikompilasi secara asli	Penumpasan prosedur tersimpan yang dikompilasi secara asli sekarang didukung. Anda dapat menggunakan sintaks EXECUTE di dalam prosedur tersimpan, selama prosedur yang dirujuk juga dikompilasi secara asli.	Ya	Tidak*

*Anda tidak dapat mengakses tabel yang dioptimalkan memori atau prosedur tersimpan yang dikompilasi secara asli dari koneksi konteks (koneksi dari SQL Server saat menjalankan modul CLR). Namun, Anda dapat membuat dan membuka koneksi lain tempat Anda dapat mengakses tabel yang dioptimalkan memori dan prosedur tersimpan yang dikompilasi secara asli.

Performa dan skalabilitas

Faktor-faktor berikut akan memengaruhi perolehan performa yang dapat dicapai dengan OLTP Dalam Memori:

Komunikasi: Aplikasi dengan banyak panggilan ke prosedur tersimpan singkat mungkin melihat perolehan performa yang lebih kecil dibandingkan dengan aplikasi dengan lebih sedikit panggilan dan lebih banyak fungsionalitas yang diterapkan di setiap prosedur tersimpan.

Eksekusi Transact-SQL: OLTP Dalam Memori mencapai performa terbaik saat menggunakan prosedur tersimpan yang dikompilasi secara asli daripada prosedur tersimpan yang ditafsirkan atau eksekusi kueri. Mungkin ada manfaat untuk mengakses tabel yang dioptimalkan memori dari prosedur tersimpan tersebut.

Pemindaian Rentang vs Pencarian Titik: Indeks non-kluster yang dioptimalkan memori mendukung pemindaian rentang dan pemindaian yang diurutkan. Untuk pencarian titik, indeks hash yang dioptimalkan memori memiliki performa yang lebih baik daripada indeks nonclustered yang dioptimalkan memori. Indeks nonkluster yang dioptimalkan memori memiliki performa yang lebih baik daripada indeks berbasis disk.

Mulai SQL Server 2016, rencana kueri untuk tabel yang dioptimalkan memori dapat memindai tabel secara paralel. Ini meningkatkan performa kueri analitik.
- Indeks hash juga menjadi dapat dipindai secara paralel di SQL Server 2016.
- Indeks nonclustered juga menjadi dapat dipindai secara paralel di SQL Server 2016.
- Indeks penyimpan kolom telah dapat dipindai secara paralel sejak awal mereka di SQL Server 2014.

Operasi indeks: Operasi indeks tidak dicatat, dan hanya ada dalam memori.

Konkurensi: Aplikasi yang performanya dipengaruhi oleh konkurensi tingkat mesin, seperti ketidakcocokan atau pemblokiran kait, meningkat secara signifikan ketika aplikasi berpindah ke OLTP Dalam Memori.

Tabel berikut mencantumkan masalah performa dan skalabilitas yang umumnya ditemukan dalam database relasional dan bagaimana OLTP Dalam Memori dapat meningkatkan performa.

Masalah	Dampak OLTP Dalam Memori
Performa Penggunaan sumber daya tinggi (CPU, I/O, jaringan atau memori).	CPU Prosedur tersimpan yang dikompilasi secara asli dapat menurunkan penggunaan CPU secara signifikan karena memerlukan instruksi yang jauh lebih sedikit untuk menjalankan pernyataan Transact-SQL dibandingkan dengan prosedur tersimpan yang ditafsirkan. OLTP Dalam Memori dapat membantu mengurangi investasi perangkat keras dalam beban kerja yang diskalakan karena satu server berpotensi memberikan throughput lima hingga sepuluh server. I/O Jika Anda mengalami hambatan I/O dari pemrosesan ke halaman data atau indeks, OLTP Dalam Memori dapat mengurangi hambatan. Selain itu, titik pemeriksaan objek OLTP Dalam Memori bersifat berkelanjutan dan tidak menyebabkan peningkatan operasi I/O secara tiba-tiba. Namun, jika kumpulan kerja tabel penting performa tidak sesuai dalam memori, OLTP Dalam Memori tidak akan meningkatkan performa karena memerlukan data untuk menjadi residen memori. Jika Anda mengalami hambatan I/O dalam pengelogan, OLTP Dalam Memori dapat mengurangi hambatan karena tidak terlalu banyak pengelogan. Jika satu atau beberapa tabel yang dioptimalkan memori dikonfigurasi sebagai tabel yang tidak tahan lama, Anda dapat menghilangkan pengelogan untuk data. Memori OLTP Dalam Memori tidak menawarkan manfaat performa apa pun. OLTP Dalam Memori dapat memberikan tekanan ekstra pada memori karena objek harus menjadi residen memori. Jaringan OLTP Dalam Memori tidak menawarkan manfaat performa apa pun. Data perlu dikomunikasikan dari tingkat data ke tingkat aplikasi.
Skalabilitas Sebagian besar masalah penskalaan dalam aplikasi SQL Server disebabkan oleh masalah konkurensi seperti ketidakcocokan pada kunci, kait, dan spinlock.	Pertikaian Kait Skenario umum adalah ketidakcocokan pada halaman terakhir indeks saat menyisipkan baris secara bersamaan dalam urutan kunci. Karena OLTP Dalam Memori tidak mengambil kait saat mengakses data, masalah skalabilitas yang terkait dengan ketidakcocokan kait akan dihapus sepenuhnya. Pertikaian Spinlock Karena OLTP Dalam Memori tidak mengambil kait saat mengakses data, masalah skalabilitas yang terkait dengan ketidakcocokan spinlock sepenuhnya dihapus. Mengunci Ketidakcocokan Terkait Jika aplikasi database Anda mengalami masalah pemblokiran antara operasi baca dan tulis, OLTP Dalam Memori menghapus masalah pemblokiran karena menggunakan bentuk baru kontrol konkurensi optimis untuk mengimplementasikan semua tingkat isolasi transaksi. OLTP Dalam Memori tidak menggunakan TempDB untuk menyimpan versi baris. Jika masalah penskalaan disebabkan oleh konflik antara dua operasi tulis, seperti dua transaksi bersamaan yang mencoba memperbarui baris yang sama, OLTP Dalam Memori memungkinkan satu transaksi berhasil dan gagal pada transaksi lainnya. Transaksi yang gagal harus diajukan kembali baik secara eksplisit maupun implisit, mencoba kembali transaksi. Dalam kedua kasus, Anda perlu membuat perubahan pada aplikasi. Jika aplikasi Anda sering mengalami konflik antara dua operasi tulis, nilai penguncian optimis berkurang. Aplikasi ini tidak cocok untuk OLTP Dalam Memori. Sebagian besar aplikasi OLTP tidak memiliki konflik tulis kecuali konflik disebabkan oleh eskalasi kunci.

Masalah

Dampak OLTP Dalam Memori

Performa

Penggunaan sumber daya tinggi (CPU, I/O, jaringan atau memori).

CPU
Prosedur tersimpan yang dikompilasi secara asli dapat menurunkan penggunaan CPU secara signifikan karena memerlukan instruksi yang jauh lebih sedikit untuk menjalankan pernyataan Transact-SQL dibandingkan dengan prosedur tersimpan yang ditafsirkan.

OLTP Dalam Memori dapat membantu mengurangi investasi perangkat keras dalam beban kerja yang diskalakan karena satu server berpotensi memberikan throughput lima hingga sepuluh server.

I/O
Jika Anda mengalami hambatan I/O dari pemrosesan ke halaman data atau indeks, OLTP Dalam Memori dapat mengurangi hambatan. Selain itu, titik pemeriksaan objek OLTP Dalam Memori bersifat berkelanjutan dan tidak menyebabkan peningkatan operasi I/O secara tiba-tiba. Namun, jika kumpulan kerja tabel penting performa tidak sesuai dalam memori, OLTP Dalam Memori tidak akan meningkatkan performa karena memerlukan data untuk menjadi residen memori. Jika Anda mengalami hambatan I/O dalam pengelogan, OLTP Dalam Memori dapat mengurangi hambatan karena tidak terlalu banyak pengelogan. Jika satu atau beberapa tabel yang dioptimalkan memori dikonfigurasi sebagai tabel yang tidak tahan lama, Anda dapat menghilangkan pengelogan untuk data.

Memori
OLTP Dalam Memori tidak menawarkan manfaat performa apa pun. OLTP Dalam Memori dapat memberikan tekanan ekstra pada memori karena objek harus menjadi residen memori.

Jaringan
OLTP Dalam Memori tidak menawarkan manfaat performa apa pun. Data perlu dikomunikasikan dari tingkat data ke tingkat aplikasi.

Skalabilitas

Sebagian besar masalah penskalaan dalam aplikasi SQL Server disebabkan oleh masalah konkurensi seperti ketidakcocokan pada kunci, kait, dan spinlock.

Pertikaian Kait
Skenario umum adalah ketidakcocokan pada halaman terakhir indeks saat menyisipkan baris secara bersamaan dalam urutan kunci. Karena OLTP Dalam Memori tidak mengambil kait saat mengakses data, masalah skalabilitas yang terkait dengan ketidakcocokan kait akan dihapus sepenuhnya.

Pertikaian Spinlock
Karena OLTP Dalam Memori tidak mengambil kait saat mengakses data, masalah skalabilitas yang terkait dengan ketidakcocokan spinlock sepenuhnya dihapus.

Mengunci Ketidakcocokan Terkait
Jika aplikasi database Anda mengalami masalah pemblokiran antara operasi baca dan tulis, OLTP Dalam Memori menghapus masalah pemblokiran karena menggunakan bentuk baru kontrol konkurensi optimis untuk mengimplementasikan semua tingkat isolasi transaksi. OLTP Dalam Memori tidak menggunakan TempDB untuk menyimpan versi baris.

Jika masalah penskalaan disebabkan oleh konflik antara dua operasi tulis, seperti dua transaksi bersamaan yang mencoba memperbarui baris yang sama, OLTP Dalam Memori memungkinkan satu transaksi berhasil dan gagal pada transaksi lainnya. Transaksi yang gagal harus diajukan kembali baik secara eksplisit maupun implisit, mencoba kembali transaksi. Dalam kedua kasus, Anda perlu membuat perubahan pada aplikasi.

Jika aplikasi Anda sering mengalami konflik antara dua operasi tulis, nilai penguncian optimis berkurang. Aplikasi ini tidak cocok untuk OLTP Dalam Memori. Sebagian besar aplikasi OLTP tidak memiliki konflik tulis kecuali konflik disebabkan oleh eskalasi kunci.

Keamanan Tingkat Baris dalam Tabel yang Dioptimalkan Memori

Keamanan Tingkat Baris didukung dalam tabel yang dioptimalkan memori. Menerapkan kebijakan Keamanan Tingkat Baris ke tabel yang dioptimalkan memori pada dasarnya sama dengan tabel berbasis disk, kecuali bahwa fungsi bernilai tabel sebaris yang digunakan sebagai predikat keamanan harus dikompilasi secara asli (dibuat menggunakan opsi WITH NATIVE_COMPILATION). Untuk detailnya, lihat bagian Kompatibilitas Lintas Fitur di topik Keamanan Tingkat Baris.

Berbagai fungsi keamanan bawaan yang penting untuk keamanan tingkat baris telah diaktifkan untuk tabel dalam memori. Untuk detailnya, lihat Fungsi Bawaan dalam Modul yang Dikompilasi Secara Asli.

EXECUTE AS CALLER - Semua modul asli sekarang mendukung dan menggunakan EXECUTE AS CALLER secara default, bahkan jika petunjuk tidak ditentukan. Ini karena diharapkan bahwa semua fungsi predikat keamanan tingkat baris akan menggunakan EXECUTE AS CALLER sehingga fungsi (dan fungsi bawaan apa pun yang digunakan di dalamnya) akan dievaluasi dalam konteks pengguna panggilan.
EXECUTE AS CALLER memiliki hit performa kecil (sekitar10%) yang disebabkan oleh pemeriksaan izin pada pemanggil. Jika modul menentukan EXECUTE AS OWNER atau EXECUTE AS SELF secara eksplisit, pemeriksaan izin ini dan biaya performa terkait akan dihindari. Namun, menggunakan salah satu opsi ini bersama dengan fungsi bawaan di atas akan menimbulkan hit performa yang jauh lebih tinggi karena peralihan konteks yang diperlukan.

Skenario

Untuk diskusi singkat tentang skenario umum di mana OLTP Dalam Memori dapat meningkatkan performa, lihat OLTP Dalam Memori.

Lihat Juga

In-Memory OLTP (Pengoptimalan In-Memory)