Last week, I encouraged you to identify and address the root causes of performance problems instead of addressing superficial symptoms. I used this question, found on the SQL Server newsgroups, to set the stage for advice on targeting problems rather than symptoms:
"Which should give better SQL Server performance: a server with a single-processor 1.6GHz Pentium 4 processor with 1GB of RAM or a server with dual 700MHz Pentium III processors with 2GB of memory?"
Many readers responded that they loved the advice on solving problems instead of symptoms but that they'd still like to know which server would be faster—the server with two processors or the server with a single processor running at a higher clock speed.
These questions made me realize that I hadn't fully addressed a major caveat to performance tuning: The answer to almost any performance-tuning question is, "It depends."
The original question didn't provide many specifics about the workload that the server would be running, but the answer to a question like this always depends on what the server's application workload will be and a host of other variables. As I pointed out last week, treating the underlying problem requires a solid understanding of the database workload that your application generates.
I'm too crafty to say definitively which server would be faster, because someone could quickly point to a case where the advice would be wrong. But here are some guidelines to help you evaluate which server would be best for a typical workload.
First, note that the cost difference between a dual-processor machine and a single-processor machine isn't that significant. You'd be surprised how many times clients have paid as much for a detailed performance study to help them decide which server they need as they would have spent by simply upgrading to the faster server in the first place. Don't get caught in a trap like that. The pressure to justify a purchasing decision can be tremendous, but keep the cost of research in mind.
Now, let's look at two extremes and see which server would be faster in each scenario. Application A is an online transaction processing (OLTP) application that takes an average of 50ms to run each transaction and needs to support hundreds of concurrent users. In this case, a dual-processor server would be much faster. Clock speed is almost meaningless for transactions that are this fast to begin with, but the ability to service multiple requests at the same time provides higher levels of concurrency and throughput.
In contrast, Application B, a data-warehousing application, usually supports only a few users, but those users run very complex queries. In this case, the extra throughput that two processors provide isn't needed. You might be better off with a faster processor that can execute the complex queries more quickly. Unfortunately, life is never this simple. Even if you had only one query running at any time, SQL Server might be able to create a parallel query plan that allows the query to be executed across two processors at the same time.
There's no getting around the hard work of understanding your application and its workload, then testing your hypotheses. Anyone who tells you otherwise has oversimplified the situation.
