SQL server 2014 内存表特性概述

 

内存优化表是SQL server2014版本中推出的新特性之一。也是基于create table创建的，只不过是驻留在内存中表。从内存读取表中的行和将这些行写入内存。 整个表都驻留在内存中。表数据的另一个副本维护在磁盘上，但仅用于持续性目的。内存中 OLTP 与 SQL Server 集成，以便在所有方面（如开发、部署、可管理性和可支持性）提供无缝体验。 内存优化表中的行是版本化的。 这意味着表中的每行都可能有多个版本。 所有行版本均维护在同一个表数据结构中。 本文主要描述SQL server 2014内存表的相关特性。

一、基本特性

是一张持续驻留在内存中的表。
    使用基于行版本化特性(等同于Oracle MVCC)，需要维护每一个行的多个不同版本。
    行版本控制用于实现对同一行的并发读取和写入，注意此处是并发。
    如表tb1有三行：r1、r2 和 r3。 r1 有三个版本，r2 有两个版本，r3 有四个版本。
    同一行的不同版本不必占用连续的内存位置。 不同的行版本可分散到整个表数据结构中。

二、持久化特性

支持事务(ACID)原则的完全持久化表，因为磁盘上会有相应的副本。
        使用延迟事务提交写入磁盘。缺点是丢失已提交但未保存到磁盘的事务。
    非持久的内存优化表，不记录这些表的日志且不在磁盘上保存它们的数据。掉电丢失，等同mysql memory引擎。

三、性能与可伸缩

使用本机编译的存储过程获得最佳性能，解释性TSQL一般。
    对于基于复杂存储过程实现逻辑，且应用较少调用的的场景，表现优异。
    内存表哈希索引高于非聚集索引，内存表非聚集索引性能高于磁盘表非聚集索引。
    解决了IO瓶颈，缺点是需要增大内存开销。
    避免了闩锁与旋转锁争用。
    基于乐观并发控制形式来实现所有事务隔离级别，解决了读阻塞写的问题。Oracle是用MVCC及undo来搞定。

四、内存优化表的使用场景

最近一个朋友找到走起君，咨询走起君内存优化表如何作高可用的问题html

你们知道，内存优化表做为In-Memory OLTP功能是从SQL Server 2014开始引入，用来对抗Oracle 12C的In-Memory OLTP选件redis

不过SQL Server的In-Memory OLTP功能是彻底内置的功能，不像Oracle须要额外付费才能得到sql

因为是比较新的技术，可能你们对内存优化表仍是比较陌生，网上也鲜有内存优化表使用场景的文章数据库

朋友公司作的业务是跟蜂鸟配送相似的配送业务，整个配送系统平台天天订单量超过30W服务器

---

坐标问题并发

系统中某一个部分须要保存跑男的坐标性能

坐标须要保存到redis和数据库，一旦坐标更新也须要更新redis中的数据优化

刚开始朋友用传统表来保存坐标数据，可是很快遇到问题，传统表在更新的速度跟不上spa

后来改用内存优化表，使用了以后日志

刚开始上传坐标的接口，延迟很大，用了内存表，100毫秒之内，搞定

这些坐标是须要持久化的，而内存优化表是彻底支持ACID的，因此也不须要担忧数据丢失的问题

内存优化表更新速度快的另外一个缘由：无锁机制，  并发（如闩锁争用或阻塞）影响的应用程序迁移到内存中 OLTP 时，其性能会显著提升。

你们知道，内存优化表须要有一个非汇集哈希主键索引，大概的表结构是

每一个跑男占用一行记录

对应到redis里面你们应该都知道怎麽存储了吧，使用redis的散列类型来存储跑男的坐标

hmset 跑男ID X坐标 value Y坐标 value 跑男在线时间 value
hmset  1  X坐标 12  Y坐标 10  跑男在线时间 60

由于数据库高可用的问题，朋友就购置了新服务器，用来搭建AlwaysOn，新服务器都用SSD固态硬盘

内存优化表的瓶颈主要在事务日志固化，虽然有延迟持久化，可是延迟持久化在乎外宕机的时候可能丢失部分数据

如今新服务器使用SSD固态硬盘以后，事务日志固化的瓶颈基本消失

使用新服务器以后，支撑30w/日订单是彻底没有问题的

---

另外一个问题是AlwaysOn问题

实际上，SQL Server 2014的AlwaysOn集群已经支持内存优化表，只是不支持在辅助副本上查询内存优化表数据，在故障转移以后

辅助副本上的内存优化表数据是彻底没有丢失的，SQL Server 2016对AlwaysOn集群的内存优化表作了改进，支持在辅助副本上查询内存优化表数据  

---

总结

实际上，若是你们对内存优化表研究比较深刻的话，内存优化表实际上至关于把redis嵌入到SQL Server，再在上面加上事务等关系型数据库特性

由于二者实现的底层都是哈希表。

注意：内存优化表跟redis同样，是纯内存操做的，因此机器内存不能过小，SQL Server在启动时候会把内存优化表数据库文件

里面的数据所有load入内存，朋友的redis服务器和SQL Server服务器都用的256G内存，内存还算足够。

五、使用

sql server 内存表
--1加内存表

EXEC sp_tableoption '表名','pintable', 'true' 

--2卸载内存表

EXEC sp_tableoption '表名','pintable', 'false' 

--2查询是否有内存表驻留

SELECT * from INFORMATION_SCHEMA.Tables 

WHERE TABLE_TYPE = 'BASE TABLE' 

          AND OBJECTPROPERTY(object_id(TABLE_NAME), 'TableIsPinned') > 0

以下是最简单的创建内存表的方法:
create   table  ##t(id  int ,name  varchar ( 50 ),years  int ,nums  int )
 -- 创建内存表

 insert  ##t  select   1 , ' nn ' , 14 , 15
 union   all   select   1 , ' nn ' , 14 , 15
 insert   into  ##t   exec  sp_gets
 -- 插入数据

 select   *   from  ##t
 -- 查询表

 drop   table  ##t
 -- 删除表

内存优化表（Memory-Optimized Tables）是SQL Server 2014的新特性，目前仅适用于评估版（Evaluation Edition）、开发版（Developer Edition）和企业版（Enterprise Edition）。

　　本系列专题将从以下 5 个部分探讨内存优化表：

（1）实现内存优化表

（2）操作内存优化表

（3）索引结构分析

（4）本机编译存储过程

（5）迁移到内存优化表

一、概述

1. 磁盘表与数据优化表

　　传统意义上的磁盘表（Disk-Based Tables）是保存在磁盘上的。针对表的数据页（page），主要有以下操作：

（1）当SQL Server需要对这个表进行增删改查的时候，从磁盘读取需要的数据页并加载到内存缓冲区。

（2）当数据页需要被修改时，首先在内存缓冲区中修改，同时修改的情况（事务）被记录到事务日志文件。

（3）当遇到检查点（Checkpoint）时，内存缓冲区中被修改过的数据页将回写到磁盘。

　　SQL Server 2014引入了OLTP数据优化，主要特色是引入了内存优化表，在内存中实现对该表的增删改查操作，从而提高OLTP的性能。

2. 内存优化表的类型

　　内存优化表可以分为以下2种类型：

（1）持久化的内存优化表

　　在创建时使用“DURABILITY = SCHEMA_AND_DATA”参数，可以在磁盘上保留了一个用于“持久化”的副本（FileStream方式）。在数据库启动时，整个表的结构和数据都将再次从磁盘装载到内存中。这类表在操作时会有数据流写入磁盘，同时也有事务日志写入磁盘。

（2）仅结构的内存优化表

　　在创建时使用“DURABILITY = SCHEMA_ONLY”参数，那么数据将只保留在内存中，没有其它副本。当数据库重启后，该表的结构被重建（一张空表），但表中的数据都已经不存在了。而且这类表在操作时没有记录事务日志。可以用作全局临时表，或者ETL时用于存储中间数据。

二、准备数据库

1. 创建一个数据库

　　事先准备好一个SQL Server 2014的数据库，例如，“MOTDB”。为了避免事务日志文件对性能的影响，我们将日志文件放在第二块硬盘，并且将恢复模式修改为“简单”。

CREATE DATABASE [MOTDB]

 CONTAINMENT = NONE

 ON  PRIMARY 

( NAME = N'MTODB', FILENAME = N'C:\MSSQL\Data\MTODB.mdf' , SIZE = 102400KB , FILEGROWTH = 102400KB )

 LOG ON 

( NAME = N'MTODB_log', FILENAME = N'D:\MSSQL\Log\MTODB_log.ldf' , SIZE = 51200KB , FILEGROWTH = 51200KB )

GO


ALTER DATABASE [MOTDB] SET RECOVERY SIMPLE 

GO

2. 添加内存优化数据文件组

　　为这个数据库添加一个内存优化数据（MEMORY_OPTIMIZED_DATA）文件组，从而启用了内存优化数据的功能。每个数据库只能有一个内存优化数据文件组。

2.1 SSMS方式

 

2.2 T-SQL方式

ALTER DATABASE [MOTDB]  ADD FILEGROUP [MOT_FileGroup]  CONTAINS MEMORY_OPTIMIZED_DATA

3. 添加FileStream数据文件

　　对于“持久化”的内存优化表，表的副本将以FileStream的格式保存到磁盘，因此需要为FileStream添加一个数据文件。

3.1 SSMS方式

 

 

3.2 T-SQL方式

ALTER DATABASE [MOTDB]  ADD FILE ( NAME = N'MOT_File', FILENAME = N'C:\MSSQL\Data\MOT_File' )  TO FILEGROUP [MOT_FileGroup]

三、实现内存优化表

1. 创建“持久化”内存优化表

　　只能使用 T-SQL 创建内存优化表，例如：

CREATE TABLE [dbo].[Table_SchemaData] ( [UserID] [int] NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 204800), [UserName] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [AddressLine1] [nvarchar](20) NULL, [AddressLine2] [nchar](3000) NULL, )  WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA )

　　T-SQL语句必须包含以下3个子句：

（1）“HASH WITH (BUCKET_COUNT = 204800)”指定 HASH 存储桶的数目为204800。建议 HASH 存储桶的数量为整个内存优化表的总行数的两倍。目前SQL Server不支持动态的Hash Bucket，因此必须手动设置该值。

（2）“MEMORY_OPTIMIZED = ON”指定表为内存优化表。

（3）“DURABILITY = SCHEMA_AND_DATA”指定内存优化表同时在硬盘上保留一个副本。

注：创建内存优化表之后，FileStream 文件夹的大小从数百 KB 增长到 153MB。

2. 创建“仅结构”的内存优化表

CREATE TABLE [dbo].[Table_SchemaOnly] ( [UserID] [int] NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 204800), [UserName] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [AddressLine1] [nvarchar](20) NULL, [AddressLine2] [nchar](3000) NULL, )  WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY )

注：“仅结构”的内存优化表不需要 FileStream，此时 FileStream 文件夹的大小基本不变。

四、内存优化表的主要技术限制

1. 排序规则

　　内存优化表的排序规则可以从数据库的排序规则继承下来，也可使用 COLLATE 关键字显式指定。 如果数据库包含内存优化表或本机编译存储过程，则无法更改数据库排序规则。

　　排序规则必须是1252代码页，例如 SQL_Latin1_General_CP1_CI_AS。否则报错。

消息 12329，级别 16，状态 103，第 1 行 内存优化表 不支持使用的排序规则所具有的代码页并非 1252 的数据类型 char(n) 和 varchar(n)。

　　作为一种变通的方案，可以使用数据类型 nchar(n) 和 nvarchar(n) 。

2. 数据行的宽度

　　每一行数据不能超过1个页（8KB）。否则报错。

消息 41307，级别 16，状态 1，第 1 行 已超过内存优化的表的 8060 字节行大小限制。请简化表定义。

3. 索引

　　非聚集哈希索引是内存优化表唯一支持的索引类型。在一个哈希索引中，数据是通过一个内存散列表进行访问的，而非固定大小页。（后文详叙）

五、确认内存优化表

1. 查看启动日志

　　重启数据库之后，启动日志（例如：C:\Progra...\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG 文件）会记录以下事件。

2014-12-23 18:18:27.16 spid24s     Recovery of database 'MOTDB' (9) is 2% complete (approximately 288 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required. 2014-12-23 18:18:32.10 spid8s      Recovery completed for database MOTDB (database ID 9) in 13 second(s) (analysis 8539 ms, redo 0 ms, undo 4832 ms.) This is an informational message only. No user action is required. 2014-12-23 18:18:32.10 spid24s     [INFO] HkCheckpointCtxtImpl::StartOfflineCkpt(): Database ID: [9]. Starting offline checkpoint worker thread on a hidden SOS scheduler. 2014-12-23 18:18:32.12 spid8s      Recovery is complete. This is an informational message only. No user action is required.

2. 查看 FileStream 数据文件

　　FileStream 数据文件实际上是一个文件夹。

 

删除文件

使用一个单独非alwayson环境的数据库测试。

一、创建内存表

---创建内存表文件组
ALTER DATABASE [test] ADD FILEGROUP [test_ag] CONTAINS MEMORY_OPTIMIZED_DATA 
GO 
----创建内存表数据库文件
ALTER DATABASE [test] 
ADD FILE 
( 
 NAME = 'test_memory', 
 FILENAME ='D:\database\memory' 
) 
TO FILEGROUP [test_ag]; 
GO

二、删除内存表数据库文件

USE [test]
GO
ALTER DATABASE [test] REMOVE FILE [test_memory]
GO

 

备注：此时还未创建表，创建完后数据库文件执行删除就无法删除，接下来试试在线文档的删除方法方法

三、官方相关的删除方法

即使已使用“DBCC SHRINKFILE”操作清空 FILESTREAM 容器，但出于各种系统维护原因，数据库可能仍然需要保留对已删除文件的引用。 sp_filestream_force_garbage_collection (TRANSACT-SQL)将运行 FILESTREAM 垃圾回收器删除这些文件时，则可以安全进行这些操作。 除非 FILESTREAM 垃圾回收器已从 FILESTREAM 容器中删除所有文件，否则 ALTER DATABASEREMOVE FILE 操作将无法删除 FILESTREAM 容器并返回错误。 建议使用以下过程删除 FILESTREAM 容器。

1.运行DBCC SHRINKFILE (TRANSACT-SQL)带有 EMPTYFILE 选项以将此容器的活动内容移动到其他容器

USE test; 
GO 
-- Create a data file and assume it contains data. 
ALTER DATABASE test 
ADD FILE ( 
 NAME = Test1data, 
 FILENAME = 'D:\database\t1data.ndf', 
 SIZE = 5MB 
 ); 
GO 
-- Empty the data file. 
DBCC SHRINKFILE (test_memory, EMPTYFILE); 
GO

2.确保已在 FULL 或 BULK_LOGGED 恢复模型中执行日志备份。

3.确保复制日志读取器作业已运行（如果相关）。

通过log_reuse_wait_desc的状态可以看到当前数据库已经无需日志备份，当然我已经执行过日志备份。

4.运行sp_filestream_force_garbage_collection (TRANSACT-SQL)强制垃圾回收器删除不再需要此容器中的任何文件

USE [test]
GO 
EXEC sp_filestream_force_garbage_collection @dbname = N'test' @filename = N' test_memory '; 

USE [test]
GO
ALTER DATABASE [test] REMOVE FILE [test_memory]
GO

还是无法删除！！！

四、问题分析

一开始是在alwayson的环境中删除，提示由于副本的原因无法删除。后面单独在一个非alwayson的环境下的数据库测试同样是无法删除，起初以为是创建了内存表的原因后面测试仅仅创建文件组和文件然后来删除文件同样是无法删除，个人猜测有可能是buffer的缘故；在buffer中一直存在内存表相关的文件存在，通过执行DBCC DROPCLEANBUFFERS命令也无法清空buffer中的内存表对象。使尽浑身解数还是无法将它删除掉，最后只能投降了！！！线上环境等不下去；只能使用最不愿使用的生成表结构导出数据的办法来重建新的数据库。

生成脚本重建数据库

创建一个新的数据库同时保证当前数据库可用（重命名当前的数据库，新创建的数据库使用之前的名称这样可以保证应用程序那边不需要改变），这样如果出现什么问题也可以及时的切换回来。

步骤如下（在允许停机维护的情况下进行）：

1.禁用所有相关作业

2禁用应用程序登入用户

同时保证相关进程事务都已完成。

ALTER LOGIN [test] DISABLE
GO

USE [master]
GO
ALTER DATABASE [test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;--将数据库设置成单用户并回滚当前连接

USE [test];---保持连接操作,防止其它用户此时进行连接
GO

3.执行checkpoint刷新所有脏页

CHECKPOINT

---返回当前buffer中每个数据库所占的buffer大小和buffer中脏页的大小
WITH CTE1
 AS ( SELECT COUNT(*) * 8 / 1024 AS dirty_cached_size_MB ,
  COUNT(*) AS dirty_pages,
  CASE database_id
  WHEN 32767 THEN 'ResourceDb'
  ELSE DB_NAME(database_id)
  END AS database_name
 FROM sys.dm_os_buffer_descriptors
 WHERE is_modified = 1
 GROUP BY DB_NAME(database_id),database_id
 ),
 CET2
 AS ( SELECT COUNT(*) * 8 / 1024 AS cached_size_MB ,
  COUNT(*) AS pages,
  CASE database_id
  WHEN 32767 THEN 'ResourceDb'
  ELSE DB_NAME(database_id)
  END AS database_name
 FROM sys.dm_os_buffer_descriptors
 GROUP BY DB_NAME(database_id),database_id
 )
 SELECT 
 CET2.database_name,
 CET2.cached_size_MB,
 --CET2.pages,
 CTE1.dirty_cached_size_MB
 --CTE1.dirty_pages 
 FROM CTE1 INNER JOIN CET2 ON CTE1.database_name = CET2.database_name

---将数据库选项改成多用户访问
ALTER DATABASE [test] 
SET MULTI_USER;

4.生成数据库脚本

5.重命名旧的数据库

注意：如果数据库是在alwayson中，需要先从可用性数据库中删除，否则无法重命名数据库。

/*
1.断开数据库所有连接同时禁止新的连接进来
2.比如禁止登入用户、将实例设为单用户模式等。
*/
----1.设置数据库脱机
USE [master] 
ALTER DATABASE [test] SET OFFLINE WITH ROLLBACK IMMEDIATE;

----2.手动修改数据库物理文件名，例如将test.mdf改成test_old.mdf

----3.语句修改
USE [master] 
ALTER DATABASE [test] 
MODIFY FILE (NAME = test, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\test_old.mdf');
GO
ALTER DATABASE [test] 
MODIFY FILE (NAME = test_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\test_old_log.ldf');
GO

---4.设置数据库在线
USE [master] 
ALTER DATABASE [test] SET ONLINE


----5.修改数据库逻辑文件名

USE [test]
GO
ALTER DATABASE [test] MODIFY FILE (NAME=N'test', NEWNAME=N'test_old')
GO
USE [test]
GO
ALTER DATABASE [test] MODIFY FILE (NAME=N'test_log', NEWNAME=N'test_old_log')
GO

----6.重命名数据库
USE [master] 
EXEC sp_renamedb N'test', N'test_old'; 

----7.查询
SELECT *
FROM sys.master_files
WHERE database_id = DB_ID('test_old');

6.创建新的数据库同时导入脚本到新的数据库

如果同时导出表结构和数据在ssms工具中执行可能会因为脚本过大无法执行，可以使用sqlcmd工具执行脚本导入，具体方法可以百度一下。当然还有其他方法就是只导出表结构然后通过“导出数据\导入数据”的方法同步数据。

注意：如果使用“导出数据\导入数据”的方法同步数据，注意勾选“启用标示插入”

7.其它

      1.如果存在alwayson记得将新的数据库加入到可用性数据库组中。

      2.将新的数据库加入到备份作业中。

      3.对比新旧两个数据库的表数量是否相同。

      4.配置登入用户新的数据库权限。

总结

内存表是2014新引入的功能所以对于新功能的第一个版本使用要比较慎重，特别是在线上环境。虽然在上线之前做过测试，但是显然备份这块的测试往往比较容易被忽略因为没有线上的这种环境。好在是这次影响的是一个新上的项目数据量和并发都很小且允许节假日停机维护；如果是非常大的系统对于需要导入导出数据肯定是非常头疼的事情关键还得看允许停机的时长。因为自己在生产环境踩了坑，写这篇文章希望后面的人可以避免踩坑。

备注：内存表在2014版本的alwayson中无法同步到辅助副本，这就导致了它的作用大打折扣，2016版本可以同步到辅助副本，建议有条件的直接上2016。

好了，以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对脚本之家的支持。