INF: How to View SQL Server 7.0 Activity Data (289274)


The information in this article applies to:

  • Microsoft SQL Server 7.0
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 "DBCC" 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:
  1. Open the trace file using SQL Profiler.
    1. On the File menu in SQL Profiler, click Open and then click Trace File.
    2. In the Open dialog box, click the trace file.
  2. Save the trace file from SQL Profiler to a SQL Server table.
    1. On the File menu, click Save As and then click Trace Table.
    2. In the Connect to SQL Server dialog box, enter the name of the SQL Server computer and then click OK.
    3. In the Destination Table dialog box, select the database, enter ActivityTrace for the table name, and then click OK.
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.

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
Modification Type:MajorLast Reviewed:4/10/2002
Keywords:kbDSupport kbinfo KB289274
©2002 Microsoft Corporation. All rights reserved.