SQL Server 监控统计阻塞脚本信息

发表于:2014-6-03 09:49

字体: | 上一篇 | 下一篇 | 我要投稿

 作者:潇湘隐者    来源:51Testing软件测试网采编

分享:
  数据库产生阻塞(Blocking)的本质原因 :SQL语句连续持有锁的时间过长 ,数目过多, 粒度过大。阻塞是事务隔离带来的副作用,它是不可避免的,而且是一个数据库系统常见的现象。 但是阻塞的时间和出现频率要控制在一定的范围内,阻塞持续的时间过长或阻塞出现过多(过于频繁),就会对数据库性能产生严重的影响。
  很多时候,DBA需要知道数据库在出现性能问题时,有没有发生阻塞? 什么时候开始的?发生在那个数据库上? 阻塞发生在那些SQL语句之间? 阻塞的时间有多长? 阻塞发生的频率? 阻塞有关的连接是从那些客户端应用发送来的?.......
  如果我们能够知道这些具体信息,我们就能迅速定位问题,分析阻塞产生的原因,  从而找出出现性能问题的根本原因,并根据具体原因给出相应的解决方案(索引调整、优化SQL语句等)。
  查看阻塞的方法比较多, 我在这篇博客MS SQL 日常维护管理常用脚本(二)里面提到查看阻塞的一些方法:
  方法1:查看那个引起阻塞,查看blk不为0的记录,如果存在阻塞进程,则是该阻塞进程的会话 ID。否则该列为零。
  EXEC sp_who active
  方法2:查看那个引起阻塞,查看字段BlkBy,这个能够得到比sp_who更多的信息。
  EXEC sp_who2 active
  方法3:sp_lock 系统存储过程,报告有关锁的信息,但是不方便定位问题
  方法4:sp_who_lock存储过程
  方法5:右键服务器-选择“活动和监视器”,查看进程选项。注意“任务状态”字段。
  方法6:右键服务名称-选择报表-标准报表-活动-所有正在阻塞的事务。
  但是上面方法,例如像sp_who、 sp_who2,sp_who_lock等,都有或多或少的缺点:例如不能查看阻塞和被阻塞的SQL语句。不能从查看一段时间内阻塞发生的情况等;没有显示阻塞的时间....... 我们要实现下面功能:
  1:  查看那个会话阻塞了那个会话
  2:阻塞会话和被阻塞会话正在执行的SQL语句
  3:被阻塞了多长时间
  4:像客户端IP、Proagram_Name之类信息
  5:阻塞发生的时间点
  6:阻塞发生的频率
  7:如果需要,应该通知相关开发人员,DBA不能啥事情都包揽是吧,那不还得累死,总得让开发人员员参与进来优化(有些问题就该他们解决),多了解一些系统运行的具体情况,有利于他们认识问题、解决问题。
  8:需要的时候开启这项功能,不需要关闭这项功能
  于是为了满足上述功能,有了下面SQL 语句
SELECT wt.blocking_session_id                  AS BlockingSessesionId
,sp.program_name                         AS ProgramName
,COALESCE(sp.LOGINAME, sp.nt_username)   AS HostName
,ec1.client_net_address                  AS ClientIpAddress
,db.name                                 AS DatabaseName
,wt.wait_type                            AS WaitType
,ec1.connect_time                        AS BlockingStartTime
,wt.WAIT_DURATION_MS/1000                AS WaitDuration
,ec1.session_id                          AS BlockedSessionId
,h1.TEXT                                 AS BlockedSQLText
,h2.TEXT                                 AS BlockingSQLText
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db
ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt
ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.dm_exec_connections ec1
ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2
ON ec2.session_id = wt.blocking_session_id
LEFT OUTER JOIN master.dbo.sysprocesses sp
ON SP.spid = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
21/212>
《2023软件测试行业现状调查报告》独家发布~

关注51Testing

联系我们

快捷面板 站点地图 联系我们 广告服务 关于我们 站长统计 发展历程

法律顾问:上海兰迪律师事务所 项棋律师
版权所有 上海博为峰软件技术股份有限公司 Copyright©51testing.com 2003-2024
投诉及意见反馈:webmaster@51testing.com; 业务联系:service@51testing.com 021-64471599-8017

沪ICP备05003035号

沪公网安备 31010102002173号