INF: Jak data blokování zobrazení SQL Server 2000

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

Souhrn

Tento článek definuje zobrazení, které lze použít k zobrazení blokování data obsažená v souboru trasování vytvořené úlohy popsané v následujícím článku databáze Microsoft Knowledge Base:
283696INF: Úloha SQL Server 2000 Performance Monitor a aktivity
Před vytvořením těchto zobrazení trasovací soubor musí být importovány do tabulky SQL Server 2000 s názvem ActivityTrace. Skript pro uloženou proceduru nazývá trace_gettable, který importuje data ze souboru trasování SQL Server 2000 tabulky je definována v následujícím článku:
283784INF: Jak data zobrazení SQL Server 2000 aktivity
Použít zobrazení, které jsou definovány pod zobrazení blokování dat uvedena jako:
  • Uvádí zobrazení v_blocked systémový proces ID (SPIDs), které jsou blokovány.
  • Uvádí zobrazení v_blocking všechny účastní blokování SPIDs.
  • Zobrazení v_blockspids1 je souhrnný seznam SPIDs jsou blokování jiných spids. RowNumber sloupce v tomto zobrazení je číslo řádku z tabulky a trasování souboru ActivityTrace. Následující dotaz slouží k zobrazení TextData sloupec z tabulky ActivityTrace řádky před řádek, uvedeny v zobrazení v_blockspid1.
    select TextData from ActivityTrace where RowNumber > 4080 and RowNumber < 5000
    						
    Změnit druhé číslo (5000) v výše dotazu na hodnotu pro RowNumber vrácený v v_blockspid1. Změnit číslo první (4080) odrážejí počet předchozích řádků chcete zobrazit. Například pokud v_blockspid1 vrátí RowNumber 52365 a chcete zobrazit 20 řádky z tabulky ActivityTrace před daný řádek vydat následující příkaz:
    select TextData from ActivityTrace where RowNumber > 52345 and RowNumber < 52365
    					
  • Zobrazení v_blockspids2 je podrobný seznam SPIDs jsou blokování jiných SPIDs.

Další informace

CREATE    view v_blocked as 
select RowNumber,StartTime
      ,TextData
      ,cast(substring(BinaryData,1,2) as smallint) as [spid]
      ,cast(substring(BinaryData,3,2) as smallint) as [kpid]
      ,cast(substring(BinaryData,5,2) as smallint) as [blocked]
      ,cast(substring(BinaryData,7,2) as binary(2)) as [waittype]
      ,cast(substring(BinaryData,9,4) as int) as [waittime]
      ,cast(substring(BinaryData,13,64) as nchar(32)) as [lastwaittype]
      ,cast(substring(BinaryData,77,512) as nchar(256)) as [waitresource]
      ,cast(substring(BinaryData,589,2) as smallint) as [dbid]
      ,cast(substring(BinaryData,591,2) as smallint) as [uid]
      ,cast(substring(BinaryData,593,4) as int) as [cpu]
      ,cast(substring(BinaryData,597,8) as bigint) as [physical_io]
      ,cast(substring(BinaryData,605,4) as int) as [memusage]
      ,cast(substring(BinaryData,609,8) as datetime) as [login_time]
      ,cast(substring(BinaryData,617,8) as datetime) as [last_batch]
      ,cast(substring(BinaryData,625,2) as smallint) as [ecid]
      ,cast(substring(BinaryData,627,2) as smallint) as [open_tran]
      ,cast(substring(BinaryData,629,60) as nchar(30)) as [status]
      ,cast(substring(BinaryData,689,86) as binary(86)) as [sid]
      ,cast(substring(BinaryData,775,256) as nchar(128)) as [hostname]
      ,cast(substring(BinaryData,1031,256) as nchar(128)) as [program_name]
      ,cast(substring(BinaryData,1287,16) as nchar(8)) as [hostprocess]
      ,cast(substring(BinaryData,1303,32) as nchar(16)) as [cmd]
      ,cast(substring(BinaryData,1335,256) as nchar(128)) as [nt_domain]
      ,cast(substring(BinaryData,1591,256) as nchar(128)) as [nt_username]
      ,cast(substring(BinaryData,1847,24) as nchar(12)) as [net_address]
      ,cast(substring(BinaryData,1871,24) as nchar(12)) as [net_library]
      ,cast(substring(BinaryData,1895,256) as nchar(128)) as [loginame]
      ,cast(substring(BinaryData,2151,128) as binary(128)) as [context_info]
  from ActivityTrace
 where substring(TextData,1,8) = N'blocking'
   and cast(substring(BinaryData,5,2) as smallint) != 0
				
CREATE   view v_blocking as
select RowNumber,StartTime
      ,TextData
      ,cast(substring(BinaryData,1,2) as smallint) as [spid]
      ,cast(substring(BinaryData,3,2) as smallint) as [kpid]
      ,cast(substring(BinaryData,5,2) as smallint) as [blocked]
      ,cast(substring(BinaryData,7,2) as binary(2)) as [waittype]
      ,cast(substring(BinaryData,9,4) as int) as [waittime]
      ,cast(substring(BinaryData,13,64) as nchar(32)) as [lastwaittype]
      ,cast(substring(BinaryData,77,512) as nchar(256)) as [waitresource]
      ,cast(substring(BinaryData,589,2) as smallint) as [dbid]
      ,cast(substring(BinaryData,591,2) as smallint) as [uid]
      ,cast(substring(BinaryData,593,4) as int) as [cpu]
      ,cast(substring(BinaryData,597,8) as bigint) as [physical_io]
      ,cast(substring(BinaryData,605,4) as int) as [memusage]
      ,cast(substring(BinaryData,609,8) as datetime) as [login_time]
      ,cast(substring(BinaryData,617,8) as datetime) as [last_batch]
      ,cast(substring(BinaryData,625,2) as smallint) as [ecid]
      ,cast(substring(BinaryData,627,2) as smallint) as [open_tran]
      ,cast(substring(BinaryData,629,60) as nchar(30)) as [status]
      ,cast(substring(BinaryData,689,86) as binary(86)) as [sid]
      ,cast(substring(BinaryData,775,256) as nchar(128)) as [hostname]
      ,cast(substring(BinaryData,1031,256) as nchar(128)) as [program_name]
      ,cast(substring(BinaryData,1287,16) as nchar(8)) as [hostprocess]
      ,cast(substring(BinaryData,1303,32) as nchar(16)) as [cmd]
      ,cast(substring(BinaryData,1335,256) as nchar(128)) as [nt_domain]
      ,cast(substring(BinaryData,1591,256) as nchar(128)) as [nt_username]
      ,cast(substring(BinaryData,1847,24) as nchar(12)) as [net_address]
      ,cast(substring(BinaryData,1871,24) as nchar(12)) as [net_library]
      ,cast(substring(BinaryData,1895,256) as nchar(128)) as [loginame]
      ,cast(substring(BinaryData,2151,128) as binary(128)) as [context_info]
  from ActivityTrace
 where substring(TextData,1,8) = N'blocking'
				
create view v_blockspids1 as
select top 100 percent
       cast(substring(BinaryData,1,2) as smallint) as [spid]
      ,min(RowNumber) as RowNumber
  from ActivityTrace
 where substring(TextData,1,8) = N'blocking'
   and cast(substring(BinaryData,5,2) as smallint) = 0
   and cast(substring(BinaryData,1,2) as smallint) in 
     (select cast(substring(BinaryData,5,2) as smallint) as spid
        from ActivityTrace
       where cast(substring(BinaryData,5,2) as smallint) != 0)
 group by cast(substring(BinaryData,1,2) as smallint)
 order by cast(substring(BinaryData,1,2) as smallint)
				
CREATE    view v_blockspids2 as 
select RowNumber,StartTime
      ,TextData
      ,cast(substring(BinaryData,1,2) as smallint) as [spid]
      ,cast(substring(BinaryData,3,2) as smallint) as [kpid]
      ,cast(substring(BinaryData,5,2) as smallint) as [blocked]
      ,cast(substring(BinaryData,7,2) as binary(2)) as [waittype]
      ,cast(substring(BinaryData,9,4) as int) as [waittime]
      ,cast(substring(BinaryData,13,64) as nchar(32)) as [lastwaittype]
      ,cast(substring(BinaryData,77,512) as nchar(256)) as [waitresource]
      ,cast(substring(BinaryData,589,2) as smallint) as [dbid]
      ,cast(substring(BinaryData,591,2) as smallint) as [uid]
      ,cast(substring(BinaryData,593,4) as int) as [cpu]
      ,cast(substring(BinaryData,597,8) as bigint) as [physical_io]
      ,cast(substring(BinaryData,605,4) as int) as [memusage]
      ,cast(substring(BinaryData,609,8) as datetime) as [login_time]
      ,cast(substring(BinaryData,617,8) as datetime) as [last_batch]
      ,cast(substring(BinaryData,625,2) as smallint) as [ecid]
      ,cast(substring(BinaryData,627,2) as smallint) as [open_tran]
      ,cast(substring(BinaryData,629,60) as nchar(30)) as [status]
      ,cast(substring(BinaryData,689,86) as binary(86)) as [sid]
      ,cast(substring(BinaryData,775,256) as nchar(128)) as [hostname]
      ,cast(substring(BinaryData,1031,256) as nchar(128)) as [program_name]
      ,cast(substring(BinaryData,1287,16) as nchar(8)) as [hostprocess]
      ,cast(substring(BinaryData,1303,32) as nchar(16)) as [cmd]
      ,cast(substring(BinaryData,1335,256) as nchar(128)) as [nt_domain]
      ,cast(substring(BinaryData,1591,256) as nchar(128)) as [nt_username]
      ,cast(substring(BinaryData,1847,24) as nchar(12)) as [net_address]
      ,cast(substring(BinaryData,1871,24) as nchar(12)) as [net_library]
      ,cast(substring(BinaryData,1895,256) as nchar(128)) as [loginame]
      ,cast(substring(BinaryData,2151,128) as binary(128)) as [context_info]
  from ActivityTrace
 where substring(TextData,1,8) = N'blocking'
   and cast(substring(BinaryData,5,2) as smallint) = 0
   and cast(substring(BinaryData,1,2) as smallint) in 
     (select cast(substring(BinaryData,5,2) as smallint) as spid
        from ActivityTrace
       where cast(substring(BinaryData,5,2) as smallint) != 0)
				

Vlastnosti

ID článku: 283725 - Poslední aktualizace: 5. listopadu 2003 - Revize: 3.2
Informace v tomto článku jsou určeny pro produkt:
  • Microsoft SQL Server 2000 Standard Edition
Klíčová slova: 
kbmt kbinfo KB283725 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:283725

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