HOW TO: View SQL Server 2000 Performance Data

Article translations Article translations
Article ID: 283886 - View products that this article applies to.
This article was previously published under Q283886
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

On This Page


This article describes views that you can use to analyze the performance data in the trace file that is created in the following article in the Microsoft Knowledge Base:
283696 INF: Job to Monitor SQL Server 2000 Performance and Activity
Before you create these views, you must import the trace file into a SQL Server 2000 table named ActivityTrace. The script for a stored procedure, trace_gettable, imports the data from the trace file to a SQL Server 2000 table. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
283784 INF: How to View SQL Server 2000 Activity Data
Use the following views to analyze performance data:
  • The v_sysperfinfo view lists the performance counters recorded by the trace from the sysperfinfo table.
  • The v_BufferCacheHitRatio view calculates the buffer cache hit ratio.
  • The v_difference view lists the difference between maximum and minimum counter values.
  • The v_Access_methods view lists all counters for the access methods object. Change %:Access Methods% in the v_Access_methods view to another object to list other counters.
  • The v_object_names view lists all the performance object names.

View v_sysperfinfo

CREATE      view v_sysperfinfo as
select top 100 percent 
,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)

View v_BufferCacheHitRatio

CREATE  view v_BufferCacheHitRatio as
select 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

View v_difference

CREATE    view v_difference as
select 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)
having max(cast(substring(TextData,116,11) as int))
			-min(cast(substring(TextData,116,11) as int)) > 0
order by [difference] desc

View v_Access_methods

CREATE view v_Access_methods as
select 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

View v_object_names

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


Article ID: 283886 - Last Review: February 27, 2014 - Revision: 4.5
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 64 bit (all editions)
kbnosurvey kbarchive kbhowtomaster kbinfo KB283886

Contact us for more help

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