Sampel dalam memori di Azure SQL Database
Berlaku untuk: Azure SQL Database
Teknologi dalam memori di Azure SQL Database memungkinkan Anda meningkatkan performa aplikasi Anda, dan berpotensi mengurangi biaya database Anda. Dengan menggunakan teknologi dalam memori di Azure SQL Database, Anda dapat mencapai peningkatan performa dengan berbagai beban kerja.
Dua sampel dalam artikel ini menggambarkan penggunaan OLTP Dalam Memori serta indeks penyimpan kolom di Azure SQL Database.
Untuk informasi selengkapnya, lihat:
- Ringkasan dan Skenario Penggunaan OLTP Dalam Memori (termasuk referensi untuk studi kasus pelanggan dan informasi untuk memulai)
- Dokumentasi untuk OLTP Dalam Memori
- Panduan Indeks Penyimpan Kolom
- Pemrosesan transaksional/analitis hibrida (HTAP), juga dikenal sebagai analitik operasional real-time
Untuk demo pengantar OLTP Dalam Memori, lihat:
- Rilis: in-memory-oltp-demo-v1.0
- Kode sumber: in-memory-oltp-demo-source-code
1. Pasang sampel In-Memory OLTP
Anda dapat membuat AdventureWorksLT
database sampel dengan beberapa langkah di portal Azure. Kemudian, gunakan langkah-langkah di bagian ini untuk menambahkan objek OLTP Dalam Memori ke database Anda AdventureWorksLT
dan menunjukkan manfaat performa.
Langkah-langkah penginstalan
Di portal Azure, buat database Premium (DTU) atau Business Critical (vCore) di server logis. Atur Sumber ke
AdventureWorksLT
database sampel. Untuk instruksi terperinci, lihat Membuat database pertama Anda di Azure SQL Database.Sambungkan ke database dengan SQL Server Management Studio (SSMS).
Salin skrip In-Memory OLTP Transact-SQL ke clipboard Anda. Skrip T-SQL membuat objek dalam memori yang diperlukan dalam
AdventureWorksLT
database sampel yang Anda buat di langkah 1.Tempelkan skrip T-SQL ke SSMS, lalu jalankan skrip. Klausul
MEMORY_OPTIMIZED = ON
dalamCREATE TABLE
pernyataan sangat penting. Contohnya:CREATE TABLE [SalesLT].[SalesOrderHeader_inmem]( [SalesOrderID] int IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED ..., ... ) WITH (MEMORY_OPTIMIZED = ON);
Kesalahan 40536
Jika Anda mendapatkan kesalahan 40536 saat menjalankan skrip T-SQL, jalankan skrip T-SQL berikut untuk memverifikasi apakah database mendukung objek dalam memori:
SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsXTPSupported');
Hasilnya 0
berarti bahwa OLTP Dalam Memori tidak didukung, dan 1
berarti OLTP didukung. OLTP Dalam Memori tersedia di tingkat Azure SQL Database Premium (DTU) dan Business Critical (vCore).
Tentang item memori yang dioptimalkan yang dibuat
Tabel: Sampel berisi tabel memori yang dioptimalkan berikut:
SalesLT.Product_inmem
SalesLT.SalesOrderHeader_inmem
SalesLT.SalesOrderDetail_inmem
Demo.DemoSalesOrderHeaderSeed
Demo.DemoSalesOrderDetailSeed
Anda dapat memfilter untuk memperlihatkan hanya tabel yang dioptimalkan memori di Object Explorer di SSMS. Saat Anda mengklik kanan Tabel, lalu navigasi ke> Filter>Pengaturan>Filter Dioptimalkan Memori. Nilainya sama dengan 1
.
Atau Anda bisa mengkueri tampilan katalog, seperti:
SELECT is_memory_optimized, name, type_desc, durability_desc
FROM sys.tables
WHERE is_memory_optimized = 1;
Prosedur tersimpan yang dikompilasi secara asli: Anda dapat memeriksa SalesLT.usp_InsertSalesOrder_inmem
melalui kueri tampilan katalog:
SELECT uses_native_compilation, OBJECT_NAME(object_id) AS module_name, definition
FROM sys.sql_modules
WHERE uses_native_compilation = 1;
2. Jalankan sampel beban kerja OLTP
Satu-satunya perbedaan antara dua prosedur tersimpan berikut adalah bahwa prosedur pertama menggunakan tabel yang dioptimalkan memori, sementara prosedur kedua menggunakan tabel on-disk reguler:
SalesLT.usp_InsertSalesOrder_inmem
SalesLT.usp_InsertSalesOrder_ondisk
Di bagian ini, Anda akan melihat cara menggunakan ostress.exe
utilitas untuk menjalankan dua prosedur tersimpan. Anda dapat membandingkan berapa lama waktu yang dibutuhkan kedua eksekusi stres untuk selesai.
Menginstal utilitas dan ostress RML
Sebaiknya, Anda harus berjalan ostress.exe
pada komputer virtual (VM) Azure. Anda akan membuat Azure VM di wilayah Azure yang sama tempat database Anda AdventureWorksLT
berada. Anda juga dapat berjalan ostress.exe
di komputer lokal Anda sebagai gantinya jika Anda dapat tersambung ke database Azure SQL Anda. Namun, latensi jaringan antara komputer Anda dan database di Azure dapat mengurangi manfaat performa OLTP Dalam Memori.
Pada VM, atau pada host apa pun yang Anda pilih, instal utilitas Replay Markup Language (RML). Utilitas termasuk ostress.exe
.
Untuk informasi selengkapnya, lihat:
- Diskusi
ostress.exe
dalam Sampel Database untuk OLTP Dalam Memori. - Sampel Database untuk In-Memory OLTP.
Skrip untuk ostress.exe
Bagian ini menampilkan skrip T-SQL yang disematkan di baris perintah kami ostress.exe
. Skrip ini menggunakan item yang dibuat oleh skrip T-SQL yang Anda pasang sebelumnya.
Saat Anda menjalankan ostress.exe
, kami sarankan Anda meneruskan nilai parameter yang dirancang untuk menekankan beban kerja menggunakan kedua strategi berikut:
- Jalankan sejumlah besar koneksi bersamaan, dengan menggunakan
-n100
. - Buat setiap koneksi berulang ratusan kali, dengan menggunakan
-r500
.
Namun, Anda mungkin ingin memulai dengan nilai yang jauh lebih kecil seperti -n10
dan -r50
untuk memastikan bahwa semuanya berfungsi.
Skrip berikut ini menyisipkan contoh pesanan penjualan dengan lima item baris ke dalam tabel memori yang dioptimalkan berikut:
SalesLT.SalesOrderHeader_inmem
SalesLT.SalesOrderDetail_inmem
DECLARE
@i int = 0,
@od SalesLT.SalesOrderDetailType_inmem,
@SalesOrderID int,
@DueDate datetime2 = sysdatetime(),
@CustomerID int = rand() * 8000,
@BillToAddressID int = rand() * 10000,
@ShipToAddressID int = rand() * 10000;
INSERT INTO @od
SELECT OrderQty, ProductID
FROM Demo.DemoSalesOrderDetailSeed
WHERE OrderID= cast((rand()*60) as int);
WHILE (@i < 20)
BEGIN;
EXECUTE SalesLT.usp_InsertSalesOrder_inmem @SalesOrderID OUTPUT,
@DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @od;
SET @i = @i + 1;
END
Untuk membuat versi _ondisk dari skrip T-SQL sebelumnya untuk ostress.exe
, Anda akan mengganti kedua kemunculan substring _inmem dengan _ondisk. Penggantian ini mempengaruhi nama tabel dan prosedur yang tersimpan.
Jalankan beban kerja stres _inmem terlebih dahulu
Anda dapat menggunakan jendela Prompt Cmd RML untuk menjalankan ostress.exe
. Parameter baris perintah mengarahkan ostress ke:
- Jalankan 100 koneksi secara serentak (-n100).
- Setiap koneksi menjalankan skrip T-SQL 50 kali (-r50).
ostress.exe -n100 -r50 -S<servername>.database.windows.net -U<login> -P<password> -d<database> -q -Q"DECLARE @i int = 0, @od SalesLT.SalesOrderDetailType_inmem, @SalesOrderID int, @DueDate datetime2 = sysdatetime(), @CustomerID int = rand() * 8000, @BillToAddressID int = rand() * 10000, @ShipToAddressID int = rand()* 10000; INSERT INTO @od SELECT OrderQty, ProductID FROM Demo.DemoSalesOrderDetailSeed WHERE OrderID= cast((rand()*60) as int); WHILE (@i < 20) begin; EXECUTE SalesLT.usp_InsertSalesOrder_inmem @SalesOrderID OUTPUT, @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @od; set @i += 1; end"
Untuk menjalankan baris perintah sebelumnya ostress.exe
:
Atur ulang konten data database dengan menjalankan perintah berikut di SSMS, untuk menghapus semua data yang dimasukkan oleh eksekusi sebelumnya:
EXECUTE Demo.usp_DemoReset;
Salin teks baris perintah sebelumnya
ostress.exe
ke clipboard Anda.<placeholders>
Ganti untuk parameter-S -U -P -d
dengan nilai yang benar.Jalankan baris perintah yang Anda edit di jendela RML Cmd.
Hasilnya adalah durasi
Ketika ostress.exe
selesai, durasi eksekusi ditulis sebagai baris output terakhir di jendela RML Cmd. Misalnya, eksekusi uji coba yang lebih singkat berlangsung sekitar 1,5 menit:
11/12/15 00:35:00.873 [0x000030A8] OSTRESS exiting normally, elapsed time: 00:01:31.867
Reset, edit untuk _ondisk, lalu jalankan ulang
Setelah Anda mendapatkan hasil dari eksekusi _inmem, lakukan langkah-langkah berikut untuk eksekusi _ondisk:
Reset database dengan menjalankan perintah berikut di SSMS untuk menghapus semua data yang dimasukkan oleh eksekusi sebelumnya:
EXECUTE Demo.usp_DemoReset;
ostress.exe
Edit baris perintah untuk mengganti semua _inmem dengan _ondisk.Jalankan ulang
ostress.exe
untuk kedua kalinya, dan ambil hasil durasi.Sekali lagi, reset database.
Hasil perbandingan yang diharapkan
Pengujian OLTP Dalam Memori kami telah menunjukkan bahwa performa meningkat sebanyak sembilan kali untuk beban kerja yang sederhana ini, dengan berjalan pada Azure VM di wilayah Azure yang sama dengan ostress.exe
database.
3. Instal sampel analitik dalam memori
Pada bagian ini, Anda membandingkan IO dan hasil statistik antara saat Anda menggunakan indeks penyimpan kolom dengan indeks b-tree tradisional.
Untuk analitik real-time pada beban kerja OLTP, sebaiknya menggunakan indeks penyimpan kolom non-kelompok. Untuk detailnya, lihat Penjelasan indeks penyimpan kolom.
Menyiapkan uji analitik columnstore
Gunakan portal Azure untuk membuat database baru
AdventureWorksLT
dari sampel. Gunakan tujuan layanan apa pun yang mendukung indeks penyimpan kolom.Salin sql_in-memory_analytics_sample ke clipboard Anda.
- Skrip T-SQL membuat objek yang diperlukan dalam
AdventureWorksLT
database sampel yang Anda buat di langkah 1. - Skrip membuat tabel dimensi dan dua tabel fakta. Setiap tabel fakta masing-masing diisi dengan 3,5 juta baris.
- Pada tujuan layanan yang lebih kecil, skrip mungkin membutuhkan waktu 15 menit atau lebih lama untuk diselesaikan.
- Skrip T-SQL membuat objek yang diperlukan dalam
Tempelkan skrip T-SQL ke SSMS, lalu jalankan skrip. Kata kunci COLUMNSTORE dalam
CREATE INDEX
pernyataan sangat penting:CREATE NONCLUSTERED COLUMNSTORE INDEX ...;
Atur
AdventureWorksLT
ke tingkat kompatibilitas terbaru, SQL Server 2022 (160):ALTER DATABASE AdventureworksLT SET compatibility_level = 160;
Tabel kunci dan indeks penyimpan kolom
dbo.FactResellerSalesXL_CCI
adalah tabel yang memiliki indeks penyimpan kolom berkluster, yang memiliki pemadatan tingkat lanjut di tingkat data .dbo.FactResellerSalesXL_PageCompressed
adalah tabel yang memiliki indeks terkluster reguler yang setara, hanya dikompresi di tingkat halaman .
4. Kueri kunci untuk membandingkan indeks penyimpan kolom
Ada beberapa jenis kueri T-SQL yang bisa Anda jalankan untuk melihat peningkatan kinerja. Pada langkah ke-2 dalam skrip T-SQL, perhatikan sepasang kueri ini. Mereka hanya berbeda pada satu baris:
FROM FactResellerSalesXL_PageCompressed AS a
FROM FactResellerSalesXL_CCI AS a
Indeks penyimpan kolom berkluster ada di FactResellerSalesXL_CCI
tabel.
Skrip T-SQL berikut mencetak aktivitas I/O logis dan statistik waktu, menggunakan SET STATISTICS IO dan MENGATUR WAKTU STATISTIK untuk setiap kueri.
/*********************************************************************
Step 2 -- Overview
-- Page compressed BTree table vs Columnstore table performance differences
-- Enable actual query plan in order to see Plan differences when executing.
*/
-- Ensure the database uses the latest compatibility level
ALTER DATABASE AdventureworksLT SET compatibility_level = 160
GO
-- Execute a typical query that joins the fact table with dimension tables.
-- Note this query will run on the page compressed table. Note down the time.
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT c.Year
,e.ProductCategoryKey
,FirstName + ' ' + LastName AS FullName
,COUNT(SalesOrderNumber) AS NumSales
,SUM(SalesAmount) AS TotalSalesAmt
,AVG(SalesAmount) AS AvgSalesAmt
,COUNT(DISTINCT SalesOrderNumber) AS NumOrders
,COUNT(DISTINCT a.CustomerKey) AS CountCustomers
FROM FactResellerSalesXL_PageCompressed AS a
INNER JOIN DimProduct AS b ON b.ProductKey = a.ProductKey
INNER JOIN DimCustomer AS d ON d.CustomerKey = a.CustomerKey
INNER JOIN DimProductSubCategory AS e on e.ProductSubcategoryKey = b.ProductSubcategoryKey
INNER JOIN DimDate AS c ON c.DateKey = a.OrderDateKey
GROUP BY e.ProductCategoryKey,c.Year,d.CustomerKey,d.FirstName,d.LastName
GO
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO
-- This is the same query on a table with a clustered columnstore index (CCI).
-- The comparison numbers are the more pronounced the larger the table is (this is an 11 million row table).
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT c.Year
,e.ProductCategoryKey
,FirstName + ' ' + LastName AS FullName
,COUNT(SalesOrderNumber) AS NumSales
,SUM(SalesAmount) AS TotalSalesAmt
,AVG(SalesAmount) AS AvgSalesAmt
,COUNT(DISTINCT SalesOrderNumber) AS NumOrders
,COUNT(DISTINCT a.CustomerKey) AS CountCustomers
FROM FactResellerSalesXL_CCI AS a
INNER JOIN DimProduct AS b ON b.ProductKey = a.ProductKey
INNER JOIN DimCustomer AS d ON d.CustomerKey = a.CustomerKey
INNER JOIN DimProductSubCategory AS e on e.ProductSubcategoryKey = b.ProductSubcategoryKey
INNER JOIN DimDate AS c ON c.DateKey = a.OrderDateKey
GROUP BY e.ProductCategoryKey,c.Year,d.CustomerKey,d.FirstName,d.LastName
GO
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO
Dalam database yang menggunakan tujuan layanan P2, Anda dapat mengharapkan sekitar sembilan kali perolehan performa untuk kueri ini dengan menggunakan indeks penyimpan kolom berkluster dibandingkan dengan indeks rowstore tradisional. Dengan tujuan layanan P15, Anda dapat mengharapkan sekitar 57 kali perolehan performa dengan menggunakan indeks penyimpan kolom.
Konten terkait
- Mulai Cepat 1: Teknologi OLTP Dalam Memori untuk Performa T-SQL yang lebih cepat
- Gunakan OLTP Dalam Memori untuk meningkatkan performa aplikasi Anda
- Memantau penyimpanan OLTP Dalam Memori
- Blog: OLTP Dalam Memori di Azure SQL Database
- OLTP Dalam Memori
- Panduan Indeks Penyimpan Kolom
- Analitik operasional real time dengan indeks penyimpan kolom
- Artikel teknis: OLTP Dalam Memori – Pola Beban Kerja Umum dan Pertimbangan Migrasi di SQL Server 2014