If you spend any time on the SQL Server Magazine discussion forums or Microsoft SQL Server newsgroups, you'll see a lot of questions about hardware setup and configuration. In fact, many of the performance problems that IT professionals encounter result from simple ignorance of good hardware design. I've spent many years as a DBA and developer, but it wasn't until I was specifically tasked with configuring enterprise-class servers that I learned to resolve many of the key difficulties in building well-tuned SQL Servers from the bare metal on up.
I frequently see IT professionals choose "throw more hardware at it" as their first tuning option; unfortunately, this is often an ineffective way to spend your company's money. I'd like to share several techniques you can use to build a server for peak performance and give you some specific recommendations for the CPU, memory, and disk I/O. Then, I'll give you several recommendations about how to prioritize your hardware options for the best application performance.
CPUs
Testing is crucial to understanding how your server will handle workload. I teamed up with Jim Drover, a performance and scalability expert, to conduct a series of SQL Server OLTP benchmarking tests. After much testing and retesting, we determined that every 10 percent increase in CPU speed generally yields a 6.5 percent boost in SQL Server's performance. Thus, a 3.3GHz CPU will perform about 6.5 percent better than a 2.9GHz CPU. This finding was consistent across different types of CPUs.
Many servers today can support multiple CPUs by using symmetric multi-processor (SMP) architecture. However, SMP servers don't scale linearly. In other words, adding one CPU to a server doesn't yield a 100 percent increase in performance. Instead, each extra CPU adds processing power on a declining curve, starting at about a 40 percent boost for the first CPU and declining to about an average 16 percent increase for every added CPU after the third on an OLTP application.
Motherboard technology has also seen advances over the past several years. However, our testing shows that stepping up to the most advanced front-side bus bandwidth will only improve performance between 3 and 5 percent over the commodity motherboard.
CPU cache. There are three types of memory cache available for CPUs. L1 cache is fixed at 16KB for data and 16KB for instructions, and is on-die, meaning it resides on the CPU chip. L2 cache is off-die, meaning not on the CPU, but is integrated into the processor cartridge and allows memory fetches at the full operating speed of the processor core. L3 cache is also off-die; it's located immediately adjacent to the CPU chip but isn't integrated with the CPU chip. Although this tiny difference in location might seem insignificant, the distance the electrons have to travel for a round-trip memory fetch is an order of magnitude greater in L3 cache than in L2 cache. Thus, L3 cache tends to be much slower, but it can contain a much larger amount of memory because it isn't part of the CPU chip.
Our testing showed that L2 cache improved performance 11 percent going from 512KB to 1MB and an additional 18.5 percent going from 1MB to 2MB. So, a CPU with a 2MB L2 cache offers 31.5 percent better performance than a CPU with 512KB of L2 cache. Most CPUs currently offer an optional 4MB L3 cache. In our testing, an extra 4MB of L3 cache improved SQL Server performance by 39 percent.
One of the most common questions users ask is, "Should I buy a cheaper CPU with a smaller L2 cache or a more expensive CPU with a larger L2 cache?" Complicating this decision is the fact that faster chips usually have smaller L2 caches than slower chips with a larger L2 cache. Here are some basic rules for selecting cache:
- When running one or two CPUs, go with the fastest CPU available even if you have to sacrifice L2 cache size. After that, if you have a choice of L2 cache size, always get the largest you can.
- When running four or more CPUs, go with the CPUs that have the largest L2 cache, even though their speed might not be as great as that of CPUs with the smaller L2 cache. In SMP configurations of four or more CPUs, SQL Server runs better with larger L2 caches.
Hyperthreaded CPUs. An intriguing feature in Intel chips is hyperthreading, which lets one chip process more than one instruction per cycle, in effect creating a second virtual CPU. With hyperthreading enabled, Windows and SQL Server see one physical CPU as two logical CPUs. Intel conservatively estimates that hyperthreading improves performance by 10 to 30 percent, but our testing showed much better performance. With hyperthreading enabled, our tests showed an average 35 percent increase in SQL Server performance at a 1000-user OLTP load and a 46 percent increase at a 500-user OLTP load. This performance boost is great, considering you'd need a non-hyperthreading CPU that's more than 1GHz faster to achieve the same performance increase.
It's important to remember that hyperthreaded CPUs share L2 and L3 cache. Consequently, applications that would benefit from a big CPU cache would likely get less of a performance benefit (although they would still get some) from hyperthreaded CPUs.
Memory
Windows and SQL Server are well known for consuming as much memory as you can throw at them, but is more memory always better? In most circumstances, yes. But you need to consider some caveats to this rule:
- Specifying minimum and maximum RAM rather than letting SQL Server dynamically allocate RAM can boost performance on systems dedicated to SQL Server, because paging is reduced.
- Parallel queries consume more memory than non-parallel queries. It's a good idea to disable the sp_configure setting Maximum Degrees of Parallelism (Max DOP) on your SQL Server unless you've specifically tested and verified a performance improvement by enabling Max DOP.
- Address Windowing Extensions (AWE) usually doesn't work well with other applications and will attempt to consume all the resources it can access, so enable AWE only on dedicated SQL Server boxes.
SQL Server 2000 Enterprise Edition allows up to 64GB of RAM when you're using AWE, but SQL Server can use the memory over 4GB only for data cache. Therefore, you can still hit memory bottlenecks on procedure cache. (Contrast that with 64-bit computing, in which the entire memory space is available for any SQL Server use, such as procedure cache.) Don't forget that other versions of SQL Server support different amounts of memory—for example, the maximum amount of memory SQL Server Standard Edition supports is 2GB.
DISK I/O
In order to properly configure and tune disk I/O, you must understand the fundamental types of redundant array of inexpensive disks (RAID), their benefits, and their drawbacks. The main benefit of RAID is availability; when one disk in a RAID array fails, other disks in the array enable the server to keep running without faltering. The second benefit of using RAID is improved performance. Since RAID arrays comprise multiple disks, you have many disk heads performing reads and writes simultaneously, thus speeding up disk I/O.
The most common types of RAID used in a SQL Server database are RAID5, RAID1, and RAID10. In addition, many enterprises are now deploying storage area networks (SANs), so we evaluated each of those as well.
RAID5. Under RAID5, each write records a data block on a data disk and a parity block on another disk in the array. For reads, RAID5 reads the data block directly and checks the parity block on reads. RAID5 requires at least three drives to implement, as Figure 1 shows. Benefits of RAID5 include one of the highest read–data transaction rates (although only a medium write-data transaction rate). RAID5's low ratio of parity disks to data disks means high efficiency, and the large number of disks gives a good aggregate transfer rate.
RAID5 has some drawbacks as well. Disk failures degrade performance moderately, but the volume can keep running. RAID5 also has the most complex controller design and is harder to rebuild in the event of a disk failure than RAID1. In addition, the individual block-data transfer rate is the same as a single disk.
RAID1. In almost every case, a RAID1 solution is superior to RAID5. To achieve the highest performance with RAID1, you need a good controller card that can perform two concurrent separate reads or writes per mirrored pair. As Figure 2 shows, RAID1 requires at least two drives to implement.
RAID1 has some great benefits. For example, it can sustain one write or two simultaneous reads per mirrored pair. That's twice the read transaction rate and the same write transaction rate as a single disk; furthermore, the transfer rate per block is equal to that of a single disk. Also, RAID1 offers 100 percent data redundancy, so you don't need to rebuild in case of a disk failure, just copy to the replacement disk. Furthermore, RAID1 has the simplest RAID storage subsystem design and can sustain multiple simultaneous drive failures (i.e., one drive failure per mirrored pair).
Unfortunately, RAID1 has a 100 percent disk overhead, since every read or write must be duplicated. And the cost of RAID1 is high because you need twice as many disks.
Prev. page
[1]
2
next page 
