I've helped a lot of people upgrade their SQL 2000 applications to SQL 2005. While many of these upgrades go well, occasionally some applications have issues and the overall workload may perform more poorly after the upgrade.
One area where I've seen issues is with ad-hoc on-line transaction processing (OLTP) systems. This class of application performs a relatively large number of simple compilations. Often, many of the resultant plans are almost identical and would never change - i.e. there's not many possible execution plans for these queries. While we make efforts to make sure that the upgrade experience is positive, major releases that take 5 years have a lot of variables that can change. This can change the "sweet spot" for getting optimal performance out of an application.