SQL 2008执行语句遇到内存不足的案例(error 701)之一

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,独享型 2核4GB
简介: SQL 2008执行语句遇到内存不足的案例(error 701)之一某个特定的存储过程在SQL2008中执行会遇到以下错误:Msg 701, Level 17, State 123, Procedure GetAl...

SQL 2008执行语句遇到内存不足的案例(error 701)之一

某个特定的存储过程在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是以8kpage为单位的,因此640000计算出来时5000MBCommitted表示当前已经使用的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的可用内存,memoryavailable 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数的一半。在多于32CPU的系统中,我们建议设置这个值为4或者8.

  

额外的问题:

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

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


https://blogs.msdn.microsoft.com/apgcdsd/2011/01/16/sql-2008error-701/


相关实践学习
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
5天前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境: 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库,SQL Server数据库存放在D盘分区中。 SQL Server数据库故障: 存放SQL Server数据库的D盘分区容量不足,管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错,连接失效,SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。
|
21天前
|
SQL Java 数据库连接
2万字实操案例之在Springboot框架下基于注解用Mybatis开发实现基础操作MySQL之预编译SQL主键返回增删改查
2万字实操案例之在Springboot框架下基于注解用Mybatis开发实现基础操作MySQL之预编译SQL主键返回增删改查
26 2
|
6天前
|
SQL 关系型数据库 MySQL
【Python】已解决:ERROR 1064 (42000): You have an error in your SQL syntax. check the manual that correspo
【Python】已解决:ERROR 1064 (42000): You have an error in your SQL syntax. check the manual that correspo
21 0
|
11天前
|
SQL 关系型数据库 MySQL
You have an error in your SQL syntax;check the manual that corresponds to you Mysql server version
You have an error in your SQL syntax;check the manual that corresponds to you Mysql server version
|
18天前
|
SQL 存储 关系型数据库
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
|
18天前
|
SQL 关系型数据库 MySQL
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
|
1月前
|
SQL 关系型数据库 PostgreSQL
【sql】PostgreSQL物化视图表使用案例
【sql】PostgreSQL物化视图表使用案例
19 0
|
1月前
|
SQL Java 数据库连接
MyBatis SQL 批量更新(代码➕案例)
MyBatis SQL 批量更新(代码➕案例)
80 0
|
SQL 缓存 Go
查看SqlServer的内存使用情况
原文:查看SqlServer的内存使用情况       上一篇提到动态T-SQL会产生较多的执行计划,这些执行计划会占用多少内存呢?今天从徐海蔚的书中找到了答案。动态视图不仅可以查到执行计划的缓存,数据表的页面缓存也可以查到,将SQL整理一下,做个标记。
1562 0
|
SQL 缓存 索引
查看SqlServer的内存使用情况
SQL SERVER内存按存放数据的类型,大概可以分为三类: 1、buffer pool,存放数据页面的缓冲区,sql server数据都是存放在一个个8K的页面里,当用户需要使用这个页面上的数据时,都是把整个页面加载到内存的buffer pool区缓存起         来。
1126 0