SQL Server has a number of decent options available for robust logging of deadlocks. It doesn't, however, have much of anything in the way to let you know when threads are just bumping into each other (as opposed to grappling each other by the throat -- i.e. deadlocking). That being said, resource contention CAN be an issue on a highly-trafficed system.
Here's a little script I've created that lets me 'capture' locks, and output details about what is causing the lock. There's nothing terribly elegant about this script. But it does serve it's purpose very well (I've been able to use it a few times to optimize performance on high-throughput systems).
Here's the script: (Warning, see the caveats below. The main point of this script is that it creates an 'infinite loop' that just keeps querying the sysprocesses table until it sees a block. When it does so, it captures the lock info and spits it out)
SET NOCOUNT ON
DECLARE @loop bit
DECLARE @spid varchar(10)
DECLARE @blocker varchar(10)
DECLARE @s int
DECLARE @b int
DECLARE @locked table (
spid int,
blocked int)
SET @loop = 1
WHILE @loop = 1 BEGIN
WAITFOR DELAY '000:00:00.003'
INSERT INTO @locked
SELECT spid,blocked
FROM sysprocesses WHERE blocked <> 0 OR spid IN (SELECT blocked FROM sysprocesses)
IF @@ROWCOUNT > 0 SET @loop = 0
END
DECLARE lc CURSOR FAST_FORWARD FOR
SELECT CAST(spid as varchar(10)),CAST(blocked as varchar(10)) FROM @locked
OPEN lc
FETCH NEXT FROM lc INTO @spid,@blocker
WHILE (@@FETCH_STATUS = 0 ) BEGIN
SET @s = CAST(@spid as int)
SET @b = CAST(@blocker as int)
PRINT '-----------------------'
PRINT 'SPID: ' + @spid + ' BLOCKED BY: ' + CAST(@blocker as varchar)
PRINT 'SPID ' + @spid + ' INPUT BUFFER: '
DBCC INPUTBUFFER(@s)
EXEC('sp_who2 ' + @s)
PRINT 'BLOCKER (' + @blocker + ') INPUT BUFFER: '
IF(@blocker <> 0) BEGIN
DBCC INPUTBUFFER(@b)
EXEC('sp_who2 ' + @b)
END
ELSE
PRINT 'NA'
FETCH NEXT FROM lc INTO @spid,@blocker
END
SELECT * FROM @locked
SELECT GETDATE()
CAVEATS:
1) This script just outputs gibberish unless you've set your output options to Text (ctrl+T, or Query > Results in Text).
2) You'll need to play with how frequently this script runs. I've set it here, in the example, to run every 3 milliseconds. On production systems I frequently run it at every 1 millisecond (after ramping up to see if the CPU can handle it). In other words, boxes I've been on have been able to handle this script without only a 5-10% increase in CPU. Your Mileage may vary, so please use responsibly.
Do you have it output to a database, or a text file? Wouldn't that get huge pretty fast?
Posted by: Jon Sharp | March 30, 2004 at 12:24 AM
Actually, the routine will just sit there in a loop until it sees a block. If no blocks occur, it just keeps looping and outputs nothing (this will 'burn up' a bit of CPU (as per my warnings/caveats). If it detects a block, it fires off a few statements that grab the last queries by the blocking/blocked PIDs, outputs them, and then it's done. I'll probably work on making a bit of a cleaner build in the future, so that it won't be dependant upon the formatting provided by QA in Text Output mode.
Posted by: Michael K. Campbell | March 30, 2004 at 12:30 AM