SQL2014学习笔记3--内存优化表的事务

时间：2022-01-02 18:25:08
不了解内存表的同学请参阅我之前写的学习笔记：
SQL2014学习笔记1--初识内存优化表
SQL SERVER2014学习笔记2--内存优化表索引简介
-------------------------------------------------正文开始--------------------------------------------------------------
        本想写内存表的备份和还原，结果写到一半发现涉及到事务的部分太多，所以单开一篇写事务，篇幅会比较短，但这是理解内存表备份还原机制的基础。

        内存表分两种，一种是只存在于内存中，和磁盘、日志都没半毛钱关系的内存表，这种表在备份还原时只会处理表结构而不处理数据。类似于tempdb中的表，不同之处就是重启数据库时前者保留表结构而后者渣都不剩。
        另一种内存表则在磁盘中有对应的数据文件做数据持久化，MS称之为“数据和差异文件”。这部分文件是通过一个叫“脱机检查点”的后台线程通过定时读取事务日志写入数据的，so，它获得的不是第一手的变更，而是日志文件里记录的变更。

        再来说事务日志与内存表的关系，说到这个关系又涉及到一个2014新出的概念：延迟性事务。延迟性事务机制允许事务提交时通过设置指定的选项，选择是否异步写入日志。什么意思呢？传统的磁盘表是数据页和日志一起写，当日志写成功后提交事务成功，这种机制下事务的执行时间取决于数据文件更新（磁盘表是页，内存表是内存地址）和事务日志更新两部分，当事务日志的更新慢于数据文件时就会拖累事务的提交速度。延迟性事务就是把这两部分分开，数据文件写入完成后事务即成功，而日志文件的写入则同时进行但不影响事务的提交。这种提交方式好处是减少可能的提交延迟时间，坏处是牺牲了一部分事务的完整性。

当延迟性事务配合内存表使用时，会使事务日志对内存表性能的影响降到最低：内存的表的事务执行完全不受日志影响，日志异步写入后，检查点线程定时更新到磁盘的数据文件中。内存表的性能提高的1B，相应的事务完整性则略有牺牲。当数据库崩溃时，没写入日志的内存表变更将丢失。

延迟性事务有两个地方指定：数据库选项中的DELAYED_DURABILITY选项和事务提交时的WITH  DELAYED_DURABILITY
数据库级别的选项可以指定延迟性事务关闭/可选/强制，只有可选时才会受COMMIT选项的影响。

最后强调一下，当内存表不选择使用延迟性事务时，可以认为它和磁盘表的事务完整性一致。

因此，对于我即将写到的内存表备份和还原，涉及到的待久化内容就是：内存表定义、日志文件和数据文件。

57 个解决方案

#1

留名围观。。。

#2

一直在观望，内存优化表的事务完整性问题。 
mark

#3

感谢楼主分享，学习 SQL2014学习笔记3--内存优化表的事务

#4

该回复于2014-04-22 10:50:05被管理员删除

#5

感谢楼主分享，学习 SQL2014学习笔记3--内存优化表的事务

#6

该回复于2014-04-22 12:46:33被管理员删除

#7

支持， 顺便mark一下

#8

快到碗里来吧。。。

#9

学习学习~~

#10

SQL2014学习笔记3--内存优化表的事务SQL2014学习笔记3--内存优化表的事务

#11

谢谢烤鸭分享！

#12

该回复于2014-04-22 15:39:01被管理员删除

#13

该回复于2014-04-23 08:13:54被管理员删除

#14

理解内存表备份还原机制

#15

该回复于2014-04-23 09:26:17被管理员删除

#16

该回复于2014-05-31 11:35:43被管理员删除

#17

学习一下，留着以后用

#18

新功能越来越多了

#19

留名围观12

#20

感谢楼主分享，学习

#21

好文章。总是支持一下的

#22

境界很高啊，一般人写不来的.

#23

境界很高啊~~~~

#24

友學多野野！

#25

真的是很有用的文章 謝謝分享

#26

坐下拜读。。。

#27

顶一个。。。

#28

新功能越来越多了

#29

该回复于2014-05-31 11:35:43被管理员删除

#30

支持一下   留个名

#31

该回复于2014-04-24 09:50:28被管理员删除

#32

支持一下   留个名

#33

感谢楼主分享，学习

#34

我是sql初学者，后面会继续关注lz的，谢谢分享 SQL2014学习笔记3--内存优化表的事务

#35

SQL2014学习笔记3--内存优化表的事务围观学习一下

#36

标准的表，能否直接转为 内存表 ？
ms应该首先想到 用户会问这个 吧？

#37

SQL2014出来了吗？

#38

sql初学者，感谢楼主分享，好好学习中 

#39

一直在观望，内存优化表的事务完整性问题。

#40

引用 36 楼 sz_haitao 的回复:
标准的表，能否直接转为 内存表 ？
ms应该首先想到 用户会问这个 吧？


不但不支持 直接转
就是新建，也与以前不一样（会出现一个设计器），而是一段sql模板！
不支持char/varchar，nvarchar(max)也不支持，每条记录不能大于8k（可能是要预计记录数对应的内存需求） 

--======================================================
-- Add MEMORY_OPTIMIZED_DATA Filegroup and Create Memory Optimized Table Template
-- Use the Specify Values for Template Parameters command (Ctrl-Shift-M) to fill in the parameter values below.
-- This template adds a MEMORY_OPTIMIZED_DATA filegroup to the database, and creates a memory optimized table and indexes on the memory optimized table.
-- The database must have a MEMORY_OPTIMIZED_DATA filegroup before the memory optimized table can be created.
--======================================================

USE ppqpks
GO

--Add MEMORY_OPTIMIZED_DATA filegroup to the database.
ALTER DATABASE ppqpks
ADD FILEGROUP mem_database_filegroup CONTAINS MEMORY_OPTIMIZED_DATA

--Add file to the MEMORY_OPTIMIZED_DATA filegroup.
ALTER DATABASE ppqpks
ADD FILE
  ( NAME = mem_database_filegroup_file1,
    FILENAME = N'e:\Database\mem_database_1')
TO FILEGROUP mem_database_filegroup

--Drop table if it already exists.
IF OBJECT_ID('dbo.mtbKeyValue','U') IS NOT NULL
    DROP TABLE dbo.mtbKeyValue
GO

--Create memory optimized table and indexes on the memory optimized table.
CREATE TABLE dbo.mtbKeyValue
(
	fid int NOT NULL, 
	fkey nvarchar(100) NOT NULL,
	fvalue nvarchar(3900) NOT NULL INDEX index_mtbkeyvalue_fvalue NONCLUSTERED (fvalue), 

   CONSTRAINT PK_sample_memoryoptimizedtable PRIMARY KEY NONCLUSTERED (fid),
   -- See SQL Server Books Online for guidelines on determining appropriate bucket count for the index
   INDEX hash_index_memtable_fkey HASH (fkey) WITH (BUCKET_COUNT = 131072)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO


报错：
消息 12328，级别 16，状态 102，第 28 行
内存优化表上的索引 不支持未使用 *_BIN2 排序规则的字符列上的索引。
消息 1750，级别 16，状态 0，第 28 行
无法创建约束或索引。请参阅前面的错误。

#41

引用 36 楼 sz_haitao 的回复:
标准的表，能否直接转为 内存表 ？
ms应该首先想到 用户会问这个 吧？

内存表的定位就不是常规表，而是少数业务操作非常频繁的表，这部分表在数据库升级后要转为内存表，不是简单的插入就可以，而是要把程序和流程都做对应的调整才能发挥出内存表的性能优势。

#42

直在观望，内存优化表的事务完整性问题。

#43

谢谢楼主分享~

#44

非常感谢！学习了。

#45

该回复于2014-05-31 11:35:44被管理员删除

#46

SQL2014学习笔记3--内存优化表的事务

#47

可以了解!!!!!!!

#48

好东西，感谢

#49

静下来，拜读 SQL2014学习笔记3--内存优化表的事务

#50

我懒看看啊啊啊

#1

留名围观。。。

#2

一直在观望，内存优化表的事务完整性问题。 
mark

#3

感谢楼主分享，学习 SQL2014学习笔记3--内存优化表的事务

#4

该回复于2014-04-22 10:50:05被管理员删除

#5

感谢楼主分享，学习 SQL2014学习笔记3--内存优化表的事务

#6

该回复于2014-04-22 12:46:33被管理员删除

#7

支持， 顺便mark一下

#8

快到碗里来吧。。。

#9

学习学习~~

#10

SQL2014学习笔记3--内存优化表的事务SQL2014学习笔记3--内存优化表的事务

#11

谢谢烤鸭分享！

#12

该回复于2014-04-22 15:39:01被管理员删除

#13

该回复于2014-04-23 08:13:54被管理员删除

#14

理解内存表备份还原机制

#15

该回复于2014-04-23 09:26:17被管理员删除

#16

该回复于2014-05-31 11:35:43被管理员删除

#17

学习一下，留着以后用

#18

新功能越来越多了

#19

留名围观12

#20

感谢楼主分享，学习

#21

好文章。总是支持一下的

#22

境界很高啊，一般人写不来的.

#23

境界很高啊~~~~

#24

友學多野野！

#25

真的是很有用的文章 謝謝分享

#26

坐下拜读。。。

#27

顶一个。。。

#28

新功能越来越多了

#29

该回复于2014-05-31 11:35:43被管理员删除

#30

支持一下   留个名

#31

该回复于2014-04-24 09:50:28被管理员删除

#32

支持一下   留个名

#33

感谢楼主分享，学习

#34

我是sql初学者，后面会继续关注lz的，谢谢分享 SQL2014学习笔记3--内存优化表的事务

#35

SQL2014学习笔记3--内存优化表的事务围观学习一下

#36

标准的表，能否直接转为 内存表 ？
ms应该首先想到 用户会问这个 吧？

#37

SQL2014出来了吗？

#38

sql初学者，感谢楼主分享，好好学习中 

#39

一直在观望，内存优化表的事务完整性问题。

#40

引用 36 楼 sz_haitao 的回复:
标准的表，能否直接转为 内存表 ？
ms应该首先想到 用户会问这个 吧？


不但不支持 直接转
就是新建，也与以前不一样（会出现一个设计器），而是一段sql模板！
不支持char/varchar，nvarchar(max)也不支持，每条记录不能大于8k（可能是要预计记录数对应的内存需求） 

--======================================================
-- Add MEMORY_OPTIMIZED_DATA Filegroup and Create Memory Optimized Table Template
-- Use the Specify Values for Template Parameters command (Ctrl-Shift-M) to fill in the parameter values below.
-- This template adds a MEMORY_OPTIMIZED_DATA filegroup to the database, and creates a memory optimized table and indexes on the memory optimized table.
-- The database must have a MEMORY_OPTIMIZED_DATA filegroup before the memory optimized table can be created.
--======================================================

USE ppqpks
GO

--Add MEMORY_OPTIMIZED_DATA filegroup to the database.
ALTER DATABASE ppqpks
ADD FILEGROUP mem_database_filegroup CONTAINS MEMORY_OPTIMIZED_DATA

--Add file to the MEMORY_OPTIMIZED_DATA filegroup.
ALTER DATABASE ppqpks
ADD FILE
  ( NAME = mem_database_filegroup_file1,
    FILENAME = N'e:\Database\mem_database_1')
TO FILEGROUP mem_database_filegroup

--Drop table if it already exists.
IF OBJECT_ID('dbo.mtbKeyValue','U') IS NOT NULL
    DROP TABLE dbo.mtbKeyValue
GO

--Create memory optimized table and indexes on the memory optimized table.
CREATE TABLE dbo.mtbKeyValue
(
	fid int NOT NULL, 
	fkey nvarchar(100) NOT NULL,
	fvalue nvarchar(3900) NOT NULL INDEX index_mtbkeyvalue_fvalue NONCLUSTERED (fvalue), 

   CONSTRAINT PK_sample_memoryoptimizedtable PRIMARY KEY NONCLUSTERED (fid),
   -- See SQL Server Books Online for guidelines on determining appropriate bucket count for the index
   INDEX hash_index_memtable_fkey HASH (fkey) WITH (BUCKET_COUNT = 131072)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO


报错：
消息 12328，级别 16，状态 102，第 28 行
内存优化表上的索引 不支持未使用 *_BIN2 排序规则的字符列上的索引。
消息 1750，级别 16，状态 0，第 28 行
无法创建约束或索引。请参阅前面的错误。

#41

引用 36 楼 sz_haitao 的回复:
标准的表，能否直接转为 内存表 ？
ms应该首先想到 用户会问这个 吧？

内存表的定位就不是常规表，而是少数业务操作非常频繁的表，这部分表在数据库升级后要转为内存表，不是简单的插入就可以，而是要把程序和流程都做对应的调整才能发挥出内存表的性能优势。

#42

直在观望，内存优化表的事务完整性问题。

#43

谢谢楼主分享~

#44

非常感谢！学习了。

#45

该回复于2014-05-31 11:35:44被管理员删除

#46

SQL2014学习笔记3--内存优化表的事务

#47

可以了解!!!!!!!

#48

好东西，感谢

#49

静下来，拜读 SQL2014学习笔记3--内存优化表的事务

#50

我懒看看啊啊啊
标签：

相关文章