INF: How to View SQL Server 7.0 Blocking Data (289276)


The information in this article applies to:

  • Microsoft SQL Server 7.0
This article was previously published under Q289276

SUMMARY

This article defines views that are used to view blocking data contained in a 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:
  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.

Use the views defined below to display blocking data as noted:
  • The v_blocked view lists the system process IDs (SPIDs) that are blocked.
  • The v_blocking view lists all of the SPIDs involved in blocking.
  • The v_blockspids1 view is a summary list of SPIDs that are blocking other SPIDs.
  • The v_blocker view is appropriate for use only when the v_blockspids1 view indicates that only one SPID is blocking. The v_blocker view shows the rows in the trace prior to the block.
  • The v_blockspids2 view is a detailed list of SPIDs that are blocking other SPIDs.

MORE INFORMATION

CREATE     view v_blocked 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 substring(TextData,1,8) = N'blocking'
   and cast(substring(BinaryData,5,2) as smallint) != 0
				
CREATE     view v_blocking 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 substring(TextData,1,8) = N'blocking'
				
create view v_blockspids1 as
select top 100 percent
       cast(substring(BinaryData,1,2) as smallint) as [spid]
      ,min(EndTime) as EndTime
  from ActivityTrace
 where substring(TextData,1,8) = N'blocking'
   and cast(substring(BinaryData,5,2) as smallint) = 0
   and cast(substring(BinaryData,1,2) as smallint) in 
     (select cast(substring(BinaryData,5,2) as smallint) as spid
        from ActivityTrace
       where cast(substring(BinaryData,5,2) as smallint) != 0)
 group by cast(substring(BinaryData,1,2) as smallint)
 order by cast(substring(BinaryData,1,2) as smallint)
				
--The v_blocker view is appropriate if only one spid is blocking.
CREATE  view v_blocker as
select top 100 percent
       a.EventClass,e.EventName,EndTime,spid,Duration
      ,Reads,Writes,CPU,ObjectID,TextData,DatabaseID
      ,TransactionID,NTUserName,NTDomainName,HostName,ApplicationName
  from activitytrace a left join trace_events e
    on a.EventClass = e.EventClass
 where spid = (select top 1 spid from v_blockspids1)
   and EndTime < (select top 1 EndTime from v_blockspids1)
 order by Endtime desc
				
CREATE    view v_blockspids2 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 substring(TextData,1,8) = N'blocking'
   and cast(substring(BinaryData,5,2) as smallint) = 0
   and cast(substring(BinaryData,1,2) as smallint) in 
     (select cast(substring(BinaryData,5,2) as smallint) as spid
        from ActivityTrace
       where cast(substring(BinaryData,5,2) as smallint) != 0)
Modification Type:MajorLast Reviewed:3/3/2001
Keywords:kbinfo KB289276
©2002 Microsoft Corporation. All rights reserved.