忆一次因SQLServer内存占用飙高导致的工厂停工

一、还有五小时到达战场

       现在回想起来，整件事还挺离谱的......
       中午午休，正在公司总部（重庆）附近和同事们一起享受午餐；
       突然接到上司电话，要求我立即出发去广州一趟，今天中午有个工厂因为我们的程序出问题导致停工了！！！
       我立即反馈，由于我们的程序都是运行在Windows上的，只要给我远程桌面权限，我可以马上开始解决，争取马上让产线复工！
       上司：“你还是立即出发，过去一趟吧，这件事特别严重，于情于理得派人过去一趟，而且客户也强调要求派人到现场的。”
       得，背上我心爱的小包，出发吧......
二、抵达现场

       一路的士与飞机，抵达现场已是五小时后，立即开始作战！
       首先，查看一下环境：环境整洁、管理完善的小型工厂，设备不多；除了厂方的生产设备以外，属于我们的只有一台服务器、多台触屏一体机、一些其他设备。
       生产设备是受我们的设备防呆管控的；现因我们的设备一直报错，导致生产设备无法生产；一条线的几个工人已经有薪休假一个班次了......
三、界定问题设备

       首先，检查所有触屏一体机的系统、程序、日志，发现全部自身无异常，但所有访问服务器接口均无响应；
       “一些其他设备”只是简单的输入或输出设备，在此事上不重要；
       已初步确认是服务器上有问题。
四、界定问题程序与原因

       从各管理员那里获得相应权限，接入局域网，用远程桌面进入服务器。
       服务器的配置，我看了直接傻眼，往好点说，除了显卡，和我十年前用九千块买的家用游戏电脑差不多。
       服务器里只有IIS和SQLServer2008，直接查看任务管理器，发现SQLServer内存占用80+%，其他程序占用不多；
       这种情况，按我这些年遇到的各种情况，我知道的基本上只有以下四种猜测了：
              1、逻辑死循环
                     ①数据与程序共同造成死循环
                            这种情况一般出现在多年前开发的程序上，也不排除现在还有人这么写......
                            举个例子，有两条数据{ID:1,ParentID:2}与{ID:2,ParentID:1}，于是程序反复调用Select ID,ParentID from x where ID=1与Select ID,ParentID from x where ID=2。
                     ②SQL死循环
                     以上两种逻辑死循环，使用SQL Server Profiler跟踪一下SQL语句就明白了；
                     不过，我这次跟踪发现没有新加入执行的SQL，先排除逻辑死循环。
              2、死锁
                     执行以下SQL语句查询正在等待锁释放的语句：

1. --查询死锁语句,获取blocked字段值
2. select * from sys.sysprocesses where spid>50 and blocked<>0
3. ----查询阻塞或者死锁的语句（@blocked替换为查询到的blocked字段值）
4. --dbcc inputbuffer(@blocked)
5. ----杀死死锁（@blocked替换为查询到的blocked字段值；不懂死锁的同行还是不要轻易执行此kill语句）
6. --kill @blocked

复制代码

                     很遗憾，查出来没有数据，本次也不是死锁造成的。
              3、内存泄漏
                     网上有很多描述各种情况下SqlServer2008内存泄漏的文章，这里不多做描述，他们的解决办法都是升级至Microsoft SQL Server 2008 SP3。
                     很可惜，服务器上就是SP3，先跳过此情况。
              4、索引与缓存
                     前面几种情况均不是，只能猜测是此情况引起的，且索引与缓存造成内存飙高的情况，我也暂时不知道怎么检查，一般直接释放内存试试。
                     顺便提一下，下面几个语句可以清除缓存，使SQLServer重新填充缓存，但不会释放已占用内存，所以不适用解决高内存占用：

1. --创建一个检查点，在该点保证全部脏页都已写入磁盘，从而在以后的恢复过程中节省时间。
2. CHECKPOINT
3. --清除存储过程相关的缓存
4. DBCC FREEPROCCACHE
5. --清除会话缓存
6. DBCC FREESESSIONCACHE
7. --清除系统缓存
8. DBCC FREESYSTEMCACHE('All')
9. --清除所有缓存
10. DBCC DROPCLEANBUFFERS

复制代码

SQL Code五、揣着糊涂装明白，开始解决问题

       通过设置最大服务器内存，避免内存占用过高，并释放多占用的内存。
       
       
       服务器共有16GB内存，这里我设置为8192MB。
       任务管理器里SQLServer进程的内存占用比开始缓慢下降，回到生产区操作触屏一体机均已正常！！
       通知客户，可以恢复生产了。
       由于发生大故障，客户也不敢立即放我走，挽留我几天......
       接下来几天，我也不想闲着，联系公司给与权限，拉取下来代码，将各设备上的程序挨个解决死循环风险、解决死锁风险、空值检测、算法优化等。
       然后，写了一份详细文档，介绍了下次再出同样问题该如何解决，给与客户。（这很难界定，我方不知道是没有还是没有收到每年的服务费，这似乎不该我方分配人力负责；对方有第三方运维人员，但并不想管，推给我们了；客户自己不懂怎么运维，无法承接；唉，一笔烂账。）
       最后，停留一周，飞回总部了。
六、一个月后，居然又出现相同问题了？

       一个月后，深夜突然来电话，居然又出现故障了？？？
       不过，由于此次正在其他大厂出差，一时走不开，只能远程解决了。
       深夜把对方服务器管理员骚扰一下，得以进入远程桌面；
       将上面检查再做一遍，发现依旧没有任何头绪，而且最大服务器内存是8192MB，但实际占用早已超过！
       侥幸，将最大服务器内存改为1024MB，又改回8192MB，居然开始了内存释放！！
       看来，还是会有一些意外情况会导致过高占用，需要定时监测、释放服务器内存：

1. /*
2.     将以下SQL设置为SQLServer代理作业，每五分钟执行一次
3. */
4. declare @mb bigint,@set_mb int,@sql nvarchar(1000),@max int,@min int,@now sql_variant
5. set @max=4096--最大服务器内存(MB)
6. set @min=1024--临时服务器内存(MB)：如果出现意外情况，实时服务器内存超过最大服务器内存，将会将最大服务器内存设置为临时服务器内存，以实现强制释放部分内存
7. set @mb=(
8.     SELECT top 1 (virtual_address_space_committed_kb / 1024) AS '已提交或已映射到物理页的已保留虚拟地址空间量（MB）'
9.         FROM sys.dm_os_process_memory
10. )--实时服务器内存(MB)
11. set @now=(
12.     select top 1 value from sys.configurations where name='max server memory (MB)'
13. )--之前设置的最大服务器内存(MB)
14. if(@mb>@max)
15.     set @set_mb=@min
16. else
17.     set @set_mb=@max
18. if(@now!=@set_mb)
19. begin
20.     set @sql='
21.         exec sp_configure ''show advanced options'', 1
22.         RECONFIGURE
23.         exec sp_configure ''max server memory'', '+convert(varchar(500),@set_mb)+'
24.         RECONFIGURE
25.     '
26.     EXECUTE(@sql)
27. end

复制代码

七、后记

       后来，我得知此次派遣，公司居然赚了一万块钱，倒也不觉得直接派去现场待一星期离谱了......

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！