存储过程执行过程中报 System.OutOfMemoryException

优化代码，而不是用什么DBCC来暴力清理。

请问具体是哪个SQL语句报错System.OutOfMemoryException?
应设法优化SQL写法,及时释放没必要用到的内存.

3楼你好，我的代码在前面空跑过，语法都没有问题，现在报错的提示内存异常的那个报错，随机出现，有时候出现在第11步，有时候出现在12步，不知道是为什么？代码里面主要是以 update 为主的语句，不知道有没有什么可以优化的部分？

据我推测：
是你update 某个字段类型设置的值太小，导致溢出的。比如 varchar(50)设的太小这类....

System.OutOfMemoryException 是 .Net 程序的错误，调试程序去！又：做存储过程是主要为了执行速度快（无网络交互）。
你这个跑出内存不足的过程还是考虑移到客户端实现吧，至少能分担下内存压力。

查看操作系统是否为32位。如果是开启awe。
如果已经是64位，硬件内存不够，要么增加硬件内存，要么就只有修改你这个存储过程了。

ALTER PROCEDURE [dbo].[pro_BL_audit_column_update_automation]  (
@startTime_10001 datetime,
@endTime_10001 datetime,
@startTime_10201 datetime,
@endTime_10201 datetime,
@this_month_snapshot_path varchar(100),
@last_month_snapshot_path varchar(100),
@this_month_basicTable_save_path varchar(100),
@last_month_basicTable_save_path varchar(100),
@this_month_log_save_path varchar(100),
@flag int
   )
as
begin
/*

输入参数: @startTime_10001
输入参数：@endTime_10001
输入参数: @startTime_10201
输入参数：@endTime_10201
输入参数：@this_month_snapshot_path
输入参数：@last_month_snapshot_path
输入参数：@this_month_basicTable_save_path
输入参数：@last_month_basicTable_save_path
输入参数：@this_month_log_save_path
输入参数：@flag 对于基础数据表采取的处理方式：
-1 为删除原来的基础数据表重新生成新的基础数据表；
1 在表名后加上日期并重新生成基础数据表（如果新生成的基础数据表已存在则会先删除掉再生成）
0 为删除原来的基础数据表跳过快照导入，日志抽取步骤，重新生成新的基础数据表
   （重复执行时推荐该参数）
输出参数：无
返回值：在 pro_exec_result 表中记载了执行结果的反馈以及错误信息
测试参数

USE [dgc_shen_ji]
GO DECLARE @return_value int EXEC @return_value = [dbo].[pro_BL_audit_column_update_automation]
@startTime_10001 = N'20140701 3:20:56',
@endTime_10001 = N'20140801 1:31:37',
@startTime_10201 = N'20140701 3:14:42',
@endTime_10201 = N'20140801 1:30:01',
@this_month_snapshot_path = N'dgc_shen_ji.dbo.Player_Common_20140801',
@last_month_snapshot_path = N'dgc_shen_ji.dbo.Player_Common_20140701',
@this_month_basicTable_save_path = N'dgc_shen_ji.dbo.stat_July',
@last_month_basicTable_save_path = N'dgc_shen_ji.dbo.stat_June',
@this_month_log_save_path = N'dgc_shen_ji.dbo.diamond_log_July_Detail',
@flag = -1 SELECT 'Return Value' = @return_value GO*/------------------  字段类型定义开始 -------------------
-- ETL 动态SQL变量
declare @sql_flag_drop varchar(3000); --flag 表删除判断动态SQL使用
declare @sql_flag_create varchar(3000); --flag 基础表建立判断动态SQL使用
declare @sql_snapshot_check varchar(3000); --snapshot 快照存在判断动态SQL使用
declare @sql_snapshot_create varchar(3000); --snapshot 快照建表，导入动态SQL使用
declare @sql_last_table1_check varchar(3000); --last_table1 上月基础表存在判断动态SQL使用
declare @sql_this_table1_check varchar(3000); --this_table1 本月基础表存在判断动态SQL使用
declare @sql_exec varchar(3000); -- 其他所有拼接的动态SQL使用-- ETL 前提条件检测变量
declare @check_result int; -- 接受CMD命令反馈结果，并用于检测
declare @check_input_file_path varchar (3000); -- 文件存放的外部路径，并用于检测---ETL 执行结果使用变量
declare @exec_date date ; --ETL执行时间
declare @etl_name varchar(200); --ETL执行名称
declare @etl_arg_date datetime; --ETL执行时使用的时间参数
declare @etl_start_time datetime; --ETL开始时间
declare @etl_end_time datetime; --ETL完成时间
declare @etl_cost_time varchar(50); --ETL耗时
declare @etl_exec_result varchar(10); --ETL执行结果
declare @etl_err_number bigint; --ETL错误号
declare @etl_err_message varchar(2000); --ETL错误信息
declare @etl_err_state bigint; --ETL执行状态
declare @etl_err_severity bigint; --ETL错误等级
declare @etl_exec_no bigint; --ETL执行批次号 ------------------  字段类型定义结束 -------------------这是PRO 的定义部分，PRO太长，我分段贴一下，请大神帮我看下。这是我写的，今天测试的时候，flag测试参数用0则可以完成，用-1的话，必须注释掉我的 SELECT “XX已更新” 字段才能继续跑成功，
这个PRO没有客户端来调用，是完全在数据库单独跑的，跑完后直接在数据库端查看结果，主机是64位的SERVER 2008  内存是16G

----------------------- DBtotalChongzhi_qichu 特殊处理根据月份判断开始 ----------------------
IF DATEPART(MM,GETDATE()) = 8
BEGIN
-- step 11.0  国服 10001 服务器的 DBtotalChongzhi_qichu 更新为上个月的 DBtotalChongzhi_qimo
set @sql_exec = 'update ' +
@this_month_basicTable_save_path + '
set
DBtotalChongzhi_qichu = b.DBtotalChongzhi_qimo
from ' +
@this_month_basicTable_save_path + ' a
inner join ' +
@last_month_basicTable_save_path + ' b
on
convert(varchar,a.account_id) = b.account_id
and
a.gameserverid = b.gameserverid
and
a.gameserverid = 10001
and
a.account_id = b.account_id'

--SELECT '更新 10001 DBtotalChongzhi_qichu 的字段完毕'
print 'step 11.0' + CHAR(13) +  @sql_exec;
--DBCC FREEPROCCACHE
--DBCC FREESESSIONCACHE
--DBCC FREESYSTEMCACHE('All')
--DBCC DROPCLEANBUFFERS
exec (@sql_exec);

-- step 11.0.1  台服 10201 服务器的 DBtotalChongzhi_qichu 更新为上个月快照的 total_recharge
set @sql_exec = 'update ' +
@this_month_basicTable_save_path + '
set
DBtotalChongzhi_qichu = b.total_recharge
from ' +
@this_month_basicTable_save_path + ' a
inner join ' +
@last_month_snapshot_path + ' b
on
convert(varchar,a.account_id) = b.account_id
and
a.gameserverid = b.gameserverid
and
a.gameserverid = 10001
and
a.account_id = b.account_id'

--SELECT '更新 10201 DBtotalChongzhi_qichu 的字段完毕'
print 'step 11.0.1' + CHAR(13) +  @sql_exec;
exec (@sql_exec);
END
----------------------- DBtotalChongzhi_qichu 特殊处理根据月份判断结束 ----------------------
ELSE
BEGIN
-- step 11.1  DBtotalChongzhi_qichu 更新为上个月的 DBtotalChongzhi_qimo
set @sql_exec = 'update ' +
@this_month_basicTable_save_path + '
set
DBtotalChongzhi_qichu = b.DBtotalChongzhi_qimo
from ' +
@this_month_basicTable_save_path + ' a
inner join ' +
@last_month_basicTable_save_path + ' b
on
convert(varchar,a.account_id) = b.account_id
and
a.gameserverid = b.gameserverid
and
a.account_id = b.account_id'

--SELECT '更新 DBtotalChongzhi_qichu 的字段完毕'
print 'step 11.1' + CHAR(13) +  @sql_exec;
exec (@sql_exec);
END

-- step 11.2 更新 DBtotalChongzhi_qichu 为 null  的字段为 0
set @sql_exec = 'update
a
set
a.DBtotalChongzhi_qichu  = 0
from ' +
@this_month_basicTable_save_path + ' a
where
DBtotalChongzhi_qichu is null'

--SELECT '更新 DBtotalChongzhi_qichu 为 NULL  的字段完毕'
print 'step 11.2' + CHAR(13) +  @sql_exec;
exec (@sql_exec); 代码部分大部分为这样的拼接语句，逻辑不复杂，但是每个语句都是拼接的，

exec (@sql_exec); 改成print，然后看看出来什么语句

step 11.0.1
update dgc_shen_ji.dbo.stat_July
set
DBtotalChongzhi_qichu = b.total_recharge
from dgc_shen_ji.dbo.stat_July a
inner join dgc_shen_ji.dbo.Player_Common_20140701 b
on
convert(varchar,a.account_id) = b.account_id
and
a.gameserverid = b.gameserverid
and
a.gameserverid = 10001
and
a.account_id = b.account_id(801699 行受影响)
step 11.2
update
a
set
a.DBtotalChongzhi_qichu = 0
from dgc_shen_ji.dbo.stat_July a
where
DBtotalChongzhi_qichu is null(106608 行受影响)
step 12.0
update dgc_shen_ji.dbo.stat_July
set
DBtotalChongzhi_qimo = b.total_recharge
from dgc_shen_ji.dbo.stat_July a
inner join dgc_shen_ji.dbo.Player_Common_20140801 b
on
convert(varchar,a.account_id) = b.account_id
and
a.gameserverid = b.gameserverid
and
a.account_id = b.account_id(908307 行受影响)版主我的语句应该是没有语法错误的，能正确执行的

回楼上，这个存储过程是更新某张数据表的各个字段用的，跑完后不显示结果，仅仅以SELECT 来提示目前PRO跑到了某个阶段了，我注释掉SELECT ***的提示后能跑完，不知道是为什么

语法没问题不代表性能没问题啊，分别贴下这些语句的执行计划：
1、
UPDATE  dgc_shen_ji.dbo.stat_July
SET     DBtotalChongzhi_qichu = b.total_recharge
FROM    dgc_shen_ji.dbo.stat_July a
        INNER JOIN dgc_shen_ji.dbo.Player_Common_20140701 b ON CONVERT(VARCHAR, a.account_id) = b.account_id
                                                              AND a.gameserverid = b.gameserverid
                                                              AND a.gameserverid = 10001
                                                              AND a.account_id = b.account_id2、
UPDATE  a
SET     a.DBtotalChongzhi_qichu = 0
FROM    dgc_shen_ji.dbo.stat_July a
WHERE   DBtotalChongzhi_qichu IS NULL3、
UPDATE  dgc_shen_ji.dbo.stat_July
SET     DBtotalChongzhi_qimo = b.total_recharge
FROM    dgc_shen_ji.dbo.stat_July a
        INNER JOIN dgc_shen_ji.dbo.Player_Common_20140801 b ON CONVERT(VARCHAR, a.account_id) = b.account_id
                                                              AND a.gameserverid = b.gameserverid
                                                              AND a.account_id = b.account_id

对于update的执行计划，可以用：
begin tran
update语句
rollbak

begin tran
UPDATE  dgc_shen_ji.dbo.stat_July
SET     DBtotalChongzhi_qichu = b.total_recharge
FROM    dgc_shen_ji.dbo.stat_July a
        INNER JOIN dgc_shen_ji.dbo.Player_Common_20140701 b ON CONVERT(VARCHAR, a.account_id) = b.account_id
                                                              AND a.gameserverid = b.gameserverid
                                                              AND a.gameserverid = 10001
                                                              AND a.account_id = b.account_id

rollback版大，这个执行计划去哪里调取啊，我比较菜……

事务更新中记录是被锁定的，按照一般隔离级别 SELECT 更新中的数据会进入等待状态。
有没有 SELECT 的差异自然大了。

回19楼的朋友，我的SELECT 是固定的字符串，不锁任何表，比如SELECT "charge_qichu 已经更新完毕"这样的，便于在编辑器查看到当前执行到某个阶段了，提示一下，应该不会存在你说的那个情况

快速一点的方法：右键图上绿色的字，选择【缺少索引xxx】那个，会在新窗口给你写好了代码，你改一下索引名字就可以执行了，执行后再看看效果

初步估计是32位的可能
是非DATA BUFFER耗尽，如编译内存资源不足等可能
这不是三五句能表达或解决问题的

你的SP中有没有用到 @@ROWCOUNT、@@ERROR？
加 SELECT 语句会改变这两个全局状态，从而导致流程控制错误。UPDATE ...SELECT 'XX已更新'
/* 加了上面的 SELECT 语句后 @@ERROR 就始终为 0，
UPDATE 的错误丢失，就进不到下面的分支中去了。 */
IF @@ERROR <> 0
BEGINEND

回楼上的，没有用到@@ROWCOUNT、@@ERROR ，我使用了
begin catch
update
pro_exec_result
set
--ETL完成时间
ETL_END_TIME = GETDATE(),
--ETL耗时
ETL_COST_TIME = ROUND(CONVERT(float,DATEDIFF(SECOND,@etl_start_time,GETDATE()))/60,2),
--ETL错误号
ETL_ERR_NUMBER = ERROR_NUMBER(),
--ETL错误信息
ETL_ERR_MESSAGE = ERROR_MESSAGE(),
--ETL执行状态
ETL_ERR_STATE = ERROR_STATE(),
--ETL错误等级
ETL_ERR_SEVERITY = ERROR_SEVERITY(),
--ETL执行结果
ETL_EXEC_RESULT = '执行失败'
from
pro_exec_result
where
ETL_EXEC_NO = @etl_exec_no
end catch
来捕捉执行结果异常状态

22楼的方法试出结果没？如果还要分析代码，就贴 @flag 相关部分、SELECT “XX已更新" 前后部分。

回楼上的，22楼的方式测试过了没用，因为我每次都是全表更新，每个人的字段都会更新，所以加不加索引我觉得没用，测试的时候发现加了索引反而整个PRO跑完要多出20s。

SELECT “XX已更新" 的输出会有多少个？

回楼上，这个SELECT XX 输出有35个！

我8楼提过，这个System.OutOfMemoryException是不是 .Net 的错误？

是在 SQL2008 里面 studio 的消息提示里面的的错误，不是 .net 的

结果页显示35个表格很费内存？
那么把 SELECT 改为 PRINT 试试，向消息页输出文本消耗总小了吧。

回楼上的，我也不明白为什么显示35个表格这么费内存，我在显示SELECT 后面每个都加了PRINT 的，不过现在去掉SELECT 就不报异常了，

回楼上的，升级硬件不大可能，我这个存储过程其实不需要输出结果，只是在不断的UPDATE这一张表的很多字段，使用临时表没什么意义，而且数据总量只有150W条左右的表

35个DataGrid，写程序从来不会用这么多！
你把输出改为文本方式，保留 SELECT 语句再试试，如果正常，那么就是显示控件耗费的内存问题了，和 SQL 无关。

回楼上，现在是采取的这个方法，注释掉了35个SELECT 后一切正常

基本可以确定原因了。
调试消息用 PRINT，要返回结果才用 SELECT.又：我39楼的意思是将结果页的输出格式从表格改为文本，保留 SELECT 可以验证一把。

调试易

存储过程执行过程中报 System.OutOfMemoryException

解决方案 »