Microsoft KB Archive/289274

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

Article ID: 289274

Article Last Modified on 4/10/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q289274



SUMMARY
This article defines views that can be used to view activity data contained in a trace file that is created by the job described in the following article in the Microsoft Knowledge Base:

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

'NOTE:' This article discusses a DBCC command (DBCC PSS) that is unsupported, and may cause unexpected behavior. Microsoft cannot guarantee that you can solve problems that result from the incorrect use of this DBCC command. Use this DBCC command at your own risk. This DBCC command may not be available in future versions of SQL Server. For a list of the supported DBCC commands, see the &quot;DBCC&quot; topic in the Transact-SQL Reference section of SQL Server Books Online.

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.

Note that you must create the trace_events table from the script below before creating the v_eventcount and v_SumDuration views because both of these views use the trace_events table.

Use the views defined below to display activity data as noted:
 * The v_eventcount view lists the count for each event class.
 * The v_SumDuration view lists the sum of the trace duration column for each event class.
 * The v_sysprocesses view lists the results from the sysprocesses table for the system process IDs (SPIDs) involved in blocking.
 * The v_syslockinfo view lists the results from the syslockinfo table for the SPIDs involved in blocking.
 * The v_dbccpss view lists the dbcc pss results for blocking SPIDs.

<div class="moreinformation_section">

MORE INFORMATION
-- Run this script to create the trace_events table. -- This table is used in the v_eventcount and v_SumDuration views. if exists (select * from dbo.sysobjects  where id = object_id(N'[trace_events]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [trace_events] create table trace_events ([EventClass] int, [EventName] varchar(31)) insert trace_events EXEC master..xp_trace_geteventnames

CREATE    view v_eventcount as select top 100 percent e.EventName, a.EventClass ,count(*) as [Number of Events] ,min(StartTime) as [Time of First Event] ,max(StartTime) as [Time of Last Event] ,datediff(mi,min(StartTime),max(StartTime)) as [minutes] from ActivityTrace a left join trace_events e   on a.EventClass = e.EventClass group by e.Eventname, a.EventClass order by [Number of Events] desc CREATE view v_SumDuration as SELECT TOP 100 PERCENT e.EventName ,COUNT(*) AS [Frequency] ,SUM(a.Duration) AS [Sum of Duration] ,SUBSTRING(a.TextData, 1, 60) AS [Substring from Text Data] FROM dbo.ActivityTrace a LEFT OUTER JOIN dbo.trace_events e   ON a.EventClass = e.EventClass GROUP BY SUBSTRING(a.TextData, 1, 60), e.EventName HAVING (SUM(a.Duration) > 0) ORDER BY SUM(a.Duration) DESC CREATE view v_sysprocesses as select 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,4) as int) as [physical_io] ,cast(substring(BinaryData,601,4) as int) as [memusage] ,cast(substring(BinaryData,605,8) as datetime) as [login_time] ,cast(substring(BinaryData,613,8) as datetime) as [last_batch] ,cast(substring(BinaryData,621,2) as smallint) as [ecid] ,cast(substring(BinaryData,623,2) as smallint) as [open_tran] ,cast(substring(BinaryData,625,60) as nchar(30)) as [status] ,cast(substring(BinaryData,685,86) as binary(86)) as [sid] ,cast(substring(BinaryData,771,256) as nchar(128)) as [hostname] ,cast(substring(BinaryData,1027,256) as nchar(128)) as [program_name] ,cast(substring(BinaryData,1283,16) as nchar(8)) as [hostprocess] ,cast(substring(BinaryData,1329,32) as nchar(16)) as [cmd] ,cast(substring(BinaryData,1331,256) as nchar(128)) as [nt_domain] ,cast(substring(BinaryData,1587,256) as nchar(128)) as [nt_username] ,cast(substring(BinaryData,1843,24) as nchar(12)) as [net_address] ,cast(substring(BinaryData,1867,24) as nchar(12)) as [net_library] ,cast(substring(BinaryData,1891,256) as nchar(128)) as [loginame] from ActivityTrace where eventclass = 82 and substring(TextData,1,8) = N'blocking' CREATE    view v_syslockinfo as select StartTime ,TextData ,cast(substring(BinaryData,2147,64) as nchar(32)) as [rsc_text] ,cast(substring(BinaryData,2211,16) as binary(16)) as [rsc_bin] ,cast(substring(BinaryData,2227,16) as binary(16)) as [rsc_valblk] ,cast(substring(BinaryData,2243,2) as smallint) as [rsc_dbid] ,cast(substring(BinaryData,2245,2) as smallint) as [rsc_indid] ,cast(substring(BinaryData,2247,4) as int) as [rsc_objid] ,cast(substring(BinaryData,2301,1) as tinyint) as [rsc_type] ,cast(substring(BinaryData,2302,1) as tinyint) as [rsc_flag] ,cast(substring(BinaryData,2303,1) as tinyint) as [req_mode] ,cast(substring(BinaryData,2304,1) as tinyint) as [req_status] ,cast(substring(BinaryData,2305,2) as smallint) as [req_refcnt] ,cast(substring(BinaryData,2307,2) as smallint) as [req_cryrefcnt] ,cast(substring(BinaryData,2309,4) as int) as [req_lifetime] ,cast(substring(BinaryData,2313,4) as int) as [req_spid] ,cast(substring(BinaryData,2317,4) as int) as [req_ecid] ,cast(substring(BinaryData,2321,2) as smallint) as [req_ownertype] from ActivityTrace where eventclass = 82 and substring(TextData,1,8) = N'blocking' CREATE      view v_dbccpss as select top 100 percent EndTime, TextData as dbccpss from ActivityTrace with (nolock) where eventclass = 82 and (substring(TextData,1,8) != N'blocking'   or  substring(TextData,1,8) != N'no block') order by Endtime

Keywords: kbinfo KB289274

-

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

© Microsoft Corporation. All rights reserved.