You are currently offline, waiting for your internet to reconnect

INF: How to View SQL Server 2000 Activity Data

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'asdeclare @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   endif @tracefile = 'none' goto finishset @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)   endelse   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)   endset @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 asselect 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 asSELECT 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.EventNameHAVING (SUM(a.Duration) > 0) ORDER BY SUM(a.Duration) DESC				
CREATE    view v_sysprocesses asselect 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 asselect 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 asselect 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				
Properties

Article ID: 283784 - Last Review: 11/05/2003 17:56:00 - Revision: 3.3

  • Microsoft SQL Server 2000 Standard Edition
  • kbinfo KB283784
Feedback