Microsoft KB Archive/289277

= INF: How to View SQL Server 7.0 Performance Data =

Article ID: 289277

Article Last Modified on 4/12/2001

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q289277



SUMMARY
This article describes views that are used to analyze the performance data from the trace file that is created by the job described in the following Microsoft Knowledge Base article:

286191 INF: Job to Monitor SQL Server 7.0 Performance and Activity

Before you create these views, the trace file must be imported into a SQL Server table named ActivityTrace as follows:

 Open the trace file using SQL Profiler.  On the File menu in SQL Profiler, click Open and then click Trace File. In the Open dialog box, click the trace file.  Save the trace file from SQL Profiler to a SQL Server table.  On the File menu, click Save as and then click Trace Table. In the Connect to SQL Server dialog box, enter the name of the SQL Server computer and then click OK.</li> In the Destination Table dialog box, select the database, enter ActivityTrace for the table name, and then click OK.</li></ol> </li></ol>

SQL Server 2000 Profiler saves a SQL Server 7.0 trace to a table with the following additional columns:
 * ClientProcessID
 * IndexID
 * Permissions
 * RowNumber
 * Success

The views in the article do not use these additional columns.

Use the views defined below to display performance data as noted:
 * 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.

<div class="moreinformation_section">

MORE INFORMATION
CREATE     view v_sysperfinfo as select top 100 percent [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 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 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 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) 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]

Keywords: kbinfo KB289277

-

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

© Microsoft Corporation. All rights reserved.