使用内存中 OLTP 改善 SQL 数据库中应用程序的性能

内存中 OLTP 可以用来改善高级 Azure SQL 数据库中的事务处理、数据引入的性能以及暂时性数据状况,且不需要提高定价层。

请按照以下步骤在现有数据库中采用内存中 OLTP。

步骤 1:确保使用的是高级数据库

只有高级数据库才支持内存中 OLTP。 如果返回的结果为 1(不是 0),则支持内存中 OLTP:

SELECT DatabasePropertyEx(Db_Name(), 'IsXTPSupported');

XTP 代表极端事务处理

步骤 2:标识要迁移到 In-Memory OLTP 的对象

SSMS 包含可以针对具有活动工作负荷的数据库运行的“事务性能分析概述”。 该报告识别要迁移到内存中 OLTP 的候选表和存储过程。

若要在 SSMS 中生成报告,请执行以下操作:

  • 在“对象资源管理器” 中,右键单击你的数据库节点。
  • 单击“报表” > “标准报表” > “事务性能分析概述”。

有关详细信息,请参阅确定表或存储过程是否应移植到内存中 OLTP

步骤 3:创建可比较的测试数据库

假设报告指出数据库的某个表在转换成内存优化的表后会带来好处。 我们建议先进行测试,以确认这项指示。

你需要创建生产数据库的测试副本。 测试数据库应该位于与生产数据库相同的服务层级别。

为了简化测试,请按以下方式调整测试数据库:

  1. 使用 SSMS 连接到测试数据库。
  2. 若要避免在查询中用到 WITH (SNAPSHOT) 选项,请按照以下 T-SQL 语句中所示设置数据库选项:

    ALTER DATABASE CURRENT
     SET
         MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
    

步骤 4:迁移表

必须创建并填充想要测试的表的内存优化副本。 你可以使用以下方式之一来创建该副本:

  • SSMS 中提供便利的内存优化向导。
  • 手动 T-SQL。

SSMS 中提供的内存优化向导

若要使用此迁移选项,请执行以下操作:

  1. 使用 SSMS 连接到测试数据库。
  2. 在“对象资源管理器”中,右键单击该表,然后单击“内存优化顾问”。

    • 此时将显示“表内存优化顾问”向导。
  3. 在向导中,单击“迁移验证”(或“下一步”按钮),查看该表是否包含任何在内存优化表中不受支持的功能。 有关详细信息,请参阅:

  4. 如果该表没有不受支持的功能,顾问可为你执行实际的架构和数据迁移。

手动 T-SQL

若要使用此迁移选项,请执行以下操作:

  1. 使用 SSMS(或类似的实用程序)连接到测试数据库。
  2. 获取表及其索引的完整 T-SQL 脚本。

    • 在 SSMS 中,右键单击你的表节点。
    • 单击“编写表脚本为” > “创建到” > “新建查询窗口”。
  3. 在脚本窗口中,将 WITH (MEMORY_OPTIMIZED = ON) 添加到 CREATE TABLE 语句。
  4. 如果存在 CLUSTERED 索引,请将其更改为 NONCLUSTERED。
  5. 使用 SP_RENAME 重命名现有表。
  6. 通过运行已编辑的 CREATE TABLE 脚本,创建新的内存优化表副本。
  7. 使用 INSERT...SELECT * INTO 将数据复制到内存优化表:
INSERT INTO <new_memory_optimized_table>
        SELECT * FROM <old_disk_based_table>;

步骤 5(可选):迁移存储过程

In-Memory 功能还可以修改存储过程,以改善性能。

本机编译存储过程的注意事项

本机编译存储过程的 T-SQL WITH 子句必须包含以下选项:

  • NATIVE_COMPILATION
  • SCHEMABINDING:表示除非丢弃存储过程,否则无法由存储过程以任何影响到存储过程的方式更改其列定义的表。

本机模块必须使用一个大型 ATOMIC 块进行事务管理。 显式 BEGIN TRANSACTION 或 ROLLBACK TRANSACTION 没有角色。 如果你的代码检测到违反业务规则,它可以使用 THROW 语句终止 ATOMIC 块。

本机编译存储过程的典型 CREATE PROCEDURE

创建本机编译存储过程的 T-SQL 通常类似于以下模板:

CREATE PROCEDURE schemaname.procedurename
    @param1 type1, …
    WITH NATIVE_COMPILATION, SCHEMABINDING
    AS
        BEGIN ATOMIC WITH
            (TRANSACTION ISOLATION LEVEL = SNAPSHOT,
            LANGUAGE = N'your_language__see_sys.languages'
            )
        …
        END;
  • 对于 TRANSACTION_ISOLATION_LEVEL,SNAPSHOT 是本机编译存储过程最常用的值。 但是,也支持其他值的子集:

    • REPEATABLE READ
    • SERIALIZABLE
  • sys.languages 视图中必须存在 LANGUAGE 值。

如何迁移存储过程

迁移步骤如下:

  1. 获取常规解释的存储过程的 CREATE PROCEDURE 脚本。
  2. 重写其标头以符合前面的模板。
  3. 确认存储过程 T-SQL 代码是否使用了任何不支持本机编译存储过程的功能。 根据需要实施应对措施。

  4. 使用 SP_RENAME 重命名旧存储过程。 或直接将它删除。
  5. 运行已编辑的 CREATE PROCEDURE T-SQL 脚本。

步骤 6:在测试环境中运行工作负荷

在测试数据库中,运行与在生产数据库中运行的工作负荷类似的工作负荷。 这样,将 In-Memory 功能用于表和存储过程所达到的性能改善应可体现出来。

工作负荷的主要属性包括:

  • 并发连接数。
  • 读/写比率。

若要修改并运行测试工作负荷,请考虑使用此处所示的 ostress.exe 便利工具。

为了尽可能减少网络延迟,请在数据库所在的同一 Azure 地理区域运行测试。

步骤 7:实施后的监视

建议你监视在生产环境中实施 In-Memory 后的性能影响:

相关链接