Azure SQL 托管实例中的内存中示例

项目
08/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 或选择的任何主机上，安装重放标记语言 (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 版本，请将两处出现的 _inmem 子字符串替换为 _ondisk。这种替换将影响表和存储过程的名称。

首先运行 _inmem 压力工作负荷

可以使用 RML 命令提示符 窗口来运行 ostress.exe 命令行。命令行参数将指示 ostress：

同时运行 100 个连接 (-n100)。
每个连接运行 T-SQL 脚本 50 次 (-r50)。

ostress.exe -n100 -r50 -S<servername>.database.chinacloudapi.cn -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 Cmd 窗口中运行编辑后的命令行。

结果是一个持续时间

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 命令行，将所有的 _inmem 替换为 _ondisk。
再次重新运行 ostress.exe，并捕获持续时间结果。
再次重置数据库（可靠地删除大量测试数据）。

预期比较结果

在与数据库处于同一 Azure 区域的 Azure VM 上运行 ostress 时，内存中测试已显示此简化工作负荷大约有 9 倍的性能改善。

3.安装内存中分析示例

本部分比较使用列存储索引与传统 b 树索引时的 IO 和统计信息结果。

通常，在对 OLTP 工作负荷进行实时分析时，最好是使用非群集列存储索引。有关详细信息，请参阅列存储索引介绍。

准备列存储分析测试

将新的 AdventureWorksLT 数据库还原到 SQL 托管实例，并使用 WITH REPLACE 覆盖之前安装的现有数据库。

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 查询类型来查看性能改进情况。在 T-SQL 脚本的步骤 2 中，请注意这一对查询。这一对查询只是在一行上有所不同：

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 托管实例配置，使用聚集列存储索引与使用传统索引向比较，可得到显著的性能提升。

通过