In-Memory：在内存中创建临时表和表变量

时间：2021-11-24 15:08:37

在Disk-Base数据库中，由于临时表和表变量的数据存储在tempdb中，如果系统频繁地创建和更新临时表和表变量，大量的IO操作集中在tempdb中，tempdb很可能成为系统性能的瓶颈。在SQL Server 2016的内存（Memory-Optimized）数据库中，如果考虑使用内存优化结构来存储临时表，表变量，表值参数的数据，那么将完全消除IO操作的负载消耗，发挥大内存的优势，大幅提高数据库的性能。

在SQL Server 2016中，能够直接创建内存优化的表类型，表变量和表值参数的数据只存储在内存中；不能直接在内存中创建临时表，但是，SQL Server提供一个变通方法（Workaround），通过行级安全RLS（Row-Level-Security）控制，指定只有当前Session才能访问特定的数据，将内存优化表转换为Session级别的临时表，间接实现临时表的局部性和自动清空特性。

一，内存优化表类型（Memory-Optimized Table Type）

内存优化表类型定义的表变量，表值参数能够大幅提高效率（efficiency），有4个显著的特点：

  • 数据仅存储在内存中，在读写数据时，不会产生任何的IO消耗，消除了tempdb的竞争和利用率；
  • 必须有一个索引，Hash 或 Nonclustered 都行；每一个内存优化表必须创建一个索引；
  • 只需要指定启用内存优化：MEMORY_OPTIMIZED = ON，只持久化Schema；
  • 必须先创建表类型，后创建表值变量；

1，创建内存优化表类型

CREATE TYPE dbo.TypeTable

AS TABLE

(

Column1  INT NOT NULL,

Column2  VARCHAR(10) NOT NULL,

INDEX idxName　NONCLUSTERED(Column1)

)

WITH(MEMORY_OPTIMIZED = ON);

2，创建内存优化表变量

declare @Table dbo.TypeTable

二，创建“临时内存优化表”

在Disk-Base数据库中，局部临时表#temp的作用域是session，创建在tempdb中，一旦session生命周期结束，系统自动回收其存储空间。在SQL Server 2016中，不能直接在tempdb中创建内存优化表。要使用临时内存优化表，有一个变通的方法，在DB中创建内存优化表，通过Row-Level-Security控制Session能够访问的数据行，间接实现Session级别的临时表。

Step1，创建内存优化表，只持久化Table Schema

CREATE TABLE dbo.SessionTempTable

(

    Column1 INT NOT NULL,

    Column2 NVARCHAR(4000) NULL,

    SpidFilter SMALLINT NOT NULL　DEFAULT (@@spid),

    INDEX ix_SpidFiler NONCLUSTERED (SpidFilter),

    --INDEX ix_SpidFilter HASH (SpidFilter) WITH (BUCKET_COUNT = 64),

    CONSTRAINT CHK_soSessionC_SpidFilter CHECK ( SpidFilter = @@spid ),

)

WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

go

Step2，创建RLS，控制用户只能访问当前Session的数据

推荐为Predicate function 和 Security Policy创建单独的Schema，然后在该Schema下创建Predicate function 和 Security Policy，对于Predicate function必须使用 NATIVE_COMPLIATION选项创建。

create schema rls

authorization dbo;

CREATE FUNCTION rls.fn_SpidFilter

(@SpidFilter smallint)

RETURNS TABLE

WITH SCHEMABINDING , NATIVE_COMPILATION

AS

RETURN

    SELECT 1 AS fn_SpidFilter

    WHERE @SpidFilter = @@spid;

go

CREATE SECURITY POLICY rls.soSessionC_SpidFilter_Policy

ADD FILTER PREDICATE rls.fn_SpidFilter(SpidFilter)

ON dbo.SessionTempTable

WITH (STATE = ON);

go

Step3，使用内存优化临时表

  • 表名替换：使用 dbo.Temp 代替 #Temp；
  • 不能创建和删除临时表：
    • 移除代码“create table #temp”，使用“delete from dbo.Temp”子句取代，将旧数据清空；
    • 移除代码“drop table #temp”，建议使用 “delete from dbo.Temp” 子句，在当前Session结束前将当前Session产生的数据清空，节省内存空间；

虽然临时表的使用和管理有点麻烦，但是，这点麻烦和大幅的性能提升来比，微不足道，建议使用内存优化表来代替临时表，体验飞一般的速度。

三，维护

1，通过RLS来创建内存临时表，如何清理临时表占用的内存空间？

试想出现异常，在当前Session将海量数据插入到临时表之后，Session异常终止，此时，Session没有来得及清空（Purge）临时表中的数据，这些数据仍然驻留在内存中。如果这种异常出现的频率很高，那么会导致内存优化表消耗大量的系统内存，必须有机制来定期清理临时表占用的内存空间。

step1，创建一个用户，RLSAdmin，授予db_owner的权限

--create User

create user RLSAdmin without login;

alter role db_owner

add member RLSAdmin;

go

step2，修改Predicate Function，如果用户是RLSAdmin，允许访问Base Table的所有数据行；

--create predicate function

CREATE FUNCTION rls.fn_SpidFilter

(@SpidFilter smallint)

RETURNS TABLE

WITH SCHEMABINDING , NATIVE_COMPILATION

AS

RETURN

    SELECT 1 AS fn_SpidFilter

    WHERE @SpidFilter = @@spid or User_Name()='RLSAdmin';

go

step3，创建Schedule，定期检查数据库中的临时表，如果发现临时表中的存在未被清理的无效数据，那么删除该部分数据，释放内存。

execute as RLSAdmin;

delete temp

from dbo.SessionTempTable temp

left join sys.dm_exec_sessions s

    on temp.SpidFilter=s.session_id

        and s<>@@spid

        and s.session_id>50

where s.session_id is null; 

revert;

该脚本仅仅提供一种思路，在产品环境中，需要多测试，以防错误删除数据。

参考文档：

Memory-Optimized Table Variables

Faster temp table and table variable by using memory optimization

Improving temp table and table variable performance using memory optimization

CREATE TYPE (Transact-SQL)

Indexes for Memory-Optimized Tables

相关文章