You'll see from an earlier post I made back in March 2008 (http://i-like-trains.blogspot.com/2008/03/upgrading-to-microsoft-sql-server-2005.html) that after an upgrade from SQL Server 2000 to 2005 many serious performance problems were encountered.
After a massive amount of testing and research I believe most of the issues are resolved by applying two Microsoft hotfixes.
These cannot be applied individually and are only available as part of a Cumulative Update package. CU Package 7 contains both of these fixes.
However, just installing CU7 will not activate these hotfixes and a two trace flags need to be set in order for any benefit to be seen.
DBCC TRACEON (212,-1)
DBCC TRACEON (4115,-1)
We are in the process of regression testing to make sure that CU7 does not break anything and that these hotfixes resolve most of our most serious performance problems. So far it's looking hopeful.