SQL Server数据量大导致的程序死掉

SQL Server数据量大导致的程序死掉

故障现象

最近几天，MES下线工位员工反馈，MES程序反应很慢，托盘已经到位了，MES屏幕上却迟迟不显示订单信息，往往延迟十秒以上才出现信息，严重影响生产节拍。起先以为是网络延迟，因为上周曾经对上一个工位进行过线路的改动，是不是动过线路影响了下线工位了，经过检查发现并无异常，ping其他网络地址都很正常，不延迟，不丢包。
我们的产线是通过托盘下的RFID来传递订单，订单信息在托盘到位时候，MES读取OPC中的RFID信息，通过观察OPC，发现RFID信息在托盘到位时候，已经进入OPC，再看屏幕，延迟10秒以后才读取出来。确实比较严重。

问题原因

既然不是网络延迟造成，也不是opc的问题，那会是哪里的问题呢。和厂家工程师沟通了很久，挨个排查了一遍，后来他问，数据库的订单表有多少数据了，是不是很久没有清理的原因。去年是在4月30日移动到历史库的，现在还不足一年的数据，一共有8万多条。按说这个数据量也不至于吧，不过怀疑来怀疑去，只有这里了，因为MES读取到opc的信息后，会去数据库联合查询订单表和产品配置表进行显示，如果查询返回慢，自然显示出来的结果就延迟了。
待生产结束后，晚上运行存储过程将订单表的数据转移到历史库，等第二天再观察一下。
第二天一开班，工人反馈已经快多了，基本上托盘一到，MES就将产品信息和工步显示出来了。看来真是数据表的内容太多导致的。

新的问题

正在以为问题解决了的时候，有一个更严重的问题发生了，下线工位的MES经常死机，一天发生好多次，以前从来没有这样的现象。而且前椅线和后排坐垫线都出现了，乖乖，治聋致哑了，这个问题是哪里的原因呢。
通过查看MES的log发现，每次程序死掉前，都会报进程死锁，然后跟一大堆ArgumentException。

报错显示SQLHelper语句存在问题，这就有点怪异了，MES程序已经运行4年了，从来没有这样的现象，明显不应该是这里的问题。百度了以后，也是建议修改SQLHelper，这个就有点扯了。看来还得另辟蹊径。

捕捉错误

在厂家工程师的帮助下，在SQL SERVER服务器上建立了一个死锁跟踪，看看数据库哪些操作造成了死锁。

经过运行一段时间后捕捉到了死锁的记录

将鼠标移动到最后的圆圈，会显示执行哪个语句造成的死锁发现，居然是拆单存储过程导致。

问题分析

查看拆单表，发现里面有40多万条数据。主机厂发给我们的每个排序订单是5个总成座椅号，到我们这里时候，需要把一个订单号放到订单表，5个总成号放到产品表，再把5个成品号拆成9个部件号放到部件表。订单表，产品表和部件表之间有约束和关联，当存储过程在做拆单操作时，会将这3个表锁定，禁止操作，拆完后再解锁表，正常情况下，这个过程瞬间就结束了，基本不会有什么影响。

存储过程先将主机厂的XML文件解析，然后将5个总成号放到拆单表，接着要和产品配置表进行比对，如果符合产品定义，则将写入订单表，产品表和部件表，不符合则报警，需要人工干预。由于数据量多了，查询比对的时间也就增加了，导致锁表时间变长，恰好这时候下线工位的MES在完成生产时候要写入部件表和产品表，当写入遇到锁表，就爆出死锁问题了。

原本计划每年做一次数据移动，看来拆单表40万条数据就无法支撑了，必须立马移动。经过几分钟的数据移动清理，拆单表保留了最近3个月的数据，其余的都移到历史库。

故障解决

再观察下线MES程序，已经没有死掉的现象了。

事件回顾

MES系统的拆单方式在去年4月份做了一次改变，先前生产模式是散件订单，主机厂在排序单出来之前，发一个BOM表，里面有座椅生产用到的零件号和数量，BOM文件通过iDoc进入SAP，SAP系统做产品比对后，返回给MES一个订单表和产品表，MES并没有拆分订单这个操作。当主机厂发送排序单的时候，里面只有订单号和排序号，MES拿到文件去数据库查找对应的订单下发到产线。所以不存在锁表。
而当主机厂改变了下发模式以后，我们也将拆单工作由MES完成，设定为1分钟拆单一次。如果有订单进来，拆单就会锁表，当数据量太大导致锁表时间变长，恰好遇到下线工位程序此时写表，撞车以后就发生写不进去导致程序无响应而死掉了。

维护计划

为了避免类似的事情再次发生，在数据库建立定时任务，每隔3个月对数据进行一次移动历史库操作，保持生产库的性能。