SQL Server Questions Answered

Why Doesn’t New Hardware Enhance SQL Server Performance?

Q: We recently implemented a new server with faster CPUs, more cores, and double the amount of memory. We restored our production database to it and performance was worse than on the old server—so bad, in fact, that we can’t use the new server. We checked the database and configuration settings and they’re the same on both servers. The OS and SQL Server versions are identical, as is the storage system. What’s going on?

A:
First off, I should say that moving to newer, better hardware doesn’t always guarantee a performance boost for a workload. Here are some generalizations:

  • If the workload on the old server is such that all the required data fits in memory, adding more memory likely won’t greatly increase performance.
  • If the workload on the old server is such that any query operations fit in memory and don’t spill into the tempdb database, adding more memory likely won’t greatly increase performance.
  • If performance on the old server doesn’t have a bottleneck in the I/O subsystem, moving to a more powerful I/O subsystem won’t greatly increase performance.
  • If the old server isn’t suffering from CPU contention, moving to a server with more processor cores might not provide a performance boost.

These generalizations are common sense, but many people still equate moving to a server with more resources to a guaranteed performance boost. One thing you would expect, though, is that moving to a server with faster CPUs should make CPU-intensive operations faster. So why is it that sometimes this isn’t the case or performance is actually worse, with all other configuration factors being equal?

The cause is likely to be power-saving modes making the CPUs run much slower than their advertised clock speeds. Depending on the CPUs, they might not kick into the highest clock speed until a significant load is placed on the server (e.g., 50 percent CPU load or more). This can drastically affect the performance of a workload, especially if CPU contention wasn’t a problem on the old server.

You can check whether the CPUs are operating at a reduced speed using the free CPU-Z tool. You can also look at the Windows Server power management profile to see if it’s configured to anything except the highest performance. For more information, check out my blog post “Are your CPUs running slowly? Tool tip and survey" and the Microsoft article “Degraded overall performance on Windows Server 2008 R2.”

One of the first things I would check in your situation is to see if a power-saving mode is enabled. You might be amazed at the performance difference after turning off power-saving modes. One other thing you can do is remove SQL Server from consideration completely and test the raw computing performance of the server using a free tool such as Geekbench.

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish