Newsgroup: it.comp.software.database
Titolo: Monitoraggio SQL Server Blocking

Ciao a tutti,
in base al documento microsoft :
Q271509 - INF How to Monitor SQL Server 2000 Blocking.htm
sto cercando di creare la store procedure che mi permetta di identificare
i bloching così da poter effettuare un analisi in caso di necessità.
Il problema è che la sintassi dello script per creare la SP che Micorsoft
pubblica nell'articolo è sbagliata! Mi va in errore nella riga 164 e 173.
Qualcuno ne sa qualcosa e può cortesemente aiutarmi?
grazie.
P.S.: ecco lo script
use master
go
if exists (select * from sysobjects where id =
object_id('dbo.sp_blocker_pss80') and sysstat & 0xf = 4)
   drop procedure dbo.sp_blocker_pss80
GO
create proc sp_blocker_pss80 (@fast int = 0)
as 
set nocount on
declare @spid varchar(6), @uid varchar(6), @blocked varchar(6)
declare @tmpchar varchar(255)
declare @time datetime
select @time = getdate()
declare @probclients table(spid smallint, blocked smallint, waittype
binary(2), primary key (blocked,spid))
insert @probclients select spid, blocked, waittype from sysprocesses where
blocked!=0 or waittype != 0x0000
if exists (select spid from @probclients)
begin
   select @tmpchar='Start time: ' + convert(varchar(26), @time, 113)
   print @tmpchar
   insert @probclients select blocked, 0, 0x0000 from @probclients
      where blocked not in (select spid from @probclients) and blocked != 0
   print ' '
   if (@fast = 1)
   begin
      select spid, status, blocked, open_tran, waitresource, waittype, 
         waittime, cmd, lastwaittype, cpu, physical_io,
         memusage,last_batch=convert(varchar(26), last_batch,113),
         login_time=convert(varchar(26), login_time,113), net_address,
         net_library,dbid, ecid, kpid, hostname,hostprocess,
         loginame,program_name, nt_domain, nt_username, uid, sid
      from master..sysprocesses
      where blocked!=0 or waittype != 0x0000
      or spid in (select blocked from @probclients where blocked != 0)
      or spid in (select spid from @probclients where blocked != 0)
      select 	spid = convert (smallint, req_spid),
         ecid = convert (smallint, req_ecid),
         rsc_dbid As dbid,
         rsc_objid As ObjId,
         rsc_indid As IndId,
         Type = case rsc_type when 1 then 'NUL'
                              when 2 then 'DB'
                              when 3 then 'FIL'
                              when 4 then 'IDX'
                              when 5 then 'TAB'
                              when 6 then 'PAG'
                              when 7 then 'KEY'
                              when 8 then 'EXT'
                              when 9 then 'RID'
                              when 10 then 'APP' end,
         Resource = substring (rsc_text, 1, 16),
         Mode = case req_mode + 1 when 1 then NULL
                                  when 2 then 'Sch-S'
                                  when 3 then 'Sch-M'
                                  when 4 then 'S'
                                  when 5 then 'U'
                                  when 6 then 'X'
                                  when 7 then 'IS'
                                  when 8 then 'IU'
                                  when 9 then 'IX'
                                  when 10 then 'SIU'
                                  when 11 then 'SIX'
                                  when 12 then 'UIX'
                                  when 13 then 'BU'
                                  when 14 then 'RangeS-S'
                                  when 15 then 'RangeS-U'
                                  when 16 then 'RangeIn-Null'
                                  when 17 then 'RangeIn-S'
                                  when 18 then 'RangeIn-U'
                                  when 19 then 'RangeIn-X'
                                  when 20 then 'RangeX-S'
                                  when 21 then 'RangeX-U'
                                  when 22 then 'RangeX-X'end,
         Status = case req_status when 1 then 'GRANT'
                                  when 2 then 'CNVT'
                                  when 3 then 'WAIT' end
         ,req_transactionID As TransID,
          req_transactionUOW As TransUOW
      from master.dbo.syslockinfo s,
         @probclients p
      where p.spid = s.req_spid
   end  -- fast set
   else  
   begin  -- Fast not set
      select spid, status, blocked, open_tran, waitresource, waittype, 
         waittime, cmd, lastwaittype, cpu, physical_io,
         memusage,last_batch=convert(varchar(26), last_batch,113),
         login_time=convert(varchar(26), login_time,113), net_address,
         net_library,dbid, ecid, kpid, hostname,hostprocess,
         loginame,program_name, nt_domain, nt_username, uid, sid
      from master..sysprocesses
      print ''
      print 'SPIDs at the head of blocking chains'
      select spid from @probclients
      where blocked = 0 and spid in (select blocked from @probclients
where spid != 0)
      print ''
      select 	spid = convert (smallint, req_spid),
         ecid = convert (smallint, req_ecid),
         rsc_dbid As dbid,
         rsc_objid As ObjId,
         rsc_indid As IndId,
         Type = case rsc_type when 1 then 'NUL'
                              when 2 then 'DB'
                              when 3 then 'FIL'
                              when 4 then 'IDX'
                              when 5 then 'TAB'
                              when 6 then 'PAG'
                              when 7 then 'KEY'
                              when 8 then 'EXT'
                              when 9 then 'RID'
                              when 10 then 'APP' end,
         Resource = substring (rsc_text, 1, 16),
         Mode = case req_mode + 1 when 1 then NULL
                                  when 2 then 'Sch-S'
                                  when 3 then 'Sch-M'
                                  when 4 then 'S'
                                  when 5 then 'U'
                                  when 6 then 'X'
                                  when 7 then 'IS'
                                  when 8 then 'IU'
                                  when 9 then 'IX'
                                  when 10 then 'SIU'
                                  when 11 then 'SIX'
                                  when 12 then 'UIX'
                                  when 13 then 'BU'
                                  when 14 then 'RangeS-S'
                                  when 15 then 'RangeS-U'
                                  when 16 then 'RangeIn-Null'
                                  when 17 then 'RangeIn-S'
                                  when 18 then 'RangeIn-U'
                                  when 19 then 'RangeIn-X'
                                  when 20 then 'RangeX-S'
                                  when 21 then 'RangeX-U'
                                  when 22 then 'RangeX-X'end,
         Status = case req_status when 1 then 'GRANT'
                                  when 2 then 'CNVT'
                                  when 3 then 'WAIT' end
         ,req_transactionID As TransID,
          req_transactionUOW As TransUOW
      from master.dbo.syslockinfo
   end -- Fast not set
   dbcc traceon(3604)
   Print ''
   Print ''
   Print
'*********************************************************************'
   Print 'Print out DBCC Input buffer for all blocked or blocking spids.'
   Print 'Print out DBCC PSS info only for SPIDs at the head of blocking
chains'
   Print
'*********************************************************************'
   declare ibuffer cursor fast_forward for
   select cast (spid as varchar(6)) as spid, cast (blocked as varchar(6))
as blocked
   from @probclients
   where (spid <> @@spid) and (blocked!=0 or waittype != 0x0000
      or spid in (select blocked from @probclients where blocked != 0))
   open ibuffer
   fetch next from ibuffer into @spid, @blocked
   while (@@fetch_status != -1)
   begin
      print ''
      print ''
      exec ("print 'DBCC INPUTBUFFER FOR SPID " + @spid + "'")
      exec ("dbcc inputbuffer (" + @spid + ")")
      print ''
      if (@blocked = '0')
      -- if DBCC PSS is not required, comment the line above, remove the
      -- comment on the line below and run the stored procedure in fast 
      -- mode
      -- if (@blocked = '0' and @fast = 0)
      begin
         exec ("print 'DBCC PSS FOR SPID " + @spid + "'")
         exec ("dbcc pss (0, " + @spid +")")
         print ''
         print ''
      end
      fetch next from ibuffer into @spid, @blocked
   end
   deallocate ibuffer
   if datediff(millisecond, @time, getdate()) > 1000
   begin
      select @tmpchar='End time: ' + convert(varchar(26), getdate(), 113)
      print @tmpchar
   end
   dbcc traceoff(3604)
end -- All
go   
-- 
questo articolo e` stato inviato via web dal servizio gratuito 
http://www.newsland.it/news
 segnala gli abusi ad 
ab...@newsland.it

 create proc sp_blocker_pss80 (@fast int = 0)
...dove l'hai preso questo codice?!?
Il documento che hai indicato corrisponde al link
http://support.microsoft.com/default.aspx?scid=271509
ed il codice di entrambe le sp in esso riportate inizia con
create procedure sp_blocker_pss80 (@latch int = 0, @fast int = 1)
Bye
-- 
Luca Bianchi
Microsoft MVP - SQL Server
http://mvp.support.microsoft.com

 create procedure sp_blocker_pss80 (@latch int = 0, @fast int = 1)
Ma che strano, il codice è all'interno di un file.htm che mi è stato
dato 2 anni fa dopo un corso tenuto da microsoft che tratta appunto
il tuning di SQL... probabilmente è stato corretto. Comunque grazie
infinite.
