For mission critical applications high performance database servers are critical, Below are proven and effective tips and recommendations for boosting performance of SQL Server database on Windows Servers. The tips include hardware recommendations and also operating system tips for optimal and high performing server, I have used these tips personally to create high performing applications on top of fast databases. Let me know if you know other tips which can also be useful.
Hardware recommendations and Tips
- Choose 64-bit processors for servers, 64-bit processors have significantly more address space. It is important to match and scale the memory and I/O subsystem with the CPU performance and vice versa. Do not compare CPU frequencies across manufacturers and generations because the comparison can be a misleading indicator of speed.
- Use Intel Nehalem architecture based Xeon processors which are available in Two, four, six, eight, ten, or twelve cores. The core size has increased with 45nm and 32 nm manufacturing process the processors are energy efficient as well. It also supports new point-to-point processor interconnect, the Intel QuickPath Interconnect, in high-end models, replacing the legacy front side bus.
- Choose large L2 or L3 processor caches. The larger caches generally provide better performance and often play a bigger role than raw CPU frequency.
- Choose servers with PCIe buses. Also, to avoid bus speed limitations, use PCIe x8 and higher slots for Gigabit Ethernet adapters.
- USE SAS ( Serial attached SCSI ) or SATA Drives which have RPM higher than 10,000 to reduce random request service times (~2 ms on average when you compare 7,200- and 15,000-RPM drives) and to increase sequential request bandwidth.
- For maximum performance and reliability, the storage controllers should offer RAID capabilities. RAID levels 0, 1, 0+1, 5, and 6 can be used.
- The latest generation of 2.5-inch enterprise-class disks can service a significantly larger number of random requests per second compared to 3.5-inch drives.
- Use Solid state ( flash ) and hybrid drives, to place only the hottest data of a workload onto nonvolatile memory. Tweak and Boost Windows 7 slow speed & performance using SSDs
- Avoid consolidating small drives into fewer high-capacity drives, which can easily reduce overall storage performance. Fewer spindles mean reduced request service concurrency and therefore potentially lower throughput and longer response times (depending on the workload intensity).
- Choose network adapters which support 64bit and DMA. Choosing a high performing network adapter is very critical for high network performance.
OS recommendations and Tips
- Use 64Bit OS and 64Bit Database server for utilizing multi core and multi processor power efficiently.
- Go for windows 2008 Server R2 which improves overall performance a lot because of improved TCP Stack performance. It has Transport offload engine, Receive side scaling for processor parallelism for Web apps, Check out Performance Tuning Guidelines for Windows Server 2008 R2
- Windows 2008 Server also has “NUMA I/O” optimizations which increases the disk performance by streamlining the cache usage. Check this very nice presentation on Windows Server 2008 – Scalability and Performance Presentation
- Place the pagefile and operating system files on separate physical disk drives
- Place the pagefile on a drive that is not fault-tolerant. Note that, if the disk fails, a system crash is likely to occur. If you place the pagefile on a fault-tolerant drive, remember that some fault-tolerant systems experience slow data writes because they write data to multiple locations.
- Use multiple disks or a disk array if you need additional disk bandwidth for paging. Do not place multiple pagefiles on different partitions of the same physical disk drive.
- Use multiple and designated drives for Windows Paging , so if your database is accessing virtual memory it is fast.
- Store “hot” data near the “beginning” of a disk because this corresponds to the outermost (fastest) tracks.
- Use RAMDISK for for paging file to have fastest virtual memory. See Boost up performance of your PC/Server by using Ramdisk-Virtual-Disk
- Deploy at least 8 – 16 GB of physical RAM, if on Intel architecture prior to “Intel® Microarchitecture, Codenamed Nehalem” then use ram with higher bus speed e.g. 1333 Mhz . For Nehalem series Xeon supported motherboard and processors which support new point-to-point processor interconnect ( the Intel QuickPath Interconnect, in high-end models, replacing the legacy front side bus) . The Nehalem implementation uses a full four-quadrant interface to achieve 25.6 GB/s, which provides exactly double the theoretical bandwidth of Intel’s 1600 MHz FSB used in the X48 chipset.
- Configure SQL Server for the Maximum Degree of Parallelism is important for utilizing multiple processors and cores to process parallel queries. Read this guide for extensive details.
Use the Microsoft ® Windows Server ™ 2003 Performance Advisor for identifying and measuring the performance of your server, Learn how to use Performance advisor here.
I hope the above tips and recommendations are useful and helpful in configuring a high performing database server. I will also talk about high performing web and application servers in coming days.