Microsoft KB Archive/289276

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

Article ID: 289276

Article Last Modified on 3/3/2001

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



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:  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.

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.

<div class="moreinformation_section">

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)

Keywords: kbinfo KB289276

-

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

© Microsoft Corporation. All rights reserved.