Configuring, Benchmarking and Validating an I/O Subsystem for SQL Server, Part 1

One of the main projects I have been working on lately is designing and implementing a completely new data and storage infrastructure for a high volume, 24×7 online environment. Some of the goals of this effort include improving OLTP performance, improving scalability, reducing the number of SQL Server processor licenses required, and improving the overall HA/DR story for the system as a whole. So, nothing too difficult to accomplish at all…

The starting point for this is to figure out how many, of what type of database servers that we need, along with the type and configuration of the required I/O subsystem. In order to do this, you need to have a pretty good idea about the type and volume of the workload you will be dealing with, whether it is more of an OLTP workload, DW/DSS workload,or a mixture of the two. The workload characteristics will have a direct impact on the processor type, server model, and type of I/O subsystem that you select.

I have argued many times in the past that the latest two socket machines have more than enough CPU, memory, and I/O capacity to handle many SQL Server OLTP workloads. If this is the case for your workload, you have the potential to reduce both your hardware costs and SQL Server processor license costs, along with the benefit of achieving better single-threaded OLTP performance than is possible with a more conventional four socket database server.

For an OLTP workload, I really like the Dell PowerEdge R710 two socket, 2U server.  It allows you to have two Intel Xeon 5600 series (32nm, six core, Westmere-EP) processors. It has an Intel 5520 chipset, and eighteen DDR3 DIMM slots (with a total capacity of up to 288GB of RAM). It has four PCI-E Gen2 expansion slots (two x8 and two x4), along with an x4 Storage slot. It also has eight 2.5” internal drive bays. This server model has been available for a couple of years now, with a good track record. If you decide to use a server that supports an Intel Xeon 5600 series processor, the exact processor you want is the 3.46GHz Intel Xeon X5690, which is their top-of-the-line model.

 

Figure 1: Dell PowerEdge R710 Server

 

Starting in about ten days, I am going to get the opportunity to configure, test, and benchmark the I/O performance of several different types of storage devices (including both DAS and internal storage) that will be attached to a shiny, new R710 server (with two Xeon X5690 processors and 192GB of RAM). I will have two Dell PowerVault MD1220 SAS enclosures, two Dell PERC H800 1GB RAID controllers, a 640GB Fusion-io Duo card, and (32) 300GB Intel 320 MLC SSDs to try out in various different configurations.

There are a number of different configurations to try here. For example, the H800 RAID controller supports a new feature called CacheCade. This lets you use up to two SSDs as a read-only cache in front of a number of conventional magnetic SAS drives in RAID arrays controlled by each RAID controller. I want to measure what effect this has on read performance. Another option to test is the best configuration for which controllers go in which PCI-E slots. I have two x8 slots, and two x4 slots, so I am planning on putting the 640GB Fusion-io Duo card in one x8 slot, one H800 in the other x8 slot, with the other H800 in one of the x4 slots.

The overall objective here is to come up with the “best” standardized configuration available with the combination of all of these devices. I want to have a standard, identical configuration for drive letters, with a specific purpose and level of I/O performance (both IOPS and throughput) for each drive letter. You need to consider where the OS and SQL Server binaries will be located, where the OS page file will be located, where the SQL Server data files will be located, where the SQL Server log files will be located, where the TempDB files will be located, and finally, where the SQL Server backup files will be located.

You need to decide what RAID level to use, and how many spindles to use for each RAID array, along with the type of drive (SSD or conventional magnetic SAS). You also need to balance size vs. performance. I think this will be a lot of fun, and I plan to blog quite a bit about the different combinations that I try during this effort.