Professional SQL Server® 2005 Performance Tuning

6.2. 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 configure 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:\. Here is an example from Windows Server 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 ...