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

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

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

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

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

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

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

因此,对于我即将写到的内存表备份和还原,涉及到的待久化内容就是:内存表定义、日志文件和数据文件。
...全文
8553 1 收藏 57
写回复
57 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
习惯性蹭分 2015-01-01
MARK下,感觉要把之前的基础打一下。
回复
引用 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的简直就没法用。
回复
yibu52 2014-06-21
学习围观
回复
朝露昙花 2014-05-07
感谢楼主分享,学习
回复
guojing6512 2014-05-06
DINGDING顶顶更健康
回复
HUSTYZHY 2014-05-05
多谢,很有用
回复
谢谢分享,mark 下
回复
baojianqsd 2014-04-30
我懒看看啊啊啊
回复
Neo_whl 2014-04-28
静下来,拜读
回复
my03131302 2014-04-27
回复
朝露昙花 2014-04-27
好东西,感谢
回复
chenghao007 2014-04-27
可以了解!!!!!!!
回复
mesage 2014-04-26
非常感谢!学习了。
回复
haitao 2014-04-25
引用 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 行 无法创建约束或索引。请参阅前面的错误。
回复
鸥翔鱼游1 2014-04-25
一直在观望,内存优化表的事务完整性问题。
回复
zhuzhu_168 2014-04-25
sql初学者,感谢楼主分享,好好学习中
回复
Uricacid 2014-04-25
谢谢楼主分享~
回复
风中映雪 2014-04-25
SQL2014出来了吗?
回复
huangxing999 2014-04-25
直在观望,内存优化表的事务完整性问题。
回复
guguda2008 2014-04-25
引用 36 楼 sz_haitao 的回复:
标准的表,能否直接转为 内存表 ? ms应该首先想到 用户会问这个 吧?
内存表的定位就不是常规表,而是少数业务操作非常频繁的表,这部分表在数据库升级后要转为内存表,不是简单的插入就可以,而是要把程序和流程都做对应的调整才能发挥出内存表的性能优势。
回复
发帖
新技术前沿
创建于2007-09-28

6124

社区成员

MS-SQL Server 新技术前沿
申请成为版主
帖子事件
创建了帖子
2014-04-21 11:54
社区公告
暂无公告