SQLServer 2014 内存优化表

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

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

 

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

 

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

 

1. USE [master]

2. GO

3. -- 在当前数据库中添加内存优化数据库文件组（每个数据库仅1个文件组）

4. ALTER DATABASE [Demo] ADD FILEGROUP [FG_MemoryTable] CONTAINS MEMORY_OPTIMIZED_DATA

5. GO

6.  

7. -- 创建新的文件添加到该文件组中

8. ALTER DATABASE [Demo]

9. ADD FILE

10. (

11. NAME = 'Demo_MemoryTable',

12. FILENAME ='F:\VMWareSystem\database\Demo'

13. )

14. TO FILEGROUP [FG_MemoryTable];

15. GO

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

（忘截图了。。）

 

创建内存优化表：

 

 

1. USE [Demo];

2. GO

3. --内存优化表

4. CREATE TABLE [MemoryTable]

5. (

6. [Guid] UNIQUEIDENTIFIER NOT NULL

7. CONSTRAINT IX_MemoryTable PRIMARY KEY NONCLUSTERED

8. HASH WITH (BUCKET_COUNT = 1024),

9. [Name] NVARCHAR(50) NOT NULL ,

10. [value] INT NULL

11. )

12. WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);/*在内存和磁盘中*/

13. GO

14. --普通表

15. CREATE TABLE [ClusterTable]

16. (

17. [Guid] UNIQUEIDENTIFIER NOT NULL

18. CONSTRAINT IX_ClusterTable PRIMARY KEY NONCLUSTERED ,

19. [Name] NVARCHAR(50) NOT NULL ,

20. [value] INT NULL

21. )

22. GO

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

 

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

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

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

 

 

测试IO情况：

 

1. -- 插入1万行数据

2. SET NOCOUNT ON

3. INSERT INTO [MemoryTable]

4. SELECT NEWID(),'hello.kk'+CONVERT(VARCHAR(10),ABS(CHECKSUM(NEWID()))%1000),ABS(CHECKSUM(NEWID()))%1000

5. GO 10000

6. SET NOCOUNT OFF

7.  

8. INSERT INTO [ClusterTable] SELECT * FROM [MemoryTable]

9. GO

10.  

11. -- 查看IO情况

12. SET STATISTICS IO ON

13. SELECT * FROM [MemoryTable] WITH(SNAPSHOT)

14. SELECT * FROM [ClusterTable]

15. SET STATISTICS IO OFF

16. GO

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

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

 

 

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

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

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

 

1. -- 创建另一张测试表

2. CREATE TABLE [MemoryTest]

3. (

4. [Guid] UNIQUEIDENTIFIER NOT NULL CONSTRAINT IX_MemoryTest PRIMARY KEY NONCLUSTERED,

5. [Name] NVARCHAR(50) NOT NULL ,

6. [value] INT NULL

7. )

8. WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);/*只在内存*/

9. GO

10.  

11. -- 插入10行数据

12. INSERT INTO [MemoryTest] SELECT NEWID(),'hello.kk',100

13. GO 10

14.  

15. -- 查看，有数据！

16. SELECT * FROM [MemoryTest]

17.  
18.  

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

20.  
21.  

22. --再查看，表中没有数据了！！

23. SELECT * FROM [MemoryTest]

24.  

25. --DROP TABLE [MemoryTest]

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

 

 

内存优化表诸多限制：

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

 

1. TRUNCATE TABLE [MemoryTable]

2. GO

3.  

4. CREATE TRIGGER TR_MemoryTable

5. ON [MemoryTable] AFTER INSERT

6. AS

7. BEGIN

8. INSERT INTO [MemoryTable]([Guid],[Name],[value])

9. SELECT [Guid],[Name],[value] FROM inserted

10. END

11. GO

12.  

13. CREATE TABLE [MemoryTeatTab]

14. (

15. [ID] INT IDENTITY(2,1) NOT NULL PRIMARY KEY NONCLUSTERED

16. )

17. WITH (MEMORY_OPTIMIZED = ON);

18. GO

19.  

20. ALTER TABLE [MemoryTable] ADD CONSTRAINT CK_value CHECK([value] BETWEEN 0 AND 1000)

21. GO

22. ALTER TABLE [MemoryTable] ALTER COLUMN [value] SMALLINT NULL

23. GO

24. ALTER TABLE [MemoryTable] ADD [value2] SMALLINT NULL

25. GO

26. CREATE NONCLUSTERED INDEX IX_MemoryTable_NAME ON [MemoryTable]([name])

27. GO

28. ALTER TABLE [MemoryTable]

29. ADD CONSTRAINT FK_MemoryTable_ClusterTable FOREIGN KEY ([Guid])

30. REFERENCES [ClusterTable]([Guid]) ON UPDATE CASCADE ON DELETE CASCADE

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

 

 

 

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

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

 

1. -- 查看内存优化表编译的 DLL

2. select description,name FROM sys.dm_os_loaded_modules

3. where name like '%xtp_t_' + cast(db_id() as varchar(10)) + '_' + cast(object_id('dbo.MemoryTable') as varchar(10)) + '.dll'

4. go

5.  

6. -- 更多 DLL

7. SELECT description,name FROM sys.dm_os_loaded_modules

8. 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

 

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

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

 

1. CREATE PROCEDURE DBO.NATIVE_SP

2. WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER

3. AS

4. BEGIN ATOMIC

5. WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT,LANGUAGE=N'简体中文')

6. SELECT TOP 5 [Guid],[Name],[value]

7. FROM DBO.MemoryTable ORDER BY NEWID()

8. END

9. GO

10.  

11. EXEC DBO.NATIVE_SP

12. GO

13.  

14. -- 查看存储过程编译的 DLL

15. select name, description FROM sys.dm_os_loaded_modules

16. where name like '%xtp_p_' + cast(db_id() as varchar(10)) + '_' + cast(object_id('dbo.NATIVE_SP') as varchar(10)) + '.dll'

17. 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 ：存储过程的语言必须设置为可用语言或语言别名之一。

 

1. -- 查看存储过程编译的 DLL

2. select name, description FROM sys.dm_os_loaded_modules

3. where name like '%xtp_p_' + cast(db_id() as varchar(10)) + '_' + cast(object_id('dbo.NATIVE_SP') as varchar(10)) + '.dll'

4. 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，则在访问内存优化表时无需通过表提示指定隔离级别。

 

以下这样使用错误!

 

1. --执行出错！

2. SET TRANSACTION ISOLATION LEVEL READ COMMITTED

3. BEGIN TRAN

4. SELECT TOP 5 [Guid],[Name],[value] FROM DBO.MemoryTable ORDER BY NEWID()

5. ROLLBACK TRAN

6. GO

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

 

 

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

 

1. --正确执行方法！以下3种！

2. SET TRANSACTION ISOLATION LEVEL READ COMMITTED

3. SELECT TOP 5 [Guid],[Name],[value] FROM DBO.MemoryTable ORDER BY NEWID()

4. GO

5.  
6.  

7. BEGIN TRAN

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

9. ROLLBACK TRAN

10. GO

11.  
12.  

13. ALTER DATABASE Demo SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ON

14. BEGIN TRAN

15. SELECT TOP 5 [Guid],[Name],[value] FROM DBO.MemoryTable ORDER BY NEWID()

16. ROLLBACK TRAN

17. GO

 

 

事务隔离测试 ：

 

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

 

1. -- 会话1

2. SET TRANSACTION ISOLATION LEVEL READ COMMITTED

3. BEGIN TRAN

4. UPDATE [MemoryTable]WITH(SNAPSHOT) SET value = 9999 WHERE Guid='2D153C8A-498D-4619-A58F-491CEAD2A031'

5. WAITFOR DELAY '00:00:20'

6. ROLLBACK TRAN

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

 

 

1. -- 会话2

2. -- 可重复读，读取的是[会话1]之前的快照

3. SET TRANSACTION ISOLATION LEVEL READ COMMITTED

4. SELECT * FROM [MemoryTable]WITH(SNAPSHOT) WHERE Guid='2D153C8A-498D-4619-A58F-491CEAD2A031'

5.  

6. -- 更改则出错

7. UPDATE [MemoryTable]WITH(SNAPSHOT) SET Name = 'KK' WHERE Guid='2D153C8A-498D-4619-A58F-491CEAD2A031'

8. /*

9. 消息 41302，级别 16，状态 110，第 3 行

10. 当前事务尝试更新自该事务启动后已更新的记录。该事务已中止。

11. 语句已终止。

12. */

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

 

 

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

 

1. -- 内存优化表允许使用更高隔离级别 REPEATABLE READ 和 SERIALIZABLE 来实现乐观并发控制。

2. -- 更改事务隔离级别为 REPEATABLE READ 或者SERIALIZABLE，然后必须在快照隔离（WITH(SNAPSHOT)）下执行语句 。

3. SET TRANSACTION ISOLATION LEVEL READ COMMITTED

4. SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

5. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

 

 

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

 

更新统计信息：

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

 

1. --更新单个内存优化表 (myschema. Mytable) 的统计信息：

2. UPDATE STATISTICS [DBO].[MemoryTable] WITH FULLSCAN, NORECOMPUTE

3.  
4.  

5. --更新当前数据库中所有内存优化表的统计信息：

6. DECLARE @sql NVARCHAR(MAX) = N''

7.  

8. SELECT @sql += N'UPDATE STATISTICS ' + quotename(schema_name(schema_id)) + N'.' + quotename(name) + N' WITH FULLSCAN, NORECOMPUTE '

9. FROM sys.tables WHERE is_memory_optimized=1

10.  

11. EXEC sp_executesql @sql

12.  

13. --内存优化表统计信息上次更新时间：

14. select t.object_id, t.name, sp.last_updated as 'stats_last_updated'

15. from sys.tables t

16. inner join sys.stats s on t.object_id=s.object_id

17. cross apply sys.dm_db_stats_properties(t.object_id, s.stats_id) sp

18. where t.is_memory_optimized=1

 

https://blog.csdn.net/kk185800961/article/details/49211011

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