锋利的SQL2014: 内存优化表

  摘自作者新书《锋利的SQL》（第2版），网购京东：http://item.jd.com/11692900.html

内存优化表是从SQLServer 2014开始引入的，它将数据的读取、写入操作都放置到内存中完成，这与传统的磁盘表是最大的不同。此功可以减少锁的等待问题，从而改善写入等待延迟，可以用于某些特殊负荷的操作。

但是，要使用内存优化表，还有一定的限制要求：

·   必须是64位Enterprise、Developer或Evaluation版SQL Server 2014。

·   处理器需要支持指令cmpxchg16b才能使用内存优化表，不过目前所有的64位处理器均支持cmpxchg16b。

·   如果数据库包含内存优化表，则无法更改数据库排序规则。

·   字符串列只有在采用BIN2排序规则的情况下，才能在该列上创建索引。并且，无法对不使用BIN2排序规则的索引字符串列使用ORDER BY或GROUP BY语句。

SQL Server需要有足够的内存来保留内存优化表和索引中的数据。如果使用行版本控制，还应当提供两倍于内存优化表和索引预期大小的内存量。

要想创建内存优化表，必须首先在数据库中创建支持内存优化的文件组。但是，此文件组一旦创建，只有在删除数据库的情况下才能删除。例如，下面的语句在AdventureWorks2014数据库中创建了名为hk_mod的文件组，该文件组存储在c:\data\hk_mod目录中。注意语句中的MEMORY_OPTIMIZED_DATA关键字，表示创建的是支持内存优化的文件组。

ALTER DATABASE AdventureWorks2014

ADD FILEGROUP hk_mod CONTAINS MEMORY_OPTIMIZED_DATA;

GO

 

ALTER DATABASE AdventureWorks2014

ADD FILE (NAME='hk_mod',FILENAME='c:\data\hk_mod')

TO FILEGROUP hk_mod;

下面的语句用于创建一个名为T1的内存优化表：

USE AdventureWorks2014;

GO

 

CREATE TABLE dbo.T1 (

Name VARCHAR(20) COLLATE Latin1_General_100_BIN2NOT NULL

      PRIMARY KEY NONCLUSTERED HASH WITH(BUCKET_COUNT = 1024),

City VARCHAR(32) NOT NULL,

LastModified DATETIME NOT NULL,

) WITH (MEMORY_OPTIMIZED = ON, DURABILITY =SCHEMA_AND_DATA);

与创建文件组时一样，上面语句中指定MEMORY_OPTIMIZED为NO，表示这是一个内存优化表。注意上面的DURABILITY选项设置，SCHEMA_AND_DATA表示是持久化数据和结构，虽然内存优化表是驻留在内存中，但是通过此项设置可以在磁盘上维护一个表的副本，在执行服务器重启等操作时，可以从该副本进行读取。不过，处于性能考虑，这个副本是存在延迟的，它是在提交事务并将控制权归还客户端后不久才保存到磁盘。作为提高性能的代价，在服务器崩溃或故障转移过程中将丢失已提交但未保存到磁盘的事务。

此外，还可以将DURABILITY设置为SCHEMA_ONLY，表示只持久化表结构。这样，这些表的日志和数据都不会存储在磁盘中，这意味着这些表上的事务不需要任何磁盘I/O操作，但是如果服务器崩溃或进行故障转移，则无法恢复数据。

内存优化表只能通过CREATETABLE语句创建，对于已建立的内存优化表是无法通过ALTER TABLE语句进行修改的。如果想修改内存优化表的结构，一种变通的方法就是再创建一个新内存优化表，将源表数据复制过去。

内存优化表的删除与磁盘表没有任何不同，例如，下面语句删除了前面创建的T1表。

DROP TABLE dbo.T1;