Bagikan melalui

Survei Area Awal dalam OLTP Dalam Memori

  • Artikel

Berlaku untuk: SQL ServerAzure SQL Database Azure SQL Managed Instance

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

Untuk OLTP Dalam Memori, 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 dukungan mereka terhadap teknologi Dalam Memori.

Di alam liar beberapa blogger menyebut OLTP Dalam Memori sebagai Hekaton.

Manfaat Fitur Dalam Memori

SQL Server menyediakan fitur Dalam Memori 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 SQL INSERTs 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 Dalam Memori.

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 OLTP Dalam Memori:

Fitur untuk Analitik Operasional

Analitik Dalam Memori mengacu pada SQL SELECTs 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.
  • 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:

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 columnstore. Postingan ini ditulis oleh Sunil Agarwal, Manajer Program di Microsoft, pada Maret 2016.

Analitik Operasional Real Time

  1. Analitik Operasional Real Time Menggunakan Teknologi Dalam Memori
  2. Analitik Operasional Real Time - Ringkasan indeks penyimpan kolom non-klusster (NCCI)
  3. Analitik Operasional Real Time: Contoh sederhana menggunakan indeks penyimpan kolom berkluster nonclustered (NCCI) di SQL Server 2016
  4. Analitik Operasional Real Time: Operasi DML dan indeks penyimpan kolom nonclustered (NCCI) di SQL Server 2016
  5. Analitik Operasional Real Time: Indeks penyimpan kolom nonclustered terfilter (NCCI)
  6. Analitik Operasional Real Time: Opsi Penundaan Kompresi untuk indeks penyimpan kolom Nonclustered (NCCI)
  7. Analitik Operasional Real Time: Opsi Penundaan Kompresi dengan NCCI dan performa
  8. Analitik Operasional Real Time: Tabel yang Dioptimalkan Memori 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: Pemuatan Massal
  2. Indeks penyimpan kolom berkluster: Pengoptimalan Beban Data - Pengelogan Minimal
  3. Indeks penyimpan kolom berkluster: Pengoptimalan Beban Data - Impor Massal Paralel

Fitur OLTP Dalam Memori

Mari kita lihat fitur utama OLTP Dalam Memori.

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 sepenuhnya 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 di-siklus online. Instruksi T-SQL tidak lagi menanggung 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 di Azure SQL Database

OLTP Dalam Memori dan Penyimpan Kolom tersedia di Azure SQL Database. Untuk detailnya, lihat Mengoptimalkan Performa menggunakan Teknologi Dalam Memori 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 menerapkan fitur OLTP Dalam Memori.

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

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

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

Contoh skrip T-SQL berikut memungkinkan database untuk OLTP Dalam Memori 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 Dalam Memori

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 yang Dioptimalkan Memori

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 yang Dioptimalkan Memori.

Merencanakan tabel dan indeks yang dioptimalkan memori Anda

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 aktual:

-- 2 = Latest SalesOrderId, for CustomerId = 42

Panduan dokumentasi dan langkah berikutnya

Contoh biasa sebelumnya memberi Anda fondasi untuk mempelajari fitur OLTP Dalam Memori yang lebih canggih. Bagian berikut adalah panduan untuk pertimbangan khusus yang mungkin perlu Anda ketahui, dan di mana Anda dapat melihat detail tentang masing-masing.

Cara kerja fitur OLTP Dalam Memori jauh lebih cepat

Subbagian berikut menjelaskan secara singkat bagaimana fitur OLTP Dalam Memori bekerja secara internal untuk memberikan peningkatan performa.

Bagaimana tabel yang dioptimalkan memori berkinerja lebih cepat

Sifat ganda: Tabel yang dioptimalkan memori memiliki sifat ganda: satu representasi dalam memori aktif, dan yang lain pada hard disk. Setiap transaksi berkomitmen untuk 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 pertikaian kait dan spinlock.

Tidak ada kunci: Tabel yang dioptimalkan memori bergantung pada pendekatan optimis untuk tujuan bersaing 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 ketidakcocokan dalam beberapa sistem volume tinggi.

Versi baris: Alih-alih kunci, 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 yang lebih sedikit: Versi sebelum dan sesudah baris yang diperbarui disimpan dalam tabel memori yang dioptimalkan. 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.

Performa proc asli lebih cepat

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

Trade-off fitur Dalam Memori

Seperti umumnya dalam ilmu komputer, perolehan performa yang disediakan oleh fitur Dalam Memori 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:

Bagian lainnya dari bagian ini 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:

Partisi tabel besar Anda: Salah satu cara untuk memenuhi permintaan banyak memori aktif adalah dengan mempartisi tabel besar Anda ke 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). Pemartisian ini adalah proses desain dan implementasi manual. Lihat:

Trade-off dari 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 yang Dioptimalkan Memori berbeda dalam beberapa cara dari indeks pada tabel lokal tradisional. Indeks Hash hanya tersedia pada tabel yang dioptimalkan memori.

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

Tabel yang dioptimalkan memori dapat dideklarasikan dengan DURABILITY = 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, kosong dari data.
  • SCHEMA_ONLY tabel dapat menjadi alternatif yang unggul untuk tabel #temporary dalam tempdb, ketika ribuan baris terlibat.

Variabel tabel juga dapat dideklarasikan sebagai memori yang dioptimalkan. Lihat:

Pertimbangan tingkat lanjut untuk modul yang dikompilasi secara asli

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

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 dapat diterima pada tingkat praktis.

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

Panduan dokumentasi untuk tabel yang dioptimalkan memori

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

Panduan dokumentasi untuk proc asli

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

Berikut adalah artikel yang menawarkan kode untuk menunjukkan keuntungan performa yang dapat Anda capai dengan menggunakan OLTP Dalam Memori: