内存中 OLTP 概述和使用方案

  • 项目

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例

内存中 OLTP 是 SQL Server 和 SQL 数据库中用于优化事务处理、数据引入、数据加载和瞬态数据方案性能的核心技术。 本文概述了内存中 OLTP 的技术和使用方案。 使用该信息可确定内存中 OLTP 是否适合你的应用程序。 本文末尾部分给出的示例介绍了内存中 OLTP 对象、性能演示引用以及资源引用，可供后续步骤使用。

内存中 OLTP 概述

对于合适的工作负荷，In-Memory OLTP 可提供显著的性能增益。 虽然在某些情况下，客户可实现高达 30 倍的性能增益，但是增益的多少取决于工作负荷。

那么是如何实现性能增益的呢？ 本质上，内存中 OLTP 通过提高数据访问和事务执行的效率和移除并发执行事务间的锁闩争用，来提升事务处理的性能。 内存中 OLTP 速度快不是因为它在内存中，而是因为针对内存中的数据进行了优化。 数据存储、访问和处理算法经完全重新设计，以此来充分利用内存中和高并发计算的最新增强功能。

现在，数据位于内存中并不就意味着发生故障时会丢失数据。 默认情况下，所有事务皆为完全耐久事务，这意味着 SQL Server 中的其他任何表可获得相同的耐久性保证：作为事务提交的一部分，所有更改会被写入到磁盘上的事务日志中。 如果在事务提交后的任何时候发生故障，当数据库重新联机时数据仍在其原来的位置。 此外，内存中 OLTP 可与 SQL Server 的所有高可用性和灾难恢复功能搭配使用，例如可用性组、故障转移群集实例、备份/还原等。

若要在数据库中使用内存中 OLTP，应使用以下一个或多个对象类型：

  • 内存优化表 用于存储用户数据。 在创建时声明一个要进行内存优化的表。
  • 非持久表 用于瞬态数据，即用于缓存，或用于中间结果集（替代传统的临时表）。 非持久表是一种使用 DURABILITY=SCHEMA_ONLY 声明的内存优化表，也就是说，对这些表所做的更改不会引发任何 IO。 在不需要考虑持续性的情况下，这可以避免使用日志 IO 资源。
  • 内存优化表类型用于表值参数 (TVP) 和存储过程中的中间结果集。 可以使用内存优化表类型取代传统表类型。 使用内存优化表类型声明的表变量和 TVP 会继承非持久内存优化表的优点：数据访问效率高，且不会引发 IO。
  • 本机编译的 T-SQL 模块 可用于通过减少处理操作所需的 CPU 周期，进一步减少单个事务所需的时间。 在创建时声明一个要本机编译的 Transact-SQL 模块。 此时，可本机编译以下 T-SQL 模块：存储过程、触发器和标量用户定义的函数。

内存中 OLTP 内置于 SQL Server 和 SQL 数据库中。 由于这些对象与其传统对象行为相似，因此仅对数据库和应用程序作出最小的更改，通常便可获得性能优势。 此外，还可将内存优化表和基于磁盘的传统表同时置于同一数据库，然后在二者间运行查询。 请参阅本文后面的每种对象类型的 Transact-SQL 脚本示例。

内存中 OLTP 使用方案

内存中 OLTP 并非一个魔力加速按钮，也并不适合所有工作负载。 例如，如果大多数查询对大范围数据执行聚合，则内存优化表实际上并不会降低 CPU 使用率。 列存储索引适用于这种情况。

注意

已知问题：对于具有内存优化表的数据库，执行不带恢复的事务日志备份，然后执行带恢复执行事务日志还原，可能会导致数据库还原过程无响应。 此问题还会影响日志传送功能。 要解决此问题，可在启动还原过程前重启 SQL Server 实例。

以下是方案和应用程序模式列表，其中展示了客户通过使用内存中 OLTP 获得了成功。

高吞吐量和低延迟事务处理

我们创建内存中 OLTP 正是为了此核心方案：支持大量事务，且各个事务延迟低而稳定。

常见工作负荷方案是：金融工具交易、体育博彩、移动游戏和广告投放。 我们注意到的另一种常见模式是会频繁读取和/或更新的“目录”。 例如，假如有多个文件，每个文件分布于多个群集节点上，则需在内存优化表中对每个文件的每个分片的位置编写目录。

实施注意事项

将内存优化表用于核心事务表，即包含性能要求最高的事务的表。 使用本机编译的存储过程来优化执行与商业事务关联的逻辑。 放入数据库中存储过程中的逻辑越多，则内存中 OLTP 带来的益处越大。

在现有应用程序中开始使用：

  1. 使用事务性能分析报表来确定要迁移的对象。
  2. 使用内存优化顾问和本机编译顾问帮助进行迁移。

数据引入，包括 IoT（物联网）

内存中 OLTP 擅长同时从多个不同的来源同时引入大量数据。 与其他目标相比，将数据引入到 SQL Server 数据库通常更有利，因为 SQL Server 会加快数据的查询速度，让你可以获得实时见解。

常见的应用程序模式是：

  • 引入传感器读数和事件，并允许通知和历史分析。
  • 管理来自多个来源的批量更新，同时将对并发读取工作负荷的影响降至最低。

实施注意事项

对数据引入使用内存优化表。 如果引入主要包括插入（而非更新），且需考虑到数据的内存中 OLTP 存储占用，则请

  • 通过使用执行 INSERT INTO <disk-based table> SELECT FROM <memory-optimized table> 的作业，定期将数据批量卸载到具有群集列存储索引且基于磁盘的表中；或者
  • 使用 临时内存优化表 管理历史数据 - 在此模式下，历史数据则驻留在磁盘上，并且数据移动由系统管理。

SQL Server 示例存储库包含一个智能网格应用程序，该应用程序使用临时内存优化表、内存优化表类型和本机编译的存储过程来提高数据引入速度，同时管理传感器数据的内存中 OLTP 存储占用：

缓存和会话状态

内存中 OLTP 技术使得 SQL Server 或 Azure SQL 数据库中的数据库引擎变成一个具有吸引力的平台，适用于维持会话状态（例如 ASP.NET 应用程序）以及缓存。

内存中 OLTP 的一个非常成功的用例便是 ASP.NET 会话状态。 通过 SQL Server，客户可实现每秒 120 万次的请求。 同时，客户已开始将内存中 OLTP 用于企业中所有中间层应用程序的缓存需求。 详细信息： bwin 如何使用 SQL Server 2016 (13.x) 内存中 OLTP 达到前所未有的性能和规模）

实施注意事项

通过将 BLOB 存储在 varbinary(max) 列中，可将非持久内存优化表用作简单的键值存储。 或者，可通过 SQL Server 和 SQL 数据库中的 JSON 支持实现半结构化缓存。 最后，可通过具有完整关系架构的非持久表（包括各种数据类型和约束）来创建完整的关系缓存。

通过利用 GitHub 上发布的脚本替换由内置 SQL Server 会话状态提供程序创建的对象，开始使用内存优化 ASP.NET 会话状态：aspnet-session-state

客户案例研究

Tempdb 对象替换

使用非持久表和内存优化表类型替换传统的基于 tempdb 的结构，例如临时表、表变量和表值参数 (TVP)。

与传统表变量和 #temp 表相比，内存优化表变量和非持久表通常会降低 CPU 使用率，并完全删除日志 IO。

实施注意事项

若要开始使用，请参阅：使用内存优化改进临时表和表变量性能。

客户案例研究

ETL（提取、转换、加载）

ETL 工作流通常包括将数据加载到临时表、转换数据和将数据加载到最终表。

将非持久内存优化表用于数据暂存。 非持久内存优化表会完全删除所有 IO，使数据访问更加高效。

实施注意事项

如果工作流中要在临时表上执行转换，可使用本机编译的存储过程来加速转换进度。 如果可以并行执行此转换，则内存优化会扩大优势。

示例脚本

开始使用内存中 OLTP 前，需要创建一个 MEMORY_OPTIMIZED_DATA 文件组。 此外，我们建议使用数据库兼容级别 130（或更高级别），并将数据库选项 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT 设置为 ON。

可使用下面的脚本在默认数据文件夹中创建文件组，然后配置推荐设置：

下面的示例脚本介绍了可在数据库中创建的内存中 OLTP 对象：

首先，为内存中 OLTP 配置数据库。

-- configure recommended DB option
ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON;
GO

可以创建具有不同持续性的表：

-- memory-optimized table
CREATE TABLE dbo.table1
( c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
  c2 NVARCHAR(MAX))
WITH (MEMORY_OPTIMIZED=ON);
GO
-- non-durable table
CREATE TABLE dbo.temp_table1
( c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
  c2 NVARCHAR(MAX))
WITH (MEMORY_OPTIMIZED=ON,
      DURABILITY=SCHEMA_ONLY);
GO

可以将表类型创建为内存中表。

-- memory-optimized table type
CREATE TYPE dbo.tt_table1 AS TABLE
( c1 INT IDENTITY,
  c2 NVARCHAR(MAX),
  is_transient BIT NOT NULL DEFAULT (0),
  INDEX ix_c1 HASH (c1) WITH (BUCKET_COUNT=1024))
WITH (MEMORY_OPTIMIZED=ON);
GO

可以创建本机编译的存储过程。 有关详细信息，请参阅从数据访问应用程序调用本机编译的存储过程。

-- natively compiled stored procedure
CREATE PROCEDURE dbo.usp_ingest_table1
  @table1 dbo.tt_table1 READONLY
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
    WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT,
          LANGUAGE=N'us_english')

  DECLARE @i INT = 1

  WHILE @i > 0
  BEGIN
    INSERT dbo.table1
    SELECT c2
    FROM @table1
    WHERE c1 = @i AND is_transient=0

    IF @@ROWCOUNT > 0
      SET @i += 1
    ELSE
    BEGIN
      INSERT dbo.temp_table1
      SELECT c2
      FROM @table1
      WHERE c1 = @i AND is_transient=1

      IF @@ROWCOUNT > 0
        SET @i += 1
      ELSE
        SET @i = 0
    END
  END

END
GO
-- sample execution of the proc
DECLARE @table1 dbo.tt_table1;
INSERT @table1 (c2, is_transient) VALUES (N'sample durable', 0);
INSERT @table1 (c2, is_transient) VALUES (N'sample non-durable', 1);
EXECUTE dbo.usp_ingest_table1 @table1=@table1;
SELECT c1, c2 from dbo.table1;
SELECT c1, c2 from dbo.temp_table1;
GO

相关内容