Speaking from experience which is humble but I think worth sharing, the major bottleneck with SQL databases (Sybase and SQL server here) is storage.
But I think it's only fair that someone first benchmarks their setup before making any wrong assumptions. In my case, CPU usage has never risen high enough to justify upgrading the CPU any time soon. Instead, I have upgraded from single drive to RAID 1 and then to RAID 10 + a bump from 8GB to 16GB of RAM.
All these RAID upgrades helped to reduce previous wait times by a factor of 2 to 6. I suspect upgrading to SSDs would be even better. If you think about it, it can all be reduced to (theoretical) bandwidth. Your [(RAM Speed + RAM Size + Memory controller) link to CPU] combo has a bandwidth limit which would be the most important factor in read operations when your data should always be cached, your particular (RAID) storage has a bandwidth limit (affects reads when cache missed and writes when flushing or with many clients writing lots of data combined).
Normalize all those limits as much as possible (bring them closer such that you don't have wasted resources) and raise them as much as possible (upgrade where needed and only if needed, don't let resources become wasted if the system won't be able to use them because some other bottleneck is in the way). In the end, your worst bottleneck will be the least performing (with least bandwidth) server subsystem in your particular configuration.
I might also add that, in the process of upgrading, I have created separate RAID configurations for the database files and the database log files. The reason was that database log files tend to be write intensive. A log file is used to recover a database from a crash and it's always written to immediately as a transaction is committed before any data is written to the database file.
A log file is also used by some database replication servers but most replication is not done instantly but frequently so the read performance impact is minimal here. At least I think so. I have done minimal benchmarking while making this upgrade so again, I recommend anyone first benchmarks their different configurations and first upgrades their storage, then RAM and network link, before thinking about upgrading their CPUs.
After more extensive upgrades on more than 5 servers, I came back to share my experience. I definitely still advocate for first upgrading storage, then RAM and then CPU. Reason is the disparity of bandwidths in the system between storage, RAM and CPU, in order of lowest to highest. So I upgraded a bunch of servers from RAID10 and dual RAID1s to SSDs.
The way I did it because of cost concerns (I have 20 more servers to upgrade) is to move the database data and objects files to an SSD (yes, just one SSD in RAID0) and move the transaction log plus tempdb to a 4xHDD RAID10 configuration. I tested with the tempdb on the SSD as well with great results (in fact very nice results with more than 15 times faster queries sometimes, yielding some reports taking seconds instead of minutes in the past) but later moved the tempdb to the disk RAID10 because of intensive write-wear concerns for the SSD.
So now basically I have observed 10-15 times faster response times per some of the longest queries. The SSD is great for reading data into RAM fast because SQL Server does not bring data into RAM until requested to and of course data first needs to be loaded into RAM to be processed by the CPU (later, in L1,L2,L3 cache), so SSDs help reduce that initial wait time by a huge factor. And SSDs also help reduce swapping times... clearing out RAM and loading new data, especially if your database is larger than can fit into RAM.
All in all, we're very pleased and been running like so for several months in a sort of slow migration process to allow the servers to run so I can collect wear-level information before I switch all my servers to this configuration. And this is just SQL Server Express! :D - Just make sure your SSDs can provide constant IOPS because that's another thing that makes a huge difference (just google it). That's why I chose Intel DC (DataCenter) series SSDs.
Best Answer
The general rule of thumb is keep the core count as low as possible, and the processor speed as high as possible. The licensing math on that proves the point at ~$7,500 USD per core for Expensive Edition.
Buying the correct hardware can pay for itself in reduced licensing costs. See Processor Selection for SQL Server by Glenn Berry. It's a great resource on how to choose a processor for SQL Server.
Once you take into consideration SQL Server's per-core licensing structure, it makes sense to always go with the fastest processor speed available, regardless of workload type, whether OLTP or analytics. Having the fastest possible core speed is never going to be a problem. Increase the core count as required, but never do that by reducing core speed.
In other words, don't think of 16 x 2.2Ghz processors being the same as 8 x 4.5Ghz processors. The cost savings of using the 2.2Ghz processors over the 4.5Ghz processors is likely to be a maximum of about $10,000 USD (for a typical Xeon-based two processor machine). Jumping from 8 cores to 16 cores with SQL Server Enterprise Edition is likely to cost over $60,000 USD in licensing fees. In other words, you might save $10,000 in hardware costs, but you'll lose an extra $50,000 in licensing.
If you decide you need a lot of parallel processing muscle, and decide you need 32 cores for the task at hand, going with the fastest cores will pay dividends in reduced processing time. Nobody will fault you for that.
Having said all that, if the choice is one CPU or more than one CPU, always go with more than one. Running SQL Server (or any DBMS) on a single CPU can cause all kinds of problems since the capability for concurrent operations is vastly limited.