SQL 2008执行语句遇到内存不足(1)——error 701-阿里云开发者社区

开发者社区> 杰克.陈> 正文

SQL 2008执行语句遇到内存不足(1)——error 701

简介: 原文: SQL 2008执行语句遇到内存不足(1)——error 701 转自:http://blogs.msdn.com/b/apgcdsd/archive/2011/01/17/sql-2008-error-701.
+关注继续查看
原文: SQL 2008执行语句遇到内存不足(1)——error 701

转自:http://blogs.msdn.com/b/apgcdsd/archive/2011/01/17/sql-2008-error-701.aspx

某个特定的存储过程在SQL 2008中执行会遇到以下错误:
Msg 701, Level 17, State 123, Procedure GetAllRevisions_Monthly, Line 22
There is insufficient system memory in resource pool 'internal' to run this query.
Msg 701, Level 17, State 65, Procedure GetAllRevisions_Monthly, Line 22
There is insufficient system memory in resource pool 'internal' to run this query.

  

我们可以从问题的描述和现象中得到什么信息:

首先我们需要确认几个问题:

1.     这个存储过程在单独执行的时候会不会遇到这个错误。也就是说,在一个没有其他用户访问的时候,单独执行这个存储过程。这一点非常重要,可以帮助我们确认SQL Server是由于总体的工作负载太高而导致无法分配足够内存执行语句,还是这条语句本身执行的问题。

2.     这个存储过程是不是每次执行都会遇到这个错误。

在这个案例中的情形,该存储过程即使在单一用户访问的时候执行,也会遇到这样的错误,并且在每次执行的时候都会出现同样的错。需要注意的是,这个存储过程在第一次执行的时候,会执行一分钟左右以后,报出错误信息。而当第二次执行和以后多次执行的时候,都是不到一秒立即报错。

这里额外的介绍一下对于语句和存储过程,多次测试的时候需要注意的问题:任何语句在第一次执行的时候,会生成执行计划并且将执行计划缓存的SQL Server的内存中,而第二次或者以后多次执行,只要之前存放的执行计划没有从内存中清除,语句和存储过程是会重用原有存储的执行计划。因此,如果我们希望每次测试都能实现语句第一次执行时候的效果,我们需要每次执行之前将缓存的执行计划手工清除。DBCC freeprocache这个命令可以帮助我们清除所有缓存的执行计划。

这个案例的测试情况如下:

Dbcc freeproccache

执行存储过程,用时一分钟,报错。

再次执行存储过程,立刻返回错误。

Dbcc freeproccahe

执行存储过程,用时一分钟,报错。

再次执行存储过程,立刻返回错误。

通过这个现象,可以得出结论,首次执行一分钟以后报错,是因为对该存储过程进行了编译和生成执行计划。而后续执行时立即报错,是重用了存储过程缓存的执行计划。也就是说,这个存储过程的解析和编译过程是没有问题的。但是一旦按照编译的执行计划执行的时候,就遇到了内存不足的问题。

以上是我们通过问题的现象和描述分析出问题的可能性。接下来我们就进入重现问题收集log的阶段。

  

对于内存不足的错误,我们需要收集哪些信息用以诊断呢?

Windows性能监视器日志:

SQLServer:Memory

Memory  --这个object描述的是windows的内存分配和可用内存情况

SQL Server 动态性能视图:

sys.dm_exec_query_memory_grants

sys.dm_os_memory_clerks

dbcc memorystatus

如何收集这些信息?

Windows性能监视器日志,只需要在windows性能监视器里面自定义一个用户收集结果集,然后将objectSQLServer:Memory添加进去就可以了。需要主要的是设置收集间隔,由于这个错误在语句开始执行后很快就会出现,因此,将收集间隔设置的过大时是难以准确收集到问题出现时的内存异常情况,我们建议将收集间隔设置为1-2秒。

收集SQL Server的动态性能视图的方法类似,我们可以使用如下脚本每隔一秒打出一次动态性能视图的结果集合SQL Server的内存情况:

while(1=1)

begin

print getdate()

print '*****sys.dm_os_memory_clerks******'

select * from sys.dm_os_memory_clerks

print '*****sys.dm_exec_query_memory_grants******'

select * from sys.dm_exec_query_memory_grants

print 'DBCC memorystatus'

dbcc memorystatus

waitfor delay '00:00:01'

end

 

在management studio中执行这个脚本,这个脚本会每隔一秒打印一次两个view的信息和dbcc 命令的输出。我们建议设置当前的查询结果输出的文件的方式来输出这个脚本的结果。

 

 

如何为这个问题收集信息?

现在我们已经知道了如何重现这个问题,也知道了对于这个问题应该收集什么信息来检查,接下来的问题是,如何收集信息?我们需要在问题出现之前,先讲两个部分信息收集启动:启动我们自己配置的windows 性能监视器日志,启动脚本。接下来,我们可以在management studio里面执行语句并且重现问题了。

 

信息的分析

1.     问题出现之前,脚本打出来的dbcc memorystatus:

Current Buffer Pool Stats

-----------------------------------

Total Buffers=1048576 (8192MB)

Max Committed=640000 (5000MB)

Committed Target=640000 (5000MB)

Committed=41344 (323MB)

Hashed=6255 (48MB)

Free=17746 (138MB)

Stolen=17343 (135MB)

Reserved=590656 (4614MB)

OutOfMemory=false

WaitingForRM=false

Failed to complete calculation of statistics!                       

Latched=0 (0MB)

Dirty=0 (0MB)

In IO=0 (0MB)

Stolen potential=1 (0MB)

这个信息如何阅读?committed target表示SQL Server可以为buffer pool分配的空间,64000是以8k的page为单位的,因此640000计算出来时5000MB。Committed表示当前已经使用的buffer pool的大小,41344计算出来时323MB。这个信息告诉我们,在问题出现之前,该系统的buffer pool里面是还有超过4677MB的空闲内存。

2.     Windows event log:

在诊断内存问题的时候,首先需要确认的就是,该内存问题是由于windows的内存缺乏导致的还是SQL Server自身的内存问题导致的。当windows遇到内存压力,没有可用内存的时候,OS被强制在其上运行的所有应用程序释放物理内存。SQL Server在这种情况下,由于buffer pool短时间之内需要释放大量内存并且急剧缩小大小,当时在SQL Server上执行的语句也会报出错误701,没有足够的内存执行语句。但是这个内存错误的本身是由于windows 强制收回内存导致的。我们会在另一个案例中详细讨论这个问题。

我们检查OS的可用内存,memory:available memory(MB),在这个案例中,问题发生之前和问题发生的时候,windows依然保持5.7GB的内存。所以这里的内存问题是SQL Server内部产生的,并不是操作系统和服务器的内存缺乏问题。

接下来我们检查SQLServer:Memory 下面的Granted workspace memory(KB),这个值代办SQL Server分配出来执行语句的内存,主要是用来做排序,哈希连接等等。

  

 

通过这个event,我们发现这里有两次巨大的内存分配(我们测试的时候执行了两次存储过程),这里的最大值为4617MB。基本上接近了buffer pool中所有的空闲内存。

我们比较在脚本中收集到的sys.dm_exec_query_memory_grants的信息:

session_id dop request_time             grant_time               requested_memory_kb granted_memory_kb required_memory_kb

---------- --- ------------------------ ------------------------ ------------------- ----------------- ------------------

53         16  02/19/2010 17:13:02.540  02/19/2010 17:13:02.540  4728392             4728392           4728392       

分配的数值和时间上完全吻合。  

如果我们遇到的问题是,发现有大量的内存分配给了用户session,但是我们不知道用户session在当时执行的语句是什么,如何通过动态性能视图来定位语句呢?在sys.dm_exec_query_memory_grants中包含了一个sql_handle的列,我们可以通过这个列去连接:

Select *,text, query_plan from FROM sys.dm_exec_query_memory_grants

CROSS APPLY sys.dm_exec_sql_text(sql_handle)

CROSS APPLY sys.dm_exec_query_plan (plan_handle)

这样我们就可以直接通过动态性能视图得到了SQL语句和该SQL语句使用的XML格式的执行计划了。我们将XML格式的执行计划另存为.sqlplan的后缀的文件,然后在management studio中打开,可以将其展开为图形界面的执行计划。如果不熟悉使用XML格式的执行计划,这种方法可以简化检查执行计划的工作。

问题分析到这里,基本上我们就可以定位出原因了,确实是因为这个存储过程执行时,SQL Server预估的内存过大,导致现有内存不足以支持存储过程的执行。

 

解决的方法:

接下来,我们就可以查看XML的执行计划,在这里,我们发现SQL Server对这个存储过程使用的并行度为16的执行方式。对于使用并行的语句,SQL Server面对的两个额外的开销:

a.  更多的内存分配

b.  更多的CPU资源消耗。

因此,一旦我们确认内存使用过多或者CPU使用率过高是跟并行执行有关,我们需要对SQLServer手工设置一个较低的最大并行度参数:

 

sp_configure 'show advanced options',1 reconfigure with override

go

sp_configure 'max degree of parallelism',4 reconfigure with override

上述命令中第一条是用来打开高级选项的,第二条是将最大并行度设置为4.以上设置不需要重启SQL Server服务。关于最大并行度的参数,我们建议设置为CPU数或者CPU数的一半。在多于32个CPU的系统中,我们建议设置这个值为4或者8.

  

额外的问题:

到目前为止,原因已经非常清楚了,而且我们得到的解决问题的方法。这里有一个疑问,为什么SQL Server会在明知道没有这么多可用内存的情况下,去生成一个需要这么多内存的执行计划呢?并且还不断的重用这个不能执行的执行计划?这样是SQL Server的产品设计有问题吗?

其实这个问题的出现是一个很巧合的情况。我们根据SQL Server的dump来分析,���现这个语句在评估和生成执行计划的时候,计算出来所需要的内存接近4670MB,当时buffer pool里面还有4677MB的内存,所以评估出来的执行计划虽然接近了零界值,但是依然是能够满足执行计划的需要的。而当SQL语句真正按照执行计划执行的时候,其实还有一些少量的内存的额外开销,正是因为加上了这些额外的开销,终于超出了剩余的4677MB的内存限制。在这种情况下,SQL Server每次对执行计划的评估和缓存都成功了,而执行的时候才会报错。这也就是为什么下次执行的时候该存储过程还是会重用缓存的执行计划。当语句到执行阶段的时候,不管成功不成功,都不会回头改写缓存的执行计划,所以SQL server就不断的为这个存储过程重用同样的执行计划,直到我们将最大并行度降为4,SQL Server生成新的并行度为4的执行计划以后,该存储过程就能正确的运行了。

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
4006 0
怎么设置阿里云服务器安全组?阿里云安全组规则详细解说
阿里云服务器安全组设置规则分享,阿里云服务器安全组如何放行端口设置教程
6359 0
阿里云服务器ECS远程登录用户名密码查询方法
阿里云服务器ECS远程连接登录输入用户名和密码,阿里云没有默认密码,如果购买时没设置需要先重置实例密码,Windows用户名是administrator,Linux账号是root,阿小云来详细说下阿里云服务器远程登录连接用户名和密码查询方法
2171 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
5670 0
windows server 2008阿里云ECS服务器安全设置
最近我们Sinesafe安全公司在为客户使用阿里云ecs服务器做安全的过程中,发现服务器基础安全性都没有做。为了为站长们提供更加有效的安全基础解决方案,我们Sinesafe将对阿里云服务器win2008 系统进行基础安全部署实战过程! 比较重要的几部分 1.
4963 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
10711 0
如何设置阿里云服务器安全组?阿里云安全组规则详细解说
阿里云安全组设置详细图文教程(收藏起来) 阿里云服务器安全组设置规则分享,阿里云服务器安全组如何放行端口设置教程。阿里云会要求客户设置安全组,如果不设置,阿里云会指定默认的安全组。那么,这个安全组是什么呢?顾名思义,就是为了服务器安全设置的。安全组其实就是一个虚拟的防火墙,可以让用户从端口、IP的维度来筛选对应服务器的访问者,从而形成一个云上的安全域。
3806 0
阿里云服务器ECS登录用户名是什么?系统不同默认账号也不同
阿里云服务器Windows系统默认用户名administrator,Linux镜像服务器用户名root
719 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,云吞铺子总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系统盘、创建快照、配置安全组等操作如何登录ECS云服务器控制台? 1、先登录到阿里云ECS服务器控制台 2、点击顶部的“控制台” 3、通过左侧栏,切换到“云服务器ECS”即可,如下图所示 通过ECS控制台的远程连接来登录到云服务器 阿里云ECS云服务器自带远程连接功能,使用该功能可以登录到云服务器,简单且方便,如下图:点击“远程连接”,第一次连接会自动生成6位数字密码,输入密码即可登录到云服务器上。
16269 0
+关注
杰克.陈
一个安静的程序猿~
9798
文章
2
问答
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载