本文共 2675 字，大约阅读时间需要 8 分钟。

在Disk-Base数据库中，由于临时表和表变量的数据存储在tempdb中，频繁的创建和更新操作会导致大量IO操作集中在tempdb上，可能成为系统性能的瓶颈。在SQL Server 2016的内存优化数据库中，可以通过使用内存优化表类型来存储临时表和表值参数的数据，从而消除IO操作的负担，充分发挥大内存的优势，显著提升数据库性能。

在SQL Server 2016中，内存优化表类型（Memory-Optimized Table Type）允许直接在内存中存储表变量和表值参数的数据，避免了IO操作的开销。虽然不能直接在内存中创建临时表，但可以通过Row-Level-Security（RLS）来实现类似的功能，将内存优化表转换为Session级别的临时表，确保数据的局部性和自动清空。

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

内存优化表类型具有以下显著特点：

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中创建内存优化表，但可以通过RLS来实现类似功能。

Step1，创建内存优化表（仅持久化表结构）

以下是创建内存优化表的示例代码：

CREATE TABLE dbo.SessionTempTable (
    Column1 INT NOT NULL,
    Column2 NVARCHAR(4000) NULL,
    SpidFilter SMALLINT NOT NULL DEFAULT (@@spid),
    INDEX ix_SpidFilter NONCLUSTERED (SpidFilter),
    -- 可选：索引为HASH类型
    -- INDEX ix_SpidFilter HASH (SpidFilter) WITH (BUCKET_COUNT = 64),
    CONSTRAINT CHK_spidFilter CHECK (SpidFilter = @@spid)
) 
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
GO

Step2，创建RLS控制访问权限

以下是创建RLS Predicate Function和Security Policy的示例代码：

-- 创建RLS授权schema
create schema rlsauthorization dbo;
-- 创建Predicate Function
create function rls.fn_SpidFilter(@SpidFilter smallint)
returns table
with schemabinding, nativeCompilation
as return
select 1 as fn_SpidFilter
where @SpidFilter = @@spid;
-- 创建Security Policy
create security policy rls.soSessionC_SpidFilter_Policy
add filter predicate rls.fn_SpidFilter(SpidFilter)
on dbo.SessionTempTable
with (STATE = ON);
GO

Step3，使用内存优化临时表

使用内存优化临时表时注意以下几点：

三、维护

1、如何清理临时表占用的内存空间

在异常终止Session时，可能会导致大量数据驻留在内存中。为此，可以通过以下步骤进行清理：

-- 创建用户RLSAdmin
create user RLSAdmin without login;
alter role db_owner add member RLSAdmin;
go

-- 允许RLSAdmin用户访问Base Table的所有数据行
create function rls.fn_SpidFilter(@SpidFilter smallint)
returns table
with schemabinding, nativeCompilation
as return
select 1 as fn_SpidFilter
where @SpidFilter = @@spid or User_Name() = 'RLSAdmin';
go

execute as RLSAdmin;
delete tempfrom dbo.SessionTempTable
where temp.SpidFilter = @@spid
and temp.session_id > 50;
revert;
go

结语

通过以上方法，可以在SQL Server 2016中实现内存优化表的使用，有效减少IO操作对tempdb的依赖，显著提升数据库性能。虽然管理稍显复杂，但收益远高于成本。

转载地址：http://iyhfk.baihongyu.com/