试试SQLServer 2014的内存优化表

试试SQLServer 2014的内存优化表

SQL Server2014存储引擎：行存储引擎，列存储引擎，内存引擎

在数据库中数据是以表的形式存储，所以存储引擎也可以称为表类型。

SQL Server 2014中的内存引擎(代号为Hekaton)将OLTP提升到了新的高度。

现在，存储引擎已整合进当前的数据库管理系统，而使用先进内存技术来支持大规模OLTP工作负载。

就算如此，要利用此新功能，数据库必须包含“内存优化”文件组和表

即所配置的文件组和表使用Hekaton技术。

幸运的是，SQL Server 2014使这一过程变得非常简单直接。

要说明其工作原理，我们来创建一个名为TestHekaton的数据库，然后添加一个内存优化文件组到此数据库

 

 

测试环境：Microsoft Azure 大陆版 虚拟机

4核 ，7G内存，Windows2012R2

SQLSERVER2014企业版

---

实验

第一个实验：内存表的简单使用

 

步骤1：创建数据库和MEMORY_OPTIMIZED_DATA文件组

USE master;

GO

CREATE DATABASE TestHekaton;

GO

ALTER DATABASE TestHekaton

ADD FILEGROUP HekatonFG CONTAINS MEMORY_OPTIMIZED_DATA;

GO

注意ALTER DATABASE语句中的ADD FILEGROUP 语句包含文件组的名称(HekatonFG)和关键字CONTAINS MEMORY_OPTIMIZED_DATA

它会指导SQL Server去创建支持内存OLTP引擎所必需的文件组类型。

注意：每个数据库只能有一个MEMORY_OPTIMIZED_DATA文件组！！

要确认此文件组已经创建，可以访问SSMS中数据库属性的Filegroups 界面，如下图所示。

 

步骤2：

添加一个存放数据文件的文件夹到文件组，可以通过ALTER DATABASE语句来实现。

添加一个存放内存优化表数据的文件夹到HekatonFG文件组：

--执行下面语句之后会在C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA路径下创建一个文件夹
--文件夹名为HekatonFile
--请不要预先创建好这个文件夹
ALTER DATABASE TestHekaton
ADD FILE
(
   NAME = 'HekatonFile',
   FILENAME ='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\HekatonFile'
)
TO FILEGROUP [HekatonFG];
GO

注意：在ADD FILE 语句中，我们只为文件路径提供了一个友好的名称。

并且，在TO FILEGROUP 语句中，指定刚才新建的文件组名字

然后可以去往数据库属性的 Files 界面来查看刚刚添加的文件，如图所示。

 

比如是这样

ALTER DATABASE [test99]
ADD FILE
(
   NAME = 'HekatonFile',
   FILENAME ='D:\DataBase\xtp\'
)
TO FILEGROUP [HekatonFG];
GO

 

步骤3：

在为数据库设置了必需的文件组和文件之后，就可以创建自己的内存优化表了。

当在定义表的时候，会指定其“持久性”。

一个内存优化表可以是持久的或非持久的。

（1）对于一个持久表是将数据存储在内存中，而且也保存在内存优化文件组中。

（2）对于一个非持久表，数据是仅存储在内存中，所以，如果系统崩溃或重启，数据就会丢失。

 

在SQL Server 2014中在不指定持久化选项时候，默认选项是持久化表，接下来我们来深入了解一下。

当定义一个持久内存优化表的时候，你还必须定义一个基于索引的主键。

除了定义主键外，还必须将表配置为内存优化的，如下CREATE TABLE 语句所示：

USE TestHekaton;
GO

CREATE TABLE Reseller
    (
      [ResellerID] INT NOT NULL
                       PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),
      [ResellerName] NVARCHAR(50) NOT NULL ,
      [ResellerType] NVARCHAR(20) NOT NULL
    )
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

INSERT  INTO Reseller
VALUES  ( 1, 'A Bike Store', 'Value Added Reseller' );

 

ResellerID 字段包含了定义为非聚集哈希索引的主键。

注意，必须包含一个WITH 语句来指定BUCKET_COUNT 的设置，它表明了在哈希索引中应该创建的bucket数量。

(每个bucket是一个槽，可以用来存放一组键值对。)

由于每个数据库只能有一个MEMORY_OPTIMIZED_DATA文件组，所以创建表的时候就不需要指定表创建在哪个MEMORY_OPTIMIZED_DATA文件组了

 

此表定义以第二个WITH 语句结束。

这里你指定MEMORY_OPTIMIZED 选项为ON 以及DURABILITY 选项为SCHEMA_AND_DATA，此选项是针对持久表的。

 

 

接着在表中插入一条记录，这样就可以进行测试了。

数据已经插入到表中

 

 

这就是创建一个内存优化表的全部步骤。

但是，要记住，SQL Server 2014对这些表有着很多限制。例如，它们不支持外键或约束检查

内存优化表不支持IDENTITY 字段或DML触发器。最为重要的是，内存耗尽会导致数据写入失败。

 

步骤4：

另一方面，内存优化表支持本地编译存储过程，只要那些存储过程只引用内存优化表。

在这种情况下，存储过程可以转化为本地代码native code，这样会执行更快且要比典型存储过程需要更少的内存。

除了只引用内存优化表，一个本地编译存储过程必须是模式绑定的并运行在一个特定执行内容内。

另外，每个本地编译存储过程必须完全由一个原子块组成。

 

下面的CREATE PROCEDURE 语句定义了一个本地编译存储过程，它从前面所创建的Reseller表中检索数据

CREATE PROCEDURE GetResellerType ( @id INT )
    WITH NATIVE_COMPILATION,
         SCHEMABINDING,
         EXECUTE AS OWNER
AS
    BEGIN
  ATOMIC WITH(TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english')
        SELECT  ResellerName ,
                ResellerType
        FROM    dbo.Reseller
        WHERE   ResellerID = @id
    END;
GO

 

在定义了参数之后，包含一个WITH 语句来指定NATIVE_COMPILATION 选项。

注意：此语句还包含SCHEMABINDING 选项和EXECUTE AS 选项，以及指定了OWNER 作为执行环境。

而WITH 语句负责实现本地编译存储过程的三大需求。

要解决原子块需求，可以在BEGIN 关键字后指定ATOMIC ，之后是另一个包含有事务隔离级别和语言的WITH 语句。

对于访问内存优化表的事务，可以使用SNAPSHOT，REPEATABLEREAD 或SERIALIZABLE 作为隔离级别。

而且，对于此语言必须使用一个可用的语言或语言别名。

这是在定义存储过程时所需要包含的全部内容。一旦创建，就可以通过执行EXECUTE 语句来对其加以测试，如下例中所示：

EXEC GetResellerType 1;

 

此语句会返回经销商的姓名和类型，在本例中分别是ABike Store和Value Added Reseller。

 

内存优化表不使用 TempDB 来存储行版本，所以使用内存优化表不用担心tempdb数据库会暴增。

 

即使没有打开snapshot隔离级别，内存优化表默认会开启snapshot隔离级别，对内存优化表的访问使用的都是snapshot隔离级别

事务不会对内存优化表使用锁和闩锁，但是又可以默认使用snapshot隔离级别保证数据一致性，这是内存优化表的强大之处

use [test99]

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [ResellerID]
      ,[ResellerName]
      ,[ResellerType]
  FROM [test99].[dbo].[Reseller] WITH (SNAPSHOT) where [ResellerID]>0 and [ResellerID] <60 

 

---

第一个实验：内存表的数据查询速度比较

聚集索引表和内存优化表的比较

建表语句

USE TestHekaton;
GO

--内存优化表
CREATE TABLE testmemory1
    (
      [ID] FLOAT NOT NULL
                       PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),
      [Name] NVARCHAR(50) NOT NULL 
    )
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

 

USE TestHekaton;
GO

--聚集索引表
CREATE TABLE testmemory2
    (
      [ID] FLOAT NOT NULL PRIMARY KEY,
      [Name] NVARCHAR(50) NOT NULL 
    )

 

---------------------------------------------------------------

插入性能比较

内存优化表

SET STATISTICS IO ON 
SET STATISTICS TIME ON
INSERT into testmemory1([id],[name])  SELECT [id] ,[name] from sysobjects
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

Table 'sysschobjs'. Scan count 1, logical reads 33, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 20 ms.
(90 row(s) affected)
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 

聚集索引表

SET STATISTICS IO ON 
SET STATISTICS TIME ON
INSERT into testmemory2([id],[name])  SELECT [id] ,[name] from sysobjects
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

Table 'testmemory2'. Scan count 0, logical reads 183, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 33, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 10 ms.
(90 row(s) affected)
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 

-------------------------------------------------------------------------------

查询性能比较

内存优化表

SET STATISTICS IO ON 
SET STATISTICS TIME ON
SELECT * FROM  testmemory1  ORDER BY [ID] DESC
SET STATISTICS IO ON
SET STATISTICS TIME ON

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
(90 row(s) affected)
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 

 

聚集索引表

SET STATISTICS IO ON 
SET STATISTICS TIME ON
SELECT * FROM  testmemory2  ORDER BY [ID] DESC
SET STATISTICS IO ON
SET STATISTICS TIME ON

(91 row(s) affected)
Table 'testmemory2'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 

可以看到内存优化表读写数据（insert 、select）的时候都看不到IO读写

 

补充测试：

 

我们先删除刚才插入的数据，内存优化表是不支持truncate table的，只能用delete from 表

只能够delete

插入测试

内存优化表

聚集索引表

 -------------------------------------------------------------------------------------------------

查询测试

内存优化表

 

聚集索引表

---

我们看一下事务日志

CHECKPOINT
GO

SELECT Context ,
Operation,
AllocUnitName
FROM sys.fn_dblog(NULL, NULL)

 

 

Context	Operation	AllocUnitName
LCX_NULL	LOP_HK	NULL
LCX_NULL	LOP_HK_CHAINED	NULL
LCX_NULL	LOP_HK	NULL
LCX_NULL	LOP_HK_CHAINED	NULL
LCX_NULL	LOP_HK_CHECKPOINT	NULL
LCX_NULL	LOP_HK	NULL
LCX_NULL	LOP_BEGIN_XACT	NULL
LCX_NULL	LOP_FS_DOWNLEVEL_OP	NULL
LCX_NULL	LOP_BEGIN_XACT	NULL
LCX_CLUSTERED	LOP_INSERT_ROWS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_INDEX_LEAF	LOP_INSERT_ROWS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_NULL	LOP_COMMIT_XACT	NULL
LCX_MARK_AS_GHOST	LOP_DELETE_ROWS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_MARK_AS_GHOST	LOP_DELETE_ROWS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_NULL	LOP_HK	NULL
LCX_NULL	LOP_FS_DOWNLEVEL_OP	NULL
LCX_HEAP	LOP_INSERT_ROWS	sys.xtp_storage
LCX_INDEX_LEAF	LOP_INSERT_ROWS	sys.xtp_storage.UQ__xtp_stor__3213E83EA8737D06
LCX_CLUSTERED	LOP_EXPUNGE_ROWS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_CLUSTERED	LOP_EXPUNGE_ROWS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_PFS	LOP_SET_BITS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_INDEX_LEAF	LOP_EXPUNGE_ROWS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_INDEX_LEAF	LOP_EXPUNGE_ROWS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_NULL	LOP_COMMIT_XACT	NULL
LCX_NULL	LOP_BEGIN_XACT	NULL
LCX_NULL	LOP_FS_DOWNLEVEL_OP	NULL
LCX_NULL	LOP_BEGIN_XACT	NULL
LCX_CLUSTERED	LOP_INSERT_ROWS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_INDEX_LEAF	LOP_INSERT_ROWS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_NULL	LOP_COMMIT_XACT	NULL
LCX_MARK_AS_GHOST	LOP_DELETE_ROWS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_PFS	LOP_SET_BITS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_MARK_AS_GHOST	LOP_DELETE_ROWS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_NULL	LOP_FS_DOWNLEVEL_OP	NULL
LCX_HEAP	LOP_INSERT_ROWS	sys.xtp_storage
LCX_INDEX_LEAF	LOP_INSERT_ROWS	sys.xtp_storage.UQ__xtp_stor__3213E83EA8737D06
LCX_NULL	LOP_COMMIT_XACT	NULL
LCX_NULL	LOP_HK	NULL
LCX_CLUSTERED	LOP_EXPUNGE_ROWS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_INDEX_LEAF	LOP_EXPUNGE_ROWS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_PFS	LOP_SET_BITS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_PFS	LOP_SET_BITS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysallocunits.clust
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrowsets.clust
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrscols.clst
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrscols.clst
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrscols.clst
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrscols.clst
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrscols.clst
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrscols.clst
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrscols.clst
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrscols.clst
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrscols.clst
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrscols.clst
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysallocunits.clust
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrowsets.clust
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysallocunits.clust
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrowsets.clust
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrscols.clst
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrscols.clst
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrscols.clst
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysallocunits.clust
LCX_CLUSTERED	LOP_COUNT_DELTA	sys.sysrowsets.clust
LCX_NULL	LOP_BEGIN_CKPT	NULL
LCX_FILE_HEADER	LOP_MODIFY_STREAMFILE_HDR	NULL
LCX_BOOT_PAGE_CKPT	LOP_XACT_CKPT	NULL
LCX_NULL	LOP_END_CKPT	NULL
LCX_NULL	LOP_HK	NULL
LCX_NULL	LOP_HK	NULL
LCX_NULL	LOP_HK	NULL
LCX_NULL	LOP_HK_CHAINED	NULL
LCX_NULL	LOP_HK	NULL
LCX_NULL	LOP_HK	NULL
LCX_NULL	LOP_BEGIN_XACT	NULL
LCX_NULL	LOP_FS_DOWNLEVEL_OP	NULL
LCX_NULL	LOP_BEGIN_XACT	NULL
LCX_CLUSTERED	LOP_INSERT_ROWS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_INDEX_LEAF	LOP_INSERT_ROWS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_NULL	LOP_COMMIT_XACT	NULL
LCX_MARK_AS_GHOST	LOP_DELETE_ROWS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_PFS	LOP_SET_BITS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_MARK_AS_GHOST	LOP_DELETE_ROWS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_PFS	LOP_SET_BITS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_NULL	LOP_HK_CHAINED	NULL
LCX_NULL	LOP_HK_CHAINED	NULL
LCX_NULL	LOP_HK_CHECKPOINT	NULL
LCX_NULL	LOP_FS_DOWNLEVEL_OP	NULL
LCX_HEAP	LOP_INSERT_ROWS	sys.xtp_storage
LCX_INDEX_LEAF	LOP_INSERT_ROWS	sys.xtp_storage.UQ__xtp_stor__3213E83EA8737D06
LCX_NULL	LOP_COMMIT_XACT	NULL
LCX_NULL	LOP_BEGIN_XACT	NULL
LCX_NULL	LOP_FS_DOWNLEVEL_OP	NULL
LCX_NULL	LOP_BEGIN_XACT	NULL
LCX_CLUSTERED	LOP_INSERT_ROWS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_INDEX_LEAF	LOP_INSERT_ROWS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_NULL	LOP_COMMIT_XACT	NULL
LCX_MARK_AS_GHOST	LOP_DELETE_ROWS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_MARK_AS_GHOST	LOP_DELETE_ROWS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_NULL	LOP_FS_DOWNLEVEL_OP	NULL
LCX_HEAP	LOP_INSERT_ROWS	sys.xtp_storage
LCX_INDEX_LEAF	LOP_INSERT_ROWS	sys.xtp_storage.UQ__xtp_stor__3213E83EA8737D06
LCX_NULL	LOP_COMMIT_XACT	NULL
LCX_NULL	LOP_HK	NULL
LCX_CLUSTERED	LOP_EXPUNGE_ROWS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_CLUSTERED	LOP_EXPUNGE_ROWS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_INDEX_LEAF	LOP_EXPUNGE_ROWS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_INDEX_LEAF	LOP_EXPUNGE_ROWS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_PFS	LOP_SET_BITS	sys.filestream_tombstone_2073058421.FSTSClusIdx
LCX_PFS	LOP_SET_BITS	sys.filestream_tombstone_2073058421.FSTSNCIdx
LCX_PFS	LOP_MODIFY_HEADER	Unknown Alloc Unit

 

---

总结

内存优化表也会写事务日志的，在读写操作的时候发现内存优化表没有I/O次数，应该是数据都已经在内存里了

 

 

内存优化表的主要技术限制
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)非聚集哈希索引（这种索引适合点查找，在一个哈希索引中，数据是通过一个内存散列表进行访问的，而非固定大小页）
(2)非聚集索引（这种索引适合范围扫描，Bw-Tree结构，Bucket 大小不固定，随数据动态扩展/收缩）

注意：内存优化表不支持列存储索引和行存索引！！

内存优化表创建后可以加多个索引，可以通过  alter table TB add index IX_TEST  语法添加，也可以通过 PRIMAY KEY 或 UNIQUE 约束隐式添加。

简单来说：  内存优化表必须要有索引，如果选择持久化，必须要加上主键

    CREATE TABLE SupportEvent  
(  
    SupportEventId   int               not null   identity(1,1)  
    PRIMARY KEY NONCLUSTERED,    --建议使用非聚集索引做主键而不是非聚集哈希索引，因为后者更接近于传统基于磁盘表上的非聚集索引

    StartDateTime        datetime2     not null,  
    CustomerName         nvarchar(16)  not null,  
    SupportEngineerName  nvarchar(16)      null,  
    Priority             int               null,  
    Description          nvarchar(64)      null  
)  
    WITH (  
    MEMORY_OPTIMIZED = ON,  
    DURABILITY = SCHEMA_AND_DATA);  
go  

    --------------------  

ALTER TABLE SupportEvent  
    ADD CONSTRAINT constraintUnique_SDT_CN  
    UNIQUE NONCLUSTERED (StartDateTime DESC, CustomerName);  
go  

ALTER TABLE SupportEvent  
    ADD INDEX idx_hash_SupportEngineerName  
    HASH (SupportEngineerName) WITH (BUCKET_COUNT = 64);  -- Nonunique.  
go  

    --------------------  
    
    

4. 内存优化表事务隔离级别
访问内存优化表的事务支持的隔离级别:SNAPSHOT,REPEATABLE READ,SERIALIZABLE,READ COMMITTED 。
内存优化表不使用锁。只能使用更高的隔离级别（如 REPEATABLE READ 和 SERIALIZABLE）。
不支持锁定提示。改为通过事务隔离级别更改。
（支持 NOLOCK 是因为 SQL Server 不对内存优化表使用锁。请注意，与基于磁盘的表不同，NOLOCK 对于内存优化表并不表示跟READ UNCOMMITTED 行为相同。）

 

 

事务

跨容器事务

术语“跨容器”源于这样的事实：事务跨两个事务管理容器运行，一个用于基于磁盘的表，另一个用于内存优化表。
一个事务需要访问磁盘表和内存优化表就叫跨容器事务

在单个跨容器事务中，可以使用不同的隔离级别来访问基于磁盘的表和内存优化表。
这种差异通过显式表提示（如 WITH (SERIALIZABLE)）或数据库选项 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT（如果事务隔离级别配置为 READ COMMITTED 或 READ UNCOMMITTED，此选项会将内存优化表的隔离级别隐式提升为 SNAPSHOT）来体现。

如果使用跨容器事务需要设置
针对整个库
ALTER DATABASE test  SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON
或
针对单条语句
WITH (SERIALIZABLE)
WITH (SNAPSHOT)

--（1）不打开MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT 要显式使用with(snapshot)
ALTER DATABASE [test] SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=off

BEGIN TRANSACTION;  

   
SELECT * FROM  [dbo].[users] AS A INNER JOIN  [dbo].[Reseller]   AS B with(snapshot)
ON A.ID=B.RESELLERID 
 

COMMIT TRANSACTION; 


--（2）打开MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT 
ALTER DATABASE [test] SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=on

BEGIN TRANSACTION;  

   
SELECT * FROM  [dbo].[users] AS A INNER JOIN  [dbo].[Reseller]   AS B 
ON A.ID=B.RESELLERID 
 

COMMIT TRANSACTION; 

 

 

对于内存优化表，另一个场景是unlogged tables功能，例如数据仓库的暂存表或会话状态表（会话结束状态数据就不需要了），这个场景对表的处理速度有要求，因为表数据不需要复制，也不会增加事务日志的负担，从而使插入、更新和删除操作更快。
对于高性能应用程序的简化非常有帮助，特别是在处理大量临时ETL（提取、转换、加载）数据时。

正如上文所说的，DURABILITY 选项决定了表的持久性，即数据在系统重启后是否还存在。
SCHEMA_ONLY：这个选项指表的数据在数据库重启后不会被持久化，只有表的结构（schema）是持久的。使用这种模式时，表的数据存储在内存中，但不会写入磁盘。
SCHEMA_AND_DATA：这个选项指表的结构和数据都将被持久化到磁盘。这确保了即使在数据库重启后，数据也会被保留。

CREATE TABLE Reseller
(
    [ResellerID] INT NOT NULL
    PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),
    [ResellerName] NVARCHAR(50) NOT NULL ,
    [ResellerType] NVARCHAR(20) NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);  --数据在数据库重启后丢弃

这样，在需要优化大量数据转储和重载场景的，内成优化表是满足这个需求的。

 

 

 

 

 

更多详细资料请参考：

SQL Server 2014 新特性——内存数据库

SQL Server 2014新特性：分区索引和内存优化表

MSDN:内存优化表

SQL Server 2014 内存优化表（1）实现内存优化表

SQLServer 2014 内存优化表

内存优化表中的事务

内存优化的表的索引

 

Bw 树的相关文章
https://docs.microsoft.com/zh-cn/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver15#inmem_nonclustered_index
https://www.microsoft.com/en-us/research/publication/the-bw-tree-a-b-tree-for-new-hardware/
https://zhuanlan.zhihu.com/p/29314464

 

如有不对的地方，欢迎大家拍砖o(∩_∩)o