Recently I've been having a number of performance related issues with SQL Server 2005.
I was finding it hard to figure out why I had so many processes stuck in AWAITING COMMAND status.
After extensive googleing I came across this little beauty: http://www.sommarskog.se/sqlutil/aba_lockinfo.html
Basically, Erland Sommarskog has created a stored proc that will get you a list of current locks happening in your database. There's a range of information given back showing you all sorts of useful data to help you identify to cause of the locks.
Process Id and if it is locking other processes or process ids with a reference to the process that is blocking it. CPU usage and memory usage, what application, command or SP the problem is coming from along with a load of other measurement it would have taken me months to do on my own.
This sp has helped me. I hope it can help you!