Azure SQL 数据库中的内存中示例

项目
11/11/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 表示支持。内存中 OLTP 在 Azure SQL 数据库 Premium (DTU) 和业务关键 (vCore) 层中提供。

关于创建的内存优化项

表：此示例包含以下内存优化表：

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 或选择的任何主机上，安装重放标记语言 (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 版本，请将两处出现的 _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，并捕获持续时间结果。
再次重置数据库。

预期比较结果

我们的内存中 OLTP 测试表明，当 ostress.exe 在与数据库位于同一 Azure 区域的 Azure 虚拟机上运行时，这种简单的工作负载的性能提高了 9 倍。

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 查询类型来查看性能改进情况。在 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 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 服务目标的数据库中，与传统行存储索引相比，使用聚集列存储索引时，此查询预期可获得约 9 倍的性能提升。对于 P15 服务目标，使用列存储索引有望获得约 57 倍的性能提升。

通过