Building the ideal Microsoft SQL Server 2012 production server can be a daunting task, with variables such as storage, RAM, or processor selection stumping even seasoned IT administrators. We last posted an analysis around selecting the best Intel Xeon E5-2600 v2 CPU for running SQL Server 2012 in non-virtualized environments. Aside from CPU, RAM is another key component when considering performance needs of SQL running on iron. For this testing we used the same testbed as the Intel processor study, this time focusing on stepping Micron PC3-12800 RAM with the best-performing CPU and storage to gauge its performance impact.
Building the ideal Microsoft SQL Server 2012 production server can be a daunting task, with variables such as storage, RAM, or processor selection stumping even seasoned IT administrators. We last posted an analysis around selecting the best Intel Xeon E5-2600 v2 CPU for running SQL Server 2012 in non-virtualized environments. Aside from CPU, RAM is another key component when considering performance needs of SQL running on iron. For this testing we used the same testbed as the Intel processor study, this time focusing on stepping Micron PC3-12800 RAM with the best-performing CPU and storage to gauge its performance impact.
Traditionally speaking, more RAM for SQL Server offers higher performance. Leveraging additional RAM lowers the I/O footprint on a given storage array and also gives some caching benefit. With everything kept equal we look at how much RAM you can dedicate to SQL Server while still maintaining a low latency profile. In this article we show the performance impact of allocating 128GB dedicated to SQL Server, which is where we currently test devices for enterprise reviews, down to 8GB.
Testbed
For this analysis we're evaluating Micron PC3-12800 DDR3 memory within a Supermicro SuperStorage Server 2027R-AR24NV running Windows Server 2012, which is 2U dual-socket box that supports the E5-2600 and E5-2600 v2 family of Intel CPUs. The system RAM is comprised of sixteen 16GB Micron PC3-12800 DIMMs that remain static for each test. To change the amount of RAM utilized by SQL Server, we change the allocation setting, limiting it to an amount selected for each stepping. We leveraged the 2.6TB Fusion ioMemory PX600 for in-host storage, which has posted very good SQL Server latency numbers (the fastest current-gen product our lab has tested).
Server Configuration
- 2 x Intel Xeon Processor E5-2687W v2 – 25M Cache, 3.40GHz, 8 cores
- Intel C602 Chipset
- Memory – 256GB (16x 16GB) 1600MHz Micron DDR3 Registered RDIMMs (128GB dedicated to system, 8-128GB dedicated to SQL)
- Windows Server 2012 Standard
- 3 x Supermicro SAS3 HBAs (LSI SAS 3008 Controllers)
- 200GB Micron P400m Boot SSD
- 1 x Mellanox ConnectX-3 Dual-Port VPI PCIe 3.0 Adapter (InfiniBand LoadGen Fabric)
- 1 x Emulex OCe11102 Dual-Port 10GbE Adapter
Storage Configuration
- SanDisk Fusion ioMemory PX600 2.6TB
- 80% Over-provisioning in high-performance mode
- Full power draw enabled
- VSL 4.1.1 Firmware/Drivers
Performance
Each specified RAM stepping interval was tested in the same manner: leveraging our SQL Server 2012 OLTP benchmark with the database sitting on the PX600 inside the single-node 2P Supermicro server. As with most storage devices in the same class, we don't worry so much about transaction per second; the emphasis is really on the latency for each of those transactions to complete.
As expected, larger amounts of RAM allocated to SQL server netted higher transactional performance. Between 128GB down to 64GB we didn't see a huge change in TPS. In fact the drop between 96GB to 64GB stayed roughly within the same margin of error. The difference from 16GB to 8GB brought amount the largest change, which tells us that 16GB is probably the minimum useful threshold for this particular test and server configuration.
Focusing in on latency, we see just how well the Fusion ioMemory PX600 maintained its low-latency performance with the 128GB, 96GB, and 64GB results scoring between 2-3ms. The drop down to 32GB bumped up latency around 66%, but the next drop down the 16GB was much more dramatic. That change increased the latency by 200%, which then increased another 153% when the RAM was decreased to 8GB.
While the storage impact wasn't shown as part of this test, each step up in DRAM allocation dramatically decreased the load seen by the storage device that the database resided on. In short, more memory translates into fewer requests hitting storage, instead staying inside system memory. While 128GB of RAM had a background load of 50-100MB/s, 16GB increased to 800MB/s (not during log-write activity which can cause large spikes for any DRAM configuration). With a lesser storage medium these results would be even more elevated.
Conclusion
When putting together a high-performance non-virtualized SQL Server there are many considerations. In the first piece we looked at CPU and its impact on SQL performance. Here we use the best performing in-host storage and CPUs to put RAM to the test. Partnering with Micron we ran the same tests, ranging from 8GB to 128GB of RAM dedicated to SQL. The results are pretty intuitive, with generally more RAM being better in terms of latency. There is a breaking point though where latency sees a dramatic peak in this configuration. At 16GB latency spikes 3X compared to 32GB and at 8GB the impact is even more severe. For best-case performance, 128GB of RAM for SQL with top clock-speed CPUs and leading PCIe storage generate latency of just 2ms.
Selecting the Best Intel Xeon E5-2600 v2 CPU for SQL Server 2012
