I/O Archives

March 2, 2012

Book Review: SQL Server Hardware

As Ben blogged about this week and I’ve always addressed in my various posts and sessions, DBAs need to know more than just how to make backups and tune queries. This includes the layers under SQL Server, including the hardware. Historically, there haven’t been a lot of resources to help a DBA when it comes to hardware and understanding it from a SQL Server perspective … that is until Glenn Berry’s (blog | Twitter) book SQL Server Hardware (Simple Talk Publishing, 2011). Truth be told, the book has been in my queue for awhile. So is it worth buying or something to skip? Read on and find out.

I don’t envy Glenn. As a fellow author, writing a book on a topic that on the surface seemingly doesn’t cater to as large of an audience as it probably should, you need to really need to think if you’re even going to tackle it. You’ve got to really love it to devote the time and energy. Knowing Glenn personally, I can say he’s definitely passionate about the topic (as I am about all things HA), and it is reflected in this book. As a fellow gearhead, I can relate to his love of hardware.

The book covers most of the topics you’d expect: processor (CPU), memory, disk and I/O, as well as things like the differences in the various Windows and SQL Server versions which will ultimately live on that hardware that is central to this book. Glenn does an excellent job at covering the basics that DBAs and those deploying SQL Server would need to understand. The best part is that it is well written and very easy to read, so even some topics that may be obtuse for “just” a DBA they will grasp with ease. That’s a hard thing to do, so kudos to Glenn there. His experience in this area really shines thorough and he gives some practical advice throughout with real world examples. It’s not like he’s saying to go spend $1,000,000 on hardware and catering to an enterprise audience; that’s a book that would have a very limited appeal and use out there. As is, it’s a book targeted just right if you ask me.

I do wish the book did cover some topics in more detail, had a few clarifications, or included some additional topics. Here’s a quick list off the top of my head:

While the discussion on benchmarks (like TPC) is definitely worthwhile, I would have rather seen content on how to baseline your systems and then how to continually benchmark over time to understand your system changes over time.
Some advice around the politics of some of getting all of this done. That’s a big aspect of any deployment.
More discussion on tradeoffs, and addressing topics like storage capacity vs. I/O needs.
Some HW (esp. older systems) requires that to get the full memory goodness, you need to populate N slots.
There are more levels of RAID than what are presented (and some storage has proprietary). Big it depends there.
A chapter on the importance of testing would be great to compliment the technical stuff.
Putting links to the MS lifecycle charts since they can change. That may have also saved a little space, too.
Windows updates (specifically SPs) are not necessarily cumulative. SQL Server’s are.
How to monitor and more specific performance counters to look at for certain things on an ongoing basis

Honestly, these things really are minor and do not affect the quality of the book at all. I can nitpick because this is a topic I know well. The bottom line is that this book is one of the few – and excellent – references for you to be able to understand what you really need to think about when planning the hardware for your deployments. If you haven’t picked this one up, I highly recommend you do. If you’ve only got a few books on your SQL Server shelf, this should be one of them.

May 5, 2010

SSDs Just Keep Getting Better

I really like my new laptop, the Panasonic CF-S9 from Japan (i5-540, 8GB of memory, 12.1″ widescreen, 8 – 12 hour battery life, under 3lbs – yes, under 3lbs). I don’t always say that as I fall out of love with laptops pretty quickly. Is it perfect? No. I’ve definitely got some gripes with it. Disk speed is not one of them. Stock, it came with the new Toshiba 256GB 2nd generation SSD that supports TRIM (THNS256GG8BBAA). The Toshiba R600 I bought last year has the Toshiba 1st generation 512GB drive (THNS512GG8BBA) which does not support TRIM. One of my concerns in the switch was overall capacity – taking a step back since I have a lot of VMs. The 512GB SSD was pretty fast, and truth be told, all of my laptops since about 2005 or 2006 have all had SSDs. But the one thing anyone who runs VMs knows is that I/O is king. The 256GB that came with the Panasonic was pretty quick, but not the fastest. I say that knowing it gave me a Windows Experience of 6.7 for disk, which is nothing to shake a stick at.

So to expand capacity, my original goal was to add USB 3.0 externally via an ExpressCard on the Panasonic. Unforuntately, Panasonic put a PCMCIA (cardbus) slot in the CF-S9, and even with an adapter, I couldn’t get my USB 3.0 ExpressCard to work. I tried an eSATA PCMCIA card and I got worse than USB 2.0 speeds, so Panasonic’s PCMCIA controller officially sucks. The lack of ExpressCard support and the poor PCMCIA implementation is my biggest disappointment with the Panasonic.

Why all the fuss? I bought the 256GB Crucial C300 CTFDDAC256MAG-1G1 – the new king of the SSD speeds in 2.5″ drives. It would be a waste of money cripple it with USB 2.0 speeds. Thankfully Panasonic didn’t make it near impossible to get the internal drive out (like Sony does with many of their models … speaking from experience, I have a very love-hate relationship with higher end Sony laptops). Just unscrew two screws, and carefully get the drive out.

I used Paragon Partition Manager 10.0 Professional (bought; not a freebie) to clone the Toshiba to the Crucial (4 hours since I was using an external USB 2.0 enclosure), popped out the Toshiba, put in the Crucial, and with fingers crossed, powered on the Panasonic. Success!

I re-ran my Windows Experience, and disk went up to a whopping 7.6. 7.6! Not too shabby. I can tell the laptop has a bit more snap to it as well. The increased write speeds do make a difference. Check out my CrystalDiskMark scores now:

It is not a drive for those who are on the fence about SSD – there are much cheaper options if you want SSD. With SSDs it’s not only about SLC vs. MLC, but it has nearly everything to do with how the SSD manufacturer has optimized its controller. Compared to the original SSDs I was using in the 32GB sizes back on my old Vaio VGN-G1 from Japan, the speeds are amazing to see how far the technology has come.

And I will tell you this as someone with a history with SSDs in laptops – they don’t significantly add to your battery life. Maybe 30 minutes, or at most, an hour, but I’d say about 30 minutes. What you do gain is a lot of silence, have less heat, and I love the fact it has no moving parts so if you accidentally jar your laptop, the drive head won’t fail. The only SSD failure (knock on wood) I had was on my Sony Vaio VGN-Z90 from a couple of years ago which had a proprietary RAID system, and this Crucial is pretty much faster than that.

Hope this helps some of you wondering about SSDs and whether they are worth it. Like anything, it depends. You do get what you pay for. Just because you may get an SSD doesn’t mean it’s going to be better than that traditional hard drive. Like many do in the SQL Server world, they go for capacity over speed and/or reliability. Know what your tradeoffs are.

March 2, 2010

USB 3.0 – I’m a Believer!

My conversion to Windows 7 RTM is complete. The new install took all of 20 minutes, but took way longer to install and configure everything I needed. I even decided to bite the bullet and install Office 10 Beta. So far, so good. The application I’ve used the most is Outlook, and it has one nice feature I really like: the ability to have different Inboxes for different accounts, each mapped to a different .ost or .pst. However, the way they implemented it is slightly annoying: each one needs to be expanded to see the Inbox. I have yet to find a way to see all of the Inboxes with a coherent view so I’m not constantly expanding and shrinking my different accounts. If anyone has an idea on doing things better, click the Contact link above.

Last week I also ordered a 2-port ExpressCard SuperSpeed USB 3.0 adapter. Since my (hopefully) soon-to-arrive laptop only has 256GB of storage (current one has 512GB of SSD goodness; it’s going to become my backup laptop), I need extra storage for my VMs and going back to plain old 7200rpm drives with USB 2.0 just isn’t going to cut it. I was thinking about getting a SSD and putting it in an enclosure, but realized that I’m still bound by USB 2.0. USB 3.0 is fairly new and very few laptops have it built in, so I figured I’d just expand out myself. It can support up to 4.8 Gb (that’s gigabits, folks) per second. That’s obviously a high end, more theoretical limit. USB 2.0 has a high water mark of 480 Mb (megabits) per second rate. That’s quite a difference, even if you get half of the rated speed for USB 3.0. USB 3.0 uses a slightly different connector on the end going into a USB 3.0 enclosure, so while you can’t use your existing USB cables, it is downlevel compatible so you can plug the other end into your computer as you would any USB cable you have now and it’ll work at the speed of your USB port. If you plug the USB 3.0 device into a USB 2.0 port under Windows 7, you see this:

Nice, huh?

If you’re curious, I ordered these from Amazon:
Superspeed USB 3.0 to SATA 3GB/s 2.5″ enclosure
2-port USB 3.0 ExpressCard
The ExpressCard supports up to that 4.8 Gbps, so right now my speed limiting factor is the drive enclosure. I didn’t find too many USB 3.0 ones out there for 2.5″ drives.

I did a quick test with two different drivers – the first, a Hitachi 7K200-200 (HTS722020K9SA00) 7200rpm drive and the Intel SSDSA1MH080G1GN first generation 80GB SSD drive. The Hitachi required the DC5V to USB cable to power it, while the SSD just required the USB 3.0 cable (keep that in mind for your configurations if you are limited on ports). Both drives had similar results:
With USB 3.0, they both achieved somewhere between 60 and 65 MB/s (megabytes in this case) according to Windows 7.
With USB 2.0, they both were only able to get up to about 30 MB/s.

In theory, 4.8Gbps should net me a max transfer of over 600 MB/s, but let’s get real: I’ll never see that on my laptop.

Wow. One simple tweak, 100% improvement in throughput. Right now I’m considering getting either a 256GB SSD drive (which would be expensive, but much lower on power consumption) or the Seagate Savvio 10K.3. The other problem is the heat that will be generated from a 10k drive in a small, non-vented enclosure. Newegg has the Savvio for just over $300 right now. That’s pretty crazy considering what a 100GB 7200rpm drive cost just a few years ago. That’s where the SSD may be a better idea. I’ll figure it out.

Either way, it’s going to be a pretty fast and flexible combination for doing demos and classes on the road. I suspect many of you reading this blog (and again, I apologize that the comment section is down so if you want to respond to this post, use Contact above) are in the same boat as I am, and it’s nice we finally have choices for powerful, lightweight rigs on the road.

Popular Posts

Categories