For a few weeks, I've been battling performance problems of an application whose database is hosted on SQL Server 2005. The database server has been running at very high CPU utilization for a few months now and we've been slowly ruling things out as the culprit. We haven't gotten to the bottom of it, but we are getting close.
Here's a great article that I stumbled upon recently that goes into great detail on how to troubleshoot performance problems in SQL Server 2005.