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.

Discuss this Blog Entry 2

on Jan 1, 2011
Quite often a database system is in tune with the delays to transfer data to/from the disks. With really faster processors, the system may want to write to disk sooner and has to wait for the rotation of the disk(s) to be in the right place. That waiting may cause queuing of the I/O request.

Do the disks have RPS (rotational position sensing) capabilities? Do they have other features such as larger cache and NCQ (Command queuing). Can NCQ be turned off, so that I/O is done FIFO?

Just my thoughts that suggest the I/O system needs looking at.
Leslie in Montreal




on Nov 22, 2013

When it comes to SQL Server performance, many organizations look to their DBAs to help fine-tune hardware configurations, optimize indexes, or weed out poorly written code and queries.
Ref: linear displacement transmitter

Please or Register to post comments.

What's SQL Server Questions Answered?

Practical tips and answers to many of your questions about SQL Server including database management and performance issues.

Contributors

Paul S. Randal

Paul Randal worked on Microsoft's SQL Server team for nine years in development and management roles, writing many of the DBCC commands. Randal was ultimately responsible for SQL Server 2008'...

Kimberly L. Tripp

Kimberly L. Tripp has been working with SQL Server since 1990, and she’s worked as a consultant, trainer, speaker, and writer specializing in core SQL Server performance tuning and availability...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×