SQL 2014新功能介绍系列1 - 内存中 OLTP (In-Memory OLTP)

SQL Server 2014引入了内存优化的数据库技术来优化OLTP的工作性能。更特别的是，它引入了内存优化的表来提高效率以及非争夺式数据访问，还引入了本地编译的存储过程来实现业务逻辑的有效执行。

内存中OLTP是一种为内存优化及OLTP性能优化设计的新的数据库引擎。内存中OLTP是完整集成在SQL Server中的，并不是一个分离的系统。用户可以通过定义一个被频繁访问的内存优化的表来利用内存中OLTP的优势。内存中OLTP是完全事务性的，持久性的，并且跟之前的SQLServer中的表一样可以通过T-SQL访问。一个查询语句既可以引用内存中OLTP的表也可以引用普通的表，一个事务同样也可以更新这两种表中的数据。只引用内存中 OLTP表的代价高的T-SQL存储过程可以通过本地编译的方式以备未来的性能优化。这种引擎是为了处理高并发的OLTP型的向外扩展的中间层事务而设计的。为了达到这个目的，它使用了不加锁的数据结构并且使用了一种新的多版本的积极并发控制技术。最终的结果是有选择性的迁移和增量迁移到内存中OLTP可以提供可预测的亚毫秒级的低延时和可线性扩展的高吞吐量的数据库事务。实际的性能提升会受到许多因素的影响，但是我们一般可以看到用户的工作负载有5X-20X的变化。

本文将简单介绍内存中OLTP的新语法，并且将向您展示如何使用T-SQL和 SQL Server Management Studio (SSMS)来探索内存中OLTP.

开始之前，我们新建一个样本数据库，如果您已经有其他数据库可以选择跳过此步骤。

-- Optional: create database 

CREATE DATABASE imoltp 

GO

SSMS: 新建一个数据库

1. 在Object Explorer里，连接到数据库引擎的一个实例，然后展开这个实例

2. 右击数据库，点击new database 然后输入数据库的名字

第一步：设置您的数据库以支持内存中OLTP

我们将要为memory_optimized_data添加一个文件组，并且添加一个该文件组的容器。这个文件组可以保证常驻内存数据的持久性，即使数据库发生崩溃或者重启。在服务器启动后的崩溃复原阶段，数据将会从该文件组复原并且重新加载回内存里。

在创建memory_optimized_data的容器时，必须指定存储位置。在本例中我们选择文件夹‘c:\data’。请确保选择的文件夹在执行脚本之前是存在的。

-- enable for in-memory OLTP - change file path as needed 

ALTER DATABASE imoltp ADD FILEGROUP imoltp_mod CONTAINS MEMORY_OPTIMIZED_DATA 

ALTER DATABASE imoltp ADD FILE (name='imoltp_mod1', filename='c:\data\imoltp_mod1') TO FILEGROUP imoltp_mod 

GO

SSMS: 添加memory_optimized_data文件组和它的容器，

1. 在Object Explorer中, 展开数据库节点, 右键点击数据库，单击属性

2. 添加一个新的内存优化数据的文件组，点击文件组页面。在MEMORY OPTIMIZED DATA选项下,单击添加文件组并设置文件组的各项值。

3. 在文件组中添加文件，点击general 页面。 在Database files下，点击添加并且输入文件的各项值。File type 设置为FILESTREAM Data。

 

第二步：创建您的第一个内存优化的表

我们现在已经准备好创建第一个内存优化的表。目前我们有两张表，‘ShoppingCart’和‘UserSession’。‘ShoppingCart’是一个持久化的表（默认值），这意味着表中的内容是存储在磁盘上的，不会因为服务器崩溃而丢失。‘UserSession’是一个非持久化的表(DURABILITY=SCHEMA_ONLY)，这意味着表中内容仅存储在内存中，服务器重启即会丢失。

注：SQL 2014 内存优化的表支持非聚集哈希索引(hash index)和非聚集索引(rang index)。hash索引的Bucket_cout建议值是表中能找到的唯一索引键值个数的4到8倍。

-- create memory optimized tables 

USE imoltp 

GO

-- durable table （持久化的表）

CREATE TABLE dbo.ShoppingCart ( 

   ShoppingCartId int not null primary key nonclustered hash with
   (bucket_count=2000000), 

   UserId int not null index ix_UserId nonclustered hash with (bucket_count=1000000), 

   CreatedDate datetime2 not null, 

   TotalPrice money 

) 

WITH (MEMORY_OPTIMIZED=ON) 

GO

-- non-durable table （非持久化的表）

CREATE TABLE dbo.UserSession ( 

   SessionId int not null primary key nonclustered hash with (bucket_count=400000), 

   UserId int not null, 

   CreatedDate datetime2 not null, 

   ShoppingCartId int, 

   index ix_UserId nonclustered hash (UserId) with (bucket_count=400000) 

) 

WITH(MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY) 

GO

SSMS：新建一个内存优化表

1. 在Object Explorer，右键单击您数据库的Tables节点，点击new,然后点击Memory Optimized Table。然后可以看到创建内存优化表的模板。

2. 替换模板中的参数，在query菜单中点击Specify Values for Template Parameters。快捷键是Ctrl-Shift-M。

第三步：加载您的数据

您可以通过多种方式把数据加载到表中，包括 INSERT .. SELECT from一个已经存在的存储在磁盘上的表和BCP。在本例中我们使用简单的INSERT语句来加载数据。

-- Basic DML 

-- insert a few rows 

INSERT dbo.UserSession VALUES (1,342,GETUTCDATE(),4) 

INSERT dbo.UserSession VALUES (2,65,GETUTCDATE(),NULL) 

INSERT dbo.UserSession VALUES (3,8798,GETUTCDATE(),1) 

INSERT dbo.UserSession VALUES (4,80,GETUTCDATE(),NULL) 

INSERT dbo.UserSession VALUES (5,4321,GETUTCDATE(),NULL) 

INSERT dbo.UserSession VALUES (6,8578,GETUTCDATE(),NULL) 

INSERT dbo.ShoppingCart VALUES (1,8798,GETUTCDATE(),NULL) 

INSERT dbo.ShoppingCart VALUES (2,23,GETUTCDATE(),45.4) 

INSERT dbo.ShoppingCart VALUES (3,80,GETUTCDATE(),NULL) 

INSERT dbo.ShoppingCart VALUES (4,342,GETUTCDATE(),65.4) 

GO

-- verify table contents 

SELECT * FROM dbo.UserSession 

SELECT * FROM dbo.ShoppingCart 

GO

SSMS：查看内存优化的表中的内容

⦁ 在Object Explorer中,右键单击你的内存优化的表, 点击Script Table as，点击SELECT To，点击New Query Editor Window然后执行显示出来的query。

第四步：更新统计信息

内存优化的表不支持auto_update_statistics，因此统计信息需要手动进行更新。您可以使用UPDATE STATISTICS来更新单个表的统计信息或者sp_updatestats来更新数据库中的所有表的统计信息。

-- update statistics on memory optimized tables 

UPDATE STATISTICS dbo.UserSession WITH FULLSCAN, NORECOMPUTE 

UPDATE STATISTICS dbo.ShoppingCart WITH FULLSCAN, NORECOMPUTE 

GO

第五步：执行查询

现在您已经做好了执行查询的准备。因为查询需要访问内存优化的表，他们将会受益于不加锁的数据结构，从而提高了数据访问的效率。以下是一些例子：

-- in an explicit transaction, assign a cart to a session and update the total price. 

-- note that the isolation level hint is required for memory-optimized tables with 

-- SELECT/UPDATE/DELETEstatements in explicit transactions 

BEGIN TRAN 

  UPDATE dbo.UserSession WITH (SNAPSHOT) SET ShoppingCartId=3 WHERE SessionId=4 

  UPDATE dbo.ShoppingCart WITH (SNAPSHOT) SET TotalPrice=65.84 WHERE ShoppingCartId=3 

COMMIT 

GO 

-- verify table contents 

SELECT * FROM dbo.UserSession u JOIN dbo.ShoppingCart s on u.ShoppingCartId=s.ShoppingCartId 

WHERE u.SessionId=4 

GO

第六步：创建本地编译的存储过程

为了进一步优化内存优化表的访问以及优化您的业务逻辑的执行，您可以选择创建本地编译的存储过程。这些存储过程是使用Transact-SQL创建的，但并不支持完整的Transaction-SQL环境。具体细节可参考联机丛书。

以下是一个访问之前我们创建的表的本地编译的存储过程的例子。

-- natively compiled stored procedure for assigning a shopping cart to a session 

CREATE PROCEDURE dbo.usp_AssignCart @SessionId int 

WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER 

AS 

BEGIN ATOMIC 

WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')

  DECLARE @UserId int, 

   @ShoppingCartId int

  SELECT @UserId=UserId, @ShoppingCartId=ShoppingCartId 

  FROM dbo.UserSession WHERE SessionId=@SessionId

  IF @UserId IS NULL 

   THROW 51000, 'The session or shopping cart does not exist.', 1

   UPDATE dbo.UserSession SET ShoppingCartId=@ShoppingCartId WHERE SessionId=@SessionId 

END 

GO

EXEC usp_AssignCart 1 

GO

下面的存储过程通过向内存优化表中插入大量数据行来测试本地编译的存储过程的性能。该脚本插入了1,000,000行数据。

需要注意的是如果写事物日志文件变成应用的性能瓶颈，SQL Server允许您采用非持久化的表(DURABILITY=SCHEMA_ONLY)来完全除去写事物日志。.

-- natively compiled stored procedure for inserting a large number of rows 

-- this demonstrates the performance of native procs 

CREATE PROCEDURE dbo.usp_InsertSampleCarts @StartId int, @InsertCount int 

WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER 

AS 

BEGIN ATOMIC 

WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')

  DECLARE @ShoppingCartId int = @StartId

  WHILE @ShoppingCartId < @StartId + @InsertCount 

  BEGIN 

   INSERT INTO dbo.ShoppingCart VALUES 

        (@ShoppingCartId, 1, '2013-01-01T00:00:00', NULL) 

   SET @ShoppingCartId += 1 

  END

END 

GO

-- insert 1,000,000 rows 

DECLARE @StartId int = (SELECT MAX(ShoppingCartId)+1 FROM dbo.ShoppingCart) 

EXEC usp_InsertSampleCarts @StartId, 1000000 

GO

-- verify the rows have been inserted 

SELECT COUNT(*) FROM dbo.ShoppingCart 

GO

SSMS：创建本地编译的存储过程

1. 在 Object Explorer中，右键单击您的数据库中的Stored Procedures 节点，点击New，然后点击 Natively Compiled Stored Procedure。创建本地编译的存储过程的模板将会显示在界面上。

2. 替换模板中的参数，点击Query 菜单下的 Specify Values for Template Parameters。快捷键是Ctrl-Shift-M.

更多关于内存中OLTP概念的细节和语法， 请参看联机丛书 。这就是今天的分享，更多SQL 2014新功能介绍请持续关注本博客。我们会在接下来的每周对此系列做更新。