Azure SQL 資料庫中的記憶體內部範例

發行項
03/12/2024

適用於：Azure SQL Database

Azure 中的記憶體內部技術 SQL 資料庫可讓您改善應用程式的效能，並可能降低資料庫的成本。藉由在 Azure SQL 資料庫中使用記憶體內部技術，您可以透過各種工作負載來達成效能改善。

在本文中，您會看到兩個範例，說明如何使用記憶體內部 OLTP，以及 Azure SQL 資料庫中的數據行存放區索引。

如需詳細資訊，請參閱

記憶體內部 OLTP 概觀和使用案例（包括客戶案例研究的參考和入門資訊）
記憶體內部 OLTP 的檔
資料行存放區索引指南
混合式交易/分析處理 (HTAP)，也稱為即時作業分析

如需更簡單但更具視覺吸引力的記憶體內部 OLTP 效能示範，請參閱：

版本︰in-memory-oltp-demo-v1.0
原始程式碼︰in-memory-oltp-demo-source-code

1.安裝記憶體內部 OLTP 範例

您可以使用 Azure 入口網站中的幾個步驟來建立AdventureWorksLT範例資料庫。然後，本節中的步驟說明如何使用記憶體內部 OLTP 物件擴充資料庫 AdventureWorksLT ，並示範效能優點。

安裝步驟

在 Azure 入口網站中，在伺服器上建立進階版（DTU）或業務關鍵（虛擬核心）資料庫。將 [ 來源 ] 設定為 AdventureWorksLT 範例資料庫。如需詳細指示，請參閱在 Azure SQL Database 中建立您的第一個資料庫。
使用連線資料庫SQL Server Management Studio （SSMS）。
將記憶體內部 OLTP Transact-SQL 腳本複製到剪貼簿。 T-SQL 腳本會在您在步驟 1 中建立的 AdventureWorksLT 範例資料庫中，建立必要的記憶體內部物件。
將 T-SQL 指令碼貼到 SSMS 中，然後執行該指令碼。 MEMORY_OPTIMIZED = ON語句中的 CREATE TABLE 子句非常重要。例如：

CREATE TABLE [SalesLT].[SalesOrderHeader_inmem](
    [SalesOrderID] int IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED ...,
    ...
) WITH (MEMORY_OPTIMIZED = ON);

錯誤 40536

如果您在執行 T-SQL 腳稿時收到錯誤 40536，請執行下列 T-SQL 腳本來驗證資料庫是否支援記憶體內部物件：

SELECT DatabasePropertyEx(DB_Name(), 'IsXTPSupported');

結果 0 表示記憶體內部不受支援，表示 1 支援記憶體內部。記憶體內部技術可在 Azure SQL 資料庫進階版（DTU）和業務關鍵（虛擬核心）層中使用。

關於已建立的記憶體最佳化項目

資料表：此範例包含下列記憶體最佳化資料表：

SalesLT.Product_inmem
SalesLT.SalesOrderHeader_inmem
SalesLT.SalesOrderDetail_inmem
Demo.DemoSalesOrderHeaderSeed
Demo.DemoSalesOrderDetailSeed

您可以篩選，只顯示 SSMS 中物件總管的記憶體優化數據表。當您以滑鼠右鍵按兩下 [資料表]，然後流覽至> [篩選>篩選] 設定> [記憶體優化]。 值等於 1。

或者您可以查詢目錄檢視，例如：

SELECT is_memory_optimized, name, type_desc, durability_desc
    FROM sys.tables
    WHERE is_memory_optimized = 1;

原生編譯的預存程式：您可以透過目錄檢視查詢進行檢查 SalesLT.usp_InsertSalesOrder_inmem ：

SELECT uses_native_compilation, OBJECT_NAME(object_id), definition
    FROM sys.sql_modules
    WHERE uses_native_compilation = 1;

2.執行範例 OLTP 工作負載

下列兩個預存程序的唯一差別在於第一個程序會使用記憶體最佳化資料表版本，而第二個程序會使用一般磁碟資料表：

SalesLT.usp_InsertSalesOrder_inmem
SalesLT.usp_InsertSalesOrder_ondisk

在本節中，您會了解如何使用便利的 ostress.exe 公用程式，在壓力層級執行兩個預存程序。您可以比較完成兩個壓力回合所需的時間。

安裝 RML 公用程式和 ostress

您最好規劃在 Azure 虛擬機器 (VM) 上執行 ostress.exe。您會在資料庫的相同 Azure 區域中AdventureWorksLT建立 Azure VM。但是，只要您可以連線到 Azure SQL 資料庫，就可以改為在本機工作站上執行ostress.exe。

在 VM 上或你選擇的任何主機上，安裝 Replay Markup Language (RML) 公用程式。這些公用程式包括 ostress.exe。

如需詳細資訊，請參閱

適用於記憶體內部 OLTP 的範例資料庫中ostress.exe討論。
記憶體內部 OLTP 的範例資料庫。
安裝 ostress.exe 的部落格。

Ostress.exe 的指令碼

本節顯示 ostress.exe 命令列中內嵌的 T-SQL 指令碼。此指令碼會使用您稍早安裝的 T-SQL 指令碼所建立的項目。

當您執行ostress.exe時，建議您使用下列兩種策略傳遞設計來強調工作負載的參數值：

使用 -n100執行大量的並行連線。
讓每個連接使用 -r500重複數百次。

不過，您可能想要從較小的值開始，例如 -n10 ， -r50 並確保一切正常運作。

下列指令碼會在下列記憶體最佳化資料表中插入有 5 個細項的範例銷售訂單：

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

若要針對 ostress.exe 製作上述 T-SQL 指令碼的 _ondisk 版本，請以 _ondisk 取代兩個出現的 _inmem 子字串。這類取代會影響資料表和預存程序的名稱。

先執行 _inmem 壓力工作負載

您可以使用 RML 命令提示字元視窗來執行 ostress.exe 命令列。命令行參數會將 ostress 導向：

同時執行 100 個連線 (-n100)。
每個連線會執行 T-SQL 指令碼 50 次 (-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"

若要執行上述的 ostress.exe 命令列：

在 SSMS 中執行下列命令來重設資料庫資料內容，以刪除先前執行插入的所有資料：
```
EXECUTE Demo.usp_DemoReset;
```
將上述 ostress.exe 命令列的文字複製到剪貼簿。
<placeholders>將參數-S -U -P -d的取代為正確的實值。
在 [RML 命令] 視窗中執行已編輯的命令列。

結果是持續時間

當ostress.exe完成時，它會將執行持續時間寫入為 RML Cmd 視窗中的最後一行輸出。例如，較短的測試回合持續大約 1.5 分鐘：

11/12/15 00:35:00.873 [0x000030A8] OSTRESS exiting normally, elapsed time: 00:01:31.867

重設，針對 _ondisk 編輯，然後重新執行

在獲得 _inmem 執行的結果之後，請針對 _ondisk 執行回合執行下列步驟：

在 SSMS 中執行下列命令來重設資料庫，以刪除先前執行插入的所有資料：
```
EXECUTE Demo.usp_DemoReset;
```
編輯 ostress.exe 命令列，以 _ondisk 取代所有的 _inmem。
第二次重新執行 ostress.exe，並擷取持續時間結果。
再次重設資料庫 (以負責刪除可能的大量測試資料)。

預期的比較結果

我們的記憶體內部測試顯示，此簡單工作負載的效能已改善九倍，且 ostress 在與資料庫位於相同 Azure 區域中的 Azure VM 上執行。

3.安裝記憶體內部分析範例

在本節中，您將比較使用資料行存放區索引與使用傳統 B 型樹狀結構索引時的 IO 和統計資料結果。

針對 OLTP 工作負載的即時分析，通常最好使用非叢集式資料行存放區索引。如需詳細資訊，請參閱已描述的資料行存放區索引。

準備資料行存放區分析測試

使用 Azure 入口網站從範例建立全新的AdventureWorksLT資料庫。
- 使用相同的名稱。
- 選擇任何進階服務層級。
將 sql_in-memory_analytics_sample 複製到剪貼簿。
- T-SQL 腳本會在您在步驟 1 中建立的 AdventureWorksLT 範例資料庫中，建立必要的記憶體內部物件。
- 腳本會建立維度數據表和兩個事實數據表。每個事實資料表會填入 350 萬個資料列。
- 此指令碼可能需要 15 分鐘才能完成。
將 T-SQL 指令碼貼到 SSMS 中，然後執行該指令碼。 語句中的 CREATE INDEX COLUMNSTORE 關鍵詞非常重要：CREATE NONCLUSTERED COLUMNSTORE INDEX ...;
設定 AdventureWorksLT 為最新的相容性層級 SQL Server 2022 （160）： ALTER DATABASE AdventureworksLT SET compatibility_level = 160;

重要資料表和資料行存放區索引

dbo.FactResellerSalesXL_CCI是具有叢集數據行存放區索引的數據表，其具有數據層級的進階壓縮。
dbo.FactResellerSalesXL_PageCompressed 是具有對等一般叢集索引的數據表，它只會 在頁面 層級壓縮。

4.比較數據行存放區索引的索引鍵查詢

有您可以執行的數種 T-SQL 查詢類型可用來查看效能改進。在步驟 2 的 T-SQL 指令碼中，請注意這一組查詢。其中的不同之處只有一行：

FROM FactResellerSalesXL_PageCompressed AS a
FROM FactResellerSalesXL_CCI AS a

叢集數據行存放區索引位於數據表中 FactResellerSalesXL_CCI 。

下列 T-SQL 腳本會針對每個查詢使用 SET STATISTICS IO 和 SET STATISTICS TIME 來列印邏輯 I/O 活動和時間統計數據。

/*********************************************************************
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

在定價層為 P2 的資料庫中，相較於傳統索引，使用叢集資料行存放區索引進行此查詢預期可提升大約九倍的效能。使用 P15 時，您可以預期使用資料行存放區索引大約可提升 57 倍的效能。

Azure SQL 資料庫中的記憶體內部範例

1.安裝記憶體內部 OLTP 範例

安裝步驟

錯誤 40536

關於已建立的記憶體最佳化項目

2.執行範例 OLTP 工作負載

安裝 RML 公用程式和 ostress

Ostress.exe 的指令碼

先執行 _inmem 壓力工作負載

結果是持續時間

重設，針對 _ondisk 編輯，然後重新執行

預期的比較結果

3.安裝記憶體內部分析範例

準備資料行存放區分析測試

重要資料表和資料行存放區索引

4.比較數據行存放區索引的索引鍵查詢

意見反應

意見反應

其他資源

Azure SQL 資料庫 中的記憶體內部範例

1.安裝記憶體內部 OLTP 範例

安裝步驟

錯誤 40536

關於已建立的記憶體最佳化項目

2.執行範例 OLTP 工作負載

安裝 RML 公用程式和 ostress

Ostress.exe 的指令碼

先執行 _inmem 壓力工作負載

結果是持續時間

重設，針對 _ondisk 編輯，然後重新執行

預期的比較結果

3.安裝記憶體內部分析範例

準備資料行存放區分析測試

重要資料表和資料行存放區索引

4.比較數據行存放區索引的索引鍵查詢

相關內容

意見反應

意見反應

其他資源

Azure SQL 資料庫中的記憶體內部範例