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.

I easily learn something new everyday, sometimes it's even valuable ;). Here's a tidbit I learned only today: When you specify table hints in a query, the WITH keyword isn't required in SQL Server 200. Here's a quote from Books Online:

The use of the WITH keyword is encouraged, although it is not currently required. In future releases of SQL Server, WITH may be a required keyword.

To be quite honest... if I were going to build any sproc, or other production worthy code I'm sure I'd leave the WITH keyword in. But for quick ad-hoc queries where I don't want to interfere with an OLTP Database (i.e. lock it) I'm quite happy being able to do something akin to:

SELECT * FROM pubs.dbo.employee NOLOCK

Instead of:

SELECT * FROM pubs.dbo.employee WITH(NOLOCK)

That may seem like "small potatoes," but in the end, not having to hit the ( and ) does make a tad bit of difference.

As for the warning that the WITH keyword may be required in future versions, I've checked it on the Yukon beta, and so far it works without the WITH keyword as well. Only time will tell if that gets changed.

Admit it, you've been there. You're sitting there working on your computer, and all of a sudden it starts chugging, or you hear the Hard Drive grinding away. A quick ctrl+shift+esc brings up your trusted friend: Windows Task Manager. Within a second you can see how hard the CPU is being hit, and by zeroing in on the processes tab and clicking the CPU column (twice -- which bugs; I wish it sorted descending by default) you can see which processes are burning up CPU.

It's true that there are better tools out there to get an idea of what 'rogue' processes are burning through your resources, but sometimes you don't care to use them (mostly because you're just trying to catch something in the act and don't have the time to start up perfmon, or SysInternals' ProcessExplorer, etc.)

I've been in a number of similar situations with SQL Server. You know something is burning through CPU on the box (cuz you can see it in Windows Task Manager on the Server, and you can see that sqlservr.exe is the culprit). Windows Task Manager won't show you what individual processes/connections etc are doing in SQL Server, because SQL Server implements its own thread scheduling via the User Mode Scheduler (UMS). So, to get an accurate idea of what exact process is burning up your server from within SQL Server, you have to gain access to threading info from the UMS. However, if you go look in Enterprise Manager at the Management > Current Activity > Process Info Node you'll notice that trying to discern what is burning through the CPU on the SQL Server end is virtually impossible as EM sorts them all as strings. Using something like:

SELECT * FROM master..sysprocesses ORDER BY CPU DESC

Is an okay idea, but the problem is that the CPU listed there is a representation of all the CPU accumulated by each PID since it connected, or since the server started operating. Unless you're really quick with numbers and can notice which cpu values are changing, and by how much, each time you hit F5 to refresh your query... you're not going to get very far.

Below is a script I've used with a great deal of success to help me get a very good handle on not only what is burning through my CPU at 'problematic times', but has given me a good insight into just what takes place on my SQL Server at any given time.

The idea for the script is a simple one. Take the wealth of information returned by querying sysprocesses, and give it a point of reference. To put it another way... create a way to query sysprocesses from an explicit starting point. The result is to create a report/query that you can refresh repeatedly which will show you quite well what is burning through your CPU since you last set a 'start point'

I've implemented this through the use of a global temp table, and a temp sproc. Feel free to tweak it in whatever way makes sense to you. The logic involved represents what I believe to be an idiom, even if it does sadly have to rely upon directly querying sys tables to execute. (The querying of sys tables directly is a non-best-practice because the resulting query is NOT impervious to change - though at the time of writing, the following 'query' not only works fine against Yukon Beta 1, but pulls back other exciting information not available in SQL Server 2000)

IF (OBJECT_ID('tempdb..#sp_sql_top')) IS NOT NULL DROP PROC #sp_sql_top
GO
CREATE PROC #sp_sql_top  
 @reset  bit = 0
AS
 SET NOCOUNT ON
 IF (@reset = 1) BEGIN
    IF (OBJECT_ID('tempdb..##sysproc')) IS NOT NULL 
  DROP TABLE ##sysproc
 END
 IF (OBJECT_ID('tempdb..##sysproc')) IS NULL  BEGIN
  SELECT spid,waittime,cpu,physical_io,[memusage],last_batch 
  INTO ##sysproc 
  FROM master..sysprocesses
 END
 -- Remove any recycled spids:
 DELETE ##sysproc FROM ##sysproc t
 INNER JOIN master..sysprocesses s ON t.spid = s.spid
 WHERE t.last_batch < s.login_time
 
 SELECT 
  r.spid,
  r.blocked,
  r.waittime - ISNULL(t.waittime,0) waittime,
  r.lastwaittype,
  r.cpu - ISNULL(t.cpu,0) cpu,
  r.physical_io - ISNULL(t.physical_io,0) phyiscal_io,
  r.[memusage] - ISNULL(t.[memusage],0) [memusage],
  r.cmd,
  r.open_tran,
  CASE 
  WHEN r.last_batch > ISNULL(t.last_batch, GETDATE() -1) 
     THEN r.last_batch ELSE NULL END last_batch,
  CAST(RTRIM(r.hostname) AS varchar(30)) hostname,
  CAST(RTRIM(r.program_name) as varchar(128)) program_name,
  r.nt_username,
  r.loginame
 FROM
  master..sysprocesses r 
  RIGHT OUTER JOIN ##sysproc t ON r.spid = t.spid
 WHERE
  r.spid <> @@SPID
 ORDER BY
  r.cpu - t.cpu DESC
GO
EXEC #sp_sql_top  -- or to refresh: EXEC #sp_sql_top 1

Fire up Query Analyzer and give this query a try. You'll be pleased. Refreshing it over and over will give you a great idea of what kind of things your threads are doing, what they're waiting on, etc.