Our web app's db server (running SQL Server 2008) is starting to get pounded and so I would like to upgrade it to a better box. We will still probably be going with a hosted solution.
I know that the more RAM the better, but what about the processor? Is it better to have a multicore processor or one that's a little more server-oriented, like Xeon or Opteron?
Yes having a Xeon processor over a standard processor will help with the overall performance of your SQL Server. The more major concern would be your RAM. High-performance RAM will also boost the performance of the SQL Server too. To top off the performance, you would probably want to get 15k rpm hard disks.
Good rule of thumb, the faster the technology (RAM, Processor, and Hard Drives) the better!
Like you said - More RAM the better. But next most important component is HDDs in RAID. Probably the "best" is RAID 0+1, sometimes called RAID 10.
I know it's probably obvious but no-one has mentioned it yet: If possible, go for a 64-bit OS. More memory and is much quicker.
Another thing to consider is licensing. Microsoft charges per socket - not per core. I believe Oracle charges a percentage of a full license per core. In either case, you could save quite a bit of money by going with fewer sockets holding higher core-count CPUs.
All this talk of better hardware, but no one talking about performance tuning your DB. Although I must agree with most of the comments, expecially RAM and HD. More and Faster is always better.