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

發行項
09/27/2024

適用於： Azure SQL 資料庫

Azure SQL 資料庫的記憶體內部技術可讓您改善應用程式的效能，還可降低資料庫成本。使用 Azure SQL 資料庫的記憶體內部技術，您可順利改善各種工作負載的效能。

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

如需詳細資訊，請參閱

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

如需記憶體內部 OLTP 的簡介示範，請參閱：

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

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

在 Azure 入口網站只要幾個步驟，即可建立 AdventureWorksLT 範例資料庫。然後，本章節中的步驟會說明，如何新增記憶體內部 OLTP 物件至您的 AdventureWorksLT 資料庫，並示範效能優點。

安裝步驟

在 Azure 入口網站中，於邏輯伺服器建立 [進階 (DTU)] 或 [業務關鍵 (vCore) 資料庫]。將 [來源] 設定為 AdventureWorksLT 範例資料庫。如需詳細指示，請參閱在 Azure SQL 資料庫中建立您的第一個資料庫。
使用 SQL Server Management Studio (SSMS)連接到資料庫。
將 In-Memory OLTP Transact-SQL 指令碼複製到剪貼簿。 T-SQL 指令碼會在步驟 1 建立的 AdventureWorksLT 範例資料庫中，建立所需的記憶體內部物件。

將 T-SQL 指令碼貼到 SSMS 中，然後執行該指令碼。 CREATE TABLE 陳述式中的 MEMORY_OPTIMIZED = ON 子句非常重要。例如：

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 表示不支援記憶體內部 OLTP，而 1 則表示提供支援。 Azure SQL 資料庫進階版 (DTU) 和業務關鍵 (虛擬核心) 階層提供了記憶體內部 OLTP。

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

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

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) AS module_name, 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。您會在 AdventureWorksLT 資料庫的相同 Azure 區域建立 Azure VM。如果您可以連線到 Azure SQL 資料庫，您可改為在本機電腦上執行 ostress.exe。不過，您的電腦與 Azure 中資料庫之間的網路延遲可能會降低記憶體內部 OLTP 的效能優勢。

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

如需詳細資訊，請參閱

記憶體內部 OLTP 的範例資料庫中的 ostress.exe 討論。
記憶體內部 OLTP 的範例資料庫。

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 Cmd 提示 視窗來執行 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 命令列的文字複製到剪貼簿。
以正確的值取代參數 -S -U -P -d 的 <placeholders>。
在 [RML 命令] 視窗中執行已編輯的命令列。

結果是持續時間

當 ostress.exe 完成時，它會在 RML 命令視窗中寫入執行持續時間做為輸出的最後一行。例如，較短的測試回合持續大約 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，並擷取持續時間結果。
再次重設資料庫。

預期的比較結果

就這個過度簡單的工作負載而言，我們的「記憶體內部 OLTP」測試顯示當 ostress.exe 是在與資料庫相同 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 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

在服務物件為 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. 用來比較資料行存放區索引的重要查詢

相關內容

意見反應

其他資源