Solid State Disks and SQL Server

  • ian-707364 (4/8/2010)


    Look at the price of a regular SAN with regular disks. It still can't compete on price! I did a price/performance comparison of a single MSA70 (25 disks) to an EVA3000 - it was 8x cheaper per MB/s. And that's a really cheap EVA. As you move to bigger SANs, the cost per MB/s gets higher still - they're not designed for high performance service levels to an individual server.

    i don't know about EVA, with with higher end EMC SAN's you get some nice software, and the ability to replicate data on a disk level to a DR location. and the build quality and support is top notch. if you have a bad part, you won't know until you get a call from EMC telling you to be onsite for a delivery in a few hours.

    I deal with HP support for servers and a backup tape library. overall it's pretty good, but sometimes i have to waste time taking a server down to run diagnostics, switch out memory to different slots, CPU's to different slots, etc.

  • Alen,

    i would like to see a test comparing SSD's with a MSA 70 full of RAID1 volumes instead of RAID10.

    If I get the time and can free up an MSA I'll see if I can cut up our DB and give it a try.

    On the EMC/SAN thing. You do get lots of perks but you do pay for them. If you are looking for speed and are pricing $/IO or $/GB the Fusion-IO expander chasis starts looking real good.

    Nadrek,

    I didn't simulate a failure scenario since we weren't able to completely finish our testing cycle.

    http://www.sqlserverio.com
    http://www.cactuss.org
    http://www.salssa.org

  • Also, we will be posting up a revised version of the article minus the typo's, grammatical errors and formatting issues.

    http://www.sqlserverio.com
    http://www.cactuss.org
    http://www.salssa.org

  • I was talking about the cluster size for formatting. However, as I understand it, currently most drives physical cluster size are 512 bytes with some new drives at 4096 supported in the latest version of Windows.

    Surely the Fusion IO cluster size refers to the raw IO block size to be written to SSD as an atomic unit? For SQL Server this should be 8192. Or am I missing something?

  • a little off-topic but maybe someone can answer this for me

    in windows 2003 and later on basic disks i run diskpart and i can create the 64k offset for performance reasons. with dynamic and GPT disks it never worked for me. i looked in the help section but never found much useful info on the difference between the three, except that basic disks are there for backward compatibility. or why the 64k offset would not create on the dynamic of GPT

  • Great article. I've been wanting to test the Fusion I/O cards not having the budget for it I appreciate your work and the article. I'm a little disappointed to hear that it takes so much ram.

    Someone referenced Brent Ozar testing tempdb? Does anyone have a link to that?

    ---------------------------------------------------------------------
    Use Full Links:
    KB Article from Microsoft on how to ask a question on a Forum

  • Firstly, as an aside, you don't have to worry about the offset with Windows Server 2008 and Windows 7 onwards - basic disks on these versions default to a 1MB offset, so unless your stripe size is bigger than 1MB, there'll be no problem.

    Coming back to diskpart in previous versions, neither GPT nor Dynamic disks support the "ALIGN =" for custom offsets. That's why you should use Basic disks for SQL Server Data/Logs/Backup

  • ian-707364 (4/8/2010)


    I was talking about the cluster size for formatting. However, as I understand it, currently most drives physical cluster size are 512 bytes with some new drives at 4096 supported in the latest version of Windows.

    Surely the Fusion IO cluster size refers to the raw IO block size to be written to SSD as an atomic unit? For SQL Server this should be 8192. Or am I missing something?

    Drives have sectors, file system have clusters. So, most drives have a 512 byte sector. Newer drives, some SAN's and the Fusion-IO have a 4096 byte sector option. That is the smallest unit that can be written to the drive. If you have a small file say 1024 bytes it will still take up a 4096 byte sector or two 512 byte sectors depending on the sector size. You are confusing sectors with file system cluster sizes. This has nothing to do with atomic writes to disk though. SQL Server can do a write as small as a sector or as large as the whole disk if need be. Atomic writes are handled by the OS and SQL Server.

    Alen,

    GPT partitions are aligned via offsets If you want to align a GPT disk you need to start at an offset of 129MB the GPT disk will automatically round to the nearest sector and don't support the align= for that reason. There are some structures that come with GPT that use the 129MB you reserved in the offset space.

    http://www.sqlserverio.com
    http://www.cactuss.org
    http://www.salssa.org

  • thx

    the reason i've used dynamic disks for SQL is to expand the array. every year larger hard drives come out and instead of buying a new MSA70, you can just buy larger hard drives, replace them one by one or add to an existing array if you have empty bays and then expand the logical drive. On Windows you would need to create a new drive, or if you have a dynamic disk you can just expand the existing volume

  • I've sent an email to my contact at Fusion IO - hopefully we'll get some definitive answers on sector size in relation to their cards

  • Has anyone here tried SQL Server on something like the ram drives from Texas Memory Systems? They aren't persistent when the machine is off like SSD's but they're supposed to be even faster.

  • ian-707364 (4/8/2010)


    I've sent an email to my contact at Fusion IO - hopefully we'll get some definitive answers on sector size in relation to their cards

    The definitive answer is 512 bytes to 8096 bytes user configurable in the 2.0 driver which isn't out yet. MLC native is 4k.

    Windows supports up to a 4096 sector.

    http://support.microsoft.com/kb/926930 for SQL Server supported sector sizes.

    http://www.sqlserverio.com
    http://www.cactuss.org
    http://www.salssa.org

  • magarity kerns (4/8/2010)


    Has anyone here tried SQL Server on something like the ram drives from Texas Memory Systems? They aren't persistent when the machine is off like SSD's but they're supposed to be even faster.

    Yep. They are crazy fast with huge IO numbers 400k or more random IO's a second. They are persisted to internal disks when the TSM disk is off and have battery backups built into the unit. They are also crazy expensive. TSM also has a PCIe card out but I've never seen it or read any real reviews on it.

    http://www.sqlserverio.com
    http://www.cactuss.org
    http://www.salssa.org

  • Nice article. Thanks for sharing.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • That strategy is working out well and giving us some head room to plan for our next round of servers where we can properly plan and size them with Fusion-IO in mind. Also, they have also announced an updated driver 1.3 that is due out soon. It reduces the memory consumption by as much as 10 fold over the current driver. With that and a 4KB block size memory pressure should be a thing of the past for them.

    What is your source for "it reduces the memory consumption by as much as 10 fold over the current driver"? Are you trusting the vendor to deliver? Or have you beta-tested the driver to confirm their statements are true?

Viewing 15 posts - 16 through 30 (of 64 total)

You must be logged in to reply to this topic. Login to reply