SQLServer2014内存优化表评测

内存优化表， 以下简称内存表。 SQLServer2014 的使用基本要求 1..NetFramework3.5sp1, 2..NetFramework4.0 3.硬盘： =6G 4.内存：最小： 1G ，推荐： =4G 5.CPU ：最小： x86 ： 1.0GHZ ， x64 ： 1.4GHZ 6.操作系统： Win7 、 WinServer2008 及以上 ( Wind

内存优化表，　以下简称内存表。

SQLServer2014的使用基本要求

1. .Net Framework 3.5 sp1 ,

2. .Net Framework 4.0

3. 硬盘：>=6G

4. 内存：最小值：1G，推荐：>=4G

5. CPU：最小值：x86：1.0 GHZ，　x64：1.4 GHZ

6. 操作系统：Win7、WinServer2008 及以上 (WindowsServer2003不支持)

内存表基本要求

1. 64 位 Enterprise、Developer 或 Evaluation 版 SQL Server 2014。(注：即只有64位系统才能使用内存优化表的功能，32位系统能安装SQL Server2014，但无法使用内存表功能)

2. SQL Server 需要有足够的内存来保留内存优化表和索引中的数据。 若要容纳行版本，您应当提供两倍于内存优化表和索引预期大小的内存量。

内存表与磁盘表的DML对比

有回收线程不断回收同标识的旧行。

内存表比磁盘表快的原理

1. 内存读取比磁盘读取快；

2. 取消了锁，采用行版本机制，读取和更新不冲突。

内存表适合的场合

需要大量的并行操作的表

内存优化表的限制

1. 不支持的数据类型：varchar(max)、nvarchar(max)、image、xml、text、ntext、rowversion、datetimeoffset、geography、geometry、hierarchyid、sql_variant、UDT;

2. 每行的总字节数不得超过 8060 个字节;

3. 不支持外键或约束检查

4. 支持 IDENTITY(1, 1)。 但是不支持使用 IDENTITY(x, y)（其中 x != 1 或 y != 1 ）定义的标识列。

5. 不支持dml触发器

6. 内存优化表中的 (var)char 列必须使用代码页 1252 排序规则。 此限制不适用于 n(var)char 列。 下列代码检索所有 1252 排序规则：

select * from sys.fn_helpcollations() where collationproperty(name, 'codepage') = 1252;

7. 如果数据库排序规则不是代码页 1252 排序规则，则本机编译的存储过程不能使用 (var)char 类型的参数、局部变量或字符串常量。

8. 无法修改表结构，只能删除表再重建

9. 索引只能建hash非聚焦索引， 不能建聚焦索引。

10. 索引只能在建表时建立，不能重建索引。

具有内存优化对象（包括内存优化数据文件组）的数据库不支持以下 SQL Server 功能。注：支持AlwaysOn

内存表与磁盘表DML性能对比

测试环境：

CPU: Intel Core i3-3240 3.40GHz

内存：4.00GB(3.86GB可用)

系统类型： Windows Server 2008 R2 Enterprise 64位

两次测试取平均值, 测试SQL见后面的附录

总结

效率：内存表对比普通的磁盘表，　在增、删、改方面有非常大的优势，　甚至达到了上百倍！但查询方面并没有太大的区别。

可行性：内存表的限制比较大，比如数据库用了内存表之后就不能使用复制、镜像、链接服务器，　内存表也不能使用触发器、约束，　每行的字节数不能超过8060字节，　内存表的结构和索引建立之后就不能修改等等。 而且必须配合本地编译的存储过程效率才能提升。仅适用于数据库不需要被限制的功能（复制、镜像等），　而且表的增、删、改非常频繁的情况。

SqlServer2014内存表对比oracle 12C的 inmemory 选件，　后者易用性更高( alter table tableName inmemory 即可)， 而且其使用对比普通表没有太大区别，　限制很少。

SqlServer2014内存表感觉有些鸡肋，　期待下一版的改进。

附录

以下是性能评测SQL:

------------------------- 1. 建库 -------------------------
USE [master]
GO
if exists(select * from sysdatabases where name='DB_TEST_MEMTB')  
    DROP DATABASE DB_TEST_MEMTB  
go  
CREATE DATABASE [DB_TEST_MEMTB]
 ON  PRIMARY
(
    NAME = N'DB_TEST_MEMTB_DATA',
    FILENAME = N'e:\db\test\DB_TEST_MEMTB_DATA.mdf',
    SIZE = 512000KB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 1024KB
), 
--下面的文件就是数据流文件了
FILEGROUP [MEM_DIR] CONTAINS MEMORY_OPTIMIZED_DATA  DEFAULT
(
    NAME = N'DB_TEST_MEMTB_DIR',
    FILENAME =N'e:\db\test\DB_TEST_MEMTB_DIR',
    MAXSIZE = UNLIMITED
)
LOG ON 
(
    NAME = N'DB_TEST_MEMTB_LOG',
    FILENAME = N'e:\db\test\DB_TEST_MEMTB_LOG.ldf',
    SIZE = 512000KB,
    MAXSIZE = 2048GB,
    FILEGROWTH = 1024KB
)
GO
------------------------- 2. 建表和本地编译存储过程 -------------------------
USE DB_TEST_MEMTB
GO
-- 1. 建立普通磁盘表
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[t_disk]') AND type in (N'U'))
	DROP TABLE [dbo].[t_disk]
GO
create table [t_disk]
(
	c1 int not null primary key,
	c2 nchar(48) not null
)
go
-- 2. 建立内存优化表 (后面的测试不使用本地编译存储过程)
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[t_mem]') AND type in (N'U'))
	DROP TABLE [dbo].[t_mem]
GO
create table [t_mem]
(
	c1 int not null primary key nonclustered hash with (bucket_count=10000000),
	c2 nchar(48) not null
) with (memory_optimized=on, durability = schema_and_data)
GO
-- 3.0 建立内存优化表 (后面的测试使用本地编译存储过程 NATIVE_COMPILATION)
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[t_mem_nc]') AND type in (N'U'))
	DROP TABLE [dbo].t_mem_nc
GO
create table t_mem_nc
(
	c1 int not null primary key nonclustered hash with (bucket_count=10000000),
	c2 nchar(48) not null
) with (memory_optimized=on, durability = schema_and_data)
GO
-- 3.1 本地编译存储过程_insert
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_t_mem_nc_Insert]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Proc_t_mem_nc_Insert]
GO
CREATE PROCEDURE [Proc_t_mem_nc_Insert] 
       @rowcount int,
       @c nchar(48)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS 
 BEGIN ATOMIC 
 WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
       declare @i int = 1

       while @i <= @rowcount
       begin
                 INSERT INTO [dbo].t_mem_nc values (@i, @c)
                 set @i += 1
       end
END
GO
-- 3.2 本地编译存储过程_delete
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_t_mem_nc_delete]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Proc_t_mem_nc_delete]
GO
CREATE PROCEDURE [Proc_t_mem_nc_delete]
	@rowcount int
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS 
 BEGIN ATOMIC 
 WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
	   DECLARE @i INT = 1
       while @i<=@rowcount
       begin
                 DELETE FROM dbo.t_mem_nc WHERE c1=@i
                 set @i += 1
       end
END
GO
-- 3.3 本地编译存储过程_update
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_t_mem_nc_update]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Proc_t_mem_nc_update]
GO
CREATE PROCEDURE [Proc_t_mem_nc_update]
	@rowcount INT,
	@c nchar(48)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS 
 BEGIN ATOMIC 
 WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
	   DECLARE @i INT = 1
       while @i<=@rowcount
       begin
                 UPDATE dbo.t_mem_nc SET c2=@c WHERE c1=@i
                 set @i += 1
       end
END
GO
-- 3.4 本地编译存储过程_select
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_t_mem_nc_select]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Proc_t_mem_nc_select]
GO
CREATE PROCEDURE [Proc_t_mem_nc_select]
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS 
 BEGIN ATOMIC 
 WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
	SELECT c1,c2 FROM dbo.t_mem_nc
END
GO
------------------------- 3. 效率评测 -------------------------
DECLARE @i INT=1,@iMax INT = 1000000      --最大一百万条记录
DECLARE @v NCHAR(48)='123456789012345678901234567890123456789012345678'
DECLARE @t DATETIME2 = sysdatetime()
--3.1 insert 
--
set nocount on
while @i<=@iMax
begin
	insert into t_disk (c1,c2) values(@i, @v)
	set @i+=1
end
select 'insert (t_disk): '+ convert(varchar(10),  datediff(ms, @t, sysdatetime()))
--
set @i=1
set @t=SYSDATETIME()
while @i<=@iMax
begin
	insert into t_mem (c1,c2) values(@i, @v)
	set @i+=1
end
select 'insert (t_mem): '+ convert(varchar(10),  datediff(ms, @t, sysdatetime()))
--
set @t=SYSDATETIME()
exec [Proc_t_mem_nc_Insert] 
	   @rowcount=@iMax,
       @c=@v
select 'insert (t_mem_nc): '+ convert(varchar(10),  datediff(ms, @t, sysdatetime()))
--结果：
--insert (t_disk):   242111
--insert (t_mem):    221358
--insert (t_mem_nc):   2147

--insert (t_disk):   243174
--insert (t_mem):    223465
--insert (t_mem_nc):   2214

--3.2 update
--时间较长，故分段执行另设变量
DECLARE @u INT=1,@uMax INT = 1000000      --最大一百万条记录
DECLARE @uv NCHAR(48)='1234567890123456789012345678901234567890abcdefgh'
DECLARE @ut DATETIME2 = sysdatetime()
set nocount on
while @u<=@uMax
begin
	update t_disk set c2=@uv where c1=@u
	set @u+=1
end
select 'update (t_disk): '+ convert(varchar(10),  datediff(ms, @ut, sysdatetime()))
--
set @u=1
set @ut=SYSDATETIME()
while @u<=@uMax
begin
	update t_mem set c2=@uv where c1=@u
	set @u+=1
end
select 'update (t_mem): '+ convert(varchar(10),  datediff(ms, @ut, sysdatetime()))
--
set @ut=SYSDATETIME()
exec [Proc_t_mem_nc_Update] 
	   @rowcount=@uMax,
       @c=@uv
select 'update (t_mem_nc): '+ convert(varchar(10),  datediff(ms, @ut, sysdatetime()))
--update (t_disk):    199369
--update (t_mem):     368297
--update (t_mem_nc):    3715

--update (t_disk):    203251
--update (t_mem):     355356
--update (t_mem_nc):    3732

--3.3 select 
DECLARE @st DATETIME2 = sysdatetime()
set nocount on
--
select c1,c2 from t_disk
select 'select (t_disk): '+ convert(varchar(10),  datediff(ms, @st, sysdatetime()))
set @st=SYSDATETIME()
select c1,c2 from t_mem
select 'select (t_mem): '+ convert(varchar(10),  datediff(ms, @st, sysdatetime()))
set @st=SYSDATETIME()
exec Proc_t_mem_nc_select
select 'select (t_mem_nc): '+ convert(varchar(10),  datediff(ms, @st, sysdatetime()))
--select (t_disk):   8934
--select (t_mem):    9278
--select (t_mem_nc): 8889

--select (t_disk):   8861
--select (t_mem):    9978
--select (t_mem_nc): 9108

--3.4 delete
--时间较长，故分段执行另设变量
DECLARE @d INT=1,@dMax INT = 1000000      --最大一百万条记录
DECLARE @dt DATETIME2 = sysdatetime()
set nocount on
while @d<=@dMax
begin
	delete from t_disk where c1=@d
	set @d+=1
end
select 'delete (t_disk): '+ convert(varchar(10),  datediff(ms, @dt, sysdatetime()))
--
set @d=1
set @dt=SYSDATETIME()
while @d<=@dMax
begin
	delete from t_mem where c1=@d
	set @d+=1
end
select 'delete (t_mem): '+ convert(varchar(10),  datediff(ms, @dt, sysdatetime()))
--
set @dt=SYSDATETIME()
exec [dbo].[Proc_t_mem_nc_delete] @rowcount=@dMax
select 'delete (t_mem_nc): '+ convert(varchar(10),  datediff(ms, @dt, sysdatetime()))

--delete (t_disk): 199438
--delete (t_mem):  342959
--delete (t_mem_nc):  928

--delete (t_disk): 199637
--delete (t_mem):  341771
--delete (t_mem_nc):  803