Enterprise Storage Bench - Microsoft SQL WeeklyMaintenance

Our final enterprise storage bench test once again comes from our own internal databases. We're looking at the stats DB again however this time we're running a trace of our Weekly Maintenance procedure. This procedure runs a consistency check on the 30GB database followed by a rebuild index on all tables to eliminate fragmentation. As its name implies, we run this procedure weekly against our stats DB.

The read:write ratio here remains around 3:1 but we're dealing with far more operations: approximately 1.8M reads and 1M writes. Average queue depth is up to 5.43.

Microsoft SQL WeeklyMaintenance - Average Data Rate

Again, huge gains from the 520 on a 6Gbps interface. Moving over to a 3Gbps interface, all of these drives basically perform the same thanks to the 3Gbps SATA limitation.

Microsoft SQL WeeklyMaintenance - Disk Busy Time

Microsoft SQL WeeklyMaintenance - Average Service Time

Enterprise Storage Bench - Microsoft SQL UpdateDailyStats Measuring How Long Your Intel SSD Will Last
Comments Locked

55 Comments

View All Comments

  • Anand Lal Shimpi - Wednesday, February 8, 2012 - link

    There's a *ton* of data that we manage. We run statistics, ad serving and forums all in house. Among other things, we can guarantee that no one funny is looking at the data we manage.

    Statistics are pretty beefy (they are one of our enterprise workloads after all) as we're tracking requests to all articles published. Couple a few hundred thousand readers per day with multiple article requests per reader and that's a lot of traffic to keep track of. Multiply all of that by a few ads per page and you can see where ad serving/tracking gets insane.

    Then there are the forums. Repeat the same workload as above but across a different, but also quite large community.

    The MS SQL server is main site, the My SQL server is forums + ads :)

    Take care,
    Anand
  • Lord 666 - Wednesday, February 8, 2012 - link

    @Anand,

    What model server and what controller was it using with the qty 8 320 drives? Been waiting for an article like for this for some time.
  • Anand Lal Shimpi - Thursday, February 9, 2012 - link

    The temporary hardware is a Dell R710 I believe. We're simply using Intel's Matrix RAID, no real need for a discrete PCIe RAID solution for what we're doing. I'll be providing more details about our final hardware configuration and how it compares to what we were running on for the past few years in the not too distant future.

    Take care,
    Anand
  • mojobary - Thursday, February 9, 2012 - link

    Hi,

    This is exactly the information I am interested in. I am a video editor, so my needs are typically long sequential reads. I would be interested in RAID adapters, iSCSI and Fibre channel RAID enclosures in respect to using SSDs. This is something that not much good information is present out in the wild. I have been researching this topic for about nine months and do not have conclusive information. Even from vendors which say "they support ssd", they don't list supported drives or even TRIM support. I typically like this site as it seems unbiased in the regard and usually helps me drive purchasing decisions.
  • bobbozzo - Monday, February 13, 2012 - link

    HDDs are pretty good at sequential reads (and writes).

    For the same $, you'd be able to get more HDDs, and therefore higher sequential performance, than SSDs.

    This will remain true until SSDs get MUCH faster sequential performance, or get MUCH cheaper than they currently are.
  • Movieman420 - Wednesday, February 8, 2012 - link

    Given the 710's obvious benefit of using HET nand it'd be nice to see a comparison between it and an eMLC equipped Ocz SF2500 Deneva 2 drive. :evil grin:
  • Anand Lal Shimpi - Thursday, February 9, 2012 - link

    I'm trying to get more enterprise SSDs in house. I've got a bunch that I'm working on now actually. Not the Deneva 2 sadly :)

    Take care,
    Anand
  • Sufo - Thursday, February 9, 2012 - link

    How about the HP for ProLiant drives? Also, anything from Anobit?

    Is the 710 a realistic option for enterprise?
  • zepi - Wednesday, February 8, 2012 - link

    I was hoping on some input regarding TRIM and SSD RAIDs in enterprise environments. What if I stick these babies to a proper raid-controller to run them in RAID 5? Or how about under other operating systems than Windows? Do the drives choke quickly if trim is not available or is it a non-issue? Does trim work in a software RAID array, assuming my operating system supports it? And how about trim / garbage collection behavior if the drives are never idle?

    Afaik Intel has released RAID 0- and RAID 1-compatible drivers that support trim, but only for Windows. Was that active in your test or does it even matter the slightest?
  • lonestar212 - Wednesday, February 8, 2012 - link

    I was about ask exactly the same thing. Very curious about this!

Log in

Don't have an account? Sign up now