转载

为什么现在我最终推荐内存OLTP

在今年的8月份，我写了篇文章，介绍了我还不推荐用户使用内存OLTP的各个理由。近日很多人告诉我，他们有一些性能的问题，并考虑使用内存OLTP来解决它们。

众所皆知，在SQL Server里内存OLTP是个非常特别的技术，在很多情况下并不适用，但这是在SQL Server 2014里的首次实现，它有很多限制，我在这篇文章里已经介绍。

感谢上帝——现在事情已经改变了！几个星期前，SQL Server 2016的CTP 3版本已经可以公开下载了。在内存OLTP领域，微软做出了巨大的改进。我们来详细看下。

首先我们创建测试的数据库：

USE master GO  -- Create new database CREATE DATABASE InMemoryOLTP GO  --Add MEMORY_OPTIMIZED_DATA filegroup to the database. ALTER DATABASE InMemoryOLTP ADD FILEGROUP InMemoryOLTPFileGroup CONTAINS MEMORY_OPTIMIZED_DATA GO  USE InMemoryOLTP GO  -- Add a new file to the previous created file group ALTER DATABASE InMemoryOLTP ADD FILE (     NAME = N'InMemoryOLTPContainer',      FILENAME = N'C:/Program Files/Microsoft SQL Server/MSSQL13.MSSQLSERVER/MSSQL/DATA/InMemoryOLTPContainer' ) TO FILEGROUP [InMemoryOLTPFileGroup] GO

外键约束(Foreign-Key Constraints)

一个最重要的改变，或者我应该说是提升——是支持外键约束！是的，你没有听错：内存OLTP现在支持外键约束。在最初的实现里通常你会期望是支持外键约束，因为这是涉及OLTP情景，但在SQL Server 2014上微软并不支持。我们来看下面的代码：

1 -- Create a parent table  2 CREATE TABLE Parent  3 (  4     ParentID INT IDENTITY(1, 1) NOT NULL,  5     Col1 CHAR(100) NOT NULL,  6     Col2 CHAR(100) NOT NULL,  7     Col3 CHAR(100) NOT NULL,  8     CONSTRAINT chk_PrimaryKey_Parent PRIMARY KEY NONCLUSTERED HASH (ParentID) WITH (BUCKET_COUNT = 1024)  9 ) 10 WITH (MEMORY_OPTIMIZED = ON) 11 GO 12  13 -- Create a child table 14 CREATE TABLE Child 15 ( 16     ChildID INT IDENTITY(1, 1) NOT NULL, 17     ParentID INT NOT NULL, 18     Col1 CHAR(100) NOT NULL, 19     Col2 CHAR(100) NOT NULL, 20     Col3 CHAR(100) NOT NULL, 21  22     -- Create a FK constraint between both tables 23     CONSTRAINT FK_Child_Parent FOREIGN KEY (ParentID)  24     REFERENCES Parent (ParentID), 25      26     CONSTRAINT chk_PrimaryKey_Child PRIMARY KEY NONCLUSTERED HASH (ChildID) WITH (BUCKET_COUNT = 1024) 27 ) 28 WITH (MEMORY_OPTIMIZED = ON) 29 GO 30  31 -- Insert some records into both tables 32 INSERT INTO Parent VALUES ('a', 'a', 'a'), ('b', 'b', 'b'), ('c', 'c', 'c') 33 INSERT INTO Child VALUES (1, 'a', 'a', 'a'), (1, 'b', 'b', 'b'), (1, 'c', 'c', 'c') 34 GO

这段代码在2个表（parent和child表）之间创建了一个简单的外键约束。另外我也在2个表里也插入了些测试数据。现在我们对这2个表进行简单的查询：

1 -- The unnecessary join is removed in the execution plan. 2 SELECT c.* FROM Parent p 3 JOIN Child c ON c.ParentID = p.ParentID 4 GO

当你看查询本身时，你会看见我只想返回child表的内容。基于外键约束，查询优化器知道在parent表里肯定有记录存在。因此查询优化器通过移除不需要的表连接来简化查询。当你看执行计划时，你会看到这个简化真的发生了——非常棒：

为什么现在我最终推荐内存OLTP

在SQL Server的内存OLTP里，这是其中一个最大的改进——支持外键约束。对于外键约束的支持同样支持本地编译的存储过程，如下代码所示：

1 -- Create a natively compiled Stored Procedure  2 CREATE PROCEDURE InMemoryOLTPProcedure  3 WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  4 AS  5 BEGIN  6    ATOMIC WITH  7    (  8       TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english'  9    ) 10  11    SELECT c.ChildID, c.ParentID, c.Col1, c.Col2, c.Col3 FROM dbo.Parent p 12    JOIN dbo.Child c ON c.ParentID = p.ParentID 13 END 14 GO

当你执行本地编译存储过程时，同样你没有可用的实际执行计划。你能查看的只有估计执行计划……

检查约束（Check Constraints）

另外非常棒的提升是现在我们支持检查约束。检查约束非常重要，因为它告诉查询优化器你数据长相的更多信息。基于这些信息，查询优化器可以给你更好性能的执行计划。下面这段代码给你展示了一个使用内存OLTP如何定义检查约束的简单例子。

1 -- You can't create a CHECK constraint on a Memory Optimized Table  2 CREATE TABLE CheckConstraint  3 (  4     ID INT IDENTITY(1, 1) PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),  5     Value INT NOT NULL DEFAULT 1 CONSTRAINT ck_Value CHECK (Value = 1)  6 )  7 WITH  8 (  9     MEMORY_OPTIMIZED = ON,  10     DURABILITY = SCHEMA_AND_DATA 11 ) 12 GO

从这个表定义你可以看到，这里我创建了一个简单的检查约束，告诉SQL Server在列值里只保存1的值。甚至否定检查在与约束检查组合也是支持的。下面这个查询会导致在执行计划里有常数扫描运算符。

1 -- Contradiction detection works with In-Memory OLTP. 2 SELECT * FROM CheckConstraint 3 WHERE Value = 0 4 GO

在字符列上的索引

回到使用SQL Server 2014的旧时光里，内存OLTP里不支持字符列上的索引，因为你必须使用BIN2排序。对于大多数人来说这是个项目障碍，因为当你在字符列上进行比较或排序时，使用另一个排序会影响结果。

使用SQL Server 2016，微软现在已经最终移除了这个限制，现在你可以在字符列上直接创建哈希或范围索引，不需要使用BIN2排序。我们来看下面的例子，在SQL Server 2016里现在是正常运行的。

1 -- Creates a table with an index on a character column.  2 -- This works now without any problems in SQL Server 2016.  3 CREATE TABLE TestTable1  4 (  5     Col1 CHAR(10) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),  6     Col2 VARCHAR(100) NOT NULL,  7     Col3 VARCHAR(100) NOT NULL  8 ) WITH  9 ( 10     MEMORY_OPTIMIZED = ON,  11     DURABILITY = SCHEMA_AND_DATA 12 ) 13 GO

非常炫——太棒了！

架构和桶数改变

在SQL Server 2014里这个真的是很糟也很烂：在内存优化表上架构修改是不完全不支持的。你需要删掉并重建你的表，即使你只想增加一个索引或修改现存的索引。抱歉，这是我绝不推荐用户使用内存OLTP的一个最主要原因。即使对于哈希索引修改桶数，你也要删除并重建你的表。

使用SQL Server 2016，生活现在好多了，一切变得简单了。首先你可以使用简单的ALTER INDEX REBUILD语句来修改现存索引的桶数。要留意的是你需要有表大小的2倍内存。下列代码显示这个提升：

1 -- We can change now the bucket count without dropping the table 2 ALTER TABLE Parent 3 ALTER INDEX chk_PrimaryKey_Parent 4 REBUILD WITH (BUCKET_COUNT = 1048576) 5 GO

另外在你创建内存优化表后，现在你可以修改你的表，甚至创建索引。我还没尝试所有的可能修改，但下面的代码可以给你一个你期望SQL Server 2016的一个大致想法。

1 -- Creates a table with an index on a character column.  2 -- This works now without any problems in SQL Server 2016.  3 CREATE TABLE TestTable1  4 (  5     Col1 CHAR(10) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),  6     Col2 VARCHAR(100) NOT NULL,  7     Col3 VARCHAR(100) NOT NULL  8 ) WITH  9 ( 10     MEMORY_OPTIMIZED = ON,  11     DURABILITY = SCHEMA_AND_DATA 12 ) 13 GO 14  15 -- Create a new system-versioned table 16 CREATE TABLE Persons 17 ( 18     ID INT IDENTITY(1, 1) PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024), 19     FirstName VARCHAR(100) NOT NULL, 20     LastName VARCHAR(100) NOT NULL, 21     City VARCHAR(100) NOT NULL, 22  23     -- Needed for System-Versioned Tables 24     StartDate DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL, 25     EndDate DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL, 26     PERIOD FOR SYSTEM_TIME (StartDate, EndDate) 27 ) 28 WITH 29 ( 30     -- Needed for System-Versioned Tables 31     SYSTEM_VERSIONING = ON 32     ( 33         -- Name of the history table (optional) 34         HISTORY_TABLE = dbo.PersonHistory 35     ), 36     MEMORY_OPTIMIZED = ON,  37     DURABILITY = SCHEMA_AND_DATA 38 ) 39 GO 40  41 -- The current table is a Memory-Optimized Table, the history table 42 -- is a traditional Disk-Based Table. 43 SELECT is_memory_optimized, * FROM sys.tables 44 WHERE object_id IN(OBJECT_ID('Persons'), OBJECT_ID('PersonHistory')) 45 GO 46  47 -- Create a parent table 48 CREATE TABLE LetsTrySchemaChanges 49 ( 50     ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024), 51     Col1 CHAR(100) NOT NULL, 52     Col2 CHAR(100) NOT NULL, 53     Col3 CHAR(100) NOT NULL 54 ) 55 WITH (MEMORY_OPTIMIZED = ON) 56 GO 57  58 -- Schema Changes are now also supported on Memory-Optimized tables. 59 ALTER TABLE LetsTrySchemaChanges ADD Test CHAR(100) NULL 60 GO 61  62 -- The creation of an index after the table creation is not supported 63 CREATE NONCLUSTERED HASH INDEX idx_Test ON LetsTrySchemaChanges(Col3) 64 WITH (BUCKET_COUNT = 1024) 65 GO 66