SQL Server 2008执行语句遇到内存不足1——error 701
转自：/b/apgcdsd/archive//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)
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'
在management studio中执行这个脚本，这个脚本会每隔一秒打印一次两个view的信息和dbcc&命令的输出。我们建议设置当前的查询结果输出的文件的方式来输出这个脚本的结果。
如何为这个问题收集信息？
现在我们已经知道了如何重现这个问题，也知道了对于这个问题应该收集什么信息来检查，接下来的问题是，如何收集信息？我们需要在问题出现之前，先讲两个部分信息收集启动：启动我们自己配置的windows&性能监视器日志，启动脚本。接下来，我们可以在management studio里面执行语句并且重现问题了。
信息的分析
1.&&&&&问题出现之前，脚本打出来的dbcc memorystatus：
Current Buffer Pool Stats
-----------------------------------
Total Buffers=92MB)
Max Committed=00MB)
Committed Target=00MB)
Committed=4MB)
Hashed=6255 (48MB)
Stolen=1MB)
Reserved=14MB)
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/:02.540&&02/19/: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
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的执行计划以后，该存储过程就能正确的运行了。
> 本站内容系网友提交或本网编辑转载，其目的在于传递更多信息，并不代表本网赞同其观点和对其真实性负责。如涉及作品内容、版权和其它问题，请及时与本网联系，我们将在第一时间删除内容！
原文: SQL 2008执行语句遇到内存不足(1)--error 701 转自:/b/apgcdsd/archive//sql-2008-error-701.aspx 某个特定的存储过程在SQL 2008中执行会遇到以下错误: Msg 701, Level 17, State 123, Proce ...
近来学习sql的时候,所以就用上了sql server 2008 ,这个版本有个很好的地方就是会智能提示,但是这种智能提示有些时候这是很烦人,比如说新建一张数据表之后,表名为 Person当使用结构化查询语句的时候,输入Person 总是会出现红色波浪线(红色波浪线一般是提示有错误,但是这里并没错)百度上有个很好的解释:这个表的字段列表,让你选择.但是,当你 ...
微软SQL Server 2008 R2中的资源分配方式与SQL Server 2005中的方式相比是一种完全不同的过程.利用资源控制器,在SQL Server 2008 R2中解决方案供应商有切实可用的方法管理CPU和内存. 资源消耗是长期以来困扰使用SQL Server的解决方案供应商的基本问题之一.任何服务器,不管它是物理的还是虚拟的,供处置的CPU和 ...
SQL Server 2005中的Output功能可以把Insert,Update和Delete的内容都返回,2008中的Output同样具有此功能,下面介绍一下把Output同2008的新T-SQL语句Merge组合使用的方法: 新建下面表: CREATE TABLE Book( ISBN varchar(20) PRIMARY KEY, Pr ...
在 Microsoft sql server management studio 里点击“编辑”——“IntelliSense”——“刷新本地缓存” 就会发现红色波浪线没了(前提是你的代码没错)
SSMS允许我们查看一个图形化的执行计划(快捷键Ctrl+L)
固定服务器角色:按照从最低级别的角色(bulkadmin)到最高级别的角色(sysadmin)的顺序进行描述:Bulkadmin:这个服务器角色的成员可以运行BULK INSERT语句.这条语句允许从文本文件中将数据导入到SQL Server 2008数据库中,为需要执行大容量插入到数据库的域账户而设计.Dbcreator:这个服务器角色的成员可以创建.更改 ...
Microsoft SQL Server 2008通过与Microsoft Office的深度集成,为所有人提供了可用的商业智能,以合适的价格提供给正确的用户以合适的工具.它的结果就是公司里所有层级的雇员都可以通过使用易于使用和功能强大的工具看到和帮助改变商业执行.与2007 Microsoft Office系统的集成使得用户可以以他们可以理解和觉得舒服的方 ...SQL Server 2008 R2占用内存越来越大解决方法
&有一台SQL Server 2008 R2数据库服务器，内存经常占用85%左右，查看进程发现sqlserver.exe使用了4G多内存。有一天服务器重启，业务跑起来后，观察sqlserver.exe内存使用一直在几百M，随后几天持续观察，发现内存使用逐渐升高，这说明SQL Server 2008 R2运行越久，占用内存会越来越大。
有了上边的分析结果，解决方法就简单了，定期重启下SQL Server 2008 R2数据库服务即可，使用任务计划定期执行下边批处理：
net stop sqlserveragent
net stop mssqlserver
net start mssqlserver
net start sqlserveragent
【声明】:黑吧安全网()登载此文出于传递更多信息之目的，并不代表本站赞同其观点和对其真实性负责，仅适于网络安全技术爱好者学习研究使用，学习中请遵循国家相关法律法规。如有问题请联系我们，联系邮箱，我们会在最短的时间内进行处理。
上一篇：【】【】当前位置： →
→ 求sql2008磁盘过高出现的原因和解决方法
求sql2008磁盘过高出现的原因和解决方法
& 作者：佚名 & 来源： 互联网 & 热度：
&收藏到→_→：
摘要: 求sql 2008 磁盘过高出现的原因和解决办法，数据库比较大，大概700g，一个单独的表大概有10g，在上面跑数据很慢，然后就停止了...
"求sql2008磁盘过高出现的原因和解决方法"：：
求sql 2008 磁盘过高出现的原因和解决办法，比较大，大概700g，一个单独的表大概有10g，在上面跑数据很慢，然后就停止了。现在的磁盘很高，根本无法执行任何操作，重启服务器和sql服务都会提示该回复中，磁盘高的离谱，回复完之后，磁盘还是居高不下。那位大侠帮帮忙，急等------解决方案--------------------内存有压力吗？log文件和数据文件有没有放在不同的磁盘？tempdb的位置？有没有做分区表？是olap的还是oltp？
------解决方案--------------------
下次请说清楚：磁盘过高是指什么？i/o？空间？不说清楚无法对症下药。我给你几个个人浅见，希望你有用：1、就算再大的，也有很大成分是历史数据，所以应该把一些历史数据存放到读性能比较高的盘。这里有几个前提：a、要分多个文件组，不要都放在primary那里。b、有做磁盘阵列。c、磁盘够大。2、对于业务表，把索引和数据也分开存放，同样使用多个文件组，我的建议是，对于所有表，分最少4个文件组，primary放系统数据，hisdata（我的命名）放历史数据，indexdata放表的索引，data放数据，把indexdata和data这两个分开放到读写都比较好的盘上，比如raid10的盘。进一步把i/o压力分开。3、如果你的cpu个数少于10个，那么按照cpu的个数，拆分tempdb，tempdb的数据文件个数与cpu个数一一对应，大小要一致，另外分1~2个tempdb的日志文件，要分配大一点。同时初始化估计都要到5g左右。自增限制到10g作于吧。够用了。4、对1000万以上的表尽量做分区，这是必须的。否则肯定慢。5、优化语句。优化索引，这个课题太大，无法一下子说清楚。但是这一步至关重要。6、根据业务，考虑把经常读的数据移到另外一个，缓解读的此文来自: 马开东博客
转载请注明出处 网址：
压力。主已写为主。7、定期重建索引（前提是索引是合理的），你那个规模，可能要每天重建了。8、定期用dbcc checkdb检查是否正常，但是对超大有特殊的dbcc，建议你自己去找一下。9、2008以后，你对实例点右键→报表→对象执行统计情况这个报表，可以看到最耗资源的10个查询，你逐个优化。我的最近案例：我把公司的最慢的功能所使用到的表的索引全部调整了一次，一个频繁调用的从40分钟降到31秒，整个系统接近提升了一倍性能，因为那个太重要了。所以你要做的事情还是很多很多，但是先从配置着手，然后重点优化性能。
------解决方案--------------------探讨下次请说清楚：磁盘过高是指什么？i/o？空间？不说清楚无法对症下药。我给你几个个人浅见，希望你有用：1、就算再大的，也有很大成分是历史数据，所以应该把一些历史数据存放到读性能比较高的盘。这里有几个前提：a、要分多个文件组，不要都放在primary那里。b、有做磁盘阵列。c、磁盘够大。2、对于业务表，把索引和数据也分开存放，同样使用多个文件组，我的建议是，对于所有表，分最少4……
------解决方案--------------------dbcc checkdb检查，如果不正常，怎么办呢？& 检查完后微软建议的解决方法是什么？能不能把你的错误信息贴出来
------解决方案--------------------内存正常，报个具体多少g，用了多少g，应该不是难题如果没有特别密集的操作，也可能是硬盘出问题了，才导致io大。。。。。是阵列的吗？
------解决方案--------------------有没有开pae/awe? 700g的内存只用了1.7g太不正常了。
------解决方案--------------------http://blog.马开东/szstephenzhou/article/details/7783252 搜索此文相关文章：此文来自: 马开东博客
网址: 站长QQ
上一篇：没有了
求sql2008磁盘过高出现的原因和解决方法_sql server相关文章
sql server_总排行榜
sql server_最新
sql server_月排行榜
sql server_周排行榜
sql server_日排行榜SQL Server占用内存过高，什么原因导致的，用什么方法可以解决_百度知道