如何: 查看 SQL Server 2000 性能数据

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

点击这里察看该文章的英文版: 283886
本文已归档。它按“原样”提供,并且不再更新。
概要
本文介绍了可用于分析性能数据,在下面的文章的 Microsoft 知识库中创建的跟踪文件中的视图:
283696INF: 作业,以监视 SQL Server 2000 性能和活动
在创建这些视图之前,必须将导入跟踪文件到名为 ActivityTrace SQL Server 2000 表。为存储过程 trace_gettable,脚本将数据从导入跟踪文件 SQL Server 2000 的表。有关更多的信息请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
283784INF: 如何查看 SQL Server 2000 活动数据
分析性能数据,请使用下列视图:
  • v_sysperfinfo 视图列出 sysperfinfo 表中跟踪记录的性能计数器。
  • v_BufferCacheHitRatio 视图计算高速缓存的命中比率。
  • v_difference 视图列出了最大和最小值的计数器值之差。
  • v_Access_methods 视图列出访问方法对象的所有的计数器。更改在视图中 v_Access_methods %:Access 方法 %到另一个对象,以列出的其他计数器。
  • v_object_names 视图列出所有性能对象的名称。
back to the top

视图 v_sysperfinfo

CREATE      view v_sysperfinfo asselect top 100 percent  RowNumber,[servername],[starttime],substring(TextData,  1,40) as [object_name],substring(TextData, 41,40) as [counter_name],substring(TextData,81,35) as [instance_name],cast(substring(TextData,116,11) as int) as [cntr_value]  from ActivityTrace where EventClass = 83 order by  substring(TextData, 1,40) ,substring(TextData,41,40) ,substring(TextData,81,35) ,starttime				
back to the top

视图 v_BufferCacheHitRatio

CREATE  view v_BufferCacheHitRatio asselect top 100 percent a.starttime		 , cast(substring(a.TextData,116,11) as int) as [hits]		 , cast(substring(b.TextData,116,11) as int) as [base]		 , cast(((cast(substring(a.TextData,116,11) as int) * 100)		 / cast(substring(b.TextData,116,11) as int)) as numeric(6,3))		as [Buffer cache hit ratio]	from ActivityTrace a join ActivityTrace b    on datepart(yy,a.starttime) = datepart(yy,b.starttime)	 and datepart(mm,a.starttime) = datepart(mm,b.starttime)   and datepart(dd,a.starttime) = datepart(dd,b.starttime)   and datepart(hh,a.starttime) = datepart(hh,b.starttime)   and datepart(mi,a.starttime) = datepart(mi,b.starttime)   and datepart(ss,a.starttime) = datepart(ss,b.starttime)   and substring(a.TextData,41,27) = N'Buffer cache hit ratio     '   and substring(b.TextData,41,27) = N'Buffer cache hit ratio base' order by a.starttime				
back to the top

视图 v_difference

CREATE    view v_difference asselect top 100 percent       substring(TextData,  1,40) as [object_name]      ,substring(TextData, 41,40) as [counter_name]      ,substring(TextData,81,35)  as [instance_name]			,max(cast(substring(TextData,116,11) as int)) as [maximum]			,min(cast(substring(TextData,116,11) as int)) as [minimum]			,max(cast(substring(TextData,116,11) as int))			-min(cast(substring(TextData,116,11) as int)) as [difference]      ,count(*) as [count]      ,datediff(mi,min(StartTime),max(StartTime)) as [minutes]  from ActivityTrace where EventClass = 83 group by       substring(TextData,  1,40)      ,substring(TextData, 41,40)      ,substring(TextData,81,35)having max(cast(substring(TextData,116,11) as int))			-min(cast(substring(TextData,116,11) as int)) > 0order by [difference] desc				
back to the top

视图 v_Access_methods

CREATE view v_Access_methods asselect top 100 percent substring(TextData,41,40) as [counter name],left(starttime,20) as [time],cast(substring(TextData,116,11) as int) as [counter value] from ActivityTrace where substring(TextData,1,40) like '%:Access Methods%' order by substring(TextData,41,40), starttime				
back to the top

视图 v_object_names

create view v_object_names asselect top 100 percent substring(TextData,charindex(':',TextData),25) as [object_name]  from ActivityTrace where EventClass = 83 group by  substring(TextData,charindex(':',TextData),25) order by [object_name]				
back to the top

属性

文章 ID:283886 - 上次审阅时间:02/24/2014 20:18:54 - 修订版本: 4.5

Microsoft SQL Server 2000 标准版, Microsoft SQL Server 2000 64 bit (all editions)

  • kbnosurvey kbarchive kbmt kbhowtomaster kbinfo KB283886 KbMtzh
反馈