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

适用于：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 数据库，并演示性能优势。

安装步骤

1. 通过 Azure 门户，在服务器上创建一个高级 (DTU) 或业务关键 (vCore) 数据库。 将“源”设置为 AdventureWorksLT 示例数据库。 有关详细说明，请参阅在 Azure SQL 数据库中创建第一个数据库。

2. 使用 SQL Server Management Studio (SSMS) 连接到该数据库。

3. 将内存中 OLTP Transact-SQL 脚本复制到剪贴板。 T-SQL 脚本在步骤 1 创建的 AdventureWorksLT 示例数据库中创建所需的内存中对象。

4. 将 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”表示不支持内存中，结果“1”表示支持。 内存中技术在 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), 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。

在 VM 或选择的任何主机上，安装重放标记语言 (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 版本，请将两处出现的 _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 命令行：

1. 请在 SSMS 中运行以下命令重置数据库数据内容，以删除前面运行的命令所插入的所有数据：

   EXECUTE Demo.usp_DemoReset;
   

2. 将上述 ostress.exe 命令行的文本复制到剪贴板。

3. 将参数 -S -U -P -d 的 <placeholders> 替换为正确的实数值。

4. 在 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 运行执行以下步骤：

1. 在 SSMS 中运行以下命令重置数据库，删除前面运行的命令所插入的所有数据：

   EXECUTE Demo.usp_DemoReset;
   

2. 编辑 ostress.exe 命令行，将所有的 _inmem 替换为 _ondisk。

3. 再次重新运行 ostress.exe，并捕获持续时间结果。

4. 再次重置数据库（可靠地删除大量测试数据）。

预期比较结果

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

3.安装内存中分析示例

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

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

准备列存储分析测试

1. 使用 Azure 门户基于示例创建全新的 AdventureWorksLT 数据库。

   • 使用相同的名称。
   • 选择任一“高级”服务层级。

2. 将 sql_in-memory_analytics_sample 复制到剪贴板。

   • T-SQL 脚本在步骤 1 创建的 AdventureWorksLT 示例数据库中创建所需的内存中对象。
   • 该脚本将创建维度表和两个事实表。 每个事实表中填充了 350 万行。
   • 该脚本可能需要 15 分钟才能完成。

3. 将 T-SQL 脚本粘贴到 SSMS，并执行该脚本。 CREATE INDEX 语句中的 COLUMNSTORE 关键字至关重要：CREATE NONCLUSTERED COLUMNSTORE INDEX ...;

4. 将 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

在采用 P2 定价层的数据库中，与传统索引相比，使用聚集列存储索引时，此查询预期可获得约 9 倍的性能提升。 对于 P15，使用列存储索引有望获得约 57 倍的性能提升。

相关内容

• 快速入门 1：通过内存中 OLTP 技术加速 T-SQL 性能
• 使用内存中 OLTP 改善应用程序性能
• 监视内存中 OLTP 存储
• 博客：Azure SQL 数据库中的内存中 OLTP
• 内存中 OLTP
• 列存储索引
• 列存储索引的实时运营分析
• 技术文章：内存中 OLTP - SQL Server 2014 中的常见工作负载模式和迁移注意事项