Choosing and Configuring Hardware - Professional SQL Server 2005 Performance Tuning - page 144

Memory
Memory very often drives the overall performance of a system and lack of it often creates a greater
perceived performance impact than the lack of any other resource. Lots of spare memory can also mask
bottlenecks in other areas so it's important to know how your memory is being used before looking at
anything else. This is covered in Chapters 2 and 3.
EffectonI/O
Lack of physical memory has a greater effect on I/O than anything else because paging activity will be
high, which means high disk activity. Obviously, when you notice your I/O throughput reducing in this
scenario, the wrong approach to take would be to buy faster disks.
EffectonCPU
The goal for CPU usage is to spend as much time as possible executing in user mode. Kernel mode rep-
resents time spent working on system processes and not your application, so a lack of physical memory
will cause high CPU utilization in kernel mode because the server has to work harder to manage what
little memory it has. Again, the wrong approach to take would be to buy faster CPUs, but could be easily
done if you only looked at CPU utilization in isolation. User mode and kernel mode are covered in more
detail in Chapter 2.
Best Practice
Memory is very cheap these days so you shouldn't have any servers with less than 2 GB
of RAM. Having 4 GB of RAM or more requires a bit of thought as to how to configure
the server to use it and this is covered in the ''Configuring the Server'' section later in
this chapter.
I/ Disk I/O is the next most likely resource to be a bottleneck on your server and is particularly important
to SQL Server because its job is to serve data from a database stored on disk. Identifying disk bottlenecks
is covered in Chapter 2, how to configure your storage for optimal performance is covered in the ''Con-
figuring the Server'' section of this chapter, and Chapter 10 details how to benchmark and fully test your
I/O subsystem.
CP CPU can often be driven by the other two subsystems, so a bottleneck in memory or disk I/O will very
often manifest itself as a CPU bottleneck. This is why you should analyze memory first, then the I/O
subsystem, followed by the processor. As discussed above, lack of memory will drive the CPU and I/O
subsystem, and I/O operations account for a significant percentage of kernel mode CPU time.
Best Practice
Buy multi-core processors with large amounts of cache. This is discussed in great detail
in the next section.