解决SQLSERVER 游标 FETCH数据失败问题

公司的软件系统使用SQLSERVER数据库。
前天晚上优化了系统中的一个重要存储过程，第二天用户使用系统的时候，开始还挺好，自我感觉不错，好像昨晚的优化工夫没有白费。但用着用着突然出现了系统卡起来后，又过了一会整个就卡死了，数据根本无法保存，严重影响用户工作进行，用户和我都十分着急。
我知道，问题肯定是出在数据库上。
立马查询下面这段SQL ,发现确实有10多个任务处于suspended挂起的状态。

--查询会话中有多少个worker在等待 
SELECT TOP 100
[session_id],
[request_id],
[start_time] AS '开始时间',
[status] AS '状态',
[command] AS '命令',
 dest.[text] AS 'sql语句', 
DB_NAME([database_id]) AS '数据库名',
[blocking_session_id] AS '正在阻塞其他会话的会话ID',
 der.[wait_type] AS '等待资源类型',
[wait_time] AS '等待时间',
[wait_resource] AS '等待的资源',
[dows].[waiting_tasks_count] AS '当前正在进行等待的任务数',
[reads] AS '物理读次数',
[writes] AS '写次数',
[logical_reads] AS '逻辑读次数',
[row_count] AS '返回结果行数'
FROM sys.[dm_exec_requests] AS der 
INNER JOIN [sys].[dm_os_wait_stats] AS dows 
ON der.[wait_type]=[dows].[wait_type]
CROSS APPLY 
 sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
WHERE [session_id]>50  
ORDER BY [cpu_time] DESC

kill 'xx' --结束sessionId 等于 xx 的会话

紧急之下，用kill 命令杀掉这些挂起的会话。但kill 掉第一拨之后，再次执行上面的查询语句，发现又出现了4个挂起的，再次kill 。两拨kill 下去，系统很快恢复了正常，暂时摆脱了困境。

但这时还不知道是哪个存储过程或哪段SQL导致了刚才数据库的堵塞，也没有去怀疑是昨晚自己改了那个存储过程导致的，因为知道自己昨晚对那个存储改的不大，只是注释掉了其中一部分不需要的语句，不至于产生这么大的影响。没有找到根本原因，那这就是个定时炸弹悬在心上。

下午的时候，用户又反馈第三方系统的数据不能同步到系统里来。要实现同步，就必须用到我昨晚优化的那个存储过程了。
首先手动执行了一次该存储过程，结果运行了7分钟仍然没有结束，而且数据依然没同步过来。运行时间太久了，可以确定这个存储过程执行中出问题了。
在SQLSERVER中开始调试这个存储，没走两步，在从存储过程中定义的一个游标中FETCH第一行数据的时候，卡住不动了，FETCH数据失败了。这还是我第一次遇到这种问题。接下来为了解决这个问题，我花了将近3个小时的时间。

  • 我先查询了定义该游标中的SELECT语句，没问题，数据正确查出来了；
  • 然后监控@@FETCH_STATUS变量，发现在OPEN 游标这一步后，@@FETCH_STATUS就变成了-1。我知道，在SQLSERVER中，和其他很多的软件系统中，-1都代表失败，0代表着成功。所以我以为在OPEN 游标这一步就失败了。OPEN游标失败，我压根不知道怎么解决。上网搜索，查不到任何一条类似的结果。冥思苦想，想不出原因在哪。因为@@FETCH_STATUS变量是SQLSERVER引掣在管理着，最后，我开始怀疑是不是SQLSERVER引掣对@@FETCH_STATUS变量的管理出了问题！！！空怀疑是不行的，那就通过测试来验证吧。接着新打开一个查询tab，写了个简单的游标，测试OPEN 游标，FETCH数据，发现一切正常，而且在这个测试中，OPEN 游标之后 ，@@FETCH_STATUS也变成了-1，看来这个-1并没有代表着失败。
  • 又回到那个问题的存储过程，OPEN 游标执行完之后(成功的)，从游标中FETCH第一行数据，然后这一步就一直在运行中，不能完成。是不是某一个数据有问题，导致FETCH数据失败呢？然后改了一下游标的定义，只SELECT 一个数据列，FETCH这一步也相应修改为只有一个数据列。再测试，还是卡在FETCH这一步出不来。好吧，我把这个存储里定义游标的SELECT语句放入我前面写的用于测试@@FETCH_STATUS的游标中试一试，结果也是一样，卡在FETCH这一步出不来。现在这可是只有一个数据列的游标啊，竟然也FETCH不出来。难道这一列数据是有问题的？有数据类型问题？试一下吧，我在定义游标的SELECT中，把SELECT 的这列数据作了下类型转换，CAST(thisColumn AS VARCHAR(64))，再试，还是一进入FETCH那一步就出不来。但是有新情况，在我点取消调试之后，显示查询结果的窗格中，竟然显示出了这一列的数据。。。这起码预示着SQLSERVER是读到数据了，这个进展大大增加了我的信心，我知道离解决这个问题不远了。
  • 继续测试，既然能读出数据了，那我在定义游标的SELECT 中加个TOP 3，只对前3行数据进行处理会怎么样呢。哈哈，这样一试，成功了FETCH出了3条数据出来，真是开心啊。经历了前面的无头绪，绝望，现在竟然成功了，这种开心真是再多money都比不了的。再继续一步，把前面加的那个数据类型转换去掉，又试，依然成功，太棒了，说明数据类型是没有问题的。
  • 再回到要解决的那个存储过程，在定义游标的SELECT里加上一个TOP 10，再调试，也成功了！！！哈哈哈哈，真不容易啊。。。

到这里，这个问题是解决了，但为什么会出现这样的问题，为什么要在游标中加个TOP n 才能FETCH出数据？在我昨晚优化之前，是没有这个TOP n 的，为什么此前一直都能正常运行？这些疑问有待续探索。

今天解决这个问题，花费了3个小时，时间是挺长的。现在回顾这个过程，如果在排查的过程中有良好的思路，逐步测试，其实是用不了这么长时间的。不过通过这次经历，我觉得今后我在面对自己从未遇到过的问题，上网求助也指望不上的时候，我有了自己的一套方法：
第一步，找出问题核心死穴，用最简化的模拟，做测试。这一步对应上面过程中的自己编写一个简单游标进行测试。
第二步，把要解决的具体问题中的数据代入第一步中的简化测试中去测试，根据结果去分析。应从多方面去做尝试，比如缩小数据集大小，数据类型转换等。必须找到一个使这一步测试能成功的解决方法。
第三步，把第二步中找到的解决方法用在待求解的原始问题中，做测试看结果。如果成功了，问题得到解决；如果失败了，仔细分析测试过程中展现出来的数据，状态，结果等，找出与方法使用前的异同，根据这些内容再去找尝试方法，直到成功。

有志者，事竟成，破釜沉舟，百二秦川终归楚；
苦心人，天不负，卧薪尝胆，三千越甲可吞吴。