Azure SQL 受控執行個體中的記憶體內部樣本

發行項
07/12/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 範例資料庫

您可以使用 SQL Server Management Studio (SSMS) 中的幾個 T-SQL 步驟來還原 AdventureWorksLT 範例資料庫。如需將資料庫還原至 SQL 受控執行個體的詳細資訊，請參閱快速入門：使用 SSMS 將資料庫還原至 Azure SQL 受控執行個體。

然後，請參照本節中的步驟，其中說明了如何使用記憶體內部 OLTP 物件擴充 AdventureWorksLT 資料庫，並示範效能優點。

開啟 SSMS 並連線您的 SQL 受控執行個體。

注意

您可以從內部部署工作站或 Azure VM 安全地連線到 Azure SQL 受控執行個體，而不需要開啟公用存取權。請參閱快速入門：設定從內部部署連線至 Azure SQL 受控執行個體的點對站連線或快速入門：設定 Azure VM 以連線到 Azure SQL 受控執行個體。
在 [物件總管] 中，以滑鼠右鍵按一下受控執行個體，然後選取 [新增查詢]，開啟新查詢視窗。
執行下列 T-SQL 陳述式，其會使用公開可用的預先設定儲存體容器和共用存取簽章金鑰，在您的 SQL 受控執行個體中建立認證。使用公開可用的儲存體時，不需要 SAS 簽章。
```
CREATE CREDENTIAL [https://mitutorials.blob.core.windows.net/examples/]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE';
```

執行下列陳述式來還原 AdventureWorksLT 資料庫範例。

RESTORE DATABASE [AdventureWorksLT] 
FROM URL = 'https://mitutorials.blob.core.windows.net/examples/AdventureWorksLT2022.bak';

執行下列陳述式來追蹤還原程序狀態。

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');

當還原程序完成時，請在 [物件總管] 中檢視 AdventureWorksLT 資料庫。您可以使用 sys.dm_operation_status 檢視來確認 AdventureWorksLT 資料庫是否已還原。

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

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

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。您會在與 SQL 受控執行個體相同的 Azure 區域中建立 Azure VM。但是，只要您可以連線到 Azure SQL 受控執行個體，就可以改為在本機工作站上執行 ostress.exe。

在 VM 上或你選擇的任何主機上，安裝 Replay Markup Language (RML) 公用程式。這些公用程式包括 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 工作負載的即時分析，通常最好使用非叢集式資料行存放區索引。如需詳細資訊，請參閱已描述的資料行存放區索引。

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

使用 WITH REPLACE 將新的 AdventureWorksLT 資料庫還原至 SQL 受控執行個體，覆寫您先前安裝的現有資料庫。

RESTORE DATABASE [AdventureWorksLT] 
FROM URL = 'https://mitutorials.blob.core.windows.net/examples/AdventureWorksLT2022.bak'
WITH REPLACE;

將 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

視 SQL 受控執行個體組態而定，相較於傳統索引，可以使用叢集式資料行存放區索引可以大幅提升此查詢的效能。

共用方式為

Azure SQL 受控執行個體中的記憶體內部樣本

1. 還原記憶體內部 OLTP 範例資料庫

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

2. 執行範例 OLTP 工作負載

安裝 RML 公用程式和 ostress

Ostress.exe 的指令碼

先執行 _inmem 壓力工作負載

結果是持續時間

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

預期的比較結果

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

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

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

4. 用來比較資料行存放區索引的重要查詢

意見反應

意見反應

其他資源

共用方式為

Azure SQL 受控執行個體中的記憶體內部樣本

1. 還原記憶體內部 OLTP 範例資料庫

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

2. 執行範例 OLTP 工作負載

安裝 RML 公用程式和 ostress

Ostress.exe 的指令碼

先執行 _inmem 壓力工作負載

結果是持續時間

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

預期的比較結果

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

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

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

4. 用來比較資料行存放區索引的重要查詢

相關內容

意見反應

意見反應

其他資源