SQLServer中如何找到和排除长时间阻塞的任务

经常听人说xx很慢，xx卡死，如何找到什么东西被卡死了？如何优化SQL性能。今天就简单分享下自己的经验。

在应用系统反应慢或者卡死，无非下面两种情况。

1. 应用系统自身的问题。

2. SQL查询读写互相阻塞。

第一种问题的排查方法很简单，即查看SQLServer数据库是否CPU 100%，是否有长时间被阻塞的查询进程，如果没有多半是应用程序的问题，或者网络问题。

第二种问题又分当前正在发生的阻塞，或者曾经发生过阻塞但是当前已工作正常。

查找正在发生的阻塞

可以用SQLServer自带的报表来快速定位，我们先模拟一个阻塞场景。

--在一个查询窗口运行下面的语句
begin tran
update Account
set name ='Test'
where ID = 21

--在另一个查询窗口运行下面的语句
select * from Account
where ID = 21

我们可以看到Select的查询正在被阻塞。现在按下图方法打开“所有正在阻塞的事务”报表。

我们可以看到，Session ID = 164的 进程正在被阻塞。阻塞者是 Session ID = 159的进程。

同时我们可以用一下SQL来显示这两个进行的详细信息

WITH cteBL (session_id, blocking_these) AS 
(SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s
CROSS APPLY (SELECT is(convert(varchar(6), er.session_id),'') + ', ' 
 FROM sys.dm_exec_requests as er
 WHERE er.blocking_session_id = is(s.session_id ,0)
 AND er.blocking_session_id <> 0
 FOR XML PATH('') ) AS x (blocking_these)
)
SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these
, batch_text = t.text, input_buffer = ib.event_info, * 
FROM sys.dm_exec_sessions s
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
INNER JOIN cteBL as bl on s.session_id = bl.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, ) AS ib
WHERE blocking_these is not  or r.blocking_session_id > 0
ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;

我们可以清楚看到，164被159阻塞，159的blocked_by是空，所以他是阻塞的头。input_buffer中我们可以看到阻塞正在执行的SQL语句，找到这个语句在应用程序中的位置，考虑这个语句为什么需要执行这么长时间？解决办法我们后面来讲。

因为大部分生产情况需要立即解决问题，所以现在我们用简单粗暴的情况先解决这个问题，执行Kill命令。（在笔者见到的很多这类情况，程序员无法排查出具体原因直接粗暴的重启数据库的不在少数）

--执行kill先杀死导致阻塞的进程

Kill 159

我们可以看到杀掉阻塞的进程后，SELECT语句立即返回了。阻塞问题暂时解决。

查找历史发生的阻塞

笔者见过很多人遇到长时间阻塞导致应用响应缓慢时候，病急乱投医，指望一个SQLServer高手来了，马上就能问题抓出来。

排查是否是阻塞导致的问题，首先要排查下面的问题：

1. 是否是服务器CPU高导致

2. 在服务器上执行一个SQL是否缓慢。

3. 网络正常。

如果以上几个都正常，那多半是阻塞问题导致。

SQLServer默认是不捕获历史的长时间阻塞的问题的，这里需要新建一个SQLServer的扩展事件。第一步命名，第二步选择不使用模板，第三步搜索block，选择blocked_process_report

然后选择几个最重要的指标，sql_text, session_id

下一步，下一步。注意在最后一步选择将数据保存到文件。点击完成。

这时我们在扩展事件里面可以看到block_report已经创建好了。

最后不要忘记设置阻塞捕获的时间的阈值。数据库默认是不会启用阻塞捕获，所以一定要启用设置这个阈值。

下面的示例将 blocked process threshold 设置为 20 秒，超过该阈值将为阻塞的每个任务生成阻塞的进程报告。

sp_configure 'show advanced options', 1 ; 
GO 
RECONFIGURE ; 
GO 
sp_configure 'blocked process threshold', 20 ; 
GO 
RECONFIGURE ; 
GO 

好了，我们再此运行之前的SQL语句（不在赘述），然后再去查看block_report会发生什么。

打开报告，可以看到查询语句被 一个未提交的 插入语句阻塞。（抱歉这里用了别人的图，笔者电脑在写这篇的时候突然这个report无法运行）只要找到这个语句在程序中哪里使用了，优化他就可以解决阻塞问题。

解决阻塞问题具体办法

常见可能导致长时间阻塞的原因无非以下几种

• 长时间在运行的事务。例如一个长时间Update\Insert大量数据记录的SQL语句。

  这类等待会等到事务运行完毕后自动解除。应该优化和避免这类大量Update/insert语句同其他SELECT语句共同执行。

  也有可能查询采取了更高的事务隔离级别，例如不可重复读的大量数据提取。导致大量数据行被加锁，阻塞Update/Insert语句执行，客户端呈现超时或卡死的现象。解决办法就是将这些长时间运行的事务错开执行，或者将查询大量数据的查询放入到专用的报表服务器。

• 事务一直保持打开，但是处于休眠状态。这类情况就是本文所演示的，一个长期未提交的事务，一直保持表上的行的排他锁导致的。

  可能是程序未正确处理异常，导致一个未提交事务。例如.net 代码中未正确使用连接池，开启了事务但是出现异常后未显示rollback事务。通过报表，找到发出这个语句的具体位置，检查代码是否未处理异常状态下的回滚。

• 客户端未提取全部数据就断开连接。

• 连接开启了隐式事务，出现异常但是未显示关闭，导致孤立的未提交事务，同第二点。

常用的优化手段

• 将大量的查询(通常是类似报表的操作) 转移到单独的服务器，使用数据库复制技术或者高可用技术，生成一台副本数据库，在副本数据库上执行大数据量查询。

• 为长时间的查询添加索引。很多复杂查询没有充分利用索引，导致扫描这会导致阻塞。例如一个批量更新的Update语句中包含一个Where条件，但是这个条件没有索引，查询不得不表扫描这些数据，这将阻塞其他进程访问这张表。

• 正确使用连接池，如果开启事务，要正确处理异常，在异常里面回滚事务。

• 避免使用隐式事务。

• 存储过程里面也要捕获异常，并回滚事务。

• 尽量缩小查询或更新的数据量。例如不要批量查询或更新几十上百万的数据。如果非要这样做，请考虑方案是否合理，或者放在夜间执行。

• 如果你的系统就是高频率的查询和更新操作并行的。例如上千上万用户高频率增删改，并且发现系统阻塞很严重，那么可以考虑将数据设置为乐观并发模式即READ_COMMITTED_SNAPSHOT 模式，该模式启用后无需更改程序既可显著解决阻塞问题。因为行版本管理，读写不会互相阻塞（依然会写写阻塞），所以并发大大提高。在采用复杂的读写分离模式前，请考虑此模式，可以避免解决方案的复杂性。