原文:试试SQLSERVER2014的内存优化表

---

实验

第一个实验：内存表的简单使用

 

步骤1：创建数据库和MEMORY_OPTIMIZED_DATA文件组

注意ALTER DATABASE语句中的ADD FILEGROUP 语句包含文件组的名称(HekatonFG)和关键字CONTAINS MEMORY_OPTIMIZED_DATA

它会指导SQL Server去创建支持内存OLTP引擎所必需的文件组类型。

注意：每个数据库只能有一个MEMORY_OPTIMIZED_DATA文件组！！

要确认此文件组已经创建，可以访问SSMS中数据库属性的Filegroups 界面，如下图所示。

 

步骤2：

添加一个数据文件到文件组，可以通过ALTER DATABASE语句来实现。

添加一个新数据文件到HekatonFG文件组：

 

 

步骤3：

在为数据库设置了必需的文件组和文件之后，就可以创建自己的内存优化表了。

当在定义表的时候，会指定其“持久性”。

一个内存优化表可以是持久的或非持久的。

（1）对于一个持久表是将数据存储在内存中，而且也保存在内存优化文件组中。

（2）对于一个非持久表，数据是仅存储在内存中的，所以，如果系统崩溃或重启，数据就会丢失。

 

在SQL Server 2014中默认用的是持久表，接下来我们来深入了解一下。

当定义一个持久内存优化表的时候，你还必须定义一个基于非聚集哈希索引的主键。

在一个哈希索引中，数据是通过一个内存散列表进行访问的，而非固定大小页。

哈希索引是在内存优化表中唯一支持的索引类型。

除了在表定义中定义主键外，还必须将表配置为内存优化的，如下CREATE TABLE 语句所示：

 

这就是创建一个内存优化表的全部步骤，其他的一切都会发生在幕后。

但是，要记住，SQL Server 2014对这些表有着很多限制。例如，它们不支持外键或约束检查（感觉类似于MYSQL的memory存储引擎），

它们也不支持IDENTITY 字段或DML触发器。最为重要的是，内存耗尽会导致写活动停止。

 

步骤4：

另一方面，内存优化表支持本地编译存储过程，只要那些存储过程只引用内存优化表。

在这种情况下，存储过程可以转化为本地代码，这样会执行更快且要比典型存储过程需要更少的内存。

除了只引用内存优化表，一个本地编译存储过程必须是模式绑定的并运行在一个特定执行内容内。

另外，每个本地编译存储过程必须完全由一个原子块组成。

 

下面的CREATE PROCEDURE 语句定义了一个本地编译存储过程，它从前例中所创建的Reseller表中检索数据

 

---

第一个实验：内存表的数据查询速度比较

聚集索引表和内存优化表的比较

建表语句

 

聚集索引表

SET STATISTICS IO ON 
SET STATISTICS TIME ON
INSERT into testmemory2([id],[name])  SELECT [id] ,[name] from sysobjects
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

 

-------------------------------------------------------------------------------

查询性能比较

内存优化表

SET STATISTICS IO ON 
SET STATISTICS TIME ON
SELECT * FROM  testmemory1  ORDER BY [ID] DESC
SET STATISTICS IO ON
SET STATISTICS TIME ON

 

 

聚集索引表

SET STATISTICS IO ON 
SET STATISTICS TIME ON
SELECT * FROM  testmemory2  ORDER BY [ID] DESC
SET STATISTICS IO ON
SET STATISTICS TIME ON

 

可以看到内存优化表读写数据（insert 、select）的时候都看不到IO读写

 

---

我们看一下事务日志

 

 

Context	Operation	AllocUnitName
LCX_NULL	LOP_HK	NULL
LCX_NULL	LOP_HK_CHAINED	NULL
LCX_NULL	LOP_HK	NULL
LCX_NULL	LOP_HK_CHAINED	NULL
LCX_NULL	LOP_HK_CHECKPOINT	NULL
LCX_NULL	LOP_HK	NULL
LCX_NULL	LOP_BEGIN_XACT	NULL
LCX_NULL	LOP_FS_DOWNLEVEL_OP	NULL
LCX_NULL	LOP_BEGIN_XACT	NULL
LCX_CLUSTERED	LOP_INSERT_ROWS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_INDEX_LEAF	LOP_INSERT_ROWS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_NULL	LOP_COMMIT_XACT	NULL
LCX_MARK_AS_GHOST	LOP_DELETE_ROWS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_MARK_AS_GHOST	LOP_DELETE_ROWS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_NULL	LOP_HK	NULL
LCX_NULL	LOP_FS_DOWNLEVEL_OP	NULL
LCX_HEAP	LOP_INSERT_ROWS	sys.xtp_storage
LCX_INDEX_LEAF	LOP_INSERT_ROWS	sys.xtp_storage.UQ__xtp_stor__3213E83EA8737D06
LCX_CLUSTERED	LOP_EXPUNGE_ROWS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_CLUSTERED	LOP_EXPUNGE_ROWS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_PFS	LOP_SET_BITS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_INDEX_LEAF	LOP_EXPUNGE_ROWS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_INDEX_LEAF	LOP_EXPUNGE_ROWS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_NULL	LOP_COMMIT_XACT	NULL
LCX_NULL	LOP_BEGIN_XACT	NULL
LCX_NULL	LOP_FS_DOWNLEVEL_OP	NULL
LCX_NULL	LOP_BEGIN_XACT	NULL
LCX_CLUSTERED	LOP_INSERT_ROWS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_INDEX_LEAF	LOP_INSERT_ROWS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_NULL	LOP_COMMIT_XACT	NULL
LCX_MARK_AS_GHOST	LOP_DELETE_ROWS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_PFS	LOP_SET_BITS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_MARK_AS_GHOST	LOP_DELETE_ROWS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_NULL	LOP_FS_DOWNLEVEL_OP	NULL
LCX_HEAP	LOP_INSERT_ROWS	sys.xtp_storage
LCX_INDEX_LEAF	LOP_INSERT_ROWS	sys.xtp_storage.UQ__xtp_stor__3213E83EA8737D06
LCX_NULL	LOP_COMMIT_XACT	NULL
LCX_NULL	LOP_HK	NULL
LCX_CLUSTERED	LOP_EXPUNGE_ROWS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_INDEX_LEAF	LOP_EXPUNGE_ROWS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_PFS	LOP_SET_BITS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_PFS	LOP_SET_BITS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysallocunits.clust
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrowsets.clust
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrscols.clst
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrscols.clst
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrscols.clst
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrscols.clst
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrscols.clst
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrscols.clst
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrscols.clst
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrscols.clst
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrscols.clst
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrscols.clst
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysallocunits.clust
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrowsets.clust
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysallocunits.clust
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrowsets.clust
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrscols.clst
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrscols.clst
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrscols.clst
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysallocunits.clust
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrowsets.clust
LCX_NULL	LOP_BEGIN_CKPT	NULL
LCX_FILE_HEADER	LOP_MODIFY_STREAMFILE_HDR	NULL
LCX_BOOT_PAGE_CKPT	LOP_XACT_CKPT	NULL
LCX_NULL	LOP_END_CKPT	NULL
LCX_NULL	LOP_HK	NULL
LCX_NULL	LOP_HK	NULL
LCX_NULL	LOP_HK	NULL
LCX_NULL	LOP_HK_CHAINED	NULL
LCX_NULL	LOP_HK	NULL
LCX_NULL	LOP_HK	NULL
LCX_NULL	LOP_BEGIN_XACT	NULL
LCX_NULL	LOP_FS_DOWNLEVEL_OP	NULL
LCX_NULL	LOP_BEGIN_XACT	NULL
LCX_CLUSTERED	LOP_INSERT_ROWS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_INDEX_LEAF	LOP_INSERT_ROWS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_NULL	LOP_COMMIT_XACT	NULL
LCX_MARK_AS_GHOST	LOP_DELETE_ROWS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_PFS	LOP_SET_BITS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_MARK_AS_GHOST	LOP_DELETE_ROWS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_PFS	LOP_SET_BITS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_NULL	LOP_HK_CHAINED	NULL
LCX_NULL	LOP_HK_CHAINED	NULL
LCX_NULL	LOP_HK_CHECKPOINT	NULL
LCX_NULL	LOP_FS_DOWNLEVEL_OP	NULL
LCX_HEAP	LOP_INSERT_ROWS	sys.xtp_storage
LCX_INDEX_LEAF	LOP_INSERT_ROWS	sys.xtp_storage.UQ__xtp_stor__3213E83EA8737D06
LCX_NULL	LOP_COMMIT_XACT	NULL
LCX_NULL	LOP_BEGIN_XACT	NULL
LCX_NULL	LOP_FS_DOWNLEVEL_OP	NULL
LCX_NULL	LOP_BEGIN_XACT	NULL
LCX_CLUSTERED	LOP_INSERT_ROWS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_INDEX_LEAF	LOP_INSERT_ROWS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_NULL	LOP_COMMIT_XACT	NULL
LCX_MARK_AS_GHOST	LOP_DELETE_ROWS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_MARK_AS_GHOST	LOP_DELETE_ROWS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_NULL	LOP_FS_DOWNLEVEL_OP	NULL
LCX_HEAP	LOP_INSERT_ROWS	sys.xtp_storage
LCX_INDEX_LEAF	LOP_INSERT_ROWS	sys.xtp_storage.UQ__xtp_stor__3213E83EA8737D06
LCX_NULL	LOP_COMMIT_XACT	NULL
LCX_NULL	LOP_HK	NULL
LCX_CLUSTERED	LOP_EXPUNGE_ROWS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_CLUSTERED	LOP_EXPUNGE_ROWS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_INDEX_LEAF	LOP_EXPUNGE_ROWS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_INDEX_LEAF	LOP_EXPUNGE_ROWS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_PFS	LOP_SET_BITS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_PFS	LOP_SET_BITS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_PFS	LOP_MODIFY_HEADER	Unknown Alloc Unit

 

---

总结

内存优化表也会写事务日志的，在读写操作的时候发现内存优化表没有I/O次数，应该是数据都已经在内存里了

 

更多详细资料可以参考：

SQL Server 2014 新特性——内存数据库

SQL Server 2014新特性：分区索引和内存优化表

MSDN:内存优化表

 

如有不对的地方，欢迎大家拍砖o(∩_∩)o 

试试SQLSERVER2014的内存优化表,布布扣,bubuko.com