Optimizing the Server


The rest of this chapter covers optimizing the server. This includes the hardware and operating system configuration to provide SQL Server with the best environment in which to execute. The three key resources that you should consider anytime you discuss optimization or performance are the CPU, memory, and I/O.

Starting with the CPU, there aren't a lot of options to play with here other than the number and type of processors. The only real configuration option to consider once the server is mounted in the data center is whether it is hyperthreading-enabled - that is, should you turn hyperthreading on or off? Other than helping you make the right decision about hyperthreading, most of this part of the chapter is focused on understanding the different processor attributes so you can make the right purchasing decisions.

Memory has more options, and it's a lot easier to add or remove memory (RAM) than it is to change the number or type of processors in a server. When you initially configure the server, you should have some idea of how much memory you might need, and understand the available configuration options. These options are primarily for non-uniform memory access (NUMA) systems. On a NUMA system, you will often have the choice of using cell local memory or interleaved memory. On most 32-bit systems today, a number of boot.ini settings can affect memory configuration. These are settings such as enabling Physical Address Extensions (PAEs), which enables the operating system's kernel to use the PAE version of the kernel, which in turn can take advantage of an additional four address lines on Intel processors that support PAE.

/3GB and /USERVA are boot.ini settings that alter the way the operating system configures the 32-bit address space. I/O is, by far, the largest and most complex section of this chapter. In many ways I/O performance is perhaps the most important part of the server configuration to get right because everything you do lives on the disk. All the code you run in the operating system, SQL Server, and any other applications start off as files on the disk. All the data you touch in SQL Server lives on the disk. Data starts out life there, is read into memory, and then has to be written back to disk before it becomes 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 actually too big a subject to cover in one chapter; it really requires a book of its own. This chapter introduces you to some of the I/O options available, and then walks through several scenarios to provide some insight into how to make the right storage configuration decisions.

Before we start, it will be helpful to 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 you refer to this book in the future, you can easily pencil in the current start of processor, memory, and I/O performance to see how the relative speeds of different elements have changed.

Typical speeds and throughput for system resources are as follows:

  • A CPU speed of 2GHz results in 8GB/sec

  • Memory speed of 500MHz results in 2GB/sec

  • Disk speed of 5 to 200MB/sec

Use these numbers to do the math for throughput for a 10GB table:

  • A 2GHz CPU with a throughput of 8GB/sec would access 10GB of data in 1.2 seconds.

  • 500MHz memory with a throughput of 2GB/sec would access 10GB of data in 5 seconds.

  • Disks with a throughput of 5MB/sec would access 10GB in 33 minutes.

Graphically, this might look something like what is shown in Figure 11-3.

image from book
Figure 11-3

Unfortunately, I can't draw the disk small enough, or the CPU big enough, to make this illustration to scale given the relative difference between CPU speed and disk speed. The conclusion here is that disk access is much slower than memory access, which is slower than the CPU. The key, therefore, is to get as much data as you can into memory, and then as much of that onto the CPU as you can.

With SQL Server, there isn't much you can do to alter how much of your data is being operated on by the CPU; that's controlled by the developers who wrote SQL Server. What you can do, though, is specify the processors with larger cache, and a higher speed. Add more memory at a faster speed, and design your storage subsystem to deliver the fastest performance possible within your requirements for speed, size, and cost.



Professional SQL Server 2005 Administration
Professional SQL Server 2005 Administration (Wrox Professional Guides)
ISBN: 0470055200
EAN: 2147483647
Year: 2004
Pages: 193

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net