Momentálne ste offline a čaká sa, kým sa znova pripojíte na internet

Ako sledovať, blokovanie v SQL Server 2005 a SQL Server 2000

DÔLEŽITÉ: Tento článok bol preložený pomocou softvéru na strojový preklad od spoločnosti Microsoft, nie prekladateľom. Spoločnosť Microsoft ponúka články preložené prekladateľmi aj strojovo preložené články, vďaka čomu máte možnosť prístupu ku všetkým článkom databázy Knowledge Base vo svojom jazyku. Strojovo preložený článok však nie je vždy perfektný. Môže obsahovať chyby týkajúce sa slovnej zásoby, syntaxe alebo gramatiky, podobne ako cudzinec môže robiť chyby, keď rozpráva vašim jazykom. Spoločnosť Microsoft nenesie zodpovednosť za akékoľvek nepresnosti, chyby alebo škody spôsobené akýmkoľvek nepresným prekladom obsahu alebo jeho použitím zo strany zákazníkov. Spoločnosť Microsoft softvér na strojový preklad pravidelne aktualizuje.

271509
SUHRN
Ak používate Microsoft SQL Server 2000, musíte použiť SQL Server 2000 služby Pack 3a (SP3a), pred použitím skriptu, ktorý je popísaný v tomto článku.

Ďalšie informácie o možnostiach získania SQL Server 2000 Service Pack 3a, po kliknutí na nasledovné číslo článku databázy Microsoft Knowledge Base:
290211Ako získať najnovší balík SQL Server 2000 service pack
Tento článok je aktualizácia pre SQL Server 2000 alebo novšími verziami SQL Server nasledujúci článok databázy Microsoft Knowledge Base, ktorý sa uplatňuje na Microsoft SQL Server 7.0:
251004 Ako sledovať, SQL Server 7.0 blokovanie
Tento článok dokumentov na používanie a konštrukciu uloženej procedúry, ktoré môžete použiť na diagnostikovanie blokovanie a výkonnosti otázok. Popis ako pochopiť a vyriešiť, blokovanie, pozri nasledujúce článok v databáze Microsoft Knowledge Base:
224453 Pochopenie a riešenie SQL Server 7.0 alebo 2000 blokovanie problémy
V Microsoft SQL Server 2005, môžete tiež použiť Blokovaný proces správa Trieda udalostí v SQL Server Profiler zachytiť informácie o úlohu, ktorá bola zablokovaná dlhšie ako zadaný časový interval.

Ďalšie informácie o Blokovaný proces správa udalosť triedy nájdete na nasledujúcej webovej lokalite Microsoft Developer Network (MSDN):
DALSIE INFORMACIE
Nasledujúci popis sp_blocker_pss08 uložená procedúra zachytáva tejto informácie. Tieto informácie sa vzťahuje aj na SQL Server 2005.
  • Počiatočný čas (v závislosti na počítači, ktorý je spustenie služby SQL Server) tak, aby tento odber vzoriek blokovanie môže byť čas-zladené s Ďalšie informácie o výkone, ako je Microsoft Windows NT výkonnosť Monitorovať denníka alebo SQL Profiler denníka.
  • Informácie o pripojení na server SQL Server, zaslanie dotaze The sysprocesses systém tabuľky.
  • Informácie o zámok zdrojov, zaslanie dotaze syslockinfo systém tabuľky.
  • Informácie o prostriedkoch čaká spustením DBCC SQLPERF(WAITSTATS).
  • Aktuálne spustený SQL Server dávky pre pripojenia, ktoré iní sú blokované alebo blokovanie iného spustením DBCC INPUTBUFFER vyhlásenie.
  • Koncový čas podľa na počítač, na ktorom je spustený Server SQL Server.
Uložená procedúra bola vytvorená s týmito Optimalizácia znížiť vplyv na výkon a blokovanie spustením to uložená procedúra:
  • Neexistuje žiadny výstup generuje pokiaľ aspoň jeden spoj Čaká sa na prostriedku.
  • The sysprocesses a syslockinfo systém tabuľky z kapitán databáza sa vykoná dotaz priamo, zvýšiť výkon a Táto uložená procedúra zabrániť stáva blokované. Preto to, ktoré sa ukladajú postup je špecifický pre Microsoft SQL Server 2000 alebo novšími verziami programu SQL Server.
  • Malé worktable je vytvorený kurzor na získanie DBCC INPUTBUFFER výstup; mal by mať podstatný vplyv na používanie v tempdb databáza.
  • Pretože blokovanie môžete zmeniť počas zhromažďovania existuje rýchly režim informácie, ktoré zvyšuje výkon znížením výsledky príslušných riadkov sysprocesses a syslockinfo systémové tabuľky.
  • Ak sa snaží sledovať-lock zdrojov čaká, závora režim existuje, ktorý spôsobuje Zamknúť výstup vynechá.
Táto uložená procedúra je užitočná spustením z ľubovoľnej dotaz nástroj. Avšak, Microsoft navrhuje, postupujte podľa týchto krokov na vykonávanie blokujúca analýza:
  1. Vytvoriť uloženej procedúry sp_blocker_pss08, ktorá je na konci tohto článku, z ľubovoľnej dotaz nástroj, kým ste pripojení s prihlasovacie údaje, ktoré má sysadmin oprávnenia na serveri SQL Server alebo inštanciu servera SQL Server máte v pláne monitorovania.
  2. Vytvoriť súbor skriptu s nasledovný dotaz spustiť uložená procedúra v slučke. Všimnite si, že omeškanie by mala byť medzi 5 a 60
    sekúnd:

    ZATIAĽ ČO 1 = 1
    ZAČAŤ
    EXEC tempdb.dbo.sp_blocker_pss08
    --Alebo pre rýchly režim
    --EXEC. tempdb.dbo.sp_blocker_pss08 @ rýchlo = 1
    --Alebo režimu zámka
    --EXEC. tempdb.dbo.sp_blocker_pss08 @ závora = 1
    WAITFOR ODKLADU "00: 00:15"
    KONIEC
    ÍSŤ
  3. Táto produkcia je veľmi užitočné, keď sú kombinované s Microsoft Sledovanie výkonu systému Windows NT denník a denník SQL Profiler, tak aj na vytvorenie súčasne sa navrhuje. Informácie o ktoré Profiler a Výkon sledovanie udalostí na zachytenie, a informácie o tom, ako spNa interpretáciu výsledkov, pozri článok v Microsoft Knowledge Základne:
    224453Pochopenie a riešenie SQL Server 7.0 alebo 2000 blokovanie problémy
  4. Spustiť súbor skriptu, ktoré sa vytvorili v kroku 2 z Isql.exe, dotaz nástroja Osql.exe alebo pomôcky Sqlcmd v príkazovom riadku systému Windows na počítači so spustenou službou SQL Server, pre ktoré máte v úmysle monitorovať na zabránenie sieťových problémov odpojenie nástroja dotaz. Nasleduje príklad príkazového riadka, že môžete použiť na spustenie Osql.exe, ktoré predpokladá, že klient je spustený z počítača so spustenou službou SQL Server, a že je názov súboru skriptu Checkblk.sql. Skontrolujte, či správne parameter -S a sledovanie pomenovanú inštanciu nahraďte názvom servera SQL Server (alebo "servername\instance") "server". Tiež opraviť -i parameter a nahradiť "checkblk.sql" s cestu a názov súboru skriptu vytvorili v kroku 2.
    osql -E -Sserver -icheckblk.sql -ocheckblk.out -w2000
    Všimnite si, že musíte použiť iné prepínače príkazového riadka pre z nasledujúcich dôvodov:

    • Na zabránenie zalomenie riadka v produkcii súbory, ktoré uľahčuje čítanie.
    • Ak chcete odoslať výstup do súboru, určeného s -o parameter, namiesto na obrazovke tak, že ak dotaz nástroj má problémy, môžete stále mať výstup do dotazu nástroj zlyhá.
Nasleduje skriptu na vytvorenie sp_blocker_pss08 uložená procedúra:
/*Note: This script is meant to have 3 creations of the same stored procedure and two of them will failwith either 207 errors or a 2714 error.*/use masterGOif exists (select * from sysobjects where id = object_id('dbo.sp_blocker_pss08') and sysstat & 0xf = 4)   drop procedure dbo.sp_blocker_pss08GOcreate procedure dbo.sp_blocker_pss08 (@latch int = 1, @fast int = 1, @appname sysname='PSSDIAG')as --version 19.2005 - 2005 or Laterif is_member('sysadmin')=0 begin  print 'Must be a member of the sysadmin group in order to run this procedure'  returnendset nocount onSET LANGUAGE 'us_english'declare @spid varchar(6)declare @blocked varchar(6)declare @time datetimedeclare @time2 datetimedeclare @dbname nvarchar(128)declare @status sql_variantdeclare @useraccess sql_variantdeclare @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 != 0x0000if 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 -- Allelse  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 Laterif is_member('sysadmin')=0 begin  print 'Must be a member of the sysadmin group in order to run this procedure'  returnendset nocount onSET LANGUAGE 'us_english'declare @spid varchar(6)declare @blocked varchar(6)declare @time datetimedeclare @time2 datetimedeclare @dbname nvarchar(128)declare @status sql_variantdeclare @useraccess sql_variantset @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 != 0x0000if 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 -- Allelse  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 SP3if is_member('sysadmin')=0begin  print 'Must be a member of the sysadmin group in order to run this procedure'  returnendset nocount ondeclare @spid varchar(6)declare @blocked varchar(6)declare @time datetimedeclare @time2 datetimedeclare @dbname nvarchar(128)declare @status sql_variantdeclare @useraccess sql_variantset @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 != 0x0000if 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 -- Allelse  print '8 No Waittypes: ' + convert(varchar(26), @time, 121) + ' '     + convert(varchar(12), datediff(ms,@time,getdate())) + ' ' + ISNULL (@@servername,'(null)') + ' 19.2000'GO 
Pre viac informácií, navštívte nasledovnú webovú lokalitu:

Upozornenie: Tento článok bol preložený automaticky

Vlastnosti

ID článku: 271509 – Posledná kontrola: 07/29/2011 08:00:00 – Revízia: 1.0

  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL 2005 Server Workgroup
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL 2005 Server Enterprise
  • 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
  • kbsqlsetup kbhowto kbinfo kbmt KB271509 KbMtsk
Pripomienky