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

作者:计算机教程

在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

本文由nba买球发布,转载请注明来源

关键词: