Demonstrasi: Peningkatan Performa OLTP Dalam Memori

Artikel
06/02/2023

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

Sampel kode dalam topik ini menunjukkan performa cepat tabel yang dioptimalkan memori. Peningkatan performa terbukti ketika data dalam tabel yang dioptimalkan memori diakses dari Transact-SQL tradisional yang ditafsirkan. Peningkatan performa ini bahkan lebih besar ketika data dalam tabel yang dioptimalkan memori diakses dari prosedur tersimpan yang dikompilasi secara asli (NCSProc).

Untuk melihat demonstrasi yang lebih komprehensif tentang potensi peningkatan performa OLTP Dalam Memori, lihat Demo Performa OLTP Dalam Memori v1.0.

Contoh kode dalam artikel ini berutas tunggal, dan tidak memanfaatkan manfaat konkurensi OLTP Dalam Memori. Beban kerja yang menggunakan konkurensi akan melihat perolehan performa yang lebih besar. Contoh kode hanya menunjukkan satu aspek peningkatan performa, yaitu efisiensi akses data untuk INSERT.

Peningkatan performa yang ditawarkan oleh tabel yang dioptimalkan memori sepenuhnya diwujudkan ketika data dalam tabel yang dioptimalkan memori diakses dari NCSProc.

Contoh Kode

Sub bagian berikut menjelaskan setiap langkah.

Langkah 1a: Prasyarat Jika Menggunakan SQL Server

Langkah-langkah dalam sub bagian pertama ini hanya berlaku jika Anda menjalankan di SQL Server, dan tidak berlaku jika Anda menjalankan di Azure SQL Database. Lakukan tindakan berikut:

Gunakan SQL Server Management Studio (SSMS.exe) untuk menyambungkan ke SQL Server Anda. Atau alat apa pun yang mirip dengan SSMS.exe tidak masalah.
Buat direktori bernama C:\data\secara manual. Sampel kode Transact-SQL mengharapkan direktori sudah ada sebelumnya.
Jalankan T-SQL pendek untuk membuat database dan grup file yang dioptimalkan memorinya.

go  
CREATE DATABASE imoltp;    --  Transact-SQL  
go  
  
ALTER DATABASE imoltp ADD FILEGROUP [imoltp_mod]  
    CONTAINS MEMORY_OPTIMIZED_DATA;  
  
ALTER DATABASE imoltp ADD FILE  
    (name = [imoltp_dir], filename= 'c:\data\imoltp_dir')  
    TO FILEGROUP imoltp_mod;  
go  
  
USE imoltp;  
go

Langkah 1b: Prasyarat Jika Menggunakan Azure SQL Database

Sub bagian ini hanya berlaku jika Anda menggunakan Azure SQL Database. Lakukan tindakan berikut:

Tentukan database pengujian mana yang sudah ada yang akan Anda gunakan untuk contoh kode.
Jika Anda memutuskan untuk membuat database pengujian baru, gunakan portal Microsoft Azure untuk membuat database bernama imoltp.

Jika Anda ingin instruksi untuk menggunakan portal Microsoft Azure untuk ini, lihat Mulai menggunakan Azure SQL Database.

Langkah 2: Buat Tabel yang Dioptimalkan Memori, dan NCSProc

Langkah ini membuat tabel yang dioptimalkan memori, dan prosedur tersimpan yang dikompilasi secara asli (NCSProc). Lakukan tindakan berikut:

Gunakan SSMS.exe untuk menyambungkan ke database baru Anda.
Jalankan T-SQL berikut ini di database Anda.

go  
DROP PROCEDURE IF EXISTS ncsp;  
DROP TABLE IF EXISTS sql;  
DROP TABLE IF EXISTS hash_i;  
DROP TABLE IF EXISTS hash_c;  
go  
  
CREATE TABLE [dbo].[sql] (  
  c1 INT NOT NULL PRIMARY KEY,  
  c2 NCHAR(48) NOT NULL  
);  
go  
  
CREATE TABLE [dbo].[hash_i] (  
  c1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),  
  c2 NCHAR(48) NOT NULL  
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA);  
go  
  
CREATE TABLE [dbo].[hash_c] (  
  c1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),  
  c2 NCHAR(48) NOT NULL  
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA);  
go  
  
CREATE PROCEDURE ncsp  
    @rowcount INT,  
    @c NCHAR(48)  
  WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
  AS   
  BEGIN ATOMIC   
  WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')  
  DECLARE @i INT = 1;  
  WHILE @i <= @rowcount  
  BEGIN;  
    INSERT INTO [dbo].[hash_c] VALUES (@i, @c);  
    SET @i += 1;  
  END;  
END;  
go

Langkah 3: Jalankan Kode

Sekarang Anda dapat menjalankan kueri yang akan menunjukkan performa tabel yang dioptimalkan memori. Lakukan tindakan berikut:

Gunakan SSMS.exe untuk menjalankan T-SQL berikut di database Anda.

Abaikan kecepatan atau data performa lainnya yang dihasilkan eksekusi pertama ini. Eksekusi pertama memastikan beberapa operasi satu kali saja dilakukan, seperti alokasi awal memori.
Sekali lagi, gunakan SSMS.exe untuk menjalankan ulang T-SQL berikut dalam database Anda.

go  
SET STATISTICS TIME OFF;  
SET NOCOUNT ON;  
  
-- Inserts, one at a time.  
  
DECLARE @starttime DATETIME2 = sysdatetime();  
DECLARE @timems INT;  
DECLARE @i INT = 1;  
DECLARE @rowcount INT = 100000;  
DECLARE @c NCHAR(48) = N'12345678901234567890123456789012345678';  
  
-- Harddrive-based table and interpreted Transact-SQL.  
  
BEGIN TRAN;  
  WHILE @i <= @rowcount  
  BEGIN;  
    INSERT INTO [dbo].[sql] VALUES (@i, @c);  
    SET @i += 1;  
  END;  
COMMIT;  
  
SET @timems = datediff(ms, @starttime, sysdatetime());  
SELECT 'A: Disk-based table and interpreted Transact-SQL: '  
    + cast(@timems AS VARCHAR(10)) + ' ms';  
  
-- Interop Hash.  
  
SET @i = 1;  
SET @starttime = sysdatetime();  
  
BEGIN TRAN;  
  WHILE @i <= @rowcount  
    BEGIN;  
      INSERT INTO [dbo].[hash_i] VALUES (@i, @c);  
      SET @i += 1;  
    END;  
COMMIT;  
  
SET @timems = datediff(ms, @starttime, sysdatetime());  
SELECT 'B: memory-optimized table with hash index and interpreted Transact-SQL: '  
    + cast(@timems as VARCHAR(10)) + ' ms';  
  
-- Compiled Hash.  
  
SET @starttime = sysdatetime();  
  
EXECUTE ncsp @rowcount, @c;  
  
SET @timems = datediff(ms, @starttime, sysdatetime());  
SELECT 'C: memory-optimized table with hash index and native SP:'  
    + cast(@timems as varchar(10)) + ' ms';  
go  
  
DELETE sql;  
DELETE hash_i;  
DELETE hash_c;  
go

Selanjutnya adalah statistik waktu output yang dihasilkan oleh uji coba kedua kami.

10453 ms , A: Disk-based table and interpreted Transact-SQL.  
5626 ms , B: memory-optimized table with hash index and interpreted Transact-SQL.  
3937 ms , C: memory-optimized table with hash index and native SP.

Lihat Juga

In-Memory OLTP (Pengoptimalan In-Memory)