Let's assume you have a limited amount of money to buy the components for a new SQL Server. What priority should you place on the different system components? You can reach a point where adding more hardware of a certain type—for example, RAM or disks—doesn't help because you have in a sense shifted your bottleneck elsewhere. So where do you get the most bang for your buck on a standard OLTP database server?
- First, spend your money on RAM. Memory affects many other subsystems of your SQL Server—for example, by caching more reads and writes to the disk subsystem. After you have enough RAM, spend your money on disk I/O.
- Once you have at least RAID5 installed, the answer is less clear because of the great expense of going to a better RAID, such as RAID1 or RAID10. If money is tight, remember to split as many disk objects as you can onto separate RAID volumes, starting with the user database file and transaction log file.
- Next, add more disks to improve read performance, and add more controllers to improve write performance. Faster disk drives—say 15,000-rpm drives compared to 10,000-rpm drives—provide a linear boost in performance.
- Finally, expend your resources to acquire the fastest CPU you can. Opt for a faster CPU instead of better L2 and L3 cache, unless you're going for a higher-end SMP configuration of four or more CPUs. If you have the money, go for the better L2 cache first and then better L3 cache. Note that L2 and L3 cache make a much bigger difference in OLAP applications than in OLTP applications. Acquire hyperthreaded CPUs on your new server and enable them. Add extra CPUs for improved performance, but remember that the performance increase per CPU drops off steeply as you add more servers.
|
With that, you're armed with the information you need for the next time you go to buy the bare metal to house your SQL Server application.
