数据库-SQL Server 2014 新特性之内存优化表--嘉为科技

方勇

【摘要】

2013年6月25日，微软发布了SQL Server 2014 CTP1。本文将为您介绍SQL Server 2014 的重点新特性——内存优化表。

【正文】

如果说SQL Server 2012 的数据库引擎最大的亮点是Always On的话，那么SQL Server 2014 最大的亮点就是内存优化表（Memory-optimized tables）了。

1. 内存表的背景

在SQL Server 2000 的年代，我们还可以通过这种方式，将表驻留在内存中：DBCC PINTABLE ( database_id , table_id )

DBCC PINTABLE 会导致将表读入到内存中。当表中的页由普通的Transact-SQL 语句读入到高速缓存中时，这些页将标记为内存驻留页。当SQL Server 需要空间以读入新页时，不会清空内存驻留页。

但是这种方式在SQL Server 2005 已经不被支持了，内存表这种概念消失了，直到SQL Server 2014。

2. 文件和存储

内存优化表必须存储到文件流文件组中，SQL Server 提供了一种MEMORY_OPTIMIZED_DATA 文件组类型专门用于指定内存优化表的逻辑存储位置。

内存优化表的文件流文件组可以包含一个或多个容器，每个容器有可以包含一个或多个

文件。文件包含了三种类型的文件：

●根文件（Root File）：包含了数据文件和增量文件的元数据

●数据文件（Data File）：存储内存优化表的记录和新插入的记录

●增量文件（Delta File）：按照事务日志顺序存储从内存优化表中删除的记录的

最小信息（行号），每个数据文件对应一个增量文件

内存优化表会使用到事务日志，同样任何增删改等操作都会写入日志，这可能是导致即使使用内存优化表，性能也无法显著提升的最大原因，可以考虑使用闪存或者SSD来解决该问题

3. 原理和机制

SQL Server 2014 新增的内存优化表让我们眼前一亮，可以改善基于磁盘的表的低性能。通过以下的原理和机制让我们获得更好的性能和可扩缩性：

●通过数据页和索引页驻留在内存，减少IO瓶颈

●采用乐观并发控制，消除了逻辑锁，提高了并发性

●本机编译存储过程，执行效率更高

我们一定会担心，使用内存优化表会不会导致因为驻留在内存中，系统宕机或者断电的时候，导致数据无法及时写回内存而丢失。内存优化表保留了关系型数据库的事务所有ACID 特征：原子性、一致性、隔离性和持久性。SQL Server和内存优化表的持久化上下文提供了以下保证：

●事务持久化：提交DDL或DML更改内存优化表的事务，更改是永久性的(不

丢失)；

●重启持久化：在系统崩溃恢复或计划重新启动后，内存优化表重新实例化以恢

复到关闭或崩溃时的状态；

●介质失败持久化：当磁盘损坏时，我们可以通过数据库的备份和还原来恢复内

存优化表到新存储

当然，内存优化表有两种持久化选项，其中有一种是不保证持久化的：

●SCHEMA_ONLY（非持久化表）：只持久化表结构和索引，重启后所有数据丢

●SCHEMA_AND_DATA（持久化表）：持久化结构和数据，类似于基于磁盘的表

接下来我们来分析一下增删改操作的流程：

●增、删、改操作执行先写入事务；

●插入操作：先插入到内存中，在CheckPoint 的时才写回数据文件

●删除操作：先在内存中删除，并记录行号到Delta文件，随后Delta文件中记

录的删除会合并到数据文件

●更新操作：更新操作在SQL Serer 中以先删除、然后插入执行

可以看到，SQL Server 的内存表主要改善的是查询性能和IO瓶颈。

4. 硬件和软件要求：

●在SQL Server 2014 x64 版中，因为x64版可以直接使用更多的内存空间

●服务器必须有足够的内存，内存优化表不能使用超过最大服务器内存的80%

5. 内存优化表使用和语法

内存优化表也不是可以随便使用的，也有着其限制条件或越发约束。

1) 创建数据库需要有一个内存优化表文件组：

CREATE DATABASE CanwayDemo

ON

PRIMARY(NAME = [hekaton_demo_data],

FILENAME = 'C:\DATA\hekaton_demo_data.mdf', size=500MB)

, FILEGROUP [hekaton_demo_fg] CONTAINS MEMORY_OPTIMIZED_DATA(

NAME = [hekaton_demo_dir],

FILENAME = 'C:\DATA\hekaton_demo_dir')

LOG ON (name = [hekaton_demo_log], Filename='C:\DATA\hekaton_demo_log.ldf', size=500MB)

COLLATE Latin1_General_100_BIN2;

GO

2) 创建表时必须创建一个非空的主键，并且必须建立非聚集的Hash索引和

BUCKET_COUNT参数

Use CanwayDemo

Go

CREATE TABLE Destination

(

col1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH

(BUCKET_COUNT = 100000),

col2 INT NOT NULL,

col3 INT NOT NULL

) WITH (MEMORY_OPTIMIZED = ON, DURABILITY =

SCHEMA_AND_DATA)

GO

3) 创建一个本机编译存储过程访问内存优化表

Use CanwayDemo

Go

CREATE PROCEDURE SelectT able_Destination

WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER

AS

BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT,

LANGUAGE = N'us_english')

SELECT col1, col2, col3 FROM dbo.Destination;

END

GO