SQLServer的一次堵塞分析

SQLServer的一次堵塞分析

今天工作人员突然报告说某个界面无法正常打开了，***个想到的便是SQLServer又发生堵塞了。在SQLServer中，做了一个5分钟运行一次的定时任务，定期扫描堵塞情况；不过五分钟有些太久了。就运行了一下查询堵塞的脚本，看看目前系统里正在发生的堵塞情况。
SELECT
blocked_query.session_id AS blocked_session_id,
blocking_query.session_id AS blocking_session_id,
blocking_sql_text.text AS blocking_sql_text,
blocked_sql_text.text AS blocked_sql_text,
waits.wait_type AS blocking_resource,
blocked_query.command AS blocked_command,
blocking_query.command AS blocking_command,
blocked_query.wait_type AS blocked_wait_type,
blocked_query.wait_time AS blocked_wait_time,
blocking_query.total_elapsed_time AS blocking_elapsed_time,
GETDATE()
FROM sys.dm_exec_requests blocked_query
JOIN sys.dm_exec_requests blocking_query ON
blocked_query.blocking_session_id = blocking_query.session_id
CROSS APPLY
(
SELECT *
FROM sys.dm_exec_sql_text(blocking_query.sql_handle)
) blocking_sql_text
CROSS APPLY
(
SELECT *
FROM sys.dm_exec_sql_text(blocked_query.sql_handle)
) blocked_sql_text
JOIN sys.dm_os_waiting_tasks waits ON
waits.session_id = blocking_query.session_id

查询结果很简单，
被堵塞的是一个select语句，堵塞的是一个触发器；两者操作的是同一个表，blocking_resource为LCK_M_S，很明显是一个读写的相互堵塞。
分析步骤理应优先从堵塞进程开始分析，然后再分析select语句
触发器的业务逻辑比较复杂，大概有600多行，其中有一二十个select、update语句
只能按顺序一个个来分析相关的select和update语句了，看看哪条sql可能出了问题
主要是看SQL的where条件是否满足索引和高选择性要求，很快便定位到一条sql语句
SELECT *** @var1=field1 FROM tablename WHERE field2=@var2 AND field1 IS NOT NULL AND primarykey<>@primarykey
该表将近10万条记录，而执行该查询，等待了1分钟却看不到执行结果。理论上是不应该的，先标记下来吧，继续往下跟踪。
很快又发现一条带数据库链接的查询
SELECT *** primarykey FROM DBLINK.DBNAME.USERNAME.tablename WHERE COND1
先试着运行一下吧，该SQL也是半天没有响应。
问题应该出现在这两个地方，需要再了解一下相应的业务逻辑再进行SQL优化，当务之急是先把该session杀掉
运行kill sessionid后，却还是无法打开程序界面，继续运行查询堵塞脚本，发现blocking_command变成了KILLED/ROLLBACK，也就是说一直处于rollback状态，没有杀成功，很奇怪。而且整个数据库似乎已经全部瘫痪了，所有应用程序均无法执行。
于是系统工程师就把数据库重启了一下，又重新打开该程序界面进行数据处理，结果很快又出现之前的症状。
后来想是不是DBLINK出现了问题，继续运行基于该DBLINK的查询试一下，发现基本上全部无法执行；还是先检查一下网络吧
系统工程师登陆到服务器上查看windows的日志，果然发现了很多网络故障，紧急处理一下网络。
再次运行查询堵塞脚本，发现堵塞已经自动消除，而那条看似很慢的SQL也很快运行出结果了。