Tuesday, March 16, 2010

SQL Saturday #33 Recap

SQL Saturday #33 was held on Saturday, 3/6, at the Microsoft campus in Charlotte. Hosted by the Charlotte SQL Server Users Group, this was the first time for a SQL Saturday in the area and I think they did a fantastic job! Peter Shire (Twitter) and Greg Gonzalez (Blog | Twitter) put a lot of hard work and effort into making this a can't-miss event by enticing speakers from around the country to come present (including 14 MVPs), securing a great facility, and landing the backing of a great group of sponsors.

Speaker Dinner
The speaker's dinner on Friday night started with a social hour at the SQL Sentry office. Hands down they've got the coolest lounge I've ever seen at an office – an XBox 360 hooked up to a ginormous screen with surround sound and a full set of Rock Band instruments on one end and a fridge full of frosty beverages on the other (for after hours relaxation, of course!). I spent most of the time at the social talking shop with Chris Skorlinski (Blog | Twitter), a Replication guru at Microsoft.

Dinner was a short walk around the corner and easily the best speaker's dinner I've ever been to. SQL Sentry sponsored and spared no expense. My favorite part about speaker dinners is the chance to catch up with friends who I only get to see a few times each year. The day of the event is always hectic and this is the one chance to get everybody in a room together at the same time. Worth the price of the plane ticket from Orlando, easily!

The Main Event
Saturday started with a keynote featuring Steve Jones (Blog | Twitter), Andy Warren (Blog | Twitter), and Rushabh Metah (Blog | Twitter) discussing the past, present, and future of the SQL Saturday brand. I presented at the first ever SQL Saturday in Orlando back in 2008 and really appreciate the opportunity it's provided for me to meet other SQL professionals and grow as a speaker. I also know how much time and effort Andy's put into getting it where it is today so I feel a sense of pride for Andy that it's grown (I think) into what he was hoping it would become.

The first of the two sessions I presented was Getting Started In Blogging and Technical Speaking (slide deck here). I counted 12 non-presenters in the room plus five bloggers\presenters (three of which I asked to sit in to help add perspective). This is an interactive session by design and despite some initial hesitation from attendees there were a lot of questions asked and some great back and forth discussion. It's also satisfying to find out that two people who came to my session decided to blog about their day here and here.

My second session, The (Solid) State Of Drive Technology (slide deck here), was less popular than I thought it would be. I was scheduled in one of the larger rooms that held 50 people but only 12 people showed. Maybe interest in solid state drives isn't there like I thought. Nevertheless, this was a first-time presentation for me and I thought it went well. I run an SSD in my laptop so it made for a cool demonstration to reboot and have the everything reloaded in less than two minutes. If nothing else I probably convinced a few people to go out and buy an SSD for their desktop\laptop!

I finished out the day by going to Kevin Kline's sessions on SQL Server Internals and Top 10 Mistakes, then hit the after party with a few other speakers before heading to the airport to catch my flight back home to Orlando.

Random Thoughts

  • Speakers love feedback. Unfortunately there were no speaker evals handed out. I put my 1st session up on SpeakerRate but how many people will take the time to go there after the event if it's just a so-so session? (And those are the people we want to get feedback from the MOST because we want to know what we can do better!)
  • Kudos for healthy menu choices during breakfast and lunch – the fruits, veggies, and fresh food were a step up from donuts and pizza.
  • Lunchtime lacked a buzz for some reason. It seemed like people didn't have a lot of encouragement to try and meet new people and network. With as many speakers as there are at these events I think a "birds of a feather" style lunch seating would work really well here – give people the chance to sit with subject matter experts and ask questions, exchange contact information, etc.
  • I noticed the Charlotte crew learned from SQL Saturday Orlando last year and had large printed poster boards with the schedule on them placed everywhere. For those that didn't have a printed copy of the schedule (me) they really helped.
  • Charlotte is home to a large contingent of Product Support Services teams, and throughout the day there was a walk-in clinic in the cafeteria for anyone with a problem. Great idea, though I don't know how many people took advantage of it because I didn't see it promoted in the pre event announcements.
  • There were a few books donated for the end-of-day raffle on display at the registration table after lunch. In the 15 minutes I spent at the table talking with someone no less than 3 people asked if they were for sale. Publishers & SQL Saturday organizers take note: there's an opportunity there! Partner up and feature books by speakers that are there that day, offer a day-of-attendance discount, etc.
  • Speaking of the raffle, I would have liked to have seen a lot of smaller items offered. Big ticket items are cool, but I think there's more satisfaction in having a lot of people walk away with smaller things than a few people walk away with something big. I know, it's a free event and you get what you pay for so it's hard to complain about not winning anything.
  • I'll say it again – for a first time SQL Saturday Peter, Greg, et. al did a great job putting this event together! If they do it again next year I'll definitely try to make the trip again!

Tuesday, March 9, 2010

Solid State Drive Performance Hands-On: Part 1

(Punch it, Hurb
Yo, I don't think we should talk about this
Come on, why not?
People might misunderstand what we're tryin' to say, you know?
No, but that's a part of life)

Let's talk about S-S-D
Let's talk about you and me
Let's talk about all the good things
And the bad things that may be
Let's talk megabytes\secs
Let's talk megabytes\secs

OK, I'm no Salt-N-Pepa, but now that I've got your attention (maybe even made you laugh?) I wnt to spend some time talking about Solid State Drives (aka SSDs). This is the first in a series of posts about the state of SSDs as they relate to the SQL Server landscape today. Here's what I'm going to cover:

Part 1: Introduction and SSD Fundamentals
Part 2: Fusion-IO ioDrive
Part 3: OCZ Z-Drive
Part 4: SSD Performance Shootout
Part 5: Are SSDs Right For You?

So let's get started!

Traditional hard drives (HDDs) have long been one of the biggest (if not the biggest) bottleneck in database performance. At the 2009 PASS Summit Dr. David DeWitt asserted during his keynote that disk performance has gotten slower in relative comparison to the improvements made in CPU and memory over the last 30 years. So much slower, in fact, that some of the smartest people on the planet are spending their time trying to figure out how to store data differently to get around the disk bottleneck. He mentioned that SSDs will really change our lives and provide "the only real hope" for fixing the disk performance issues.

Unfortunately one of the biggest problems with SSDs is that for years they've been extraordinarily expensive – so expensive that only the likes of the military have been able to afford them. The good news is that they're finally coming down to a price point that even SMBs can swallow. Today you can purchase SSDs in new laptops and the storage technology behind SSDs exists in mobile phones, MP3 players, and USB thumb drives.

But are SSDs a good match for SQL Server? Before I can answer that question, I'll start at the very beginning with a look at how SSDs work.

Form Factor
SSDs come in one of four form factors: SATA, PCI Express, SAS, and Fibre Channel. Generally you'll find SATA drives in notebooks and desktops ($), PCI Express drives in servers ($$), and SAS & Fibre Channel drives in SANS ($$$). PCI Express based drives are usually not bootable and require some sort of driver or software install before they can be used by the OS. Also, PCI Express based drives contain their own controller card; as such if you want to put these into a RAID solution you're limited to a software based RAID.

Storage – SLC vs. MLC
Unlike HDDs which have a lot of moving parts, SSDs are all the same at heart: a bunch of NAND flash chips and no mechanical components. Data on flash memory is stored in individual memory cells made of floating gate transistors. Today's memory cells are capable of storing either two or four states. Cells that store two states are called Single Level Cell (SLC) and hold one bit (e.g. 0 or 1). Cells that store four states are called Multi Level Cell (MLC) and hold two bits. Because SLC stores less data per cell than MLC it not only costs more to manufacture but requires a larger physical footprint to store the same amount of memory as its MLC counterpart so consequently you pay (a lot) more for an SLC based SSD.

Write Endurance
NAND flash has a limited number of times that it can be written to before it fails. SLC based flash have a lifetime of approximately 100,000 writes per cell and MLC's lifetime is around 3,000-10,000 writes per cell. A technique called wear levelling is used to avoid burning out a cell by ensuring that writes take place evenly across all cells on the SSD. A simple example is a least recently used (LRU) queue, but every manufacturer has their own wear levelling "secret sauce" to maximize the life of their drive.

Eventually cells do fail for one reason or another, so to counter this manufacturers add extra capacity to their drives that isn't available to the OS. For example, if you buy a 120 GB drive there may actually be 144 GB of capacity (e.g. 20% extra) on the board. This is known as "over provisioning".

Space Management
When data is deleted from an SSD it isn't really deleted – it's just marked as not in use…in order to reuse the space safely the SSD needs to be told it's OK to overwrite. This is referred to as a TRIM command, and prior to Windows 7 & Windows 2008 R2 implementation of TRIM was left to each SSD manufacturer (Win7 & 2008 R2 now support this as part of the OS). Some implementations required massive amounts of RAM, and in some cases very undesirable things could happen to performance (or to the drive itself) if there wasn't enough RAM available. TRIM is a background process and not something that you need to be concerned about running manually.

Now that you know the fundamentals of how SSDs work, let's talk performance. In part 2 of this series I will take a look at how the ioDrive, a PCI Express based solution from Fusion IO, handles SQL Server activity.

Note: This post is part of T-SQL Tuesday #004 which is focused on IO this week. Follow the link to check out the other IO related posts from some great SQL bloggers!