SQLServer 2014 内存优化表

内存优化表是 SQLServer 2014 的新功能，它是可以将表放在内存中，这会明显提升DML性能。
关于内存优化表，更多可参考两位大侠的文章：SQL Server 2014新特性探秘(1)-内存数据库 试试SQLSERVER2014的内存优化表

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

添加内存优化数据库文件组：
[sql] view plain copy 在CODE上查看代码片派生到我的代码片
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
这里添加的数据库文件，指定的是一个路径。路径文件如下：
（忘截图了。。）

创建内存优化表：
[sql] view plain copy 在CODE上查看代码片派生到我的代码片
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情况：
[sql] view plain copy 在CODE上查看代码片派生到我的代码片
-- 插入1万行数据
SET NOCOUNT ON
INSERT INTO [MemoryTable]
SELECT NEWID(),'hello.kk'+CONVERT(VARCHAR(10),ABS(CHECKSUM(NEWID()))%1000),ABS(CHECKSUM(NEWID()))%1000
GO 10000
SET NOCOUNT OFF

INSERT INTO [ClusterTable] SELECT * FROM [MemoryTable]
GO

-- 查看IO情况
SET STATISTICS IO ON
SELECT * FROM [MemoryTable] WITH(SNAPSHOT)
SELECT * FROM [ClusterTable]
SET STATISTICS IO OFF
GO

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

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

IO 结果，内存表 [MemoryTable] 没有跟踪到IO ，物理表 [ClusterTable] 可以看到进行了读取。
这里也看不出数据是否在内存中，现在再测试一次。到底数据是不是在内存呢？
现在测试：新建内存优化表，注意 DURABILITY = SCHEMA_ONLY ，数据只保留在内存中。
[sql] view plain copy 在CODE上查看代码片派生到我的代码片
-- 创建另一张测试表
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',100
GO 10

-- 查看，有数据！
SELECT * FROM [MemoryTest]


-- 注意：现在重启 sqlserver 服务！


--再查看，表中没有数据了！！
SELECT * FROM [MemoryTest]

--DROP TABLE [MemoryTest]

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

内存优化表诸多限制：
内存表不支持 修改bucket_count，truncate table，DML触发器，IDENTITY初始值或增量不为1的情况。
内存表不支持ALTER 操作(约束检查、外键、添加或删除索引、添加或删除列、更改列属性)
以下几种情况在内存优化表是不支持的：
[sql] view plain copy 在CODE上查看代码片派生到我的代码片
TRUNCATE TABLE [MemoryTable]
GO

CREATE TRIGGER TR_MemoryTable
ON [MemoryTable] AFTER INSERT
AS
BEGIN
INSERT INTO [MemoryTable]([Guid],[Name],[value])
SELECT [Guid],[Name],[value] FROM inserted
END
GO

CREATE TABLE [MemoryTeatTab]
(
[ID] INT IDENTITY(2,1) NOT NULL PRIMARY KEY NONCLUSTERED
)
WITH (MEMORY_OPTIMIZED = ON);
GO

ALTER TABLE [MemoryTable] ADD CONSTRAINT CK_value CHECK([value] BETWEEN 0 AND 1000)
GO
ALTER TABLE [MemoryTable] ALTER COLUMN [value] SMALLINT NULL
GO
ALTER TABLE [MemoryTable] ADD [value2] SMALLINT NULL
GO
CREATE NONCLUSTERED INDEX IX_MemoryTable_NAME ON [MemoryTable]([name])
GO
ALTER TABLE [MemoryTable]
ADD CONSTRAINT FK_MemoryTable_ClusterTable FOREIGN KEY ([Guid])
REFERENCES [ClusterTable]([Guid]) ON UPDATE CASCADE ON DELETE CASCADE
GO

消息 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”未受到 内存优化表 的支持。

使用内存优化表，还有一个重要的概念：本机编译
本机编译可提高访问数据的速度和执行查询的效率。在服务器重启过程中，将重新编译内存优化表。为了加快数据库恢复速度，本机编译的存储过程不会在服务器重启过程中重新编译，而是在首次执行时编译。如果编译失败或中断，则某些生成的文件将不会被删除。出于支持性目的这些文件被有意保留，并且在删除数据库时会被删除。
[sql] view plain copy 在CODE上查看代码片派生到我的代码片
-- 查看内存优化表编译的 DLL
select description,name FROM sys.dm_os_loaded_modules
where name like '%xtp_t_' + cast(db_id() as varchar(10)) + '_' + cast(object_id('dbo.MemoryTable') as varchar(10)) + '.dll'
go

-- 更多 DLL
SELECT description,name FROM sys.dm_os_loaded_modules
where 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

本机编译存储过程: 存储过程的内部参数或设置都是必要的！在创建时编译，而解释型存储过程在首次执行时编译。
（更多参考： 本机编译存储过程）
[sql] view plain copy 在CODE上查看代码片派生到我的代码片
CREATE PROCEDURE DBO.NATIVE_SP
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT,LANGUAGE=N'简体中文')
SELECT TOP 5 [Guid],[Name],[value]
FROM DBO.MemoryTable ORDER BY NEWID()
END
GO

EXEC DBO.NATIVE_SP
GO

-- 查看存储过程编译的 DLL
select name, description FROM sys.dm_os_loaded_modules
where 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 ：存储过程的语言必须设置为可用语言或语言别名之一。
[sql] view plain copy 在CODE上查看代码片派生到我的代码片
-- 查看存储过程编译的 DLL
select name, description FROM sys.dm_os_loaded_modules
where 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，则在访问内存优化表时无需通过表提示指定隔离级别。

以下这样使用错误!
[sql] view plain copy 在CODE上查看代码片派生到我的代码片
--执行出错！
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
SELECT TOP 5 [Guid],[Name],[value] FROM DBO.MemoryTable ORDER BY NEWID()
ROLLBACK TRAN
GO

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

正确执行方法！以下3种！
[sql] view plain copy 在CODE上查看代码片派生到我的代码片
--正确执行方法！以下3种！
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT TOP 5 [Guid],[Name],[value] FROM DBO.MemoryTable ORDER BY NEWID()
GO


BEGIN TRAN
SELECT TOP 5 [Guid],[Name],[value] FROM DBO.MemoryTable WITH(SNAPSHOT) ORDER BY NEWID()
ROLLBACK TRAN
GO


ALTER DATABASE Demo SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ON
BEGIN TRAN
SELECT TOP 5 [Guid],[Name],[value] FROM DBO.MemoryTable ORDER BY NEWID()
ROLLBACK TRAN
GO

事务隔离测试 ：

打开会话1执行如下语句，使更新堵塞：
[sql] view plain copy 在CODE上查看代码片派生到我的代码片
-- 会话1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
UPDATE [MemoryTable]WITH(SNAPSHOT) SET value = 9999 WHERE Guid='2D153C8A-498D-4619-A58F-491CEAD2A031'
WAITFOR DELAY '00:00:20'
ROLLBACK TRAN

打开会话2，查询或更新同一条语句：
[sql] view plain copy 在CODE上查看代码片派生到我的代码片
-- 会话2
-- 可重复读，读取的是[会话1]之前的快照
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT * 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.当前事务所依赖的前一事务已终止，当前事务无法再提交。
[sql] view plain copy 在CODE上查看代码片派生到我的代码片
-- 内存优化表允许使用更高隔离级别 REPEATABLE READ 和 SERIALIZABLE 来实现乐观并发控制。
-- 更改事务隔离级别为 REPEATABLE READ 或者SERIALIZABLE，然后必须在快照隔离（WITH(SNAPSHOT)）下执行语句 。
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

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

更新统计信息：
默认情况下，不更新针对内存优化表的统计信息。对于基于磁盘的表，如果自上次 sp_updatestats (Transact-SQL) 以来已修改表，sp_updatestats (Transact-SQL) 仅更新更新统计信息。对于内存优化的表，sp_updatestats (Transact-SQL) 始终生成更新的统计信息。
[sql] view plain copy 在CODE上查看代码片派生到我的代码片
--更新单个内存优化表 (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=1

EXEC 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) sp
where t.is_memory_optimized=1