SQLServer 2014 内存优化表

内存优化表是 SQLServer 2014 的新功能，它是可以将表放在内存中，这会明显提升DML性能。

关于内存优化表，更多可参考两位大侠的文章：SQL Server 2014新特性探秘(1)-内存数据库  试试SQLSERVER2014的内存优化表

创建内存优化表也很简单，以下测试：

添加内存优化数据库文件组：

USE [master]GO--在当前数据库中添加内存优化数据库文件组（每个数据库仅1个文件组）ALTER DATABASE [Demo] ADD FILEGROUP [FG_MemoryTable] CONTAINS MEMORY_OPTIMIZED_DATA GO--创建新的文件添加到该文件组中ALTER DATABASE [Demo]ADD FILE(   NAME = 'Demo_MemoryTable',   FILENAME ='F:\VMWareSystem\database\Demo')TO FILEGROUP [FG_MemoryTable];GO

这里添加的数据库文件，指定的是一个路径。路径文件如下：

（忘截图了。。）

创建内存优化表：

USE [Demo];GO--内存优化表CREATE TABLE [MemoryTable]([Guid] UNIQUEIDENTIFIER NOT NULL CONSTRAINT IX_MemoryTable PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),[Name] NVARCHAR(50) NOT NULL ,[value] INT NULL)WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);/*在内存和磁盘中*/GO--普通表CREATE TABLE [ClusterTable]([Guid] UNIQUEIDENTIFIER NOT NULL CONSTRAINT IX_ClusterTable PRIMARY KEY NONCLUSTERED ,[Name] NVARCHAR(50) NOT NULL ,[value] INT NULL)GO

创建内存优化表当前只能用脚本创建。WITH 语句来指定BUCKET_COUNT 的设置，它表明了在哈希索引中应该创建的bucket数量。(每个bucket是一个槽，可以用来存放一组键值对。)微软建议bucket的数量为表唯一列数量的一到两倍。

MEMORY_OPTIMIZED = ON ：为启用内存优化表；

DURABILITY = SCHEMA_AND_DATA ：表示数据存储在内存中和文件组中。

（DURABILITY = SCHEMA_ONLY 表示数据只存储在内存中）

测试IO情况：

--插入1万行数据SET NOCOUNT ONINSERT  INTO [MemoryTable] SELECT NEWID(),'hello.kk'+CONVERT(VARCHAR(10),ABS(CHECKSUM(NEWID()))%1000),ABS(CHECKSUM(NEWID()))%1000GO 10000SET NOCOUNT OFFINSERT INTO [ClusterTable] SELECT * FROM [MemoryTable]GO--查看IO情况SET STATISTICS IO ONSELECT * FROM [MemoryTable] WITH(SNAPSHOT)SELECT * FROM [ClusterTable]SET STATISTICS IO OFFGO

(10000 行受影响)
(1 行受影响)

(10000 行受影响)
表 'ClusterTable'。扫描计数 1，逻辑读取 68 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
(1 行受影响)

 IO 结果，内存表 [MemoryTable] 没有跟踪到IO ，物理表 [ClusterTable] 可以看到进行了读取。

这里也看不出数据是否在内存中，现在再测试一次。到底数据是不是在内存呢？

现在测试：新建内存优化表，注意 DURABILITY = SCHEMA_ONLY ，数据只保留在内存中。

--创建另一张测试表CREATE TABLE [MemoryTest]([Guid] UNIQUEIDENTIFIER NOT NULL CONSTRAINT IX_MemoryTest PRIMARY KEY NONCLUSTERED,[Name] NVARCHAR(50) NOT NULL ,[value] INT NULL)WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);/*只在内存*/GO--插入10行数据INSERT  INTO [MemoryTest] SELECT NEWID(),'hello.kk',100GO 10--查看，有数据！SELECT * FROM [MemoryTest]--注意：现在重启 sqlserver 服务！--再查看，表中没有数据了！！SELECT * FROM [MemoryTest]--DROP TABLE [MemoryTest]

重启sqlserver 服务后数据查询不到了！因为数据只保留在内存中，任何导致sqlserver服务停止都会导致数据丢失。也就是说明了数据是在内存中的！ 执行 DBCC DROPCLEANBUFFERS 对内存优化表数据无影响。

内存优化表诸多限制：

内存表不支持 修改bucket_count，truncate table，DML触发器，IDENTITY初始值或增量不为1的情况。
内存表不支持ALTER 操作(约束检查、外键、添加或删除索引、添加或删除列、更改列属性)
以下几种情况在内存优化表是不支持的：

TRUNCATE TABLE [MemoryTable]GOCREATE TRIGGER TR_MemoryTableON [MemoryTable] AFTER INSERT ASBEGININSERT INTO [MemoryTable]([Guid],[Name],[value])SELECT [Guid],[Name],[value] FROM insertedENDGOCREATE TABLE [MemoryTeatTab]([ID] INT IDENTITY(2,1) NOT NULL PRIMARY KEY NONCLUSTERED)WITH (MEMORY_OPTIMIZED = ON);GOALTER TABLE [MemoryTable] ADD CONSTRAINT CK_value CHECK([value] BETWEEN 0 AND 1000)GOALTER TABLE [MemoryTable] ALTER COLUMN [value] SMALLINT NULLGOALTER TABLE [MemoryTable] ADD [value2] SMALLINT NULLGOCREATE NONCLUSTERED INDEX IX_MemoryTable_NAME ON [MemoryTable]([name])GOALTER TABLE [MemoryTable] ADD CONSTRAINT FK_MemoryTable_ClusterTable FOREIGN KEY ([Guid])REFERENCES [ClusterTable]([Guid]) ON UPDATE CASCADE ON DELETE CASCADEGO

消息 10794，级别 16，状态 92，第 113 行
此 语句 “TRUNCATE TABLE”未受到 内存优化表 的支持。
消息 10794，级别 16，状态 77，过程 TR_MemoryTable，第 116 行
此 操作 “CREATE TRIGGER”未受到 内存优化表 的支持。
消息 12339，级别 16，状态 21，第 125 行
内存优化表 不支持种子以及增量值使用 1 以外的数值。
消息 10794，级别 16，状态 14，第 132 行
此 操作 “ALTER TABLE”未受到 内存优化表 的支持。
消息 10794，级别 16，状态 14，第 134 行
此 操作 “ALTER TABLE”未受到 内存优化表 的支持。
消息 10794，级别 16，状态 14，第 136 行
此 操作 “ALTER TABLE”未受到 内存优化表 的支持。
消息 10794，级别 16，状态 12，第 138 行
此 操作 “CREATE INDEX”未受到 内存优化表 的支持。
消息 10794，级别 16，状态 14，第 140 行
此 操作 “ALTER TABLE”未受到 内存优化表 的支持。

使用内存优化表，还有一个重要的概念：本机编译

本机编译可提高访问数据的速度和执行查询的效率。在服务器重启过程中，将重新编译内存优化表。为了加快数据库恢复速度，本机编译的存储过程不会在服务器重启过程中重新编译，而是在首次执行时编译。如果编译失败或中断，则某些生成的文件将不会被删除。出于支持性目的这些文件被有意保留，并且在删除数据库时会被删除。

--查看内存优化表编译的 DLLselect description,name FROM sys.dm_os_loaded_moduleswhere name like '%xtp_t_' + cast(db_id() as varchar(10)) + '_' + cast(object_id('dbo.MemoryTable') as varchar(10)) + '.dll'go--更多 DLLSELECT description,name FROM sys.dm_os_loaded_moduleswhere description = 'XTP Native DLL'

description name

-------------------- ----------------------
XTP Native DLL D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\xtp\5\xtp_t_5_949578421.dll

本机编译存储过程: 存储过程的内部参数或设置都是必要的！在创建时编译，而解释型存储过程在首次执行时编译。

（更多参考： 本机编译存储过程）

CREATE PROCEDURE DBO.NATIVE_SPWITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNERASBEGIN ATOMICWITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT,LANGUAGE=N'简体中文')SELECT TOP 5 [Guid],[Name],[value]FROM DBO.MemoryTable ORDER BY NEWID()ENDGOEXEC DBO.NATIVE_SPGO--查看存储过程编译的 DLLselect name, description FROM sys.dm_os_loaded_moduleswhere name like '%xtp_p_' + cast(db_id() as varchar(10)) + '_' + cast(object_id('dbo.NATIVE_SP') as varchar(10)) + '.dll'go

参数说明如下：

NATIVE_COMPILATION ： 表示本地编译

SCHEMABINDING ：本机编译存储过程必须绑定到其引用的对象的架构

EXECUTE AS OWNER ：本机编译的存储过程不支持 EXECUTE AS CALLER，这是默认执行上下文。因此，需要指定执行上下文。支持选项 EXECUTE AS OWNER、EXECUTE ASuser 和 EXECUTE AS SELF。

BEGIN ATOMIC ：本机编译的存储过程正文必须由恰好一个原子块构成。原子块确保存储过程的原子执行。如果在活动事务的上下文外调用该过程，它将开始一个新事务，这个新事务在原子块的末尾提交。（更多参考 ：原子块）

TRANSACTION ISOLATION LEVEL ：必须设置事务隔离级别 SNAPSHOT、REPEATABLEREAD 和 SERIALIZABLE。

LANGUAGE ：存储过程的语言必须设置为可用语言或语言别名之一。

--查看存储过程编译的 DLLselect name, description FROM sys.dm_os_loaded_moduleswhere name like '%xtp_p_' + cast(db_id() as varchar(10)) + '_' + cast(object_id('dbo.NATIVE_SP') as varchar(10)) + '.dll'go

更改存储过程则出错：
消息 10794，级别 16，状态 25，过程 NATIVE_SP，第 168 行
此 操作 “ALTER PROCEDURE”未受到 本机编译的存储过程 的支持。


事务隔离级别为 READ COMMITTED 则出错：
消息 10794，级别 16，状态 81，过程 NATIVE_SP，第 171 行
此 事务隔离级别 “READ COMMITTED”未受到 本机编译的存储过程 的支持。

内存优化表事务隔离级别 ：

访问内存优化表的事务支持的隔离级别:SNAPSHOT,REPEATABLE READ,SERIALIZABLE,READ COMMITTED 。
内存优化表不使用锁。可以使用更高的隔离级别（如 REPEATABLE READ 和 SERIALIZABLE）声明所需的保障。
不支持锁定提示。改为通过事务隔离级别声明所需的保障。

（支持 NOLOCK 是因为 SQL Server 不对内存优化表使用锁。请注意，与基于磁盘的表不同，NOLOCK 对于内存优化表并不暗示 READ UNCOMMITTED 行为。）


对于自动提交事务，隔离级别 READ COMMITTED 会隐式映射到内存优化表的 SNAPSHOT。显式或隐式用户事务不支持 READ COMMITTED。
因此，如果 TRANSACTION ISOLATION LEVEL 会话设置设为 READ COMMITTED，则在访问内存优化表时无需通过表提示指定隔离级别。

以下这样使用错误!

--执行出错！SET TRANSACTION ISOLATION LEVEL READ COMMITTEDBEGIN TRANSELECT TOP 5 [Guid],[Name],[value]FROM DBO.MemoryTable ORDER BY NEWID()ROLLBACK TRANGO

消息 41368，级别 16，状态 0，第 198 行
仅对自动提交事务支持使用 READ COMMITTED 隔离级别访问内存优化的表。它不适用于显式或隐式事务。请使用表提示(如 WITH (SNAPSHOT))为内存优化的表提供支持的隔离级别。

正确执行方法！以下3种！

--正确执行方法！以下3种！SET TRANSACTION ISOLATION LEVEL READ COMMITTEDSELECT TOP 5 [Guid],[Name],[value]FROM DBO.MemoryTable ORDER BY NEWID()GOBEGIN TRANSELECT TOP 5 [Guid],[Name],[value]FROM DBO.MemoryTable WITH(SNAPSHOT)ORDER BY NEWID()ROLLBACK TRANGOALTER DATABASE Demo SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ONBEGIN TRANSELECT TOP 5 [Guid],[Name],[value]FROM DBO.MemoryTable ORDER BY NEWID()ROLLBACK TRANGO

事务隔离测试 ：

打开会话1执行如下语句，使更新堵塞：

--会话1SET TRANSACTION ISOLATION LEVEL READ COMMITTEDBEGIN TRANUPDATE [MemoryTable]WITH(SNAPSHOT) SET value = 9999 WHERE Guid='2D153C8A-498D-4619-A58F-491CEAD2A031'WAITFOR DELAY '00:00:20'ROLLBACK TRAN

打开会话2，查询或更新同一条语句：

--会话2--可重复读，读取的是[会话1]之前的快照SET TRANSACTION ISOLATION LEVEL READ COMMITTEDSELECT * FROM [MemoryTable]WITH(SNAPSHOT) WHERE Guid='2D153C8A-498D-4619-A58F-491CEAD2A031'--更改则出错UPDATE [MemoryTable]WITH(SNAPSHOT) SET Name = 'KK' WHERE Guid='2D153C8A-498D-4619-A58F-491CEAD2A031'/*消息 41302，级别 16，状态 110，第 3 行当前事务尝试更新自该事务启动后已更新的记录。该事务已中止。语句已终止。*/

发现查询并没有堵塞，也就相当于可重复读了！

有一些与访问内存优化表的事务有关的错误情形:
41302.当前事务尝试更新自事务启动以来已更新过的记录。(如果两个事务尝试更新同一行，则会发生写/写冲突)
41305.因某个可重复读取验证失败，当前事务无法提交。
41325.因某个序列化读取验证失败，当前事务无法提交。
41301.当前事务所依赖的前一事务已终止，当前事务无法再提交。

--内存优化表允许使用更高隔离级别 REPEATABLE READ 和 SERIALIZABLE 来实现乐观并发控制。--更改事务隔离级别为 REPEATABLE READ 或者SERIALIZABLE，然后必须在快照隔离（WITH(SNAPSHOT)）下执行语句 。SET TRANSACTION ISOLATION LEVEL READ COMMITTEDSET TRANSACTION ISOLATION LEVEL REPEATABLE READSET TRANSACTION ISOLATION LEVEL SERIALIZABLE 

其他说明：
内存优化表不支持跨数据库事务。访问内存优化表的每个事务都无法访问多个数据库（对 tempdb 进行的读/写访问以及对系统主数据库进行的只读访问除外）。
内存优化表不支持分布式事务。以 BEGIN DISTRIBUTED TRANSACTION 开头的分布式事务无法访问内存优化表。
内存优化表不支持锁定。内存优化表不支持通过锁提示（如 TABLOCK、XLOCK、ROWLOCK）实现的显式锁。

更新统计信息：

默认情况下，不更新针对内存优化表的统计信息。对于基于磁盘的表，如果自上次 sp_updatestats (Transact-SQL) 以来已修改表，sp_updatestats (Transact-SQL) 仅更新更新统计信息。对于内存优化的表，sp_updatestats (Transact-SQL) 始终生成更新的统计信息。 

--更新单个内存优化表 (myschema. Mytable) 的统计信息：UPDATE STATISTICS [DBO].[MemoryTable] WITH FULLSCAN, NORECOMPUTE--更新当前数据库中所有内存优化表的统计信息：DECLARE @sql NVARCHAR(MAX) = N''SELECT @sql += N'UPDATE STATISTICS ' + quotename(schema_name(schema_id)) + N'.' + quotename(name) + N' WITH FULLSCAN, NORECOMPUTE 'FROM sys.tables WHERE is_memory_optimized=1EXEC sp_executesql @sql--内存优化表统计信息上次更新时间：select t.object_id, t.name, sp.last_updated as 'stats_last_updated'from sys.tables t inner join sys.stats s on t.object_id=s.object_id cross apply sys.dm_db_stats_properties(t.object_id, s.stats_id) spwhere t.is_memory_optimized=1

更多参考：内存优化表简介