INF: 如何查看 SQL Server 2000 阻止数据

注意:这篇文章是由无人工介入的微软自动的机器翻译软件翻译完成。微软很高兴能同时提供给您由人工翻译的和由机器翻译的文章, 以使您能使用您的语言访问所有的知识库文章。然而由机器翻译的文章并不总是完美的。它可能存在词汇,语法或文法的问题,就像是一个外国人在说中文时总是可能犯这样的错误。虽然我们经常升级机器翻译软件以提高翻译质量,但是我们不保证机器翻译的正确度,也不对由于内容的误译或者客户对它的错误使用所引起的任何直接的, 或间接的可能的问题负责。

点击这里察看该文章的英文版: 283725
本文已归档。它按“原样”提供,并且不再更新。
概要
这篇文章定义了可用于查看阻止由该作业下面 Microsoft 知识库中相应的文章中所述创建一个跟踪文件中包含的数据的视图:
283696INF: 作业,以监视 SQL Server 2000 性能和活动
在创建这些视图之前,必须到名为 ActivityTrace SQL Server 2000 表中导入跟踪文件。在下面的文章中定义称为 trace_gettable,从而将数据从跟踪文件导入到 SQL Server 2000 表上,一个存储过程的脚本:
283784INF: 如何查看 SQL Server 2000 活动数据
使用下面定义的阻塞数据显示为设定的视图:
  • v_blocked 视图列出系统进程 id (spid) 被阻止的。
  • v_blocking 视图列出了所有参与阻塞的 spid。
  • v_blockspids1 视图是当前阻塞其他 spid 的 spid 的摘要列表。在此视图中的 RowNumber 列是行号从 ActivityTrace 表和跟踪文件。若要查看从 ActivityTrace 表之前对 v_blockspid1 视图中列出行的行 TextData 列使用下面的查询。
    select TextData from ActivityTrace where RowNumber > 4080 and RowNumber < 5000						
    更改第二个 RowNumber v_blockspid1 中返回的值将上述查询中的编号 (5000)。更改第一个数 (4080),以反映您要查看的上一个行数。例如对于如果 v_blockspid1 返回 RowNumber 52365,并且您要查看 20 行 ActivityTrace 表之前对该行中的发出下面的语句:
    select TextData from ActivityTrace where RowNumber > 52345 and RowNumber < 52365					
  • v_blockspids2 视图是当前阻塞其他 spid 的 spid 的详细的列表。
更多信息
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 asselect 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 asselect 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)				

警告:本文已自动翻译

属性

文章 ID:283725 - 上次审阅时间:01/16/2015 21:22:41 - 修订版本: 3.2

Microsoft SQL Server 2000 标准版

  • kbnosurvey kbarchive kbmt kbinfo KB283725 KbMtzh
反馈