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

[guguda2008]

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

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

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

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

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

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

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

[guguda2008]

引用 36 楼 sz_haitao 的回复:

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

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

[huangxing999]

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

[风中映雪]

SQL2014出来了吗？

[Uricacid]

谢谢楼主分享~

[zhuzhu_168]

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

[鸥翔鱼游1]

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

[haitao]

引用 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 行 无法创建约束或索引。请参阅前面的错误。

[mesage]

非常感谢！学习了。

[chenghao007]

可以了解!!!!!!!

[朝露昙花]

好东西，感谢

[my03131302]

[Neo_whl]

静下来，拜读

[baojianqsd]

我懒看看啊啊啊

[我现在在路上]

谢谢分享，mark 下

[HUSTYZHY]

多谢，很有用

[guojing6512]

DINGDING顶顶更健康

[朝露昙花]

感谢楼主分享，学习

[yibu52]

学习围观

[好记忆不如烂笔头abc]

引用 40 楼 sz_haitao 的回复:

[quote=引用 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 行 无法创建约束或索引。请参阅前面的错误。 [/quote] sqlserver内存表和oracle12c的内存表选件使用上差太多档次了。 oracle的内存表使用几乎没有任何限制，几乎和普通表一样。一个简单的命令转换即可。而sqlserver的简直就没法用。

[习惯性蹭分]

MARK下，感觉要把之前的基础打一下。