The Effect of Hardware on SQL Server Performance

Many DBAs begin to tune a poorly performing database system at the hardware level. This is not always the best idea – most of the issues I’ve found in performance are in code, not in hardware components. For instance, if you add a good index, the system doesn’t have to scan an entire table, and so you eliminate a costly scan operation. No hardware change needed.

But that doesn’t mean you shouldn’t have a properly designed hardware system, or make changes in it after you’ve evaluated the code on your system. There are four components you should focus on:

  1. CPU
  2. Memory
  3. I/O
  4. NIC

You notice that I’ve put CPU at the top – most of the time you’ll hear that memory is king in a database. And it is – but a 32-bit CPU uses memory in a different way than x64 or IA64, so in my opinion it’s the first place to start. A close third is the I/O subsystem. Drives are one of the slowest things in your system, so faster drives and a good plan for spreading out the data on them is a great investment.

You might be surprised to see I’ve included the NIC in this list. I’ve run into several situations where the network layer is the very slowest thing on the computer. The point is to evaluate the network card to see if it is overwhelmed.

I’ll show you some specifics for how you can monitor these items in future posts.