BlockingDetectionMonitor

CREATE proc [dbo].[BlockingDetectionMonitor]
 @alarm_cnt_blocked int =5,
 @alarm_max_waittime int=2000,
 @pager_max_waittime int=12000, -- add by lansz @ 2008.04.17
 @display_max_locknum int=50
as
 set nocount on
 declare @spid varchar(6), @uid varchar(6), @blocked varchar(6)
 declare @tmpchar varchar(255)
 declare @time datetime
 --blocked info
 declare @cnt_blocked smallint
 declare @status varchar (300)
 declare @hostname   varchar (300)
 declare @hostprocess   varchar (8) a
 declare @loginame   varchar (300)
 declare @program_name   varchar (300)
 --declare @nt_domain   varchar (300)
 --declare @nt_username   varchar (300)
 declare @open_tran   smallint
 --declare @waitresource   varchar (256)
 --declare @waittype   binary (2)
 declare @waittime   int
 declare @max_waittime   int
 declare @cmd   varchar (16)
 --declare @lastwaittype   varchar (32)
 --declare @cpu   int
 --declare @physical_io   bigint
 --declare @login_time   varchar (100)
 --declare @runtime   varchar (100)

 declare @dbname varchar(50)
 declare @blockedobjectid varchar(100)
 declare @blockedobject  varchar(50)
 declare @type  varchar(50)
 declare @Resource  varchar(50)
 declare @Mode varchar(50)

 declare @EventType  varchar(500)
 declare @Parameters  varchar(500)
 declare @EventInfo  varchar(5000)

 declare @article_id int
 declare @agentname varchar(500) 
 declare @mailcontent varchar(8000)
 declare @commandcontent varchar(8000)
 declare @lockedcontent varchar(8000)
 declare @sendmail varchar(8000)
 declare @send_address varchar(50)
 declare @target_address varchar(5000)
 declare @command varchar(8000)
 declare @commandnum int
 declare @locknum int

 set @send_address='tech@joyo.com'
 set @target_address='joyo-db-monitoring@amazon.com;fusn-ordering-oncall@amazon.com;fusn-cust-oncall@amzon.com'
 set @time = getdate()
 set @mailcontent=' '
 set @commandcontent=' '
 set @lockedcontent= ' '
 set @locknum=0
 --被堵塞进程个数
 select @cnt_blocked=count(*),@max_waittime = max(waittime)
 from master.dbo.sysprocesses  with(nolock)
 where blocked<>0

 --被堵塞进程最大等待时间
 --select @max_waittime = max(waittime)
 --from master.dbo.sysprocesses
 --where blocked<>0 

 --被堵塞进程个数大于报警个数，被堵塞进程最大等待时间大于报警等待时间
 if @cnt_blocked >= @alarm_cnt_blocked or @max_waittime >= @alarm_max_waittime
 begin
  declare @probclients table(blocker_spid smallint)
  --查找堵塞源
  insert @probclients(blocker_spid)
  select spid
  from master.dbo.sysprocesses with(nolock)
  WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses with(nolock)) AND blocked=0

  if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[blocked_spid_info]'))
  begin
   drop table blocked_spid_info
  end
  select spid,status,hostname,hostprocess,
   loginame,program_name,open_tran,waittime,cmd into blocked_spid_info
  from master..sysprocesses with(nolock),@probclients
  where spid =blocker_spid

  If exists(select * from blocked_spid_info with(nolock))
  begin
     --显示被堵塞进程个数和被堵塞进程最大等待时间
--   set @mailcontent=@mailcontent+'堵塞发生在(Blocking occured at )'+convert(varchar(30),@time,120)+'

---

'
--     +'

被堵塞的进程数 / 报警值

最大等待时间/报警值

Blocking occured at '+convert(varchar(30),@time,120)+'

---

'
     +'

blocking process no/threshold	max waittime/threshold
'+convert(varchar(100),@cnt_blocked)+'/'+ convert(varchar(100),@alarm_cnt_blocked)+'	'+convert(varchar(100),@max_waittime)+'/'+ convert(varchar(100),@alarm_max_waittime) +'

'

   --发送邮件显示堵塞进程(blocker)的系统信息
--     set @mailcontent=@mailcontent+'堵塞者(Blocker)

---

'
     set @mailcontent=@mailcontent+' Blocker

---

'
           +'

spid

status

hostname

hostprocess

loginame

program_name

open_tran

           +'

waittime'+
           +'cmd'
           +''

      select * from blocked_spid_info
      declare blocked_cursor cursor for
      select spid,status, hostname,hostprocess,
          loginame,program_name,open_tran,waittime,cmd
      from blocked_spid_info with(nolock)
      open blocked_cursor
      fetch next from blocked_cursor into @spid,@status,@hostname,@hostprocess,
     @loginame,@program_name,@open_tran,@waittime,@cmd
      while @@fetch_status = 0
    begin
     set @mailcontent=@mailcontent+'

'
              +''+@spid+''+
              +''+@status+''+
              +''+@hostname+''+
              +''+@hostprocess+''+
              +''+@loginame+''+
              +''+@program_name+''+
              +''+convert(varchar(100),@open_tran)+''+
              +''+convert(varchar(100),@waittime)+''+
              +''+@cmd+''

     fetch next from blocked_cursor into @spid,@status,@hostname,@hostprocess,
             @loginame,@program_name,@open_tran,@waittime,@cmd
       end
      set @mailcontent=@mailcontent+'

'
      close blocked_cursor
      deallocate blocked_cursor
 
      Print '*********************************************************************'
     Print 'Print out DBCC Input buffer for blocked spids.'
      Print '*********************************************************************'
    
--      set @commandcontent=@commandcontent+' 堵塞进程执行的命令

---

'
      set @commandcontent=@commandcontent+' blocker sql command

---

'
           +'

spid

EventType

Parameters

EventInfo

堵塞进程锁的对象

---

'
   set @lockedcontent=@lockedcontent+' blocker lock object

---

'
           +'

spid	dbname	locked object	type	Resource	Mode
'+@spid+'	'+@EventType+'	'+@Parameters+'	'+REPLACE(REPLACE(@EventInfo,char(10),' '),char(13),' ')+'

    --only alter locknum<50 这样减少大批量更新是监控给系统带来的压力
    print @locknum
    if @locknum    begin   
     --发送邮件显示堵塞进程(blocker)堵塞的对象是什莫
     if exists (select * from master.dbo.sysobjects where id = object_id(N'[dbo].[blockedobject]'))
     begin
      drop table master.dbo.blockedobject
     end
           CREATE TABLE  master.dbo.blockedobject(
        spid varchar(6),
          dbname varchar(50),
        blockedobjectid varchar(100),
        blockedobject  varchar(50),
        type  varchar(50),
        Resource  varchar(50),
        Mode varchar(50)
          )
     insert into master.dbo.blockedobject(spid,dbname,blockedobjectid,type,Resource,Mode)
     select  convert (smallint, req_spid) As spid,
       d.name as dbname,
       rsc_objid As blockedobjectid,
       substring (v.name, 1, 4) As Type,
       substring (rsc_text, 1, 16) as Resource,
       substring (u.name, 1, 8) As Mode
     from  master.dbo.syslockinfo,
       master.dbo.spt_values v,
       master.dbo.spt_values x,
       master.dbo.spt_values u,
       master.dbo.sysdatabases d
     where   master.dbo.syslockinfo.rsc_type = v.number
             and v.type = 'LR'
             and master.dbo.syslockinfo.req_status = x.number
             and x.type = 'LS'
             and master.dbo.syslockinfo.req_mode + 1 = u.number
             and u.type = 'L'
             and req_spid in (@spid)
              and d.dbid=rsc_dbid
             and substring (u.name, 1, 8)='X'
 
     declare blockedobject_cursor cursor static for
        select spid,dbname,blockedobjectid,type,Resource,Mode
     from master.dbo.blockedobject
        open blockedobject_cursor
 
        fetch next from blockedobject_cursor into @spid,@dbname,@blockedobjectid,@type,@resource,@mode
       while (@@fetch_status = 0)
        begin         
      exec('update master.dbo.blockedobject '
          +'set blockedobject =(select name '
         +'from '+@dbname+'.dbo.sysobjects '
         +'where id='+@blockedobjectid
         +') '
          +'where spid='+@spid+' and blockedobjectid = '+@blockedobjectid
                 )
      select @blockedobject=blockedobject from master.dbo.blockedobject  where spid=@spid and blockedobjectid =@blockedobjectid
      set @lockedcontent=@lockedcontent+'

'+''+@spid+''
                     +''+@dbname+''
                                            +''+@blockedobject+''
                     +''+@type+''
                       +''+@Resource+''
                       +''+@Mode+''
                       +''
      --print @lockedcontent
      --print @commandcontent
      fetch next from blockedobject_cursor into @spid,@dbname,@blockedobjectid,@type,@resource,@mode
     end
  
     deallocate blockedobject_cursor
    end
    fetch next from ibuffer into @spid
     end
  deallocate ibuffer
  
  if @locknum   set @lockedcontent=@lockedcontent+''
  else
   set @lockedcontent=' Total blocking process lock object num: '+convert(varchar(1000),@locknum)+', Pls check it ASAP!'
--   set @lockedcontent=' 堵塞进程锁的对象共 '+convert(varchar(1000),@locknum)+'个!请尽快查找问题！'
  
  set @commandcontent=@commandcontent+''
  set @mailcontent=@mailcontent+@commandcontent+@lockedcontent+''
  
  --发送邮件显示被堵塞进程的系统信息
--    set @mailcontent=@mailcontent+' 被堵塞进程

---

'
    set @mailcontent=@mailcontent+' Blocking processes

---

'
          +'

spid	blocked	status	hostname	hostprocess	loginame	program_name	open_tran	waittime	cmd
'+@spid+'	'+@blocked+'	'+@status+'	'+@hostname+'	'+@hostprocess+'	'+@loginame+'	'+@program_name+'	'+convert(varchar(100),@open_tran)+'	'+convert(varchar(100),@waittime)+'	'+@cmd+'

    fetch next from blocked_processes into @spid,@blocked,@status,@hostname,@hostprocess,
            @loginame,@program_name,@open_tran,@waittime,@cmd
      end

     set @mailcontent=@mailcontent+'

'
     close blocked_processes
     deallocate blocked_processes 
   
  if @cnt_blocked>=50 or @max_waittime>=@pager_max_waittime
  begin
   set @target_address=@target_address+';joyo-db-alarm@amazon.com'
   --set @mailcontent=@mailcontent+' kill the process '
  end
  --send mail to monitor
  select @sendmail='H:\sky-eyes\bin\mailsend.exe  '+@send_address+' '+@target_address+'  "['+@@SERVERNAME+']Blocking is detected.[Alarm][BlockingDetectionMonitor]"  " '+@mailcontent+'"'
  print @sendmail

  exec master..xp_cmdshell @sendmail
       end
        end
       
GO

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/756652/viewspace-242532/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/756652/viewspace-242532/