See the question and my original answer on StackOverflow

I suggest you have a deep look at the super cool SQL Server's Dynamic Management Views feature:

Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.

This article is a good start with DMVs, although it was written for SQL 2005 (DMVs feature first appearance): Troubleshooting Performance Problems in SQL Server 2005, especially the 'blocking' chapters.