Glenn Berry's SQL Server Performance

Back in April of 2011, I wrote a post that compared the merits of using an ancient, but actual rack mounted server versus a new desktop based system for testing and development work. It is pretty common for old retired Production server hardware to be passed down for use in Development and Testing environments. In an ideal world, you would have a Test environment that was an exact match from a hardware perspective for your Production environment, but I have never seen this actually happen due to budget constraints.

The basic argument is that the typical two or four socket server from several years ago would have much less CPU capacity in almost every case and would sometimes have less memory capacity than a decent, new single socket desktop-based system. The limiting factor in both cases will more than likely be I/O capacity and performance. Since I wrote this post nine moths ago, a few things have changed, so that seems like a good reason to revisit the subject.

Your three main good desktop platform choices right now are an older 45nm Core i7 “Bloomfield” system (using a Core i7 960 processor) with an X58 chipset, a newer 32nm Core i7 “Sandy Bridge” (using a Core i7-2600, 2600K or 2700K processor) with an H67 or Z68 chipset, or an even newer 32nm Core i7 “Sandy Bridge-E” (using an Intel Core i7-3930K processor) with an X79 chipset.

The older 45nm Nehalem-based Core i7 system has six memory slots, so it can support 24GB of RAM using 4GB DDR3 RAM sticks. It will have plenty of CPU performance and capacity for most development and testing purposes (more than many older four socket rack mounted production servers), and you should not have any driver issues with Windows Server 2008 R2. The newer 32nm Sandy Bridge Core i7 system only has four memory slots, so it can currently support 32GB of RAM (with 8GB DDR3 RAM sticks). The Sandy Bridge system will have about 50% more CPU capacity than the Nehalem system.  Both the venerable Bloomfield and newer Sandy Bridge have four cores, plus hyper threading so you will see eight logical processors in the operating system.

The newest 32nm Sandy Bridge-E system can have eight memory slots, so it can support up to 64GB of RAM with 8GB DDR3 memory sticks (which currently cost about $70 each). The Core i7-3930K processor has six cores, plus hyper threading so you will see twelve logical processors in the operating system. It is basically the desktop version of the upcoming Sandy Bridge-EP Xeon that is supposed to be released in April for the two socket server space. The Sandy Bridge-EP will have up to eight cores. Of course the Sandy Bridge-E uses a different processor socket than the older Sandy Bridge. Sandy Bridge-E motherboards are about twice as expensive as Sandy Bridge motherboards, and the Core i7 3930K processor is about twice as much money as the Core i7 2600K. It does not make much sense to spring for the top of the line Core i7-3960X processor in this context.

The other big variable that is changed is storage. Due to the disastrous and tragic flooding in Thailand, there is an ongoing shortage of traditional hard drives that is predicted to continue for quite some time. It has been difficult to even buy traditional hard drives, and the prices have at least doubled in many cases. This means that you might want to look a little more seriously at springing for some SSD storage, assuming your budget and space requirements will let you go down that route. If you decide to build your own desktop system, you can buy a larger tower case that has room for a lot of drives inside (anywhere from six to twelve). You can also pick a motherboard that can support a large number of SATA devices, plus you can supplement it with additional PCI-E SATA controllers. You will want to get a decent power supply with lots of SATA power connectors (which you can supplement using splitters and Molex to SATA connectors).  Whatever you do, don’t hobble your shiny new desktop test server by trying to run it with just one or two 7200 rpm SATA drives. The more drives you can afford, the more I/O performance you can support, by splitting your data and log files on different drives, having multiple data files on different drives, having your backup files on different drives, having TempDB on a separate drive, etc. Just like a real database server!

One new SSD choice that I am pretty excited about is Intel’s 520 series. It uses the high performance SandForce 2281 controller that Intel has spent nearly a year tweaking for better reliability. AnandTech has a good review of it here. The 180GB size seems to be the sweet-spot for price/performance.