SQLServer的一次堵塞分析

来源:互联网 发布:电子产品软件规格书 编辑:程序博客网 时间:2024/03/29 05:44


SQLServer的一次堵塞分析(2010-08-27)
今天工作人员突然报告说某个界面无法正常打开了,第一个想到的便是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 TOP 1 @var1=field1 FROM tablename WHERE field2=@var2 AND field1 IS NOT NULL AND primarykey<>@primarykey
该表将近10万条记录,而执行该查询,等待了1分钟却看不到执行结果。理论上是不应该的,先标记下来吧,继续往下跟踪。
很快又发现一条带数据库链接的查询
SELECT TOP 1 primarykey FROM DBLINK.DBNAME.USERNAME.tablename WHERE COND1
先试着运行一下吧,该SQL也是半天没有响应。
问题应该出现在这两个地方,需要再了解一下相应的业务逻辑再进行SQL优化,当务之急是先把该session杀掉
运行kill sessionid后,却还是无法打开程序界面,继续运行查询堵塞脚本,发现blocking_command变成了KILLED/ROLLBACK,也就是说一直处于rollback状态,没有杀成功,很奇怪。而且整个数据库似乎已经全部瘫痪了,所有应用程序均无法执行。
于是系统工程师就把数据库重启了一下,又重新打开该程序界面进行数据处理,结果很快又出现之前的症状。
后来想是不是DBLINK出现了问题,继续运行基于该DBLINK的查询试一下,发现基本上全部无法执行;还是先检查一下网络吧
系统工程师登陆到服务器上查看windows的日志,果然发现了很多网络故障,紧急处理一下网络。
再次运行查询堵塞脚本,发现堵塞已经自动消除,而那条看似很慢的SQL也很快运行出结果了。

至此堵塞问题已解决。
鉴于SQLServer的锁的隔离机制被设置为READ_COMMITTED_SNAPSHOT,读和写会导致冲突,问题的根源也就不难理解了,但造成问题的最终原因却可能是多方面的。

原创粉丝点击