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

SQL Server2014新特性之内存优化表

方勇

【摘要】

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

【正文】

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

1. 内存表的背景

在SQLServer 2000的年代我们还可以通过这种方式将表驻留在内存中

DB CC PINTABLE(database_id, table_id)

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

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

2.文件和存储

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

内存优化表的文件流文件组可以包含一个或多个容器每个容器有可以包含一个或多个文件。文件包含了三种类型的文件

 根文件Root File 包含了数据文件和增量文件的元数据

 数据文件Data File 存储内存优化表的记录和新插入的记录

 增量文件Delta File 按照事务日志顺序存储从内存优化表中删除的记录的

最小信息行号 每个数据文件对应一个增量文件

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

3.原理和机制

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

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

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

 本机编译存储过程执行效率更高

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

 事务持久化提交DDL或DML更改内存优化表的事务更改是永久性的(不

丢失) 

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

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

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

存优化表到新存储

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

 S CHEMA_ONLY非持久化表 只持久化表结构和索引重启后所有数据丢

失

 SCHEMA_AND_DATA持久化表 持久化结构和数据类似于基于磁盘的表接下来我们来分析一下增删改操作的流程

 增、删、 改操作执行先写入事务

 插入操作先插入到内存中在CheckPoint的时才写回数据文件

 删除操作先在内存中删除并记录行号到D elta文件随后D elta文件中记录的删除会合并到数据文件

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

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

4.硬件和软件要求

 在SQL Server 2014 x64版中 因为x64版可以直接使用更多的内存空间 服务器必须有足够的内存 内存优化表不能使用超过最大服务器内存的80%

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

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

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

CREATE DATABASE CanwayDemo

ON

PRIMARY(NAME=[hekaton_demo_data],

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

, [hekaton_demo_fg]CONTAINS MEMORY_OPTIMIZED_DATA(

N AM E=[h e k a to n_d e m o_dir],

='C:\DATA\hekaton_demo_dir')

LOG ON (name = [hekaton_demo_log], '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 =S CHEMA_AND_DATA)

GO

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

Use CanwayDemo

Go

CREATE PROCEDURE SelectTable_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