Say you’ve got an IBM 3950, a high-end rack-mount server with 4 sockets, 4 cores each (16 cores total) and 64 gigs of memory.  If you’ve got several of these servers, you can connect them together via a daisy chain.  IBM makes a special interconnect cable that plugs into the back of these.  After wiring them together, you can go into the BIOS of each server, do some tweaking, and presto – you have one ginormous server.  If we’ve got four of these identically configured 3950s, that means we now have a server with 64 cores (4 servers x 4 sockets each x 4 cores each) and 256 gigs of memory (4 servers x 64.)  This is what IBM demoed at last year’s PASS Summit keynote – they were running several of these servers daisy-chained together to form one 192-core SQL Server.

When you press the power button on the main node, all four servers boot together – but only the keyboard/monitor of one of the servers is active.  It really functions as one big server.  Data passes back and forth over the interconnect cable to keep everybody marching together.  You install Windows just like you normally would.

From a hardware perspective, though, you have to pay attention.  You have to have exactly the same processors in each node, but what if:

• You don’t have the same amount of memory in each node
• You have IO cards (host bus adapters, network cards, etc) in only one of the nodes
• You start a program that does CPU work on one node, but needs all 256gb of memory (and that memory lives on other nodes)

You’re going to pay a performance penalty whenever you have to send data across that interconnect between servers.  It’s still quick, don’t get me wrong – we’re not talking Ethernet speeds here – but it’s not as fast as when it’s all on the same motherboard.

This is where NUMA comes in – among other things, it helps manage processors and memory to make sure that each processor is using the right memory.  If you’re managing a server with more than 2 CPU sockets, you want to learn the basics of NUMA just to make sure you’re not paying a performance penalty for a simple configuration tweak.  These same problems exist for high-end enterprise-class servers even when they’re not built of daisy-chained boxes, but I like using this example because it’s easier to understand.  I wish I’d have taken more pictures of these when I was working at Southern Wine, because we used ‘em there, and it’s easier to understand when you see it visually too.

I’m on a crappy cell modem connection or I’d blog more links, but if you’ve got good intro links for other readers about NUMA, post ‘em here in the comments.

Why NUMA matters to admins is a post from: Brent Ozar - Too Much Information.
If you'd like to quote this content on other sites, please read how to use my blog's content.

“The first rule of Fight Club is: you do not talk about Fight Club. The second rule of Fight Club is: you DO NOT talk about Fight Club!”

Tom LaRock (Blog – @SQLRockstar) used this line (and many others) to explain what happens at the MVP Summit, and I had to come back to it after my experience yesterday at the Microsoft Certified Master training.

Microsoft Certified Masters get trained on not just the current version of SQL Server, but past versions as well.  Paul Randal and Kimberly Tripp have done a great job throughout of saying, “This covers 2008, but if you’re doing this on SQL Server 2005, then ___ applies, and if you’re using SQL Server 2000 then ___ applies.”  We’ve even talked 6.5 and 7.0.  Before the training, a few readers asked why someone should pay $20k for training that isn’t applicable when the next version of SQL Server comes out.  Sitting in the training, the answer’s really obvious: it’s about answers.  People still ask me questions about SQL Server 2000, especially when I present on DMVs, and it’s so helpful to get the 2008 training in the context of how it became to be the way it is.

During yesterday’s training, someone asked a future-feature question and there was some quick discussion about NDAs.  I feel for Microsoft employees who have to deal with this; all day long, they’re working on projects with tomorrow’s software, but whenever they run into an outsider, they have to stop and ask what the audience’s NDA covers.  I was sitting in the audience armed with information I’ve learned under three different NDAs:

• MVP-level NDA
• Quest Software MS-partner level NDA
• MCM-attendee-level NDA

It’s funny to see who knows what first.  The focus of the MCM isn’t on future versions – it’s tough enough just to get trained on the way things are today – so MCM candidates don’t get the lowdown on vNext.

I hate posting stuff like this because I don’t want you, dear reader, to think that I’m bragging about what I know.  I could go on for days about the things I don’t know that are way more valuable than the things I know.  Bear Grylls knows six ways to kill me with my NDAs and live on them for a week underwater.  If I gave you the choice between listening to the NDA stuff I know for an hour, versus watching a free Kimberly Tripp video on indexing strategies, you should choose the latter.  If that link doesn’t take you straight to Kim’s video, use the right-side menus to search for her under speakers.

SQL MCM Day 2: NDA Club is a post from: Brent Ozar - Too Much Information.
If you'd like to quote this content on other sites, please read how to use my blog's content.

SQL MCM Swag

Okay, maybe not, but day one of my Microsoft Certified Master training for SQL Server was pretty darned cool. I arrived to find my name on a spot in the classroom, plus my very own swag! We got:

• A t-shirt that says “Microsoft Certified Master” – which I refuse to wear before I pass the final exams, or else I’ll be jinxing myself
• A leather notepad that says “Microsoft Certified Master” – which, you might guess, I’m not writing in either
• A USB key that says “Microsoft Certified Master” – but I had to break down and use that because our slide decks and scripts were on there
• A bottle opener that says – you guessed it – “Microsoft Certified Master.”

This is clearly the right program for me because I like to wear clothes, take notes, and drink beer.

Our class of 11 attendees got to interact with Paul Randal (Blog – @PaulRandal) all day. I didn’t use the term “lecture” or “listen” because it was a really interactive session with lots of questions, and I loved hearing some of the attendees launch off with their own answers. This is one hell of a skilled group. Only @SQLSoldier is on Twitter, though, and I was glad to hear Paul talk a little about #SQLHelp. I’m already looking forward to being able to give back to the community with the knowledge I’m gaining here. I had a few “Ah-HA!” moments where I put things together that would have saved me a ton of troubleshooting time in the past.

Unfortunately, I can’t share the slide decks or code samples, but I’d point you to the SQL MCM reading list instead. David Ikeda and Joe Sack have done a great job of compiling that list, and from what I’ve seen so far (and I’ve read ahead in the slide decks), the reading list really reflects the course material. I busted my rear trying to read all of the course material ahead of time, and it really paid off. I can’t imagine trying to keep up if I hadn’t read all of that, and furthermore, I’m going to have to go back and reread some of the C#/ServiceBroker/XML stuff again before next week.

The training proceeds at a breakneck pace. The slides are chock full of bullet points, and normally I’d abhor that, but when the material is this technically complex it makes sense. I’ll be rereading those slides (and Paul’s excellent in-deck notes) over the months to come. And no, I’m not kissing Paul’s rear in an attempt to pass – the exams aren’t administered by the speakers.

We finished up around 6:30PM, and we could have gone longer but some of the attendees were ready to call it a day. I grabbed something to eat, called Erika, and by now (8PM when I’m writing this) it’s time to crack open the virtual books again. The instructors assign optional homework that helps reinforce the day’s lessons. At a glance, I’m betting tonight’s would take an hour or two, but here’s the funny part – with the knowledge I gained today, I’m dying to go spend several hours digging into different servers. There’s things I want to go check on StackOverflow.com’s servers and on my clients’ servers right away. (And no, Paul, it’s not auto-shrink or Instant File Initialization, hahaha.)

Unfortunately, there’s only so many hours in the day. I banged out this blog post while eating dinner, and now it’s time to make some tough decisions. I could:

• Do the optional homework from Paul for tonight – but at least half of it covers things I kinda know, and the other half I’m pretty confident I learned enough today to get me over the hump
• Start digging into tomorrow’s material – which covers areas where I’m not as strong, but I don’t think I wanna tackle it without hearing Paul’s explanations first
• Do the optional homework from David Ikeda (this rotation’s lead) for this week, a group exercise designed to help candidates help each other pass a difficult topic. I know that topic forwards and backwards, and I’m really confident that I don’t need any studying on it whatsoever. However, if I put a couple of hours into it, I’ll be able to help other people pass.
• Start digging into next week’s topics, which are going to be much tougher for me to pass
• Go to bed and get a full night’s sleep before the fun starts again tomorrow

My gut says I need to brew a pot of coffee and do the group homework. Tonight’s the last night I’m spending alone, though – tomorrow after class, I’ll kick start a group study session.

SQL MCM Day 1: The Ides of March is a post from: Brent Ozar - Too Much Information.
If you'd like to quote this content on other sites, please read how to use my blog's content.

I wasn’t all that fond of school, if I’m honest.  My most vivid recollection of high school is that I constantly forgot my locker combination.  I kept having to go to the office for them to remind me, and it got so bad that the secretary knew my combination by heart.  My memories of college consist of playing MUDs and writing English papers about how Metallica songs were a reflection of the Odyssey.  I’d tell you about my memories of the time I tried to go back to college, but I don’t really remember it.  I remember having an accounting class, never opening the book, and getting an A anyway because I’d already done that stuff in the real world by that point.

My home for the next 3 weeks

I didn’t like school because I wasn’t all that challenged, but that definitely isn’t the problem this time.  I’m completely positive I’ll be the least qualified, least experienced guy in the room.  It’s not that I think I’m stupid – I’m pretty darned quick, and I can figure things out with the best of ‘em – but this isn’t the kind of place stupid people go.  Between the fees, airfare, hotel, meals, studying, and 3 weeks of downtime, it costs tens of thousands of dollars to get here.  I’m not a big fish in a small pond here – typical MCM rotations have 10-20 students.  I’ve corresponded with two of ‘em, a production DBA at Microsoft, and an outsider who’s paying his own way.  I can’t imagine how nervous I’d be if I was forking out the money myself.  I keep thanking my lucky stars that I’m privileged to work for Quest Software and that they’re picking up the tab.  My coworkers say that they believe in me, but I warned ‘em that I’ll be calling them in about a week, asking them to remind me how smart I am and how much they believe in me, heh.

I took last week off to finish my preparations – read the last of the prerequisite reading list, get to Inbox Zero, and take care of some tasks around the house.  I also wanted to ease my coworkers into what it’d be like to not have me around for a few weeks.  While I do like to say I have a fake job, I do still have a lot of people who depend on me, and I want this process to go as easy as possible for them.  With that in mind, I set my Out-of-Office reply to be:

I’m out of the office until April 5th attending Microsoft Certified Master training.  I won’t be responding to emails during this time due to the intensive nature of the training.  When I return, I’ll be deleting all of my emails, so if there’s something you still need from me upon my return, please let me know after April 5th.  I know this sounds hardcore, but during the first week of my absence there were already questions about which emails I still needed to address versus which ones were solved elsewhere, so this makes everything easier for all of us.

For SQLServerPedia questions, please email Andy.Grant@Quest.com and Christian.Hasker@Quest.com.

For SQL Server questions, you can get very fast help at http://ServerFault.com for DBA questions and http://StackOverflow.com for developer questions.

As Andy Leonard says, “For emergencies, please call 911, then leave me an email.  I’d love to read about it when I get back.”

Thanks, and wish me luck!

My office for the next 3 weeks - Microsoft Building 40

I had to add that deleting-all-emails stuff in that first paragraph after a few days because I was already starting to get a disturbing number of emails that said, “I got your message, but whenever you get back…”  People were taking the Out-of-Office message as an automatic acceptance of whatever tasks they wanted to send my way – computer questions, webcasts, meetings, etc – and the delete-all-emails puts the burden back on them.  No, that’s not something everybody can pull off, but I live by just saying no, and it’s gotten me this far.

I stocked up on cash because the food vendors on the Microsoft campus don’t take credit cards.  That seemed a little odd to me, so I asked a few of my Microsoft buddies.  Turns out there’s several reasons why you have to pay cash for your lunch:

• Steve Ballmer insists on making a big show at lunchtime.  “Can anybody here break a $10,000 bill?  Anybody?  Damn, guess I’ll have to eat what Connie packed.  Beluga caviar AGAIN?”
• They used to take credit cards, but the portable payment devices ran on Windows Mobile 6.  The cafeteria staff kept quitting in frustration.
• Due to the recession, none of the employees have good enough credit to get cards.
• The campus is run entirely on Microsoft technologies, and SQL Server isn’t secure enough yet for credit card numbers.
• Microsoft’s cafeterias are actually a giant money-laundering operation for the mob.  Their biggest cash cow isn’t Office, if you get my drift.

I’m not entirely sure which one I believe, but there you have it.

Alright, no more funny business – time to get serious, because I’m starting class on the Ides of March.

MCM Day 0: No More Cramming is a post from: Brent Ozar - Too Much Information.
If you'd like to quote this content on other sites, please read how to use my blog's content.

Thing is, we’re not impressed by the stuff we already know.  If I know it, I figure lots of other people know it – because frankly, I’m not a rocket surgeon.  We think we need to craft some amazing, jaw-dropping gem of a T-SQL script that does magical things, or else everybody’s gonna laugh at us.

So whenever I’m writing a new presentation, I remind myself that I gotta stop trying to impress presenters, and focus on impressing attendees instead.  Sure, I’d love to write something Paul Randal or Kalen Delaney would admire, but they’re not the kind of people attending my sessions.

To stay in touch with my attendees, I ask a lot of questions when I’m out presenting.  In my 101-level course at our recent #SQLServerPediaTraining day, I took a poll of the attendees to ask how they currently find out what’s happening on their SQL Servers.  More than half of the audience used sp_who, sp_who2, or Activity Monitor.  These are okay solutions, but there’s such better stuff out there!  My 101-level course introduced people to Dynamic Management Views (DMVs) and Adam Machanic’s excellent sp_WhoIsActive, both of which are free and blow the doors off sp_who2.

The fact is there’s more junior people than senior people.  The For Dummies books sell better than internals books.  If you’re going to write blog posts or presentations, there’s absolutely nothing wrong with delivering information about something that feels very old to you.  The key to being a successful presenter isn’t about delivering the newest information – it’s about delivering information in the newest ways.

Doing a technical presentation involves:

• Assembling the technical content – not necessarily writing it from the ground up, but gathering material that you want to show the audience.  For example, in my 101-level course, I didn’t write a new replacement for sp_who2.  I simply gathered a better set of choices and showed the audience how to get them and use them.
• Crafting a story around the content – if you string a bunch of bullet points together and break them up onto different slides, you’d better have extremely exciting bullet points, or else people are gonna get bored.  Build segues to smoothly transition from one topic to the next.  Have pictures.  Tell a story.  Your presentation should have an identity bigger than just the technical content.  In my 101-level course, I called it Reading the Signs, and I tried to illustrate my points with road signs.  I’ve used themes like race cars, playing doctor, and lolcats.
• Telling that story in front of people – no matter how good your presentation is, if you’re not relaxed when you tell it, people will notice, and they’ll remember it.  Rehearse, rehearse, rehearse.  If you’re going to present at a local event, practice in your company first, delivering that same presentation to your coworkers.  If you’re going to present regionally, practice at a local user group first.  If you’re going to present nationally, practice at a SQLSaturday event first.  Build up or you’ll fall down.

But above all, remember that all of these points matter.  Stop focusing so much on that first bullet point, and realize that you can be a well-respected presenter without the most advanced content.

Time and again, my most popular presentations are the ones that help beginners get started with something that seems really difficult to them.  Attendees rule.

Who’s Your Target Audience? is a post from: Brent Ozar - Too Much Information.
If you'd like to quote this content on other sites, please read how to use my blog's content.

Meetings are like bad blocking queries – they tie up precious resources and stop other people from getting work done.  Today, let’s talk about how to optimize both of ‘em.

Minutes of typical corporate meeting

Do Your Homework Ahead of Time

If you’re going to hold a meeting, build the list of decisions or tasks you want to accomplish during the meeting.  Send out that list ahead of time before you schedule the meeting, and you might be pleasantly surprised at how much work gets done without the meeting, because:

• Some tasks really only need one person to accomplish them
• Some decisions are only made by one executive (without input)
• Some research can’t be done during a meeting
• Some people will do just about anything (including extra work) to avoid meetings

When designing your SQL Server transactions, put together an agenda.  Out of the list of tasks, try to move everything out of the transaction.  Can you run some of the queries ahead of time before BEGIN TRAN?  If so, that lessens the number of locks you need for your transaction, and makes your transaction run faster.  When examining each query in the transaction, ask yourself:

• If this data changed between the time I queried it and the time the transaction committed, would it be a problem?  If you’re fetching the customer’s bank balance before you update it, that would be bad.  If, however, you’re checking the date the customer’s account opened, it probably isn’t going to change while the transaction runs.  That might be a good candidate to move out of the transaction.
• If I had to rollback the transaction, would this query be affected?  If it’s just a select, the answer is probably not – but your mileage may vary.

This is especially important for transactions that run frequently, like hundreds of times per minute.  In one engagement, we found a common transaction to update an employee’s details.  In the beginning, it checked the employee’s supervisor’s status before updating the employee record.  By moving that query out of the transaction and doing the work ahead of time, we cut the transaction execution time by over 90% without affecting the integrity of the query.

Minimize the Number of Objects Involved

Know why most meetings and transactions are like an OK Go video?  There’s way too many things involved.

And if you think that machine is complicated, think for a second about the process of filming it.  Everything had to work perfectly, the camera had to smoothly move throughout the entire operation, the guys had to move from one position to the next to hit their mark in time, and the whole thing is choreographed in a way that you don’t see the guys moving from spot to spot.  If you like this, check out their marching band version or their famous treadmill video, and note that they’re all done in one very long take with no camera changes.  (The Rube Goldberg one does have a cut in it, though.)

The more people involved in a meeting, the tougher scheduling gets, and the more people sit around twiddling their thumbs during the meeting.  If you only need someone for 5 minutes of the 60-minute meeting, try to accomplish that goal ahead of time and free up 55 minutes of their time.

When designing your SQL transactions, be acutely aware of every table you’re inviting to the party.  SQL Server has to schedule these objects because you’re not the only one who wants to use them.  The less locks you incur, the faster your transaction can get in and get out.

Have Less Meetings (and Transactions)

Do you really need to tie up multiple resources at the exact same time in order to get your work done?

Or are you just taking the lazy, selfish approach and grabbing lots of resources just because you can?

Coworkers and tables seem like easy resources to commandeer at a moment’s notice because you don’t pay the price initially.  As your work piles up, though, performance suffers.  Making the right call ahead of time pays off in the long run.

Tuning SQL Server Transactions – and Meetings is a post from: Brent Ozar - Too Much Information.
If you'd like to quote this content on other sites, please read how to use my blog's content.

Here’s a quote from the announcement:

“At first glance, the results seem fairly clear: 81% of the 1,573 respondents want a PASS Summit on the East Coast at least every four years.”

I was expecting a strong vote to move the Summit around, but 81% – that’s amazing.  Four out of every five people agreed.  It’s hard to achieve that kind of agreement in any kind of meeting at all!  The article continues to say:

“When we look at responses from only 2008 and 2009 Summit attendees (our most successful ones by far), the number who want a future Summit outside of Seattle drops to 69%.”

Let’s think about that for a second – if we want to increase PASS Summit attendance, that means getting new people to attend.  Forget the opinions of the people who’ve already been – what about the folks who haven’t been yet?  That’s the opinions we need to hear if we want to raise attendance.  Rushabh doesn’t quote that number, but it must be higher than 81% because the average went down 12% when these people were excluded.  Wow.

Seattle. Get Used To It.

So PASS started investigating what it would take to move the Summit, and they said:

“We would not be able to achieve anywhere near the same level of support from Microsoft as we do when Summit is held in Seattle. We would lose out on at least 50% and likely 75% of Microsoft presenters, developers, and SQLCAT and CSS staff – all things a majority of survey respondents listed as important or very important.”

This is interesting, because this is where we need the raw data to answer a deeper question.  My question is, “Of people who haven’t attended the Summit before, how important is access to Microsoft staff?”  If we asked them to rank the following in order of importance, I’d be curious what they chose:

• Access to Microsoft staff
• Access to Microsoft MVPs
• Access to your peers, SQL Server community members
• Low hotel and food costs (under $150/day as opposed to over $200/day)
• Summit location nearby (within 4 hours driving distance)

When I was a DBA, I didn’t attend the PASS Summit to talk to Microsoft folks, although that was a nice fringe benefit.  I wanted to spend more time with people who had the same job, challenges, and duties that I had.  I wanted to get tips on how to do my job better, and that’s something I didn’t get from Microsoft.

But back to the press release, which notes:

“Based on Microsoft’s release cycle history (major release cycles run approximately every 3 years, with minor ones often coming in between), 2011 or 2012 will likely be a launch year. It would be disappointing for the community to lose out on the advantages of being in Seattle during a potential release year.”

Uh, hold on a second here.  If major release cycles run every 3 years, and minor ones come in between, then the odds of having a release on any given year is 2/3!  Every year is a potential release year.  As far as I’m concerned, if Microsoft wants to promote a release, they can come to the community.  This is exactly their strategy in Europe – they’re coordinating with community leaders to host regional events all over Europe for the launch of SQL Server 2008 R2.  Why does the community have to come begging to Microsoft’s doorstep?  They’re the ones making money off us.

The final word is that we’ll be in Seattle for the foreseeable future:

“The Board has decided to hold PASS Summit 2011 and PASS Summit 2012 in Seattle for the reasons listed above.”

Well, the good news is that they laid out their rationale in public for all to see.  That’s awesome, and I cheer their movement toward transparency.  I don’t agree with their decision, but I’m glad they didn’t try to hide the poll results from us.  The users clearly don’t agree with the leadership, but we elected ‘em.  I’m also excited to see that this little ruckus has caused at least one person, Jack Corbett, to consider running for election this year.  If you don’t like the way things go, this might be a great year to run.  Having 81% of the members on your side on a given issue makes for a great campaign platform, that’s for sure.  “My name is ___, and I’m committed to bringing the PASS Summit to the East Coast, unlike the current Board who wants to keep it in Seattle through 2012.”  Presto, you’re in.

The PASS Summit Location Survey Results is a post from: Brent Ozar - Too Much Information.
If you'd like to quote this content on other sites, please read how to use my blog's content.

Fusion-IO ioDrive

How fast are SSDs?  So fast that good ones overwhelm the capacity of the connection between the server and the drive.  The SATA bus maxes out at around 300 MB/s, and a good SSD can saturate that connection.  In order to get your money’s worth out of an SSD, you have to connect it with something faster than SATA.  Value is especially important given the pricing of solid state drives – more on that in a minute.

Fusion-IO ioDrives get around this limitation because they’re not SATA drives; they plug directly into your server’s much faster PCI Express bus.  These cards can push several times more data per second than SATA drives can. Other vendors using this approach include OCZ Z-drives and RAMSAN.  Of course, this connection method only pays off when the drive uses top-notch memory chips, and after briefly testing some of Fusion-IO’s products in their lab, I can vouch that they’re using the good stuff.

How Fast Are Fusion-IO Drives?

As a former SAN administrator, I’m anal retentive about reliability and uptime.  I’ve heard FusionIO drives sold as a “SAN in your hand,” but with a single drive, there’s not enough failure protection for my personal tastes.  I wouldn’t run any storage device without redundancy, so I ran most of my tests in a RAID 1 configuration – a mirrored pair of Fusion-IO SSDs.  Keep in mind that since these devices have their own built-in controllers, any RAID setup must be a software RAID setup managed by Windows.  Software RAID has a bad reputation, but it’s the only choice available when working with these drives.  I was initially worried about the performance impact of software RAID, but I didn’t have anything to worry about.

I tested several different ioDrive models using my SQLIO scripts as seen on SQLServerPedia and got blazing results.  Here’s a fairly typical set of results from a pass doing random reads:

C:\SQLIO>sqlio -kR -t2 -s120 -o8 -frandom -b64 -BH -LS P:\SQLIO\TestFile1.dat
sqlio v1.5.SG
using system counter for latency timings, 2929716 counts per second
2 threads reading for 120 secs from file P:\SQLIO\TestFile1.dat
using 64KB random IOs
enabling multiple I/Os per thread with 8 outstanding
buffering set to use hardware disk cache (but not file cache)
using current size: 10240 MB for file: P:\SQLIO\TestFile1.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 22787.54
MBs/sec:  1424.22
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 0
Max_Latency(ms): 106
histogram:
ms: 0  1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 100  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0

I know what you’re saying, though.  “Wow, Brent, that’s faster than any SAN I’ve ever seen before, but solid state drives are slower for writes, right?”  Yes, here’s a set of results for writes:

C:\SQLIO>sqlio -kW -t2 -s120 -o1 -frandom -b64 -BH -LS P:\SQLIO\TestFile1.dat
sqlio v1.5.SG
using system counter for latency timings, 2929716 counts per second
2 threads writing for 120 secs to file P:\SQLIO\TestFile1.dat
using 64KB random IOs
enabling multiple I/Os per thread with 1 outstanding
buffering set to use hardware disk cache (but not file cache)
using current size: 10240 MB for file: P:\SQLIO\TestFile1.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 10114.24
MBs/sec:   632.14
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 0
Max_Latency(ms): 54
histogram:
ms: 0  1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 100  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0

Write performance was about half as much as read performance, but that wasn’t because Fusion-IO drives were significantly slower in writing.  They managed the same 500-600 MBs/sec write performance when using one drive alone (rather than RAID).  When I put them in a RAID 0 (striped) configuration, I frequently got over 1,000 MBs/sec.  To put things in perspective, the fastest solid state drives in Anandtech’s recent showdown only achieved 337 MBs/sec in their absolute best case scenarios.

The only way to outperform a Fusion-IO drive is to invest six figures in a SAN and hire a really sharp SAN admin. These drives consistently outperformed every storage I’ve ever seen short of SANs wired up with true active-active multipathing software, because typical 4Gb fiber connections can’t sustain this kind of throughput.  Random access, sequential access, lots of threads, few threads, reads, writes, you name it – once we had them configured properly, I couldn’t get them to perform any slower than 550 MBs/sec, which is faster than you can drive 4Gb fiber.  Connecting directly to the PCI Express bus really pays off here, and makes everything simple.

Configuring SQL Server on a SAN is hard.  To really wring the best performance out of it, you have to design the pathing, the queue lengths on the host bus adapters, the RAID arrays, the SAN caching, the database filegroups and files, and sometimes SQL Server table partitioning in order to get everything to work in concert.  Fusion-IO drives return SQL Server configuration to its easiest: plug it in, put data and logs on the same array, and just create databases with the GUI, using one data file and one log file.  As long as your hardware environment is solid, it’s pretty darned hard to screw up a database configuration on Fusion-IO drives.

The Drawbacks of PCI Express Drives

Connecting data storage devices as PCI Express cards isn’t all unicorns and rainbows. Since they’re PCI Express cards, they can’t be used in blades unless you’ve got special PCI Express expansion blades.

These cards are connected directly to one server, which means they can’t be used in clustering environments.  If your data is mission-critical, you’re probably using a cluster to protect yourself from server failures.  Protecting from failures is even more important when you’ve got a single point of failure – like, well, a single PCI Express card with all your data.  If something should happen to go wrong with one of these devices, you can’t simply fail over to another server unless you’re doing synchronous database mirroring, something I rarely see in production.

You can get some form of protection by using software RAID 1: building a simple mirror with Windows between two Fusion-IO drives in the same server.  Whenever data is written to the volume, Windows will automatically write it to both Fusion-IO drives.  Software RAID gets a bad rap, but in my brief testing, I saw no performance penalty when using this configuration.

However, when a drive fails, you probably won’t be hot-swapping these while the server is in operation.  With server-class RAID arrays, you can pull a failed hard drive out of a server and replace it on the fly.  The RAID controller will rebuild the array while the server is still online.  Data access speeds will be slower while the array is rebuilt, but at least the server can stay up the entire time without an outage.  Not so with PCI Express cards: the server will have to be pulled out of the rack and opened up in order to access the drives.  This requires careful cabling – something I don’t see often in datacenters.

And Yes, You Need to Be Paranoid

During my testing, before Fusion-IO ironed out all of the configuration issues, I kept having drives drop offline.  Normally I’d blame my own stupidity, but my tests were run in FusionIO’s datacenter, on their servers, configured by their staff.  I connected via remote desktop, set up SQLIO per my SQLIO tutorial at SQLServerPedia, and ran the tests.  During my tests, ioDrives appeared to have failed and FusionIO staff had to replace them.  It took several weeks for us to narrow down several unfortunate problems.

If you truly try to push your IO subsystems to the limit, a Fusion-IO subsystem will expose more weaknesses than other storage subsystems because it has so much more throughput.  Some of the problems included motherboard issues, driver problems, OS configuration errors, and even insufficient power supplies that couldn’t handle the load of multiple drives.

Buyers need to be aware that this is a version 1 product with version 1 best practices and documentation.  When you put something like this into your infrastructure, make sure you’re actually adding reliability.  In my post about adding reliability to your infrastructure, I pointed out:

“The only way a twin-engine plane is more reliable is if just one of the two engines is enough to power the airplane safely. If the airplane requires both engines in order to maneuver and land, then the second engine didn’t add reliability: it just added complexity, expense and maintenance woes.”

The ironic part about my FusionIO testing woes was that they only happened in RAID scenarios.  The drives were actually more reliable without RAID – when I added RAID, I could knock a drive offline in minutes.  The faster we wanted to go, the more careful the team had to be with other pieces of the infrastructure.

FusionIO drives solve a real problem, and they can deliver staggering performance, but just like any other new technology, you should test them thoroughly in your own environment before deploying them in production.  Make sure to test them in the exact configuration you plan to deploy – if you’re going to deploy them in a RAID configuration, test them that way, rather than testing individual drives and assuming they’ll hold up in RAID configs.  In the case of Fusion-IO drives, you should probably even test using similar power supplies to production in order to improve your odds.

Where I’d Use Fusion-IO Drives in Database Servers

If you’re experiencing heavy load problems in TempDB, and if you’re not using a cluster, a Fusion-IO drive can probably solve the problem with less engineering effort than any other solution.  Simply shut down the server, drop in an ioDrive, change SQL Server’s TempDB location to point to the ioDrive, and start SQL Server up again.  Your TempDB IO won’t travel over the same storage paths that your database needs, which frees up more bandwidth for your data and log traffic.  TempDB requests stay on the PCI Express bus and don’t hit your SAN.

If you’ve got power, cooling, and space constraints in your datacenter, but you need to add more storage performance (but not necessarily capacity), a Fusion-IO drive makes sense.  You’ll gain more storage throughput this way than by adding several shelves of bulky, hot hard drives that require lots of power.  On a cost-per-gigabyte basis, this won’t make economic sense, but if you’re buying storage for performance needs, the cost equation is different.

If you need to scale an OLTP database that doesn’t require high availability, you might consider skipping clustering and trying database mirroring instead.  Use two SQL Servers in the same datacenter, both equipped with Fusion-IO drives, and make sure they’ve got plenty of bandwidth between them to keep up with synchronous (or asynchronous) mirroring.  You could argue that this provides a higher availability than clustering, since it uses two different storage back ends.  I’ve had SANs fail, and I can see how this might be attractive in some environments.  StackOverflow strikes me as an excellent candidate – the databases are small enough to fit on Fusion-IO’s drives, and the servers are rack-mounted as opposed to blades.

I’m also intrigued at the ioDrive’s potential to offload transaction log load.  I can envision a scenario where databases are in full recovery mode, but the load of logging and transaction log backups is starting to put a strain on the server’s IO.  Moving the transaction logs onto the Fusion-IO drive eases the load on the SAN (not just the hard drives, but the cabling between the servers and the SAN controller).

I wish I had SQL Server benchmarks for these scenarios to share with you, but the testing process ended up taking several weeks, and I ran out of time.  Thankfully, Paul Randal is blogging about his experiences with Fusion-IO drives.

My verdict: these drives can solve some tough challenges. I’m not saying that because I’m being paid to, either; Fusion-IO was gracious enough to give me access to their labs for my testing, but I didn’t get compensated.  Quite the opposite – I sank a lot of time into this project.  Folks who follow me on Twitter may remember that I struggled with Fusion-IO during the initial stages of the testing as we went through one hardware failure after another.  After the problems we encountered and the weeks of investigation, I’m glad to finally be able to say without hesitation that you should check out FusionIO’s products.  Their throughput may push the rest of your infrastructure to its limits, but hey – that can be a good problem to have!

This post is a part of T-SQL Tuesday, which is focusing on storage/IO this week.  If you liked this post, head over there to see what else is happening in the blogosphere this week!

Fusion-IO ioDrive Review is a post from: Brent Ozar - Too Much Information.
If you'd like to quote this content on other sites, please read how to use my blog's content.

You can access the videos, slide decks, links, scripts, and more for free at QuestKB.com/Live.

#SQLServerPediaTraining Archive Now Online is a post from: Brent Ozar - Too Much Information.
If you'd like to quote this content on other sites, please read how to use my blog's content.

“If it looks, smells and tastes like bacon, then it has to be #sqlserverpediatraining. Best training there is.“

I agree!  Here were some of the other quotes:

“The best tool to detect performance issues is to use a DMV (delicious meat vitamin) BACON. #sqlserverpediatraining” – @CameronMergel

“Quest has saved my bacon more than once. #sqlserverpediatraining” – @CameronMergel

“select * from #SQLServerPediaTraining” – @charlezhunt

Dr. Ozar MDF, Database Shrink

“you know it’s serious when Dr’s gloves come off. #sqlserverpediatraining #questqa http://tweetphoto.com/13147759” – @greeleygeek

“#sqlserverpediatraining Excellent training session, so dedicated they even drop test their schwag before sending it out” – @Tarwn

“I loved learning that @BrentO is @kekline ’s private dancer, dancing for cache #SQLServerpediatraining #questQA” – @SQLChicken

“when @kekline is in the crib, @BrentO drops a Kindle like it’s hot.. #SQLServerPediaTraining” – @SQLJackal

I want to thank everybody who made that training day so much fun.  Kevin, Ari, and I had a blast, and we’re already looking forward to the next one.

And the #SQLServerPediaTraining Netbook Winner Is…@JBrown414! is a post from: Brent Ozar - Too Much Information.
If you'd like to quote this content on other sites, please read how to use my blog's content.