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 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,
	[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 操作(约束检查、外键、添加或删除索引、添加或删除列、更改列属性)
以下几种情况在内存优化表是不支持的:

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



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

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

--	查看内存优化表编译的 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


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

(更多参考: 本机编译存储过程

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

--	查看存储过程编译的 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],[Name],[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],[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执行如下语句,使更新堵塞:

--	会话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


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


©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页