Survei Area Awal di OLTP In-Memory

Berlaku untuk: SQL Server (semua versi yang didukung) Azure SQL Database Azure SQL Managed Instance

Artikel ini ditujukan untuk pengembang yang terburu-buru mempelajari dasar-dasar fitur performa OLTP In-Memory Microsoft SQL Server dan Azure SQL Database.

Untuk In-Memory OLTP, artikel ini menyediakan hal berikut:

• Penjelasan cepat tentang fitur.
• Sampel kode inti yang mengimplementasikan fitur.

SQL Server dan SQL Database hanya memiliki variasi kecil dalam mendukung teknologi In-Memory.

Di alam liar beberapa blogger menyebut In-Memory OLTP sebagai Hekaton.

Manfaat Fitur In-Memory

SQL Server menyediakan fitur In-Memory yang dapat sangat meningkatkan performa banyak sistem aplikasi. Pertimbangan paling lurus ke depan dijelaskan di bagian ini.

Fitur untuk OLTP (Pemrosesan Transaksi online)

Sistem yang harus memproses sejumlah besar INSERT SQL secara bersamaan adalah kandidat yang sangat baik untuk fitur OLTP.

• Tolok ukur kami menunjukkan bahwa peningkatan kecepatan dari 5 kali hingga 20 kali lebih cepat dapat dicapai dengan adopsi fitur In-Memory.

Sistem yang memproses perhitungan berat dalam Transact-SQL adalah kandidat yang sangat baik.

• Prosedur tersimpan yang didedikasikan untuk perhitungan berat dapat berjalan hingga 99 kali lebih cepat.

Nantinya Anda dapat mengunjungi artikel berikut yang menawarkan demonstrasi perolehan performa dari In-Memory OLTP:

• Demonstrasi: Peningkatan Performa In-Memory OLTP menawarkan demonstrasi skala kecil dari potensi perolehan performa yang lebih besar.
• Database Sampel untuk In-Memory OLTP menawarkan demonstrasi skala yang lebih besar.

Fitur untuk Analitik Operasional

In-Memory Analytics mengacu pada SQL SELECT yang menggabungkan data transaksional, biasanya dengan dimasukkannya klausul GROUP BY. Jenis indeks yang disebut penyimpan kolom adalah pusat analitik operasional.

Ada dua skenario utama:

• Analitik Operasional Batch mengacu pada proses agregasi yang berjalan baik setelah jam kerja atau pada perangkat keras sekunder yang memiliki salinan data transaksional.
  • Azure Synapse Analytics juga berkaitan dengan analitik operasional batch.
• Analitik Operasional real time mengacu pada proses agregasi yang berjalan selama jam kerja dan pada perangkat keras utama yang digunakan untuk beban kerja transaksional.

Artikel saat ini berfokus pada OLTP, dan bukan pada Analitik. Untuk informasi tentang cara indeks penyimpan kolom membawa Analytics ke SQL, lihat:

• Mulai menggunakan Columnstore untuk analitik operasional real time
• Panduan Indeks Penyimpan Kolom

Columnstore

Urutan posting blog yang sangat baik secara elegan menjelaskan indeks penyimpan kolom dari beberapa perspektif. Sebagian besar postingan menjelaskan lebih lanjut konsep analitik operasional real-time, yang didukung penyimpan kolom. Postingan ini ditulis oleh Sunil Agarwal, Manajer Program di Microsoft, pada Maret 2016.

Analitik Operasional Real Time

1. Analitik Operasional Real Time Menggunakan Teknologi In-Memory
2. Analitik Operasional Real-Time - Ringkasan indeks penyimpan kolom non-klusster (NCCI)
3. Analitik Operasional Real Time: Contoh sederhana menggunakan indeks penyimpan kolom berkluster non-kluster (NCCI) pada SQL Server 2016
4. Analitik Operasional Real-Time: Operasi DML dan indeks penyimpan kolom nonkluster (NCCI) pada SQL Server 2016
5. Analitik Operasional Real Time: Indeks penyimpan kolom non-kluster terfilter (NCCI)
6. Analitik Operasional Real Time: Opsi Penundaan Kompresi untuk indeks penyimpan kolom non-kluster (NCCI)
7. Analitik Operasional Real Time: Opsi Penundaan Kompresi dengan NCCI dan performa
8. Analitik Operasional Real Time: tabel Memory-Optimized dan indeks penyimpan kolom

Defragmentasi indeks penyimpan kolom

1. Defragmentasi indeks penyimpan kolom menggunakan Perintah REORGANIZE
2. Kebijakan Penggabungan indeks penyimpan kolom untuk REORGANIZE

Impor data secara massal

1. Penyimpanan Kolom Berkluster: Muat Massal
2. Indeks penyimpan kolom berkluster: Pengoptimalan Beban Data - Pengelogan Minimal
3. Indeks penyimpan kolom berkluster: Pengoptimalan Beban Data - Impor Massal Paralel

Fitur OLTP In-Memory

Mari kita lihat fitur utama In-Memory OLTP.

Tabel yang dioptimalkan memori

Kata kunci T-SQL MEMORY_OPTIMIZED, pada pernyataan CREATE TABLE, adalah bagaimana tabel dibuat untuk ada dalam memori aktif, bukan pada disk.

Tabel yang dioptimalkan memori memiliki satu representasi dirinya sendiri dalam memori aktif, dan salinan sekunder pada disk.

• Salinan disk adalah untuk pemulihan rutin setelah shutdown-then-restart server atau database. Gandaitas memori-plus-disk ini benar-benar tersembunyi dari Anda dan kode Anda.

Modul yang dikompilasi secara asli

Kata kunci T-SQL NATIVE_COMPILATION, pada pernyataan CREATE PROCEDURE, adalah bagaimana prosedur tersimpan yang dikompilasi secara asli dibuat. Pernyataan T-SQL dikompilasi ke kode mesin pada penggunaan pertama proc asli setiap kali database diputar secara online. Instruksi T-SQL tidak lagi menahan interpretasi lambat dari setiap instruksi.

• Kami telah melihat kompilasi asli menghasilkan durasi yang 1/100 dari durasi yang ditafsirkan.

Modul asli hanya dapat mereferensikan tabel yang dioptimalkan memori, dan tidak dapat mereferensikan tabel berbasis disk.

Ada tiga jenis modul yang dikompilasi secara asli:

• Prosedur tersimpan yang dikompilasi secara asli.
• Fungsi yang ditentukan pengguna (UDF) yang dikompilasi secara asli, yang bersifat skalar.
• Pemicu yang dikompilasi secara asli.

Ketersediaan dalam Azure SQL Database

In-Memory OLTP dan Columnstore tersedia di Azure SQL Database. Untuk detailnya lihat Mengoptimalkan Performa menggunakan Teknologi In-Memory di SQL Database.

1. Pastikan tingkat >kompatibilitas = 130

Bagian ini memulai urutan bagian bernomor yang bersama-sama menunjukkan sintaks transact-SQL yang dapat Anda gunakan untuk mengimplementasikan fitur OLTP In-Memory.

Pertama, penting bahwa database Anda diatur ke tingkat kompatibilitas setidaknya 130. Selanjutnya adalah kode T-SQL untuk melihat tingkat kompatibilitas saat ini tempat database Anda saat ini diatur.

SELECT d.compatibility_level
    FROM sys.databases as d
    WHERE d.name = Db_Name();

Selanjutnya adalah kode T-SQL untuk memperbarui tingkat, jika perlu.

ALTER DATABASE CURRENT
    SET COMPATIBILITY_LEVEL = 130;

2. Tingkatkan ke SNAPSHOT

Ketika transaksi melibatkan tabel berbasis disk dan tabel yang dioptimalkan memori, kami menyebutnya transaksi lintas kontainer. Dalam transaksi seperti itu, sangat penting bahwa bagian transaksi yang dioptimalkan memori beroperasi pada tingkat isolasi transaksi bernama SNAPSHOT.

Untuk menerapkan tingkat ini dengan andal untuk tabel yang dioptimalkan memori dalam transaksi lintas kontainer, ubah pengaturan database Anda dengan menjalankan T-SQL berikut.

ALTER DATABASE CURRENT
    SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

3. Buat FILEGROUP yang dioptimalkan

Pada Microsoft SQL Server, sebelum Anda dapat membuat tabel memori yang dioptimalkan, Anda harus terlebih dahulu membuat FILEGROUP yang Anda nyatakan CONTAINS MEMORY_OPTIMIZED_DATA. FILEGROUP ditetapkan ke database Anda. Untuk detailnya lihat:

• FILEGROUP yang Dioptimalkan Memori

Pada Azure SQL Database, Anda tidak perlu dan tidak dapat membuat FILEGROUP seperti itu.

Contoh skrip T-SQL berikut memungkinkan database untuk In-Memory OLTP dan mengonfigurasi semua pengaturan yang direkomendasikan. Ini berfungsi dengan SQL Server dan Azure SQL Database: enable-in-memory-oltp.sql.

Perhatikan bahwa tidak semua fitur SQL Server didukung untuk database dengan grup file MEMORY_OPTIMIZED_DATA. Untuk detail tentang batasan, lihat: Fitur SQL Server yang tidak didukung untuk OLTP In-Memory

4. Buat tabel yang dioptimalkan memori

Kata kunci Transact-SQL yang penting adalah kata kunci MEMORY_OPTIMIZED.

CREATE TABLE dbo.SalesOrder
    (
        SalesOrderId   integer   not null   IDENTITY
            PRIMARY KEY NONCLUSTERED,
        CustomerId   integer    not null,
        OrderDate    datetime   not null
    )
        WITH
            (MEMORY_OPTIMIZED = ON,
            DURABILITY = SCHEMA_AND_DATA);

Pernyataan TRANSACT-SQL INSERT dan SELECT terhadap tabel yang dioptimalkan memori sama dengan untuk tabel biasa.

ALTER TABLE untuk tabel Memory-Optimized

UBAH TABEL... ADD/DROP dapat menambahkan atau menghapus kolom dari tabel yang dioptimalkan memori, atau indeks.

• CREATE INDEX dan DROP INDEX tidak dapat dijalankan terhadap tabel yang dioptimalkan memori, gunakan ALTER TABLE ... ADD/DROP INDEX sebagai gantinya.
• Untuk detailnya lihat Mengubah Tabel Memory-Optimized.

Merencanakan tabel dan indeks yang dioptimalkan memori Anda

• Indeks untuk Tabel Memory-Optimized
• Konstruksi SQL Transaksi Tidak Didukung oleh OLTP In-Memory

5. Buat prosedur tersimpan yang dikompilasi secara asli (proc asli)

Kata kunci penting adalah NATIVE_COMPILATION.

CREATE PROCEDURE ncspRetrieveLatestSalesOrderIdForCustomerId  
        @_CustomerId   INT  
        WITH  
            NATIVE_COMPILATION,  
            SCHEMABINDING  
    AS  
    BEGIN ATOMIC  
        WITH  
            (TRANSACTION ISOLATION LEVEL = SNAPSHOT,
            LANGUAGE = N'us_english')  
      
        DECLARE @SalesOrderId int, @OrderDate datetime;
      
        SELECT TOP 1  
                @SalesOrderId = s.SalesOrderId,  
                @OrderDate    = s.OrderDate  
            FROM dbo.SalesOrder AS s  
            WHERE s.CustomerId = @_CustomerId  
            ORDER BY s.OrderDate DESC;  
      
        RETURN @SalesOrderId;  
    END;  

Kata kunci SCHEMABINDING berarti tabel yang dirujuk dalam proc asli tidak dapat dihilangkan kecuali proc asli dihilangkan terlebih dahulu. Untuk detailnya lihat Membuat Prosedur Tersimpan yang Dikompilasi Secara Asli.

Perhatikan bahwa Anda tidak perlu membuat prosedur tersimpan yang dikompilasi secara asli untuk mengakses tabel yang dioptimalkan memori. Anda juga dapat mereferensikan tabel yang dioptimalkan memori dari prosedur tersimpan tradisional dan batch ad hoc.

6. Jalankan proc asli

Isi tabel dengan dua baris data.

INSERT into dbo.SalesOrder  
        ( CustomerId, OrderDate )  
    VALUES  
        ( 42, '2013-01-13 03:35:59' ),
        ( 42, '2015-01-15 15:35:59' );

Panggilan EXECUTE ke prosedur tersimpan yang dikompilasi secara asli mengikuti.

DECLARE @LatestSalesOrderId int, @mesg nvarchar(128);
      
EXECUTE @LatestSalesOrderId =  
    ncspRetrieveLatestSalesOrderIdForCustomerId 42;
      
SET @mesg = CONCAT(@LatestSalesOrderId,  
    ' = Latest SalesOrderId, for CustomerId = ', 42);
PRINT @mesg;  

Berikut adalah output PRINT yang sebenarnya:

-- 2 = Latest SalesOrderId, for CustomerId = 42  

Panduan untuk dokumentasi dan langkah berikutnya

Contoh biasa sebelumnya memberi Anda fondasi untuk mempelajari fitur yang lebih canggih dari In-Memory OLTP. Bagian berikut adalah panduan untuk pertimbangan khusus yang mungkin perlu Anda ketahui, dan ke tempat Anda dapat melihat detail tentang masing-masing.

Cara kerja fitur OLTP In-Memory jauh lebih cepat

Subbagian berikut menjelaskan secara singkat bagaimana fitur OLTP In-Memory bekerja secara internal untuk memberikan peningkatan performa.

Bagaimana performa tabel yang dioptimalkan memori lebih cepat

Sifat ganda: Tabel yang dioptimalkan memori memiliki sifat ganda: satu representasi dalam memori aktif, dan yang lainnya di hard disk. Setiap transaksi diterapkan pada kedua representasi tabel. Transaksi beroperasi terhadap representasi memori aktif yang jauh lebih cepat. Tabel yang dioptimalkan memori mendapat manfaat dari kecepatan memori aktif yang lebih besar versus disk. Selanjutnya, kegigihan memori aktif yang lebih besar membuat praktis struktur tabel yang lebih canggih yang dioptimalkan untuk kecepatan. Struktur canggih juga tanpa halaman, sehingga menghindari overhead dan ketidakcocokan kait dan spinlock.

Tidak ada kunci: Tabel yang dioptimalkan memori bergantung pada pendekatan optimis untuk tujuan bersaing dari integritas data versus konkurensi dan throughput tinggi. Selama transaksi, tabel tidak menempatkan kunci pada versi apa pun dari baris data yang diperbarui. Ini dapat sangat mengurangi pertikaian dalam beberapa sistem volume tinggi.

Versi baris: Alih-alih mengunci, tabel yang dioptimalkan memori menambahkan versi baru dari baris yang diperbarui dalam tabel itu sendiri, bukan dalam tempdb. Baris asli disimpan sampai setelah transaksi dilakukan. Selama transaksi, proses lain dapat membaca versi asli baris.

• Saat beberapa versi baris dibuat untuk tabel berbasis disk, versi baris disimpan sementara dalam tempdb.

Pengelogan lebih sedikit: Sebelum dan sesudah versi baris yang diperbarui disimpan dalam tabel yang dioptimalkan memori. Pasangan baris menyediakan banyak informasi yang secara tradisional ditulis ke file log. Ini memungkinkan sistem untuk menulis lebih sedikit informasi, dan lebih jarang, ke log. Namun integritas transaksi dipastikan.

Bagaimana proc asli berkinerja lebih cepat

Mengonversi prosedur tersimpan yang ditafsirkan secara teratur menjadi prosedur tersimpan yang dikompilasi secara asli sangat mengurangi jumlah instruksi yang akan dijalankan selama durasi.

Trade-off fitur In-Memory

Seperti umum dalam ilmu komputer, perolehan performa yang disediakan oleh fitur In-Memory adalah trade-off. Fitur yang lebih baik membawa manfaat yang lebih berharga daripada biaya tambahan fitur. Anda dapat menemukan panduan komprehensif tentang trade-off di:

• Rencanakan adopsi Fitur OLTP In-Memory Anda di SQL Server

Bagian lainnya mencantumkan beberapa pertimbangan perencanaan dan trade-off utama.

Trade-off tabel yang dioptimalkan memori

Perkirakan memori: Anda harus memperkirakan jumlah memori aktif yang akan digunakan tabel yang dioptimalkan memori Anda. Sistem komputer Anda harus memiliki kapasitas memori yang memadai untuk menghosting tabel yang dioptimalkan memori. Untuk detailnya lihat:

• Memantau dan Memecahkan Masalah Penggunaan Memori
• Memperkirakan Persyaratan Memori untuk Tabel Memory-Optimized
• Ukuran Tabel dan Baris dalam Tabel Memory-Optimized

Partisi tabel besar Anda: Salah satu cara untuk memenuhi permintaan banyak memori aktif adalah dengan mempartisi tabel besar Anda menjadi bagian dalam memori yang menyimpan baris data terbaru yang panas versus bagian lain pada disk yang menyimpan baris warisan dingin (seperti pesanan penjualan yang telah dikirim sepenuhnya dan selesai). Partisi ini adalah proses desain dan implementasi manual. Lihat:

• Pemartisian Tingkat Aplikasi
• Pola Aplikasi untuk Tabel Memory-Optimized Partisi

Trade-off proc asli

• Prosedur tersimpan yang dikompilasi secara asli tidak dapat mengakses tabel berbasis disk. Proc asli hanya dapat mengakses tabel yang dioptimalkan memori.
• Ketika proc asli berjalan untuk pertama kalinya setelah server atau database baru-baru ini dibawa kembali online, proc asli harus dikompresi ulang satu kali. Ini menyebabkan penundaan sebelum proc asli mulai berjalan.

Pertimbangan tingkat lanjut untuk tabel yang dioptimalkan memori

Indeks untuk tabel Memory-Optimized berbeda dalam beberapa cara dari indeks pada tabel lokal tradisional. Indeks Hash hanya tersedia pada tabel yang dioptimalkan memori.

• Indeks Hash untuk Tabel Memory-Optimized
• Indeks Nonkluster untuk Tabel Memory-Optimized

Anda harus merencanakan untuk memastikan akan ada memori aktif yang cukup untuk tabel yang dioptimalkan memori yang direncanakan dan indeksnya. Lihat:

• Membuat dan Mengelola Storage untuk Objek Memory-Optimized

Tabel yang dioptimalkan memori dapat dideklarasikan dengan DURABILITAS = SCHEMA_ONLY:

• Sintaks ini memberi tahu sistem untuk membuang semua data dari tabel yang dioptimalkan memori ketika database diambil secara offline. Hanya definisi tabel yang dipertahankan.
• Ketika database kembali online, tabel yang dioptimalkan memori dimuat kembali ke memori aktif, kosongkan data.
• SCHEMA_ONLY tabel dapat menjadi alternatif yang unggul untuk #temporary tabel dalam tempdb, ketika ribuan baris terlibat.

Variabel tabel juga dapat dinyatakan sebagai memori dioptimalkan. Lihat:

• Tabel sementara dan variabel tabel yang lebih cepat dengan menggunakan pengoptimalan memori

Pertimbangan tingkat lanjut untuk modul yang dikompilasi secara asli

Jenis modul yang dikompilasi secara asli yang tersedia melalui Transact-SQL adalah:

• Prosedur tersimpan yang dikompilasi secara asli (proc asli).
• Fungsi skalar yang ditentukan pengguna yang dikompilasi secara asli.
• Pemicu yang dikompilasi secara asli (pemicu asli).
  • Hanya pemicu yang dikompilasi secara asli yang diizinkan pada tabel yang dioptimalkan memori.
• Fungsi bernilai tabel yang dikompilasi secara asli.
  • Meningkatkan performa tabel sementara dan variabel tabel menggunakan pengoptimalan memori

Fungsi yang ditentukan pengguna (UDF) yang dikompilasi secara asli berjalan lebih cepat daripada UDF yang ditafsirkan. Berikut adalah beberapa hal yang perlu dipertimbangkan dengan UDF:

• Saat T-SQL SELECT menggunakan UDF, UDF selalu dipanggil sekali per baris yang dikembalikan.
  • UDF tidak pernah berjalan sebaris, dan sebaliknya selalu dipanggil.
  • Perbedaan yang dikompilasi kurang signifikan daripada overhead panggilan berulang yang melekat pada semua UDF.
  • Namun, overhead panggilan UDF sering kali dapat diterima pada tingkat praktis.

Untuk data pengujian dan penjelasan tentang performa UDF asli, lihat:

• Melunakkan dampak RBAR dengan UDF Native Compiled pada SQL Server 2016
• Posting blog Fungsi yang Ditentukan Pengguna yang Dikompilasi Secara Asli oleh Gail Shaw, tertanggal Januari 2016.

Panduan dokumentasi untuk tabel yang dioptimalkan memori

Lihat artikel lain ini yang membahas pertimbangan khusus untuk tabel yang dioptimalkan memori:

• Migrasi ke OLTP In-Memory
  • Menentukan apakah Tabel atau Prosedur Tersimpan Harus Dialihkan ke In-Memory OLTP
  • Laporan Analisis Performa Transaksi di SQL Server Management Studio membantu Anda mengevaluasi apakah In-Memory OLTP akan meningkatkan performa aplikasi database Anda.
  • Gunakan Advisor Pengoptimalan Memori untuk membantu Anda memigrasikan tabel database berbasis disk ke In-Memory OLTP.
• Pencadangan, Pemulihan, dan Pemulihan Tabel Memory-Optimized
  • Penyimpanan yang digunakan oleh tabel yang dioptimalkan memori bisa jauh lebih besar dari ukurannya dalam memori, dan mempengaruhi ukuran cadangan database.
• Transaksi dengan Tabel Memory-Optimized
  • Menyertakan informasi tentang logika coba lagi di T-SQL, untuk transaksi pada tabel yang dioptimalkan memori.
• Dukungan SQL Transact untuk OLTP In-Memory
  • T-SQL dan jenis data yang didukung dan tidak didukung, untuk proc bertabel dan asli yang dioptimalkan memori.
• Ikat Database dengan tabel Memory-Optimized ke Kumpulan Sumber Daya, yang membahas pertimbangan tingkat lanjut opsional.

Panduan dokumentasi untuk proc asli

Artikel berikut, dan artikel turunannya dalam daftar isi (TOC), menjelaskan detail tentang prosedur tersimpan yang dikompilasi secara asli.

• Prosedur tersimpan yang dikompilasi secara asli

Tautan terkait

• Artikel awal: OLTP Dalam Memori (Pengoptimalan Dalam Memori)

Berikut adalah artikel yang menawarkan kode untuk menunjukkan keuntungan performa yang dapat Anda capai dengan menggunakan In-Memory OLTP:

• Demonstrasi: Peningkatan Performa In-Memory OLTP menawarkan demonstrasi skala kecil dari potensi perolehan performa yang lebih besar.
• Database Sampel untuk In-Memory OLTP menawarkan demonstrasi skala yang lebih besar.