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

Configuring the Server
The three key resources on the server are: Memory, I/O, and CPU.

Let's start with memory. It's easier to add or remove memory (RAM) than it is to change the number
or type of processors in a server. SQL Server 2005 even supports adding additional RAM to a
server without rebooting (we call it hot-add memory, but more on that later). When you initially con-
figure the server, you should have some idea how much memory you might need, and this may even
determine the type of CPU you purchase. On 32-bit systems, for example, there are a number of switches
you can add to boot.ini that can affect the memory configuration. If you've never seen boot.ini before,
it's a hidden file on the root of your boot drive, usually c: \ .HereisanexamplefromWindowsServer

2003 Enterprise Edition:

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1) \ WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1) \ WINDOWS="Windows Server 2003, Enterprise"
/noexecute=optout /fastdetect /PAE /3GB

/PAE enables the operating system to use more than 4 GB RAM and /3GB allows you to increase the
virtual memory address space for applications at the expense of the kernel. You'll look at these switches
plus some others in more detail later, so don't worry if it sounds strange at this point.

In many ways I/O performance is perhaps the most important part of the server configuration to get
right. The reason for this is that everything you do lives on the disk. All the code you run in the operating
system, SQL Server, and any other applications, starts off as files on the disk. All the data you touch in
SQL Server lives on the disk. It starts out life there, gets read into memory, and then has to be written
back to disk before it's a permanent change. Every change you make in SQL Server is written to the SQL
log file, which lives on disk. All these factors make a good I/O configuration an essential part of any
SQL Server system.

I/O configuration is a big enough subject that it really requires a topic all of its own to cover. We're going
to introduce you to some of the options available and then walk through several scenarios to provide
some insight into how to make the right storage configuration decisions.

There aren't a lot of options to play with when dealing with CPUs, so you need to make sure that you
make an informed decision when choosing them to begin with. SQL Server 2008 will enable ''hot-add
CPUs'' (similar to the ''hot-add memory'' support in SQL Server 2005) but the feature requires specialized
hardware to work. Ironically, this means you'll still need to plan to eventually need more CPUs.

Before you go any further, put each of the three server resources back into perspective in terms of their
relative performance. The following is an outline that's relevant to today's systems. As systems change
at some time in the future, you can easily pencil in the current state of processor, memory, and I/O
performance to see how the relative speeds of different pieces have changed.

Typical speeds and throughput for system resources are:

A CPU speed of 2 GHz results in 8 GB/Sec

❑

Memory speed of 500 MHz results in 2 GB/Sec

❑

A reasonable single hard disk results in 40 MB/Sec

❑