INF: How to View SQL Server 2000 Activity Data (283784)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q283784

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:

283696 INF: Job to Monitor SQL Server 2000 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 2000 table named ActivityTrace. The stored procedure defined below, trace_gettable, imports the data from the trace file to a SQL Server 2000 table.

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

The syntax for the trace_gettable stored procedure is as follows: 
trace_gettable, @tablename, @tracefile, @numfiles
The arguments have the following datatypes and defaults:
  • @tablename sysname = N'ActivityTrace'
    This argument is the name of the table where the trace data will be copied. If the table does not exist, the stored procedure will create it. Otherwise, the table will be truncated before importing the data. Note that the other stored procedures in this article expect the table name to be ActivityTrace.
  • @tracefile nvarchar(245) = 'none'
    This argument is the name of the trace file. If no trace file is specified, the C:\ActivityTrace.ini file is read to find the name of the trace file.
  • @numfiles nvarchar(3) = '1'
    This argument specifies the number of rollover files to be read, including the initial file specified in filename. number_files is int. A value of '1' indicates one file is read, and a value of '-1' indicates that all files are read.
CREATE   proc trace_gettable @tablename sysname = N'ActivityTrace'
 , @tracefile nvarchar(245) = 'none', @numfiles nvarchar(3) = '1'
as
declare @cmd nvarchar(2048)
if @tracefile = 'none'
begin
   create table #t1 ([c1] nvarchar(128))
   bulk insert #t1 FROM 'C:\ActivityTrace.ini'
   select @tracefile = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245)) from #t1 where left(c1,3) = '@tr'
   drop table #t1
   end
if @tracefile = 'none' goto finish
set @tracefile = @tracefile + '.trc'
if exists (select * from dbo.sysobjects where id = object_id(@tablename) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   begin
   set @cmd = 'truncate table ' + @tablename
   exec (@cmd)
   end
else
   begin
   set @cmd = 'CREATE TABLE [' + @tablename + '](
  [RowNumber] [int] identity,
  [TextData] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 	[BinaryData] [image] NULL ,
	[DatabaseID] [int] NULL ,
	[TransactionID] [bigint] NULL ,
	[NTUserName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[NTDomainName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[HostName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ClientProcessID] [int] NULL ,
	[ApplicationName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LoginName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SPID] [int] NULL ,
	[Duration] [bigint] NULL ,
	[StartTime] [datetime] NULL ,
	[EndTime] [datetime] NULL ,
	[Reads] [bigint] NULL ,
	[Writes] [bigint] NULL ,
	[CPU] [int] NULL ,
	[Permissions] [int] NULL ,
	[Severity] [int] NULL ,
	[EventSubClass] [int] NULL ,
	[ObjectID] [int] NULL ,
	[Success] [int] NULL ,
	[IndexID] [int] NULL ,
	[IntegerData] [int] NULL ,
	[ServerName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[EventClass] [int] NOT NULL ,
	[ObjectType] [int] NULL ,
	[NestLevel] [int] NULL ,
	[State] [int] NULL ,
	[Error] [int] NULL ,
	[Mode] [int] NULL ,
	[Handle] [int] NULL ,
	[ObjectName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DatabaseName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FileName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[OwnerName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[RoleName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TargetUserName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DBUserName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LoginSid] [image] NULL ,
	[TargetLoginName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TargetLoginSid] [image] NULL ,
	[ColumnPermissions] [int] NULL 
  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'
  exec (@cmd)
   end

set @cmd = 'insert into [' + @tablename + '] SELECT TextData, BinaryData, DatabaseID, TransactionID, NTUserName, NTDomainName
, HostName, ClientProcessID, ApplicationName, LoginName, SPID, Duration, StartTime, EndTime
, Reads, Writes, CPU, [Permissions], Severity, EventSubClass, ObjectID, Success, IndexID
, IntegerData, ServerName, EventClass, ObjectType, NestLevel, State, Error, Mode, Handle
, ObjectName, DatabaseName, [FileName], OwnerName, RoleName, TargetUserName, DBUserName
, LoginSid, TargetLoginName, TargetLoginSid, ColumnPermissions
 FROM ::fn_trace_gettable(''' + @tracefile + ''',' + @numfiles + ')'
exec (@cmd)
finish:
				
-- 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 values (10,'RPC:Completed')
Insert trace_events values (11,'RPC:Starting')
Insert trace_events values (12,'SQL:BatchCompleted')
Insert trace_events values (13,'SQL:BatchStarting')
Insert trace_events values (14,'Login')
Insert trace_events values (15,'Logout')
Insert trace_events values (16,'Attention')
Insert trace_events values (17,'ExistingConnection')
Insert trace_events values (18,'ServiceControl')
Insert trace_events values (19,'DTCTransaction')
Insert trace_events values (20,'Login Failed')
Insert trace_events values (21,'EventLog')
Insert trace_events values (22,'ErrorLog')
Insert trace_events values (23,'Lock:Released')
Insert trace_events values (24,'Lock:Acquired')
Insert trace_events values (25,'Lock:Deadlock')
Insert trace_events values (26,'Lock:Cancel')
Insert trace_events values (27,'Lock:Timeout')
Insert trace_events values (28,'DOP Event')
Insert trace_events values (33,'Exception')
Insert trace_events values (34,'SP:CacheMiss')
Insert trace_events values (35,'SP:CacheInsert')
Insert trace_events values (36,'SP:CacheRemove')
Insert trace_events values (37,'SP:Recompile')
Insert trace_events values (38,'SP:CacheHit')
Insert trace_events values (39,'SP:ExecContextHit')
Insert trace_events values (40,'SQL:StmtStarting')
Insert trace_events values (41,'SQL:StmtCompleted')
Insert trace_events values (42,'SP:Starting')
Insert trace_events values (43,'SP:Completed')
Insert trace_events values (44,'Reserved ')
Insert trace_events values (45,'Reserved ')
Insert trace_events values (46,'Object:Created')
Insert trace_events values (47,'Object:Deleted')
Insert trace_events values (48,'Reserved')
Insert trace_events values (49,'Reserved')
Insert trace_events values (50,'SQL Transaction')
Insert trace_events values (51,'Scan:Started')
Insert trace_events values (52,'Scan:Stopped')
Insert trace_events values (53,'CursorOpen')
Insert trace_events values (54,'Transaction Log')
Insert trace_events values (55,'Hash Warning')
Insert trace_events values (58,'Auto Update Stats')
Insert trace_events values (59,'Lock:Deadlock Chain')
Insert trace_events values (60,'Lock:Escalation')
Insert trace_events values (61,'OLE DB Errors')
Insert trace_events values (67,'Execution Warnings')
Insert trace_events values (68,'Execution Plan')
Insert trace_events values (69,'Sort Warnings')
Insert trace_events values (70,'CursorPrepare')
Insert trace_events values (71,'Prepare SQL')
Insert trace_events values (72,'Exec Prepared SQL')
Insert trace_events values (73,'Unprepare SQL')
Insert trace_events values (74,'CursorExecute')
Insert trace_events values (75,'CursorRecompile')
Insert trace_events values (76,'CursorImplicitConversion')
Insert trace_events values (77,'CursorUnprepare')
Insert trace_events values (78,'CursorClose')
Insert trace_events values (79,'Missing Column Statistics')
Insert trace_events values (80,'Missing Join Predicate')
Insert trace_events values (81,'Server Memory Change')
Insert trace_events values (82,'User Configurable')
Insert trace_events values (83,'User Configurable')
Insert trace_events values (84,'User Configurable')
Insert trace_events values (85,'User Configurable')
Insert trace_events values (86,'User Configurable')
Insert trace_events values (87,'User Configurable')
Insert trace_events values (88,'User Configurable')
Insert trace_events values (89,'User Configurable')
Insert trace_events values (90,'User Configurable')
Insert trace_events values (91,'User Configurable')
Insert trace_events values (92,'Data File Auto Grow')
Insert trace_events values (93,'Log File Auto Grow')
Insert trace_events values (94,'Data File Auto Shrink')
Insert trace_events values (95,'Log File Auto Shrink')
Insert trace_events values (96,'Show Plan Text')
Insert trace_events values (97,'Show Plan ALL')
Insert trace_events values (98,'Show Plan Statistics')
Insert trace_events values (99,'Reserved')
Insert trace_events values (100,'RPC Output Parameter')
Insert trace_events values (101,'Reserved')
Insert trace_events values (102,'Audit Statement GDR')
Insert trace_events values (103,'Audit Object GDR')
Insert trace_events values (104,'Audit Add/Drop Login')
Insert trace_events values (105,'Audit Login GDR')
Insert trace_events values (106,'Audit Login Change Property')
Insert trace_events values (107,'Audit Login Change Password')
Insert trace_events values (108,'Audit Add Login to Server Role')
Insert trace_events values (109,'Audit Add DB User')
Insert trace_events values (110,'Audit Add Member to DB')
Insert trace_events values (111,'Audit Add/Drop Role')
Insert trace_events values (112,'App Role Pass Change')
Insert trace_events values (113,'Audit Statement Permission')
Insert trace_events values (114,'Audit Object Permission')
Insert trace_events values (115,'Audit Backup/Restore')
Insert trace_events values (116,'Audit DBCC')
Insert trace_events values (117,'Audit Change Audit')
Insert trace_events values (118,'Audit Object Derived Permission')
				
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 RowNumber,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,8) as bigint) as [physical_io]
      ,cast(substring(BinaryData,605,4) as int) as [memusage]
      ,cast(substring(BinaryData,609,8) as datetime) as [login_time]
      ,cast(substring(BinaryData,617,8) as datetime) as [last_batch]
      ,cast(substring(BinaryData,625,2) as smallint) as [ecid]
      ,cast(substring(BinaryData,627,2) as smallint) as [open_tran]
      ,cast(substring(BinaryData,629,60) as nchar(30)) as [status]
      ,cast(substring(BinaryData,689,86) as binary(86)) as [sid]
      ,cast(substring(BinaryData,775,256) as nchar(128)) as [hostname]
      ,cast(substring(BinaryData,1031,256) as nchar(128)) as [program_name]
      ,cast(substring(BinaryData,1287,16) as nchar(8)) as [hostprocess]
      ,cast(substring(BinaryData,1303,32) as nchar(16)) as [cmd]
      ,cast(substring(BinaryData,1335,256) as nchar(128)) as [nt_domain]
      ,cast(substring(BinaryData,1591,256) as nchar(128)) as [nt_username]
      ,cast(substring(BinaryData,1847,24) as nchar(12)) as [net_address]
      ,cast(substring(BinaryData,1871,24) as nchar(12)) as [net_library]
      ,cast(substring(BinaryData,1895,256) as nchar(128)) as [loginame]
      ,cast(substring(BinaryData,2151,128) as binary(128)) as [context_info]
  from ActivityTrace
 where eventclass = 82 and substring(TextData,1,8) = N'blocking'
				
CREATE     view v_syslockinfo as
select RowNumber,StartTime
      ,TextData
      ,cast(substring(BinaryData,2279,64) as nchar(32)) as [rsc_text]
      ,cast(substring(BinaryData,2343,16) as binary(16)) as [rsc_bin]
      ,cast(substring(BinaryData,2359,16) as binary(16)) as [rsc_valblk]
      ,cast(substring(BinaryData,2375,2) as smallint) as [rsc_dbid]
      ,cast(substring(BinaryData,2377,2) as smallint) as [rsc_indid]
      ,cast(substring(BinaryData,2379,4) as int) as [rsc_objid]
      ,cast(substring(BinaryData,2383,1) as tinyint) as [rsc_type]
      ,cast(substring(BinaryData,2384,1) as tinyint) as [rsc_flag]
      ,cast(substring(BinaryData,2385,1) as tinyint) as [req_mode]
      ,cast(substring(BinaryData,2386,1) as tinyint) as [req_status]
      ,cast(substring(BinaryData,2387,2) as smallint) as [req_cnt]
      ,cast(substring(BinaryData,2389,2) as smallint) as [req_cryrefcnt]
      ,cast(substring(BinaryData,2391,4) as int) as [req_lifetime]
      ,cast(substring(BinaryData,2395,4) as int) as [req_spid]
      ,cast(substring(BinaryData,2399,4) as int) as [req_ecid]
      ,cast(substring(BinaryData,2403,2) as smallint) as [req_ownertype]
      ,cast(substring(BinaryData,2405,8) as bigint) as [req_transactionID]
      ,cast(substring(BinaryData,2413,16) as uniqueidentifier) as [req_transactionUOW]
  from ActivityTrace
 where eventclass = 82 and substring(TextData,1,8) = N'blocking'
				
CREATE       view v_dbccpss as
select top 100 percent RowNumber, 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 RowNumber
Modification Type:MajorLast Reviewed:11/5/2003
Keywords:kbinfo KB283784
©2003 Microsoft Corporation. All rights reserved.