Sampel dalam memori di Azure SQL Managed Instance
Berlaku untuk: Azure SQL Managed Instance
Teknologi dalam memori di Azure SQL Managed Instance memungkinkan Anda meningkatkan performa aplikasi Anda, dan berpotensi mengurangi biaya database Anda. Dengan menggunakan teknologi dalam memori di Azure SQL Managed Instance, Anda dapat mencapai peningkatan performa dengan berbagai beban kerja.
Dalam artikel ini Anda akan melihat dua sampel yang mengilustrasikan penggunaan OLTP dalam memori, serta indeks penyimpan kolom di Azure SQL Managed Instance.
Untuk informasi selengkapnya, lihat:
- Gambaran Umum dan Skenario Penggunaan OLTP dalam memori (termasuk referensi ke 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 performa yang lebih sederhana, tetapi lebih menarik secara visual untuk OLTP dalam memori, lihat:
- Rilis: in-memory-oltp-demo-v1.0
- Kode sumber: in-memory-oltp-demo-source-code
1. Pulihkan database sampel OLTP dalam memori
Anda dapat memulihkan AdventureWorksLT
database sampel dengan beberapa langkah T-SQL di SQL Server Management Studio (SSMS). Untuk informasi selengkapnya tentang memulihkan database ke instans terkelola SQL Anda, lihat Mulai Cepat: Memulihkan database ke Azure SQL Managed Instance dengan SQL Server Management Studio.
Kemudian, langkah-langkah di bagian ini menjelaskan bagaimana Anda dapat memperkaya database Anda AdventureWorksLT
dengan objek OLTP dalam memori dan menunjukkan manfaat performa.
Buka SQL Server Management Studio dan sambungkan ke instans terkelola SQL Anda.
Catatan
Koneksi ke Azure SQL Managed Instance Anda dari stasiun kerja lokal Anda atau Azure VM dapat dibuat dengan aman, tanpa membuka akses publik. Pertimbangkan Mulai Cepat: Mengonfigurasi koneksi titik-ke-situs ke Azure SQL Managed Instance dari lokal atau Mulai Cepat: Mengonfigurasi Azure VM untuk menyambungkan ke Azure SQL Managed Instance.
Di SQL Server Object Explorer, klik kanan instans terkelola Anda, lalu pilih Kueri Baru untuk membuka jendela kueri baru.
Jalankan pernyataan T-SQL berikut, yang menggunakan kontainer penyimpanan yang telah dikonfigurasi sebelumnya yang tersedia untuk umum dan kunci tanda tangan akses bersama untuk membuat kredensial di instans terkelola SQL Anda. Dengan penyimpanan yang tersedia untuk umum, tidak diperlukan tanda tangan SAS.
CREATE CREDENTIAL [https://mitutorials.blob.core.windows.net/examples/] WITH IDENTITY = 'SHARED ACCESS SIGNATURE';
Jalankan pernyataan berikut untuk memulihkan database contoh
AdventureWorksLT
.RESTORE DATABASE [AdventureWorksLT] FROM URL = 'https://mitutorials.blob.core.windows.net/examples/AdventureWorksLT2022.bak';
Jalankan pernyataan berikut untuk melacak status proses pemulihan Anda.
SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete , dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE');
Saat proses pemulihan selesai, lihat
AdventureWorksLT
database di Object Explorer. Anda dapat memverifikasi bahwa database dipulihkanAdventureWorksLT
dengan menggunakan tampilan sys.dm_operation_status .
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 memeriksa tampilan katalog, misalnya:
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), definition
FROM sys.sql_modules
WHERE uses_native_compilation = 1;
2. Jalankan sampel beban kerja OLTP
Satu-satunya perbedaan antara kedua prosedur yang tersimpan berikut adalah prosedur pertama menggunakan versi tabel memori dioptimalkan, sementara prosedur kedua menggunakan tabel on-disk reguler:
SalesLT.usp_InsertSalesOrder_inmem
SalesLT.usp_InsertSalesOrder_ondisk
Pada bagian ini, Anda akan melihat cara menggunakan utilitas ostress.exe yang praktis untuk menjalankan kedua prosedur tersimpan pada tingkat yang penuh stres. Anda dapat membandingkan berapa lama waktu yang dibutuhkan kedua eksekusi stres untuk selesai.
Menginstal utilitas dan ostress RML
Idealnya, Anda berencana untuk menjalankan ostress.exe pada komputer virtual Azure (VM). Anda akan membuat Azure VM di wilayah Azure yang sama dengan instans terkelola SQL Anda. Tetapi Anda dapat menjalankan ostress.exe di stasiun kerja lokal Anda sebagai gantinya, selama Anda dapat terhubung ke instans terkelola Azure SQL Anda.
Pada VM, atau pada host mana pun yang Anda pilih, pasang utilitas Replay Markup Language (RML). Utilitas sudah termasuk ostress.exe.
Untuk informasi selengkapnya, lihat:
- Diskusi ostress.exe dalam Database Sampel untuk OLTP dalam memori.
- Database Sampel untuk OLTP dalam memori.
- Blog untuk memasang ostress.exe.
Skrip untuk ostress.exe
Bagian ini menampilkan skrip T-SQL yang disematkan pada baris perintah ostress.exe kami. 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 harus mengganti kedua substring _inmem dengan _ondisk. Penggantian ini mempengaruhi nama tabel dan prosedur yang tersimpan.
Jalankan beban kerja stres _inmem terlebih dahulu
Anda dapat menggunakan jendela RML Cmd Prompt untuk menjalankan baris perintah ostress.exe kami. 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 ostress.exe sebelumnya:
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 dari baris perintah ostress.exe sebelumnya ke clipboard Anda.
<placeholders>
Ganti untuk parameter-S -U -P -d
dengan nilai riil yang benar.Jalankan baris perintah yang Anda edit di jendela RML Cmd.
Hasilnya adalah durasi
Ketika ostress.exe selesai, ia menulis durasi eksekusi sebagai baris akhir outputnya di jendela Cmd RML. 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;
Edit baris perintah ostress.exe untuk mengganti semua _inmem dengan _ondisk.
Jalankan ostress.exe untuk kedua kalinya, dan simpan hasil durasinya.
Sekali lagi, reset database (untuk menghapus data tes yang mungkin dalam jumlah besar).
Hasil perbandingan yang diharapkan
Pengujian 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
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
Pulihkan database baru
AdventureWorksLT
ke instans terkelola SQL Anda, menimpa database yang sudah ada yang Anda instal sebelumnya, menggunakanWITH REPLACE
.RESTORE DATABASE [AdventureWorksLT] FROM URL = 'https://mitutorials.blob.core.windows.net/examples/AdventureWorksLT2022.bak' WITH REPLACE;
Salin sql_in-memory_analytics_sample ke clipboard Anda.
- Skrip T-SQL membuat objek dalam memori 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.
- Skrip mungkin membutuhkan waktu 15 menit untuk selesai.
- Skrip T-SQL membuat objek dalam memori 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, yang 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 dalam 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 v/s Columnstore table performance differences
-- Enable actual Query Plan in order to see Plan differences when Executing
*/
-- Ensure Database is in 130 compatibility mode
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 Prior query on a table with a clustered columnstore index CCI
-- The comparison numbers are even more dramatic the larger the table is (this is an 11 million row table only)
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
Bergantung pada konfigurasi instans terkelola SQL, Anda dapat mengharapkan perolehan performa yang signifikan untuk kueri ini dengan menggunakan indeks penyimpan kolom berkluster dibandingkan dengan indeks tradisional.
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
- 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