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.


Purposeful, Customer-Inspired Design

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.

This entry was posted in Computer Hardware, Processors, SQL Server 2008 R2, Storage Subsystems. Bookmark the permalink.

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

  1. Clark Cruz says:

    Your benchmarking project sounds very exciting for comparing and analysis of current hardware options for a SQL Server host. Some years ago I had the pleasure of working with an ERP implementation and setup Dell servers and an EMC SAN for hosting SQL Server. It will be nice to see what levels of performance your system configurations can reach.

  2. Bender says:

    Looking forward to the series. I just ordered 2 710’s with x5690’s as well. It will be fun to set it up while reading your blog. Let’s race them!

    Scott Newman

  3. Wes Brown says:

    Hey Glen,

    I’m actually testing the next version of CacheCade 2.0 which also allows write caching. I don’t have a spiffy R710 but I do have a bunch of disks and a hand full of SSD’s


  4. Robert Miller says:

    Looking forward to the continuation of this series.

  5. Pingback: Something for the Weekend – SQL Server Links 12/08/11

  6. Jack Vamvas says:

    I’d be interested to hear about your block size settings. For example , if you define 1000 IOPs as the workload requirement , what blcok size would you use?
    For different types of activity , different block sizes are optimal.

  7. Looking forward to hearing your results!

    There’s a faster X5698 processor (2-core, 4.4GHz) that’s also available on the R710. (You have to ask Dell for it, though, according to this paper:

    Two cores might not be enough, but the 4.4GHz would help if you were really running single-threaded stuff.

    • Daniel,
      The X5968 has been estimated at about ~$20000 per CPU from Dell and HP, compared to the X5960 that has a recommended channel price of $1663. Spend that money on memory and I/O instead.

      • Glenn Berry says:

        Yeah, the two-core X5698 is extremely expensive, and the loss in overall CPU capacity (from going from six cores down to two cores) to get that extra clock speed would not be worth it in most scenarios,

  8. Sumeet says:

    This will be very useful for the SQL Server community. Nice going Glenn.

  9. Pingback: Building A New Storage Test Server | SQL Server Input/Output

  10. Alex says:

    Hi Glen,
    We have run a proof of concept along similar lines (SQL 2005, 4 x ioDrive Duo 640 MLC with 48 cores, 256 GB RAM vs. our present SQL server platform).
    Please email me at alex-at-e05-dot-co-dot-uk if you want me to forward you our results.
    I am looking forward to your next post and the results.

    Kind Regards

  11. Richard Wildmann says:

    Hi Glenn,

    Any news here for us? I can hardly wait what findings you have concerning the FusionIO and TempDB.

    Thanks lots,


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s