Unfortunately, SQL servers aren't generally scalable. Offloading joins and aggregation onto (inexpensive) app servers can increase over all system performance, despite lack of advanced query planning.
Bollocks. SQL scales fine unless you are stupid in the way you use it. All the crap performance I have seen in the last couple of years have been from doing crap like you suggest - doing joins and aggregation at the application level. Our front page currently makes over 1000 database calls because of this sort of nonsense.
