SQLServer 2014 内存优化表

发布时间：2020-12-12 13:12:28 所属栏目：MsSql教程来源：网络整理

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

内存优化表是 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:VMWareSystemdatabaseDemo'
)
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,[value] INT NULL
)
GO

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

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

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

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

测试IO情况：

--	插入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 ，数据只保留在内存中。

--	创建另一张测试表
CREATE TABLE [MemoryTest]
(
	[Guid] UNIQUEIDENTIFIER NOT NULL CONSTRAINT IX_MemoryTest PRIMARY KEY NONCLUSTERED,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 操作(约束检查、外键、添加或删除索引、添加或删除列、更改列属性)
以下几种情况在内存优化表是不支持的：

TRUNCATE TABLE [MemoryTable]
GO

CREATE TRIGGER TR_MemoryTable
ON [MemoryTable] AFTER INSERT 
AS
BEGIN
INSERT INTO [MemoryTable]([Guid],[Name],[value])
SELECT [Guid],[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”未受到内存优化表的支持。

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

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

--	查看内存优化表编译的 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 FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAxtp5xtp_t_5_949578421.dll

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

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

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

--	查看存储过程编译的 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，则在访问内存优化表时无需通过表提示指定隔离级别。

以下这样使用错误!

--执行出错！
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
	SELECT TOP 5 [Guid],[value]	FROM DBO.MemoryTable ORDER BY NEWID()
ROLLBACK TRAN
GO

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

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

--正确执行方法！以下3种！
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT TOP 5 [Guid],[value]	FROM DBO.MemoryTable ORDER BY NEWID()
GO


BEGIN TRAN
	SELECT TOP 5 [Guid],[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],[value]	FROM DBO.MemoryTable ORDER BY NEWID()
ROLLBACK TRAN
GO

事务隔离测试：

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

--	会话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，查询或更新同一条语句：

--	会话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.当前事务所依赖的前一事务已终止，当前事务无法再提交。

--	内存优化表允许使用更高隔离级别 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) 始终生成更新的统计信息。?

--更新单个内存优化表 (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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!