February 14, 2007 at 2:20 pm
I'm running the "pss80" Microsoft stored proc (on a 2000 SP4 server) to identify locking/blocking
problems and one spid showed up as the head of a blocking chain for about 8 minutes this morning:
"SPIDs at the head of blocking chains
spid
------
86 "
Going a little farther down in the file, it said that it was the following stored proc:
"************************************************************
Print out DBCC Input buffer for all blocked or blocking spids.
************************************************************
DBCC INPUTBUFFER FOR SPID 86
EventType Parameters EventInfo
--------- ---------- ---------------------
RPC Event 0 spw_check_server;1 "
However this stored proc is a very basic stored proc that is read
uncommitted. My question is this: how can this be blocking if it is read uncommitted??
Any help would be much appreciated as I am getting intermitent timeout problems on this server and all the perf mon stats look great (per other posts).
Here is the stored proc:
ALTER PROCEDURE dbo.spw_check_server
(
@serverID int,
@errorID int,
@timeDiff int,
@log bit Output
)
AS
set transaction isolation level read uncommitted
Declare @count as int
set @log = 1
select @count = count(*) from tbllog a, tbllogdetail b
where logtypeid = 2
and serverID = @serverID
and A.logid = b.logid
and b.errorID = @errorid
and a.logtime > dateadd(mi, -@timeDiff, getdate())
If @count > 0
BEGIN
set @log = 0
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
February 15, 2007 at 3:21 pm
Hm, doesn't seem to be a reason for that... Have you looked at sys.sysprocesses? You can check there to see what your spid is blocking. Also, it will tell you the CPU usage, and for the blocked processes you can see the wait type. It could be an issue with waiting for physical resources, not locks. Although I usually see that only with large table variables or temp tables...
Here, I've hacked up a script to look at the number of blocked spids, CPU usage, and what sql the spid is currently running. I tossed object_name() in there as well, but that will only work if you run this in the same database as the object being executed by the spid you're checking. Note that the sql and object name are the actual object, not the root object. That doesn't seem to apply to your instance, but if you use this elsewhere, note sprocA calling sprocB will be reported as
select
cnt as Blocked_spids, cpu, object_name(objectid) as name, substring(fn.text, stmt_start/2, (stmt_end - stmt_start) / 2) as sqlcode
from sys.sysprocesses sysp
cross apply ::fn_get_sql(sysp.sql_handle) fn
left join (select blocked, count(*) as cnt from sys.sysprocesses where blocked <> 0 group by blocked) blockcnt on sysp.spid = blockcnt.blocked
where spid = 86
Rick
townsends.ca
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply