SQLServer2014内存优化表评测

2022-11-20 07:52:00

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

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. 取消了锁，采用行版本机制，读取和更新不冲突。

内存表适合的场合
需要大量的并行操作的表

具有内存优化对象（包括内存优化数据文件组）的数据库不支持以下 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

码农公寓

相关文章