真是鸡肋，食之无味弃之可惜！！！SQL2014的内存优化表让我说你什么好？

[天-笑]

终于装上SQL2014了，在网上一片对SQL2014内存优化表的赞美声中，本人也开始了内存优化表的学习中。
在此感谢以下两位：
http://www.cnblogs.com/lyhabc/p/3691911.html
http://bbs.csdn.net/topics/390752803?page=1
通过上面两篇文章，我也顺利的完成了内存优化表的测试工作，还是蛮顺利的，内存优化表确实性能高，
那么问题来了，我为什么说内存优化表是鸡肋呢？下面我来说下我的测试结果，及个人看法，欢迎大家拍砖！！！
测试环境 WINDOWS SERVER2008R2 （64） ；SQL2014 Enterprise Edition（64）；
我给SQL分配的最大内存，和最小内存均为2G。
既然是内存优化表，我就要测试SQL对内存的控制情况。
我新建了一张测试表，并初始插入100W条数据（大约几十M，在一个事务中提交），
然后将表上的列更新成一个新值，或者NULL，表存储变为200M，然后用delete (truncate 语句不能用)删除表数据，再次插入100W条数据库后，表存储为300M实际上，就是内存占到了300M；如此反复后该表存储占到了大约1G的量，后续大概又重复了2-3次后，系统报错，提示内存不够用了。这个时候再去看内存优化表，存储已经变成0M。
那么我的问题来了：
假设某个系统用内存优化表，那么删除数据后内存不应该回收吗？即使SQL不回收，你也应该提供一个SQL语句让我
主动回收某个内存优化表的内存吧？难道就任由内存增加，直到报错（也许我不在一个事务中提交几十M的数据不会报错）？怎么能让我放心使用内存优化表！！！

[天-笑]

论坛里的大侠，宗师们呢？ 一起来讨论下呗？？？？

[LongRui888]

引用 1 楼 shmilywcd 的回复:

论坛里的大侠，宗师们呢？ 一起来讨论下呗？？？？

你把测试代码贴出来，我有空也试试

[天-笑]

引用 2 楼 yupeigu 的回复:

[quote=引用 1 楼 shmilywcd 的回复:] 论坛里的大侠，宗师们呢？ 一起来讨论下呗？？？？

你把测试代码贴出来，我有空也试试[/quote]

create table [Test] ( 
c1 int not null primary key nonclustered hash with (bucket_count=1000000), 
c2 nchar(48) not null)
with (memory_optimized=on, durability = schema_and_data) 


/* 以下部分重复执行，直到报错*/
set statistics time off
set nocount on  -- inserts - 1 at a time     
declare @starttime datetime2 = sysdatetime(),        
		@timems int  
declare @i int = 1 
declare @rowcount int = 1000000
declare @c nchar(48) = N'12345678901234567890123456789012345678'  
----------------------------- --- disk-based table and interpreted Transact-SQL -----------------------------   
delete from Test
set @i = 1 
set @starttime = sysdatetime()   
begin tran 
	while @i <= @rowcount 
	begin       
		insert into Test values (@i, @c)        
		set @i += 1 
	end
commit    
set @timems = datediff(ms, @starttime, sysdatetime()) 
select '时间' + cast(@timems as varchar(10)) + ' ms'

----------------------------------------------------------------------------------
--下面这样执行也会运行到报错
set statistics time off
set nocount on  -- inserts - 1 at a time     
declare @starttime datetime2 = sysdatetime(),        
		@timems int  
declare @i int = 1 
declare @rowcount int = 10000000
declare @c nchar(48) = N'12345678901234567890123456789012345678'  
----------------------------- --- disk-based table and interpreted Transact-SQL -----------------------------   
delete from Test
set @i = 1 
set @starttime = sysdatetime()   

	while @i <= @rowcount 
	begin       
		insert into Test values (@i, @c)        
		set @i += 1 
	end
   
set @timems = datediff(ms, @starttime, sysdatetime()) 
select '时间' + cast(@timems as varchar(10)) + ' ms'

资源池 'default' 没有足够的系统内存来运行此查询。

[好记忆不如烂笔头abc]

delete 不能降低高水位啊。 你用truncate试试？

[吉普赛的歌]

内存限制到2GB， 重启sqlserver服务。 然后按你的脚本运行了14分钟， 没有发现崩溃的现象。

[LongRui888]

引用 4 楼 jycjyc 的回复:

delete 不能降低高水位啊。 你用truncate试试？

高水位线是oracle才有的

[LongRui888]

试了一下这个代码，运行第一段，插入100w条数据，运行多次，并没有报错。 但是第二段代码，插入1000w条数据，很长时间，一直卡在那里，我查了一下等待类型是 WRITE LOG。。。

[LongRui888]

仔细看了一下2段代码，其他都差不多，只有一个 区别，就是 第一个代码是在一个事务中的，而第二个代码是 每次插入都提交一次。 这可能是出现writelog的原因。 测试了一下，用第二段代码插入100w条数据，一共耗时：时间416430 ms，也就是6.9分钟。 从这个上来说，批量插入最好还是用第一段代码，在一个事务里，批量操作后，一次性提交，这样效率高很多

[yahle]

按照我的理解，系统应该是直接把存储到磁盘里的数据映射到内存里了，所以就算是delete，在磁盘（内存）里，也只是标记删除，真的要清理出表空间，试试下面的方法。 http://www.cnblogs.com/Joe-T/archive/2012/06/08/2541806.html

[Tiger_Zhao]

[Quote=引用 楼主给第二个的连接的帖子:]在某一个相同的时间戳段里，存在两个数据文件，一个记录这段时间内插入的行，一个记录这段时间内删除的行，查询时把这个表之前的所有文件读出来，第一个文件减掉第二个文件，就是真实的表数据了。[/Quote]
插入后删除，看起来记录数为0，但是所有记录还是要写入这两个文件的啊。
又因为内存插入/删除比硬盘IO快好多，大量操作后某个时点你看起来存储“应该是”0M，但是“实际上”里面全是没有写入硬盘的ghost记录，所有才会报内存不够用的错误。
让服务器空闲一会应该就正常了。

#5的看起来硬盘性能极好啊

[wang_peng_yl]

SQL2014的内存优化表你说的很对，就是　食之无味弃之可惜 别的不说， 安全性就是个问题，一但谁不小心重起服务器，数据全没了 它是有机制保存数据到硬盘，但用了后访问速度又下来，和正常的表没什么区别了 所以食之无味弃之可惜

[l274747948]

你好 我测试了一下，发现查询速度并没有变快。。为什么呢？ 使用内存优化顾问生成新的内存表

SELECT top 1000000 *
  FROM [dbo].[Consultation_Rvinfo1] order by H_Id
GO


SELECT top 1000000 *
  FROM [dbo].Consultation_Rvinfo1_old order by H_Id
GO

[l274747948]

表里有800多万数据 查100万 都是要24秒