Keeping an eye on contention
Adam Machanic wrote the everuseful sp_whoisactive that can handle the task. A description of the procedure can be found at SP_WhoIsActive: Get detailed information about the sessions running on your SQL Server system