{"id":693,"date":"2010-05-06T15:01:00","date_gmt":"2010-05-06T15:01:00","guid":{"rendered":"\/blogs\/paul\/post\/Benchmarking-Introducing-SSDs-(Part-1-not-overloaded-log-file-array).aspx"},"modified":"2015-06-22T16:41:21","modified_gmt":"2015-06-22T23:41:21","slug":"benchmarking-introducing-ssds-part-1-not-overloaded-log-file-array","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/benchmarking-introducing-ssds-part-1-not-overloaded-log-file-array\/","title":{"rendered":"Benchmarking: Introducing SSDs (Part 1: not overloaded log file array)"},"content":{"rendered":"

\nWell it's been almost 6 weeks since my last benchmarking blog post as I got side-tracked with the <\/font>Myth-a-Day<\/font><\/a> series and doing real work for clients :-) <\/font>\n<\/p>\n

\nIn the <\/font>benchmarking series<\/font><\/a> I've been doing, I've been tuning the hardware we have here at home so I can start running some backup and other tests. In the last post I'd finally figured out how to tune my networking gear so the performance to my iSCSI arrays didn't suck – see <\/font>Benchmarking: 1-TB table population (part 5: network optimization again)<\/font><\/a>. <\/font>\n<\/p>\n

\nNow I've decided that instead of using a 1-TB data set, I'm going to use a 100-GB data set so I don't have to wait 6 hours for each test to finish, it also means that I can start to make use of the SSDs that the nice folks at Fusion-io sent me to test out (sorry guys – I did say it would be a while until I got around to it!). See <\/font>New hardware to play with: Fusion-io SSDs<\/font><\/a>. I've got two 640-GB cards but I can only install one right now as I need the other PCI-E slot for the NIC card so I can continue to make comparisons with the iSCSI gear without compromising network performance. Then I'll move on to some pure iSCSI vs pure SSD tests. <\/font>\n<\/p>\n

\nI've been starting to discuss SSDs a lot more in the classes that I teach and there's some debate over where to put an SSD if you can afford one. I've heard many people say that using them for transaction logs and\/or tempdb is the best use of them. We all know what the real answer is though, don't we – that's right – IT DEPENDS! <\/font>\n<\/p>\n

\nWhen to consider SSDs?<\/strong> <\/font><\/font>\n<\/p>\n

\nAn SSD effectively removes the variability in latency\/seek-time that you experience with traditional disks, and does best for random read\/write workloads compared to sequential ones (I'm not going to regurgitate all the specs on the various drives – Google is your friend). Now, for *any* overwhelmed I\/O subsystem, putting an SSD in there that removes some of the wait time for each IO is probably going to speed things up and lead to a workload performance boost – but is that the *best* use of an SSD? Or, in fact, is that an appropriate use of hardware budget? <\/font>\n<\/p>\n

\nIf you have an overwhelmed I\/O subsystem, I'd say there are two cases you fall into: <\/font>\n<\/p>\n

    \n
  1. \n
    \n\tSQL Server is doing more I\/Os than it has to because the query workload has not been tuned <\/font>\n\t<\/div>\n
      \n
    1. \n
      \n\t\tYou can tune the queries <\/font>\n\t\t<\/div>\n<\/li>\n
    2. \n
      \n\t\tYou can't do anything about the queries but you can add more memory <\/font>\n\t\t<\/div>\n<\/li>\n
    3. \n
      \n\t\tYou can't to anything about the queries and you can't add more memory <\/font>\n\t\t<\/div>\n<\/li>\n<\/ol>\n<\/li>\n
    4. \n
      \n\tThe query workload has been tuned, and the I\/O subsystem is still overwhelmed. <\/font>\n\t<\/div>\n<\/li>\n<\/ol>\n

      \nI'd say that case #2 is when you want to start looking at upgrading the I\/O subsystem, whether with SSDs or not. <\/font>\n<\/p>\n

      \nIf you're in case #1, there are three sub-cases: <\/font>\n<\/p>\n

        \n
      1. \n
        \n\tYou can tune the queries <\/font>\n\t<\/div>\n<\/li>\n
      2. \n
        \n\tYou can't do anything about the queries but you can add more memory <\/font>\n\t<\/div>\n<\/li>\n
      3. \n
        \n\tYou can't to anything about the queries and you can't add more memory <\/font>\n\t<\/div>\n<\/li>\n<\/ol>\n

        \nIf you're in #1.1, go tune the queries first before dropping a bunch of money on hardware. Hardware will only get you so far – sooner or later your workload will scale past the capabilities of the hardware and you'll be in the same situation. <\/font>\n<\/p>\n

        \nIf you're in #1.2, consider giving SQL Server more memory to use for its buffer pool so it can hold more of your workload in memory and cut down on I\/Os that way. This is going to be cheaper than buying SSDs. <\/font>\n<\/p>\n

        \nIf you're in case #1.3, you're in the same boat as for case #2. <\/font>\n<\/p>\n

        \nNow you might want to start considering SSDs, as well as more traditional I\/O subsystems. But what to put on it? Again, any overwhelmed I\/O subsystem will benefit from an SSD, but only if you put it in the right place.<\/font>\n<\/p>\n

        \nI have some clients who are using SSDs because they can't connect any more storage to their systems and this gives them a lot of flexibility without the complexity of higher-end storage systems. Others are thinking of SSDs as a greener alternative to traditional spinning disks. <\/font>\n<\/p>\n

        \nThe other thing to consider is redundancy. A 640-GB SSD card presents itself as 2 x 320-GB drives when installed in the OS. As you know, I'm big into availability so to start with I'm going to use these as a 320-GB RAID-1 array (not truly redundant as they're both in the same PCI-E slot – but I can't use both PCI-E slots for SSDs yet). I need to make sure that if a drive fails, I don't lose the ability to keep my workload going. This is what many people forget – just because you have an SSD, doesn't mean you should compromise redundancy. Now don't get me wrong, I'm not saying anything here that implies SSDs are any more likely to fail than any other technology – but it's a drive, and prudence dictates that I get some redundancy in there. <\/font>\n<\/p>\n

        \nThe first test: not overwhelmed transaction log<\/strong> <\/font><\/font>\n<\/p>\n

        \nBut which of your data and log files to put onto an SSD? It depends on your workload and which portion of your I\/O subsystem is overloaded. This is what I'm going to investigate in the next few posts. <\/font>\n<\/p>\n

        \nFor my first set of tests I wanted to see what effect and SSD would have on my workload if the transaction log isn't the bottleneck but I put in on an SSD, as much advice seems to suggest. In other words, is the log *always* the best thing to put on an SSD? I'll do data files in the next test. <\/font>\n<\/p>\n

        \nThis is a narrow test scenario – inserting 100-GB of server-generated data into a single clustered index. The post <\/font>Benchmarking: 1-TB table population (part 1: the baseline)<\/font><\/a> explains the scripts I'm using. The hardware setup I'm using is explained in <\/font>Benchmarking hardware setup<\/font><\/a>, with the addition of the PCI-E mounted 640-GB ioDrive Duo. <\/font>\n<\/p>\n

        \nFor the data file, I had it pre-grown to 100GB, on a RAID-10 array with 8 x 15k 300-GB SCSI drives. <\/font>\n<\/p>\n

        \nFor the log file, I had a variety of sizes and auto-growths, both on a RAID-10 array with 8 x 7.2k 1-TB SCSI drives, and on the 320-GB RAID-1 SSD. The log configurations I tested are: <\/font>\n<\/p>\n