Share via

Sampel dalam memori di Azure SQL Database

  • Artikel

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.

Dalam artikel ini Anda akan melihat dua sampel yang mengilustrasikan penggunaan OLTP dalam memori, serta indeks penyimpan kolom di Azure SQL Database.

Untuk informasi selengkapnya, lihat:

Untuk demo performa yang lebih sederhana, tetapi lebih menarik secara visual untuk OLTP dalam memori, lihat:

1. Instal sampel OLTP dalam memori

Anda dapat membuat AdventureWorksLT database sampel dengan beberapa langkah di portal Azure. Kemudian, langkah-langkah di bagian ini menjelaskan bagaimana Anda dapat memperkaya database Anda AdventureWorksLT dengan objek OLTP dalam memori dan menunjukkan manfaat performa.

Langkah-langkah penginstalan

  1. Di portal Azure, buat database Premium (DTU) atau Business Critical (vCore) di server. Atur Sumber ke AdventureWorksLT database sampel. Untuk instruksi terperinci, lihat Membuat database pertama Anda di Azure SQL Database.

  2. Koneksi ke database dengan SQL Server Management Studio (SSMS).

  3. Salin skrip OLTP Transact-SQL dalam memori ke clipboard Anda. Skrip T-SQL membuat objek dalam memori yang diperlukan dalam AdventureWorksLT database sampel yang Anda buat di langkah 1.

  4. Tempelkan skrip T-SQL ke SSMS, lalu jalankan skrip. Klausul MEMORY_OPTIMIZED = ON dalam CREATE 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 dalam memori tidak didukung, dan 1 berarti didukung. Teknologi 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 navigasikan ke> Filter>Pengaturan> Memori Dioptimalkan. 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 dari database Anda AdventureWorksLT . Tetapi Anda dapat menjalankan ostress.exe di stasiun kerja lokal Anda sebagai gantinya, selama Anda dapat tersambung ke database 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:

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:

  1. Atur ulang konten data database dengan menjalankan perintah berikut di SSMS, untuk menghapus semua data yang dimasukkan oleh eksekusi sebelumnya:

    EXECUTE Demo.usp_DemoReset;

  2. Salin teks dari baris perintah ostress.exe sebelumnya ke clipboard Anda.

  3. <placeholders> Ganti untuk parameter -S -U -P -d dengan nilai riil yang benar.

  4. 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:

  1. Reset database dengan menjalankan perintah berikut di SSMS untuk menghapus semua data yang dimasukkan oleh eksekusi sebelumnya:

    EXECUTE Demo.usp_DemoReset;

  2. Edit baris perintah ostress.exe untuk mengganti semua _inmem dengan _ondisk.

  3. Jalankan ostress.exe untuk kedua kalinya, dan simpan hasil durasinya.

  4. 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

  1. Gunakan portal Azure untuk membuat database baru AdventureWorksLT dari sampel.

    • Gunakan nama yang sama.
    • Pilih tingkat layanan Premium mana saja.

  2. 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.

  3. Tempelkan skrip T-SQL ke SSMS, lalu jalankan skrip. Kata kunci COLUMNSTORE dalam CREATE INDEX pernyataan sangat penting: CREATE NONCLUSTERED COLUMNSTORE INDEX ...;

  4. 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

Dalam database dengan tingkat harga P2, kueri ini diharapkan mencapai sekitar sembilan kali lipat peningkatan performa dengan menggunakan indeks penyimpan kolom dibandingkan menggunakan indeks tradisional. Dengan P15, Anda dapat merasakan sekitar 57 kali lipat peningkatan performa dengan menggunakan indeks penyimpan kolom.

Konten terkait