日度归档:2018年7月5日

mssql sqlserver 阻塞监控方法分享


摘要:
下文讲述sqlserver 阻塞的处理方法,如下所示:
实验环境:sqlserver 2008 R2



sql server阻塞产生的原因:

sql语句对对象锁定时间过长,粒度过大,数目过多,阻塞是一个数据库中常见的问题,我们需合理的控制阻塞出现的频率,并有效的处理阻塞后导致的异常现象。
————————————————————————————
作为DBA,我们需要时常监控数据库是否出现阻塞,每次阻塞的时间,那些sql语句导致的阻塞,阻塞发生的频率,阻塞从那个客户端中发出。。。
下文将讲述获取阻塞的方法:

  ---方法1:
  exec  sp_who active 
   ---运行以上脚本信息,并查找blk列不为0的记录
  ------------------------------------------------
  方法2:
  exec  sp_who2 active 
   ---运行以上脚本,并查找blkby存在值得行记录
  -----根据以上脚本所获取的spId列,然后使用
  dbcc inputbuffer(spid)  ---获取相应的sql脚本信息
 ---------------------------------------------------------
 

方法3:
在SSMS上,查看活动监视器,观察资源等待的情况,如下图所示:
 
以上的阻塞查看方法,我们可以通过实时运行这些系统存储过程,然后进行相关对比,只能获取阻塞所涉及的sql脚本信息,
无法获取阻塞发生多长时间 阻塞发生的时间点,阻塞发生的频率,下文将介绍第四种方法,通过sql脚本的方式,获取阻塞信息,

 
 ---方法4:
    SELECT c.blocking_session_id                  AS [阻塞进程ID]
      ,f.program_name                         AS [阻塞应用程序名称]
      ,COALESCE(f.LOGINAME, f.nt_username)   AS [阻塞数据库主机名称] 
      ,d.client_net_address                  AS [阻塞客户端Ip]
      ,b.name                                 AS [被阻塞数据库名称]        
      ,c.wait_type                            AS [阻塞类型]                    
      ,d.connect_time                        AS [阻塞开始时间]
      ,c.WAIT_DURATION_MS/1000                AS [等待时间]
      ,d.session_id                          AS [当前进程ID]
      ,e.TEXT                                 AS [当前语句]
      ,f.TEXT                                 AS [阻塞sql语句]
FROM sys.dm_tran_locks AS a
INNER JOIN sys.databases b
  ON b.database_id = a.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS c
  ON a.lock_owner_address = c.resource_address
INNER JOIN sys.dm_exec_connections d
  ON d.session_id = a.request_session_id
INNER JOIN sys.dm_exec_connections e
  ON e.session_id = c.blocking_session_id
LEFT OUTER JOIN master.dbo.sysprocesses f
  ON f.spid = c.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(d.most_recent_sql_handle) AS e
CROSS APPLY sys.dm_exec_sql_text(e.most_recent_sql_handle) AS f
 


注意事项:
以上脚本可以获取一段时间内数据库阻塞情况,我们可以通过作业的形式对数据库阻塞的信息,进行快照,方便开发人员对系统的性能做相关分析。