January 28, 2009 at 9:58 am
Hi
This sounds like an easy one, but I'm damned if I can find a way.
SQL 2000 SP4. I want to insert a row in a table every time a blocking lock occurs.
Can it be done? And if so, how? sp_trace will allow me to record where a lock is acquired or released, or where a timeout occurs following a blocking lock, but I want to know when a blocking lock occurs, even if just for one second.
??
Brian
January 28, 2009 at 10:49 am
check out this link
http://support.microsoft.com/default.aspx?scid=kb;en-us;271509#kb2
It explains how to write blocks to a file but im sure the process can be modified to write to a table. It creates a store proc, sp_blocker_pss80, which outputs blocking info when it occurs.
January 29, 2009 at 5:17 am
Hi
Thanks for the response. I'd seen this, however it doesn't solve the problem. Microsoft suggest that you use that proc to periodically check for blocks - however I want to do the opposite. I want the occurrence of a block to trigger code that records the event.
With Microsoft's suggestion, you
1. Have the overhead of this proc running constantly, checking for blocking locks and 99% of the time not finding any.
2. Will miss blocks that occur between polls.
Anyone any idea how to get the block itself to trigger code?
Brian
January 29, 2009 at 8:26 am
You could setup a trace on the blocked process report event and have that trace logged to a table. This event doesn't trigger immediately but only after the process has bee blocked for a specific amount of time which is set with sp_configure. If you set the threshold very low you should get alot of the blocks or at least the problematic ones. You will incur some overhead of the trace running.
January 29, 2009 at 8:34 am
Err, you could set up a notification in performance monitor but becareful of those monitors, I heard they leave a nasty bite.
Max
January 29, 2009 at 8:46 am
Does that exist in SQL2000?
January 29, 2009 at 8:46 am
Sorry - this forum doesn't show quoted text for some crazy reason. I meant, does the blocked process report event exist in SQL 2000?
January 29, 2009 at 9:35 am
Unfortunately the blocked process report event does not exist in 2000. You are posting in the 2005 form so even though, looking back, I see you stated you were on SQL2000, didn't take it into consideration when suggesting answer. Unfortunately, at this point, I am out of ideas. If you were to use the stored proc I suggested above and run it in a very short interval you would catch blocks that are affecting users. If there is blocking for a short amount of time couple sec or less that shouldn't be something to be to concerned about.
I guess a question that should have been asked is, Why do you want to log all blocks?
Err, you could set up a notification in performance monitor but becareful of those monitors, I heard they leave a nasty bite.
there doesn't seem to be a perfmon counter for blocks, only locks
January 29, 2009 at 9:55 am
Hi
Thanks for your answer. Running any proc at short intervals is a desperate waste of resources surely seeing as how 99% of the time it will find nothing?
Also, sorry about the posting - I'm not used to these forums, but noted for future reference 🙂
Your question regarding why I would want to record short blocks is a good one! My reason is that I am measuring stats on run times of stored procedures, and if one is blocked for, say 2 seconds by another proc, and this happens frequently, then the information would be very useful in trying to performance tune an SP that takes 3 seconds and should run in less than 1, but sometimes does for no explainable reason. [All examples above are hypothetical to protect the innocent] I can think of several reasons why this *might* happen, but I would like to know whether or not it *is* happening. Get it?
So basically I'm not trying to trap blocks so that I can prevent blocks, I'm trying to record blocks so that I can take them into account when gathering stats on queries that need tweaking. If an SP always runs sub-second I am happy. If an SP takes on average between 1 and 4 seconds, it could be because it's frequently blocked by a frequently running process for 2-3 seconds, and unless I know which process does the short-term blocking, I'm barking up the wrong tree.
Although the more I think of it, if a block does trigger my code, I'd still have the issue that I don't know how long the block continued for. Sigh.
January 29, 2009 at 10:22 am
Robert klimes (1/29/2009)
...Err, you could set up a notification in performance monitor but becareful of those monitors, I heard they leave a nasty bite.
there doesn't seem to be a perfmon counter for blocks, only locks
Indeed, I believe they're called deadlocks in the SQLServer:Locks object on a sql2k server? I thought they were also referred to blocking locks?
Max
January 29, 2009 at 10:29 am
Deadlocks and blocks are different. Blocking locks can become deadlocks after time. But I just need blocks!!
I'm stunned that I can't capture this data without polling, which is just a no-no.
January 30, 2009 at 11:04 am
Some of this code I got off of here. I run this every minute on my server I was able to to track down a lot of unnecessary locking and eliminate quite a few problems within a few months. My locking and deadlocking problems are almost nil during the day
CREATE TABLE
--------------------------------------------------------------------------------------
CREATE TABLE [dbo].[BlockingLocks](
[spid] [smallint] NULL,
[dbid] [int] NULL,
[ObjId] [int] NULL,
[IndId] [int] NULL,
[Type] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Resource] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Mode] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Status] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lastwaittype] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[waittime] [int] NULL,
[hostname] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[program_name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cmd] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[loginame] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cpu] [int] NULL,
[physical_io] [int] NULL,
[memusage] [int] NULL,
[BlockDateTime] [datetime] NULL,
[BlockClearTime] [datetime] NULL,
[ClearedFlag] [bit] NULL
)
CREATE PROCEDURE TO POPULATE TABLE
-------------------------------------------------------------------------------------
CREATE procedure [dbo].[sp_Identify_Blocking_Locks]
@MinuteMonitorINT
as
/*
*/
begin
declare @cntBlocks int
set noCount on
-- create a temporary table to hold current locks status reported by sp_lock
Create table #CurrLocks
( spid smallint,
dbid int,
ObjId int ,
IndId int,
Type varchar(4),
Resource varchar(16),
Mode varchar(8),
Status varchar(5)
)
-- populate temp table
insert #CurrLocks exec sp_Lock
select distinct b.spid
INTO #DeleteLockInfo
from BlockingLocks a join #CurrLocks b on a.ObjId = b.ObjId and a.dbid = b.dbid where b.Mode like '%X%' AND a.clearedflag = 0
DELETE FROM #CurrLocks
WHERE spid IN
(select spid from #DeleteLockInfo)
UPDATE B SET clearedflag = 1, BlockClearTime = GETDATE()
FROM
BlockingLocks B
WHERE
clearedflag = 0 AND SPID NOT IN (SELECT SPID FROM #DeleteLockInfo)
Insert BlockingLocks
select C.*, s.lastwaittype,waittime,hostname,program_name,cmd,loginame,cpu,physical_io,memusage, GETDATE(), NULL, 0
from #CurrLocks c
inner join master.dbo.sysprocesses s
on s.spid = c.spid
where Mode like '%X%'
-- Count locks older than X amount of minutes
SELECT
@cntBlocks = count (objId)
FROM
BlockingLocks
WHERE
clearedflag = 0
AND DATEDIFF( mi, BlockDateTime, GETDATE() ) > @MinuteMonitor
Drop table #CurrLocks
DROP TABLE #DeleteLockInfo
return (@cntBlocks)
end
CREATE AND SCHEDULE JOB WITH THIS CODE
--------------------------------------------------------------------------------------
DECLARE @cnt INT, @Msg VARCHAR( 200 )
EXEC @cnt = sp_Identify_Blocking_Locks 4
SET @Msg = 'There are currently ' + CAST( @cnt AS VARCHAR(10)) + ' locks older than 4 minute(s) on the server'
IF @cnt > 0
BEGIN
EXEC master.dbo.sp_send_cdosysmail
'email@email.com'
,'email@email.com'
,'Locking Issues On SERVER'
,@Msg
END
ELSE
BEGIN
PRINT 'No locking problems'
END
DELETE
FROM
DBADMIN.dbo.BlockingLocks
WHERE
DATEDIFF( d, BlockDateTime, GETDATE() ) > 7
January 30, 2009 at 11:57 am
Someone give me some insulin 'cause this is soo sweet.
Max
February 2, 2009 at 4:23 am
Hi
Thanks a lot for the code. It looks like polling is the only way in 2000, which is unfortunate, but manageable I suppose 🙂
Thanks to everyone who offered suggestions and tips here!
All the best
Brian
February 2, 2009 at 8:17 am
I have one suggestion that wouldn't involve polling. What about adding the check if process is blocked and timing inside the stored procs you are trying to time. You would have to account for the extra time in that stored proc for the blocking check and you wouldn't want to leave it in there permanently, but it could get you the info you want to know.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply