Jak sledovat blokování SQL Server 2005 a SQL Server 2000

Překlady článku Překlady článku
ID článku: 271509 - Produkty, které se vztahují k tomuto článku.
Rozbalit všechny záložky | Minimalizovat všechny záložky

Souhrn

Pokud používáte Microsoft SQL Server 2000, je třeba použít SQL Server 2000 Services Pack 3a (SP3a) použít skript, který je popsán v tomto článku.

Další informace o získání aktualizace SQL Server 2000 Service Pack 3a klepnutím na následující číslo článku databáze Microsoft Knowledge Base:
290211Jak získat nejnovější aktualizaci service pack pro SQL Server 2000
Tento článek je aktualizace SQL Server 2000 nebo novější verze serveru SQL následující článek znalostní báze Microsoft Knowledge Base, který se vztahuje na Microsoft SQL Server 7.0:
251004 Sledování serveru SQL Server 7.0 blokování
Tento článek dokumentuje použití a návrh uložené procedury lze diagnostikovat blokování a výkonem. Popis jak porozumět a blokování vyřešit naleznete v následujících článek znalostní báze Microsoft Knowledge Base:
224453 Vysvětlení a řešení SQL Server 7.0 nebo 2000 blokující problémy
Microsoft SQL Server 2005, můžete použít také Blokované zprávy procesu Třída události v serveru SQL Profiler pro sběr informací o úkolu, který byl zablokován pro více než specifikovaný čas.

Další informace o Blokované zprávy procesu Událost třídy, navštivte následující Web společnosti Microsoft Developer Network (MSDN):
http://msdn2.microsoft.com/en-us/library/ms191168.aspx

Další informace

Následující popis sp_blocker_pss08 Tyto informace zachytí uložené procedury. Tyto informace platí také pro SQL Server 2005.
  • Počáteční čas (podle počítače, který je spuštěn SQL Server) tak, aby toto blokování vzorků může být čas zarovnán s dalších informací o výkonu, například výkonu systému Windows NT Microsoft Sledování protokolu nebo protokolu SQL Profiler.
  • Informace o připojení k serveru SQL Server pomocí dotazu Složka sysprocesses systémové tabulky.
  • Informace o uzamčení prostředků pomocí dotazu syslockinfo systémové tabulky.
  • Informace o prostředcích čeká spuštěním DBCC SQLPERF(WAITSTATS).
  • Aktuální spuštění dávkového serveru SQL pro připojení ostatní jsou blokovány nebo jiné blokování spuštěním DBCC INPUTBUFFER prohlášení.
  • Koncový čas podle počítače se systémem SQL Server.
Vytvoření uložené procedury s následujícími Optimalizace snížit vliv na výkon a spuštěním tohoto blokování uložená procedura:
  • Neexistuje žádný výstup generovaný pokud alespoň jedno připojení čeká na prostředek.
  • Na sysprocesses and syslockinfo z tabulky systému Předloha databáze jsou dotazovány přímo, zvýšení výkonu a Tento uložený postup zabránit stávají blokovány. Proto tato uložená postup je Microsoft SQL Server 2000 nebo novější verze serveru SQL Server.
  • Vytvoření malé worktable kurzoru získat Výstup DBCC INPUTBUFFER; To by nebude mít znatelný vliv na používání v databáze tempdb database.
  • Protože při shromažďování blokování lze změnit Existuje rychlý režim informace, což zvyšuje výkon snížením výsledky příslušných řádků sysprocesses and syslockinfo systémové tabulky.
  • Pokud se pokoušíte sledovat-uzamčení prostředku čeká západka režimu existuje, což způsobuje, že výstup vynechává zámku.
Tento uložený postup je užitečné spuštěním z libovolný dotaz nástroj. Microsoft však naznačuje, postupujte takto provést blokování analýzy:
  1. Vytvoření uložené procedury sp_blocker_pss08, který je dodáván na konci tohoto článku z libovolný dotaz Nástroj během připojení přihlášení s sysadmin oprávnění na serveru SQL Server nebo instanci serveru SQL Server Chcete sledovat.
  2. Vytvoření souboru skriptu následující dotaz spustit uložené procedury ve smyčce. Všimněte si, že by měla být zpoždění mezi 5 a 60
    sekund:

    PŘI 1 = 1
    ZAČÍT
    EXEC tempdb.dbo.sp_blocker_pss08
    --Nebo pro rychlý režim
    --EXEC tempdb.dbo.sp_blocker_pss08 @ fast = 1
    --Nebo západka režimu
    --Tempdb.dbo.sp_blocker_pss08 @ západka EXEC = 1
    ZPOŽDĚNÍ WAITFOR "00: 00:15"
    KONEC
    PŘEJÍT
  3. Tento výstup je velmi užitečné v kombinaci s Microsoft Sledování výkonu systému Windows NT protokolu a protokolu SQL Profiler, tak i při vytváření současně navrhne. Informace o které Profiler a Sledování výkonu událostí k zachycení a informace o Interpretace výsledků, naleznete v následujícím článku znalostí Microsoft Base:
    224453Vysvětlení a řešení SQL Server 7.0 nebo 2000 blokující problémy
  4. Spusťte soubor skriptu vytvořený v kroku 2, Isql.exe, Osql.exe dotazu nástroj nebo nástroj Sqlcmd v příkazovém řádku systému Windows v počítači se serverem SQL Server, u kterého chcete sledovat zabránit odpojení nástroje potíží se sítí. Následuje příkazového řádku například slouží ke spuštění Osql.exe, který předpokládá spuštění klienta z počítače se serverem SQL Server, a že Checkblk.sql je název souboru skriptu. Zkontrolujte, zda parametr -S opravit a nahraďte "server" název serveru SQL Server (nebo "servername\instance") Pokud sledujete pojmenované instance. Opravit také -i parametr a nahradit "checkblk.sql" cesta a název souboru skriptu vytvořeného v kroku 2.
    osql -E -Sserver -icheckblk.sql -ocheckblk.out -w2000
    Musíte použít další přepínače příkazového řádku pro z následujících důvodů:

    • Zabránit zalomení řádku ve výstupu souborů, které usnadňuje čtení.
    • K odeslání výstupu do souboru s -o Parametr namísto na obrazovce tak, že pokud nástroj query má problémy, můžete mít výstup stále dokud nástroj dotaz nezdaří.
Následuje vytvoření skriptu sp_blocker_pss08 uložená procedura:
/*
Note: This script is meant to have 3 creations of the same stored procedure and two of them will fail
with either 207 errors or a 2714 error.
*/
use master
GO
if exists (select * from sysobjects where id = object_id('dbo.sp_blocker_pss08') and sysstat & 0xf = 4)
   drop procedure dbo.sp_blocker_pss08
GO
create procedure dbo.sp_blocker_pss08 (@latch int = 1, @fast int = 1, @appname sysname='PSSDIAG')
as 
--version 19.2005 - 2005 or Later
if is_member('sysadmin')=0 
begin
  print 'Must be a member of the sysadmin group in order to run this procedure'
  return
end

set nocount on
SET LANGUAGE 'us_english'
declare @spid varchar(6)
declare @blocked varchar(6)
declare @time datetime
declare @time2 datetime
declare @dbname nvarchar(128)
declare @status sql_variant
declare @useraccess sql_variant
declare @request varchar(12)

set @time = getdate()
declare @probclients table(spid smallint, request_id int, ecid smallint, blocked smallint, waittype binary(2), dbid smallint,
   ignore_app tinyint, primary key (blocked, spid, request_id, ecid))
insert @probclients select spid, request_id, ecid, blocked, waittype, dbid,
   case when convert(varchar(128),hostname) = @appname then 1 else 0 end
   from master.dbo.sysprocesses where blocked!=0 or waittype != 0x0000

if exists (select spid from @probclients where ignore_app != 1)
begin
   set @time2 = getdate()
   print ''
   print '9.0 Start time: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,@time2)) + ' 19.2005 '+ltrim(str(@latch))+' '+ltrim(str(@fast)) 

   insert @probclients select distinct blocked, 0, 0, 0, 0x0000, 0, 0 from @probclients
      where blocked not in (select spid from @probclients) and blocked != 0

   if (@fast = 1)
   begin
      print ''
      print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)

      select spid, status, blocked, open_tran, waitresource, waittype, 
         waittime, cmd, lastwaittype, cpu, physical_io,
         memusage, last_batch=convert(varchar(26), last_batch,121),
         login_time=convert(varchar(26), login_time,121),net_address,
         net_library, dbid, ecid, kpid, hostname, hostprocess,
         loginame, program_name, nt_domain, nt_username, uid, sid,
         sql_handle, stmt_start, stmt_end, request_id
      from master.dbo.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)

      print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate())) 

      print ''
      print 'SYSPROC FIRST PASS'
      select spid, request_id, ecid, waittype from @probclients where waittype != 0x0000

      if exists(select blocked from @probclients where blocked != 0)
      begin
         print 'Blocking via locks at ' + convert(varchar(26), @time, 121)
         print ''
         print 'SPIDs at the head of blocking chains'
         select distinct spid from @probclients -- change: added distinct
            where blocked = 0 and spid in (select blocked from @probclients where spid != 0)
         if @latch = 0 and exists (select spid from @probclients where waittype between 0x0001 and 0x0017) -- Change: exists
         begin
            print 'SYSLOCKINFO'
            select @time2 = getdate()

            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
               --and ((p.waittype between 0x0001 and 0x0017) or ()) --change: added line

            print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate())) 
         end -- latch not set
      end
      else
         print 'No blocking via locks at ' + convert(varchar(26), @time, 121)
      print ''
   end  -- fast set

   else  
   begin  -- Fast not set
      print ''
      print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)

      select spid, status, blocked, open_tran, waitresource, waittype, 
         waittime, cmd, lastwaittype, cpu, physical_io,
         memusage, last_batch=convert(varchar(26), last_batch,121),
         login_time=convert(varchar(26), login_time,121),net_address,
         net_library, dbid, ecid, kpid, hostname, hostprocess,
         loginame, program_name, nt_domain, nt_username, uid, sid,
         sql_handle, stmt_start, stmt_end, request_id
      from master.dbo.sysprocesses

      print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate())) 

      print ''
      print 'SYSPROC FIRST PASS'
      select spid, request_id, ecid, waittype from @probclients where waittype != 0x0000

      if exists(select blocked from @probclients where blocked != 0)
      begin
         print 'Blocking via locks at ' + convert(varchar(26), @time, 121)
         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)
         if @latch = 0
         begin
            print 'SYSLOCKINFO'
            select @time2 = getdate()

            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

            print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate())) 
         end -- latch not set
      end
      else
        print 'No blocking via locks at ' + convert(varchar(26), @time, 121)
      print ''
   end -- Fast not set

   print 'sys.dm_os_wait_stats'
   select * from sys.dm_os_wait_stats where waiting_tasks_count > 0
   print 'OWS'

   Print ''
   Print '*********************************************************************'
   Print 'Print out DBCC Input buffer for all blocked or blocking spids.'
   Print '*********************************************************************'

   declare ibuffer cursor fast_forward for
   select distinct cast (spid as varchar(6)) as spid, cast (request_id as varchar(12)) as request_id
   from @probclients
   where (spid <> @@spid) and (spid > 50) and
      ((blocked!=0 or (waittype != 0x0000 and ignore_app = 0))
      or spid in (select blocked from @probclients where blocked != 0))
   open ibuffer
   fetch next from ibuffer into @spid, @request
   while (@@fetch_status != -1)
   begin
      print ''
      print 'DBCC INPUTBUFFER FOR SPID ' + @spid +'('+@request+')'
      exec ('dbcc inputbuffer (' + @spid + ',' + @request +')')

      fetch next from ibuffer into @spid, @request
   end
   deallocate ibuffer

   Print ''
   Print '*******************************************************************************'
   Print 'Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.'
   Print '*******************************************************************************'
   declare ibuffer cursor fast_forward for
   select distinct cast (dbid as varchar(6)) from @probclients
   where dbid != 0
   open ibuffer
   fetch next from ibuffer into @spid
   while (@@fetch_status != -1)
   begin
      print ''
      set @dbname = db_name(@spid)
      set @status = DATABASEPROPERTYEX(@dbname,'Status')
      set @useraccess = DATABASEPROPERTYEX(@dbname,'UserAccess')
      print 'DBCC OPENTRAN FOR DBID ' + @spid + ' ['+ @dbname + ']'
      if @status = N'ONLINE' and @useraccess != N'SINGLE_USER'
         dbcc opentran(@dbname)
      else
         print 'Skipped: Status=' + convert(nvarchar(128),@status)
            + ' UserAccess=' + convert(nvarchar(128),@useraccess)

      print ''
      if @spid = '2' select @blocked = 'Y'
      fetch next from ibuffer into @spid
   end
   deallocate ibuffer
   if @blocked != 'Y' 
   begin
      print ''
      print 'DBCC OPENTRAN FOR DBID  2 [tempdb]'
      dbcc opentran ('tempdb')
   end

   print 'End time: ' + convert(varchar(26), getdate(), 121)
end -- All
else
  print '8 No Waittypes: ' + convert(varchar(26), @time, 121) + ' ' 
     + convert(varchar(12), datediff(ms,@time,getdate())) + ' ' + ISNULL (@@servername,'(null)') + ' 19.2005'
GO    

create procedure dbo.sp_blocker_pss08 (@latch int = 1, @fast int = 1, @appname sysname='PSSDIAG')
as 
--version 19.20003 --2000 SP3 or Later
if is_member('sysadmin')=0 
begin
  print 'Must be a member of the sysadmin group in order to run this procedure'
  return
end

set nocount on
SET LANGUAGE 'us_english'
declare @spid varchar(6)
declare @blocked varchar(6)
declare @time datetime
declare @time2 datetime
declare @dbname nvarchar(128)
declare @status sql_variant
declare @useraccess sql_variant

set @time = getdate()
declare @probclients table(spid smallint, ecid smallint, blocked smallint, waittype binary(2), dbid smallint,
   ignore_app tinyint, primary key (blocked, spid, ecid))
insert @probclients select spid, ecid, blocked, waittype, dbid,
   case when convert(varchar(128),hostname) = @appname then 1 else 0 end
   from master.dbo.sysprocesses where blocked!=0 or waittype != 0x0000

if exists (select spid from @probclients where ignore_app != 1 or waittype != 0x020B)
begin
   set @time2 = getdate()
   print ''
   print '8.2 Start time: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,@time2)) + ' 19.20003 ' +ltrim(str(@latch))+' '+ltrim(str(@fast)) 

   insert @probclients select distinct blocked, 0, 0, 0x0000, 0, 0 from @probclients
      where blocked not in (select spid from @probclients) and blocked != 0

   if (@fast = 1)
   begin
      print ''
      print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)

      select spid, status, blocked, open_tran, waitresource, waittype, 
         waittime, cmd, lastwaittype, cpu, physical_io,
         memusage, last_batch=convert(varchar(26), last_batch,121),
         login_time=convert(varchar(26), login_time,121),net_address,
         net_library, dbid, ecid, kpid, hostname, hostprocess,
         loginame, program_name, nt_domain, nt_username, uid, sid,
         sql_handle, stmt_start, stmt_end
      from master.dbo.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)

      print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate())) 

      print ''
      print 'SYSPROC FIRST PASS'
      select spid, ecid, waittype from @probclients where waittype != 0x0000

      if exists(select blocked from @probclients where blocked != 0)
      begin
         print 'Blocking via locks at ' + convert(varchar(26), @time, 121)
         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)
         if @latch = 0
         begin
            print 'SYSLOCKINFO'
            select @time2 = getdate()

            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

            print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate())) 
         end -- latch not set
      end
      else
         print 'No blocking via locks at ' + convert(varchar(26), @time, 121)
      print ''
   end  -- fast set

   else  
   begin  -- Fast not set
      print ''
      print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)

      select spid, status, blocked, open_tran, waitresource, waittype, 
         waittime, cmd, lastwaittype, cpu, physical_io,
         memusage, last_batch=convert(varchar(26), last_batch,121),
         login_time=convert(varchar(26), login_time,121),net_address,
         net_library, dbid, ecid, kpid, hostname, hostprocess,
         loginame, program_name, nt_domain, nt_username, uid, sid,
         sql_handle, stmt_start, stmt_end
      from master.dbo.sysprocesses

      print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate())) 

      print ''
      print 'SYSPROC FIRST PASS'
      select spid, ecid, waittype from @probclients where waittype != 0x0000

      if exists(select blocked from @probclients where blocked != 0)
      begin
         print 'Blocking via locks at ' + convert(varchar(26), @time, 121)
         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)
         if @latch = 0
         begin
            print 'SYSLOCKINFO'
            select @time2 = getdate()

            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

            print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate())) 
         end -- latch not set
      end
      else
        print 'No blocking via locks at ' + convert(varchar(26), @time, 121)
      print ''
   end -- Fast not set

   print 'DBCC SQLPERF(WAITSTATS)'
   dbcc sqlperf(waitstats)

   Print ''
   Print '*********************************************************************'
   Print 'Print out DBCC Input buffer for all blocked or blocking spids.'
   Print '*********************************************************************'

   declare ibuffer cursor fast_forward for
   select distinct cast (spid as varchar(6)) as spid
   from @probclients
   where (spid <> @@spid) and 
      ((blocked!=0 or (waittype != 0x0000 and ignore_app = 0))
      or spid in (select blocked from @probclients where blocked != 0))
   open ibuffer
   fetch next from ibuffer into @spid
   while (@@fetch_status != -1)
   begin
      print ''
      print 'DBCC INPUTBUFFER FOR SPID ' + @spid
      exec ('dbcc inputbuffer (' + @spid + ')')

      fetch next from ibuffer into @spid
   end
   deallocate ibuffer

   Print ''
   Print '*******************************************************************************'
   Print 'Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.'
   Print '*******************************************************************************'
   declare ibuffer cursor fast_forward for
   select distinct cast (dbid as varchar(6)) from @probclients
   where dbid != 0
   open ibuffer
   fetch next from ibuffer into @spid
   while (@@fetch_status != -1)
   begin
      print ''
      set @dbname = db_name(@spid)
      set @status = DATABASEPROPERTYEX(@dbname,'Status')
      set @useraccess = DATABASEPROPERTYEX(@dbname,'UserAccess')
      print 'DBCC OPENTRAN FOR DBID ' + @spid + ' ['+ @dbname + ']'
      if @status = N'ONLINE' and @useraccess != N'SINGLE_USER'
         dbcc opentran(@dbname)
      else
         print 'Skipped: Status=' + convert(nvarchar(128),@status)
            + ' UserAccess=' + convert(nvarchar(128),@useraccess)

      print ''
      if @spid = '2' select @blocked = 'Y'
      fetch next from ibuffer into @spid
   end
   deallocate ibuffer
   if @blocked != 'Y' 
   begin
      print ''
      print 'DBCC OPENTRAN FOR DBID  2 [tempdb]'
      dbcc opentran ('tempdb')
   end

   print 'End time: ' + convert(varchar(26), getdate(), 121)
end -- All
else
  print '8 No Waittypes: ' + convert(varchar(26), @time, 121) + ' ' 
     + convert(varchar(12), datediff(ms,@time,getdate())) + ' ' + ISNULL (@@servername,'(null)') + ' 19.20003'
GO    

create procedure dbo.sp_blocker_pss08 (@latch int = 1, @fast int = 1, @appname sysname='PSSDIAG')
as 
--version 19.2000 -- 2000 before SP3
if is_member('sysadmin')=0
begin
  print 'Must be a member of the sysadmin group in order to run this procedure'
  return
end

set nocount on
declare @spid varchar(6)
declare @blocked varchar(6)
declare @time datetime
declare @time2 datetime
declare @dbname nvarchar(128)
declare @status sql_variant
declare @useraccess sql_variant

set @time = getdate()
declare @probclients table(spid smallint, ecid smallint, blocked smallint, waittype binary(2), dbid smallint,
   ignore_app tinyint, primary key (blocked, spid, ecid))
insert @probclients select spid, ecid, blocked, waittype, dbid,
   case when convert(varchar(128),hostname) = @appname then 1 else 0 end
   from master.dbo.sysprocesses where blocked!=0 or waittype != 0x0000

if exists (select spid from @probclients where ignore_app != 1 or waittype != 0x020B)
begin
   set @time2 = getdate()
   print ''
   print '8 Start time: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,@time2)) + ' 19.2000 '+ltrim(str(@latch))+' '+ltrim(str(@fast)) 

   insert @probclients select distinct blocked, 0, 0, 0x0000, 0, 0 from @probclients
      where blocked not in (select spid from @probclients) and blocked != 0

   if (@fast = 1)
   begin
      print ''
      print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)

      select spid, status, blocked, open_tran, waitresource, waittype, 
         waittime, cmd, lastwaittype, cpu, physical_io,
         memusage,last_batch=convert(varchar(26), last_batch,121),
         login_time=convert(varchar(26), login_time,121), net_address,
         net_library, dbid, ecid, kpid, hostname, hostprocess,
         loginame, program_name, nt_domain, nt_username, uid, sid
      from master.dbo.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 waittype != 0x0000)

      print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate())) 

      print ''
      print 'SYSPROC FIRST PASS'
      select spid, ecid, waittype from @probclients where waittype != 0x0000

      if exists(select blocked from @probclients where blocked != 0)
      begin
         print 'Blocking via locks at ' + convert(varchar(26), @time, 121)
         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)
         if @latch = 0
         begin
            print 'SYSLOCKINFO'
            select @time2 = getdate()

            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

            print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate())) 
         end -- latch not set
      end
      else
         print 'No blocking via locks at ' + convert(varchar(26), @time, 121)
      print ''
   end  -- fast set

   else  
   begin  -- Fast not set
      print ''
      print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)

      select spid, status, blocked, open_tran, waitresource, waittype, 
         waittime, cmd, lastwaittype, cpu, physical_io,
         memusage,last_batch=convert(varchar(26), last_batch,121),
         login_time=convert(varchar(26), login_time,121), net_address,
         net_library, dbid, ecid, kpid, hostname, hostprocess,
         loginame, program_name, nt_domain, nt_username, uid, sid
      from master.dbo.sysprocesses

      print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate())) 

      print ''
      print 'SYSPROC FIRST PASS'
      select spid, ecid, waittype from @probclients where waittype != 0x0000

      if exists(select blocked from @probclients where blocked != 0)
      begin
         print 'Blocking via locks at ' + convert(varchar(26), @time, 121)
         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)
         if @latch = 0
         begin
            print 'SYSLOCKINFO'
            select @time2 = getdate()

            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

            print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate())) 
         end -- latch not set
      end
      else
        print 'No blocking via locks at ' + convert(varchar(26), @time, 121)
      print ''
   end -- Fast not set

   print 'DBCC SQLPERF(WAITSTATS)'
   dbcc sqlperf(waitstats)

   Print ''
   Print '*********************************************************************'
   Print 'Print out DBCC Input buffer for all blocked or blocking spids.'
   Print '*********************************************************************'

   declare ibuffer cursor fast_forward for
   select distinct cast (spid as varchar(6)) as spid
   from @probclients
   where (spid <> @@spid) and 
      ((blocked!=0 or (waittype != 0x0000 and ignore_app = 0))
      or spid in (select blocked from @probclients where blocked != 0))
   open ibuffer
   fetch next from ibuffer into @spid
   while (@@fetch_status != -1)
   begin
      print ''
      print 'DBCC INPUTBUFFER FOR SPID ' + @spid
      exec ('dbcc inputbuffer (' + @spid + ')')

      fetch next from ibuffer into @spid
   end
   deallocate ibuffer

   Print ''
   Print '*******************************************************************************'
   Print 'Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.'
   Print '*******************************************************************************'
   declare ibuffer cursor fast_forward for
   select distinct cast (dbid as varchar(6)) from @probclients
   where dbid != 0
   open ibuffer
   fetch next from ibuffer into @spid
   while (@@fetch_status != -1)
   begin
      print ''
      set @dbname = db_name(@spid)
      set @status = DATABASEPROPERTYEX(@dbname,'Status')
      set @useraccess = DATABASEPROPERTYEX(@dbname,'UserAccess')
      print 'DBCC OPENTRAN FOR DBID ' + @spid + ' ['+ @dbname + ']'
      if @status = N'ONLINE' and @useraccess != N'SINGLE_USER'
         dbcc opentran(@dbname)
      else
         print 'Skipped: Status=' + convert(nvarchar(128),@status)
            + ' UserAccess=' + convert(nvarchar(128),@useraccess)

      print ''
      if @spid = '2' select @blocked = 'Y'
      fetch next from ibuffer into @spid
   end
   deallocate ibuffer
   if @blocked != 'Y' 
   begin
      print ''
      print 'DBCC OPENTRAN FOR DBID  2 [tempdb]'
      dbcc opentran ('tempdb')
   end

   print 'End time: ' + convert(varchar(26), getdate(), 121)
end -- All
else
  print '8 No Waittypes: ' + convert(varchar(26), @time, 121) + ' '
     + convert(varchar(12), datediff(ms,@time,getdate())) + ' ' + ISNULL (@@servername,'(null)') + ' 19.2000'
GO 
Další informace naleznete na následujícím webu:
http://blogs.msdn.com/psssql/archive/2007/02/21/SQL-Server-2005-Performance-statistics-Script.aspx

Vlastnosti

ID článku: 271509 - Poslední aktualizace: 29. července 2011 - Revize: 18.0
Informace v tomto článku jsou určeny pro produkt:
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Workgroup Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Enterprise Edition
Klíčová slova: 
kbsqlsetup kbhowto kbinfo kbmt KB271509 KbMtcs
Strojově přeložený článek
Důležité: Tento článek byl přeložen pomocí software společnosti Microsoft na strojový překlad, ne profesionálním překladatelem. Společnost Microsoft nabízí jak články přeložené překladatelem, tak články přeložené pomocí software na strojový překlad, takže všechny články ve Znalostní databázi (Knowledge Base) jsou dostupné v češtině. Překlad pomocí software na strojový překlad ale není bohužel vždy dokonalý. Obsahuje chyby ve skloňování slov, skladbě vět, nebo gramatice, podobně jako když cizinci dělají chyby při mluvení v češtině. Společnost Microsoft není právně zodpovědná za nepřesnosti, chyby nebo škody vzniklé chybami v překladu, nebo při použití nepřesně přeložených instrukcí v článku zákazníkem. Společnost Microsoft aktualizuje software na strojový překlad, aby byl počet chyb omezen na minimum.
Projděte si také anglickou verzi článku:271509

Dejte nám zpětnou vazbu

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com