SQLServer2014内存优化表评测
2021/4/16 19:30:15
本文主要是介绍SQLServer2014内存优化表评测，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
内存优化表， 以下简称内存表。
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
这篇关于SQLServer2014内存优化表评测的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
- 2024-01-08Docker下的SqlServer发布订阅启用
- 2023-06-05Docker安装MS SQL Server并使用Navicat远程连接
- 2023-05-25深入浅出 SQL Server CDC 数据同步
- 2023-05-12通过空间占用和执行计划了解SQL Server的行存储索引
- 2023-04-24以SQLserver为例的Dapper详细讲解
- 2022-11-30SQL server高级函数查询
- 2022-11-26SQL SERVER数据库服务器CPU不能全部利用原因分析
- 2022-11-21SQL Server 时间算差值/常用函数
- 2022-11-20调试Archery连接SQL Server提示驱动错误
- 2022-10-22SQL Server 完整、差异备份+完整、差异还原(详细讲解，规避错误)
