Microsoft KB Archive/283886

= HOW TO: View SQL Server 2000 Performance Data =

Article ID: 283886

Article Last Modified on 12/26/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 64-bit Edition

-



This article was previously published under Q283886



IN THIS TASK

 * SUMMARY
 * View v_sysperfinfo
 * View v_BufferCacheHitRatio
 * View v_difference
 * View v_Access_methods
 * View v_object_names



SUMMARY
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.

back to the top

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

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 back to the top

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) ,substring(TextData,81,35) having max(cast(substring(TextData,116,11) as int)) -min(cast(substring(TextData,116,11) as int)) > 0 order by [difference] desc back to the top

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 back to the top

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 substring(TextData,charindex(':',TextData),25) order by [object_name] back to the top

Keywords: kbhowtomaster kbinfo KB283886

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.