关于 SQLSERVER 的 内存中(OLTP) 功能的探索记录.



一. 概览

1.0 应用场景

• 高吞吐量和低延迟事务处理（支持大量事务，且各个事务延迟低而稳定）。

• 必须同时处理大量 SQL INSERT 的系统最适合采用 OLTP 功能。

  速度可提升 5 倍到超过 20 倍不等。

• 处理 Transact-SQL 中的繁重计算的系统很适合采用此功能。

  专用于繁重计算的存储过程可提速高达 99 倍。

• 如果大多数查询针对大范围数据执行聚合，则内存优化表实际上并不会降低 CPU 使用率（应该使用列存储）。

1.1 可用性

• 通过以下语句检查数据库是否支持内存中(OLTP)技术。 对于SQL Azure，则只有升级到“高级”或“业务关键”定价层才可以支持。

    SELECT DatabasePropertyEx(DB_Name(), 'IsXTPSupported');
    -- 1:支持 0:不支持
  

1.2 内存优化表 (memory-optimized table)

内存优化表实际上包含一张磁盘表和一张内存表，对内存表的操作最终都会持久化到磁盘表上，即使在服务器重启后，内存中的数据不会丢失。且两张表对于用户和应用程序来说是不可见的，对外只表现为一张表（数据和索引等都在内存中）。

• 通过在 CREATE TABLE 语句之后添加 WITH (MEMORY_OPTIMIZED=ON) 来创建一个内存优化表

    CREATE TABLE DEMO (  
       [Id] INT IDENTITY PRIMARY KEY NONCLUSTERED,
       [Name] NVARCHAR(64)
    ) WITH (MEMORY_OPTIMIZED=ON)  
    GO
  

• 内存优化表不支持 聚集索引，需要在 PRIMARY KEY 字段上手动指定使用 非聚集索引。

• 内存优化表不支持 计算列。
• 内存优化表不支持 外键。
• 内存优化表不支持 TRUNCATE 语句。
• 内存优化表不支持 WITH(READPAST) 表提示。
• 内存优化表不支持将任何列标记为 rowversion。
• 不能 MERGE INTO 内存表。
• 不能对主键列的值进行 UPDATE，只能删除原有行并插入新行。
• 不能对内存优化表使用CREATE INDEX和DROP INDEX的方式创建索引，只能在创建表时指定，或通过 ALTER TABLE...ADD/DROP INDEX 的方式指定索引。

• 内存优化表支持跨数据事务，但内存优化表变量可以在跨库查询中使用。

  更多限制条件参见：不受支持的 T-SQL 与 不支持的功能

1.3 非持久化的内存表 (non-durable table)

• 通过在 CREATE TABLE 语句之后添加 WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY) 来创建一个非持久化的内存优化表

   CREATE TABLE DEMO (  
      [Id] INT IDENTITY PRIMARY KEY NONCLUSTERED,
      [Name] NVARCHAR(64)
   ) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY)  
   GO
  

• 非持久化的内存表主要用来代替临时表，可以避免占用日志和IO资源。

1.4 内存优化表类型 (memory-optimized table type)

• 通过在 CREATE TYPE {Name} AS TABLE 语句之后添加 WITH (MEMORY_OPTIMIZED=ON) 来创建一个内存优化表类型

    CREATE TYPE DEMO AS TABLE(  
       [Id] INT IDENTITY,
       [Name] NVARCHAR(64)
    ) WITH (MEMORY_OPTIMIZED=ON)  
    GO
  

• 可用于替代传统表类型，通常用于表值参数 (TVP) 和存储过程中间结果集的存储（表变量）

• 同样可以避免占用日志和IO资源。

1.5 本机编译的 T-SQL 模块 (natively compiled T-SQL)

• 适用于 存储过程 / 触发器 / 用户自定义标量值函数

• 通过减少处理操作所需的 CPU 周期，以进一步减少单个事务所需的时间。

  本机编译T-SQL模块所需的持续时间是解释持续时间的 1/100。

• 本机模块只能引用内存优化表，而不能引用基于磁盘的表。

• 通过 WITH NATIVE_COMPILATION, SCHEMABINDING 来创建本机编译的T-SQL模块：

    -- PROCEDURE
    CREATE PROCEDURE dbo.usp_Demo
         @TargetId INT
    WITH NATIVE_COMPILATION, SCHEMABINDING
    AS
    BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE = N'English')
        -- PROCEDURE BODY
    END
    GO
  
    -- FUNCTION
    CREATE FUNCTION [dbo].[ufnLeadingZeros_native](@Value int)   
    RETURNS varchar(8)   
    WITH NATIVE_COMPILATION, SCHEMABINDING  
    AS   
    BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')  
        -- FUNCTION BODY
        RETURN (@ReturnValue)
    END
    GO
  

• ATOMIC 原子块：BEGIN ATOMIC 属于 ANSI SQL 标准，只有在本机编译T-SQL模块中适用。

  原子块在事务内执行（以原子方式）。 或者块中的整个语句都成功，或者整个块都将回滚到在块的开始处创建的保存点。 此外，会话设置对于原子块而言是固定的。 以不同设置在会话中执行相同的原子块将导致相同的行为，与当前会话的设置无关。参见 ATOMIC块。

• SCHEMABINDING 表示：除非先删除本机过程，否则不能删除本机过程中引用的表。

• 本机模块的编译和重新编译：

  在过程创建时进行初始编译；在数据库或服务器重新启动后首次执行该过程时进行自动重新编译；使用以下语句进行手动编译。

    -- @objname nvarchar(776): 当前库中存储过程、触发器、表、视图、自定义函数的限定/未限定的名称
    -- 如果对象是存储过程、触发器、自定义函数的名称，则他们将在下次运行时重新编译。
    -- 如果对象是表名或视图名，则所有引用该表或视图的存储过程、触发器或用户定义函数将在下次运行时重新编译。
    -- return: 0（成功）或非零数字（失败）
    EXEC sp_recompile [ @objname = ] 'object'
  

• 内存表和本机编译存储过程将会被编译为DLL，通常情况下不需要手动管理。参见内存表和存储过程的编译。

• 限制条件：

  • 本机编译的存储过程支持使用 TRY、 CATCH和 THROW 构造进行错误处理，不支持RAISERROR。
  • 本机编译的存储过程中 TOP 表达式要返回的行数不能超过8000.
  • 本机编译的存储过程只支持 Nested Loops Join，不支持 Merge Join 和 Hash Join。

• 最佳实践：

  • 在执行聚合、嵌套、循环、多条记录的增删改查时效率最高，只操作单条记录并没有显著的效率提升。
  • 使传递给过程的参数类型与过程定义中的类型相匹配。
  • 在调用本机编译的存储过程时使用序数（无名称）参数。 要实现最高效的执行，请勿使用命名参数。

---

二. 在表分区模式下混合使用内存优化表与传统磁盘表

适用场景：对近期较新数据的访问比较频繁，但对很久之前的老数据的访问频率很低。

按照 微软官方文档 的描述，需要保证磁盘表和内存优化表的表结构相同（仅限于字段，不包括索引等，因为其本质上还是两张表）。步骤如下：

1. 根据已经存在的News表（已做表分区），创建表结构相同的内存优化表，eg: News_Hot
2. 再创建一张传统磁盘表，表结构仍然需要保持一致，eg: News_Staging
3. 将原有磁盘表 News 重命名为 News_Cold

4. 创建视图 News，关联 News_Hot 与 News_Cold，查询时使用此视图，这样不需要改变程序中的表名。

   可以在视图中添加列Hot BIT(1) DEFAULT(0)。

   待确认：是否需要根据冷热数据分别查询，全部使用视图是否会影响效率
5. 做 INSERT 操作时将数据写入内存优化表 News_Hot

6. 如果知道数据位于内存优化表中，则可以使用本机编译存储过程访问和操作数据，否则，则只能使用常规T-SQL将将内存优化表与已分区的磁盘表进行联接。

   待确认：连接后是否可以直接进行UPDATE操作？是否只能先确认数据位置，然后进行UPDATE操作？

7. 冷热数据切换

   7.1. 将内存优化表中的部分冷数据插入临时表中（使用截止日期或自增Id）。 7.2. 从内存优化表中删除相同数据。 7.3. 在磁盘表中新建分区，并将临时表中的数据写入磁盘表中。

   待确认：是否可以使用已存在的预设分区（e.g. 每10W个Id设置一个分区）？

---

三. 迁移

3.1 迁移步骤

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

3. 将数据库的兼容级别至少设置为 130。

    -- 查询数据库兼容级别
    SELECT compatibility_level FROM sys.databases WHERE name = Db_Name()
    -- 设置数据库兼容级别
    ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 130
   

4. 设置事务隔离级别

   当事务同时涉及基于磁盘的表和内存优化表时，我们将该事务称为跨容器事务。 在此类事务中，事务的内存优化部分必须以名为SNAPSHOT的事务隔离级别运行。

    ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON
   

5. 创建内存优化文件组 (Azure SQL 跳过此步骤)

   参见 创建内存优化文件组

   Azure SQL 数据库并不支持此操作。

6. 手动编写脚本将磁盘表转换为内存表

   此过程中，表必须处于脱机状态

   1. 挂起应用程序活动。
   2. 执行完整备份。
   3. 对基于磁盘的表进行重命名。
   4. 发出 CREATE TABLE 语句以创建新的内存优化表。
   5. 从基于磁盘的表使用嵌套 SELECT 插入 (INSERT INTO) 内存优化表。
   6. 删除 (DROP) 基于磁盘的表。
   7. 执行另一个完整备份。
   8. 继续应用程序活动。