试试 SQLSERVER2014的 内存 优化 表 SQL Server 2014中的 内存 引擎(代号为Hekaton)将OLTP提升到了新的高度。 现在，存储引擎已整合进当前的数据库管理系统，而使用先进 内存 技术来支持大规模OLTP工作负载。 就算如此，要利用此新功能，数据库必须包含 内存
试试 SQLSERVER2014的 内存 优化 表
SQL Server 2014中的 内存 引擎(代号为Hekaton)将OLTP提升到了新的高度。
现在，存储引擎已整合进当前的数据库管理系统，而使用先进 内存 技术来支持大规模OLTP工作负载。
就算如此，要利用此新功能，数据库必须包含[ 内存 优化 ]文件组和表
即所配置的文件组和表使用Hekaton技术。
幸运的是，SQL Server 2014使这一过程变得非常简单直接。
要说明其工作原理，我们来创建一个名为 TestHekaton 的数据库，然后添加一个 内存 优化 文件组到此数据库
测试环境 ：Microsoft Azure 大陆版 虚拟机
SQLSERVER2014企业版
实验
第一个实验： 内存 表的简单使用
步骤1：创建数据库和MEMORY_OPTIMIZED_DATA文件组
USE master; GO CREATE DATABASE TestHekaton; GO ALTER DATABASE TestHekaton ADD FILEGROUP HekatonFG CONTAINS MEMORY_OPTIMIZED_DATA; GO
注意ALTER DATABASE语句中的ADD FILEGROUP 语句包含文件组的名称( HekatonFG )和关键字 CONTAINS MEMORY_OPTIMIZED_DATA
它会指导SQL Server去创建支持 内存 OLTP引擎所必需的文件组类型。
注意： 每个数据库只能有一个 MEMORY_OPTIMIZED_DATA 文件组！！
要确认此文件组已经创建，可以访问SSMS中数据库属性的Filegroups 界面，如下图所示。
步骤2：
添加一个数据文件到文件组，可以通过ALTER DATABASE语句来实现。
添加一个新数据文件到HekatonFG文件组：
ALTER DATABASE TestHekaton ADD FILE ( NAME = ' HekatonFile ' , FILENAME = ' C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\HekatonFile ' ) TO FILEGROUP [ HekatonFG ] ; GO
注意：在ADD FILE 语句中，我们只为文件路径和文件名提供了一个友好的名称。
并且，在TO FILEGROUP 语句中，为新文件组指定名称。
然后可以去往数据库属性的 Files 界面来查看刚刚添加的文件，如图所示。
步骤3：
在为数据库设置了必需的文件组和文件之后，就可以创建自己的 内存 优化 表了。
当在定义表的时候，会指定其[ 持久性] 。
一个 内存 优化 表可以是 持久的 或 非持久的 。
（1）对于一个持久表是将数据存储在 内存 中，而且也保存在 内存 优化 文件组中。
（2）对于一个非持久表， 数据是仅存储在 内存 中的 ，所以，如果系统崩溃或重启，数据就会丢失。
在SQL Server 2014中默认用的是持久表，接下来我们来深入了解一下。
当定义一个持久 内存 优化 表的时候，你还必须定义一个基于非聚集哈希索引的主键。
在一个哈希索引中，数据是通过一个 内存 散列表进行访问的，而非固定大小页。
哈希索引是在 内存 优化 表中唯一支持的索引类型。
除了在表定义中定义主键外，还必须将表配置为 内存 优化 的，如下CREATE TABLE 语句所示：
USE TestHekaton;
GO
CREATE TABLE Reseller
(
[ ResellerID ] INT NOT NULL
PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024 ),
[ ResellerName ] NVARCHAR ( 50 ) NOT NULL ,
[ ResellerType ] NVARCHAR ( 20 ) NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA);
INSERT INTO Reseller
VALUES ( 1 , ' A Bike Store ' , ' Value Added Reseller ' );
ResellerID 字段 定义包含了定义为非聚集哈希的主键。
注意，必须包含一个WITH 语句来指定BUCKET_COUNT 的设置，它表明了在哈希索引中应该创建的bucket数量。
(每个bucket是一个槽，可以用来存放一组键值对。)
微软建议bucket的数量应是一到两倍于你所期望的表所要包含的唯一索引键的数量。
此表定义以第二个WITH 语句结束。
这里你指定MEMORY_OPTIMIZED 选项为ON 以及DURABILITY 选项为SCHEMA_AND_DATA，此选项是针对持久表的。
接着在表中插入一条记录，这样就可以进行测试了。
数据已经插入到表中
这就是创建一个 内存 优化 表的全部步骤，其他的一切都会发生在幕后。
但是，要记住，SQL Server 2014对这些表有着很多限制。例如，它们不支持 外键 或 约束 检查（感觉类似于MYSQL的memory存储引擎），
它们也不支持IDENTITY 字段或DML触发器。最为重要的是， 内存 耗尽会导致写活动停止。
步骤4：
另一方面， 内存 优化 表支持本地编译存储过程，只要那些存储过程只引用 内存 优化 表。
在这种情况下，存储过程可以转化为本地代码，这样会执行更快且要比典型存储过程需要更少的 内存 。
除了只引用 内存 优化 表，一个本地编译存储过程必须是模式绑定的并运行在一个特定执行内容内。
另外，每个本地编译存储过程必须完全由一个原子块组成。
下面的CREATE PROCEDURE 语句定义了一个本地编译存储过程，它从前例中所创建的Reseller表中检索数据
CREATE PROCEDURE GetResellerType ( @id INT )
WITH NATIVE_COMPILATION,
SCHEMABINDING,
EXECUTE AS OWNER
AS
BEGIN
ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = ' us_english ' )
SELECT ResellerName ,
ResellerType
FROM dbo.Reseller
WHERE ResellerID = @id
END ;
GO
在定义了参数之后，包含一个WITH 语句来指定NATIVE_COMPILATION 选项。
注意：此语句还包含SCHEMABINDING 选项和EXECUTE AS 选项，以及指定了OWNER 作为执行环境。
而WITH 语句负责实现本地编译存储过程的三大需求。
要解决原子块需求，可以在BEGIN 关键字后指定ATOMIC ，之后是另一个包含有事务隔离级别和语言的WITH 语句。
对于访问 内存 优化 表的事务，可以使用SNAPSHOT，REPEATABLEREAD 或SERIALIZABLE 作为隔离级 。
而且，对于此语言必须使用一个可用的语言或语言别名。
这是在定义存储过程时所需要包含的全部内容。一旦创建，就可以通过执行EXECUTE 语句来对其加以测试，如下例中所示：
EXEC GetResellerType 1 ;
此语句会返回经销商的姓名和类型，在本例中分别是ABike Store和Value Added Reseller。
第一个实验： 内存 表的数据查询速度比较
聚集索引表和 内存 优化 表的比较
建表语句
USE TestHekaton;
GO
-- 内存 优化 表
CREATE TABLE testmemory1
(
[ ID ] FLOAT NOT NULL
PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024 ),
[ Name ] NVARCHAR ( 50 ) NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA);
USE TestHekaton;
GO
-- 聚集索引表
CREATE TABLE testmemory2
(
[ ID ] FLOAT NOT NULL PRIMARY KEY ,
[ Name ] NVARCHAR ( 50 ) NOT NULL
)
---------------------------------------------------------------
插入性能比较
内存 优化 表
SET STATISTICS IO ON SET STATISTICS TIME ON INSERT into testmemory1( [ id ] , [ name ] ) SELECT [ id ] , [ name ] from sysobjects SET STATISTICS IO OFF SET STATISTICS TIME OFF
Table ' sysschobjs ' . Scan count 1 , logical reads 33 , physical reads 0 , read - ahead reads 0 , lob logical reads 0 , lob physical reads 0 , lob read - ahead reads 0 . SQL Server Execution Times: CPU time = 0 ms, elapsed time = 20 ms. ( 90 row(s) affected) SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.
聚集索引表
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
Table ' testmemory2 ' . Scan count 0 , logical reads 183 , physical reads 0 , read - ahead reads 0 , lob logical reads 0 , lob physical reads 0 , lob read - ahead reads 0 . Table ' sysschobjs ' . Scan count 1 , logical reads 33 , physical reads 0 , read - ahead reads 0 , lob logical reads 0 , lob physical reads 0 , lob read - ahead reads 0 . SQL Server Execution Times: CPU time = 0 ms, elapsed time = 10 ms. ( 90 row(s) affected) SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.
-------------------------------------------------------------------------------
查询性能比较
内存 优化 表
SET STATISTICS IO ON SET STATISTICS TIME ON SELECT * FROM testmemory1 ORDER BY [ ID ] DESC SET STATISTICS IO ON SET STATISTICS TIME ON
SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. ( 90 row(s) affected) SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.
聚集索引表
SET STATISTICS IO ON SET STATISTICS TIME ON SELECT * FROM testmemory2 ORDER BY [ ID ] DESC SET STATISTICS IO ON SET STATISTICS TIME ON
( 91 row(s) affected) Table ' testmemory2 ' . Scan count 1 , logical reads 2 , physical reads 0 , read - ahead reads 0 , lob logical reads 0 , lob physical reads 0 , lob read - ahead reads 0 . SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.
可以看到 内存 优化 表读写数据（insert 、select）的时候都看不到IO读写
补充测试：
我们先删除刚才插入的数据， 内存 优化 表是不支持truncate table的，只能用delete from 表
只能够delete
插入测试
内存 优化 表
聚集索引表
-------------------------------------------------------------------------------------------------
查询测试
内存 优化 表
聚集索引表
我们看一下事务日志
CHECKPOINT GO SELECT Context , Operation, AllocUnitName FROM sys.fn_dblog( NULL , NULL )
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的内存优化表的详细内容...