The most successful deployments of virtual SQL Servers have a few things in common. When a company is doing all of these things, odds are they’re going to be very happy with their virtual SQL Servers:
10. Use vSphere 4.1, not earlier versions. vSphere 4.1 introduced major improvements in multi-CPU scheduling that vastly improve performance. SQL Server will break queries out across multiple virtual CPUs, and it expects all of those vCPUs to perform identically. vSphere 4.1 helps make that happen. You can read more about the vSphere CPU scheduler here.
9. When using blades, avoid overcommitting switch uplinks. Virtualization works great with blades – but only when the SAN and network teams monitor utilization rates between the blade chassis switches and the rest of the networks. All too frequently, the SQL Server is thrown into a blade chassis with dozens of other servers all competing for the same small amount of storage and network bandwidth leaving the blade chassis. Everyone looks at their monitoring systems and can’t understand why the backups, big queries, and maintenance jobs run so slow, and the culprit is the hidden small uplink.
8. Avoid 1Gb iSCSI for storage. While this cheap and easy-to-use storage works great for most servers, it doesn’t work as well for SQL Server. If you’re happy with the speed of SQL Server running on local storage, you can be in for a rude surprise with 1Gb iSCSI.
7. Test storage performance before deployment. I’m partial to SQLIO, which might be the worst-named tool in history. It has absolutely nothing to do with SQL Server – it doesn’t require SQL Server, and it doesn’t mimic SQL Server. It just flat out hammers IO using whatever parameters you pass in. Using a test file of the same size as the expected database, the storage should consistently exceed 100MB/sec whether it’s random or sequential, reads or writes. Higher numbers than that may be required due to the application design or end user expectations, but that’s a good foot-in-the-door number.
6. Compensate for slow storage with memory. If you can’t get the storage performance you need, you can help by caching as much of the database as possible in memory. If the projected database size is 50GB but the virtual server only has 16GB of memory, then it’s not going to be able to cache the entire database. Perhaps the users won’t be querying old data, in which case you might be able to get by with less.
5. Ensure CPU power saving is turned off. While Intel’s latest Xeon processors provide impressive power savings, they won’t ramp up to full processor speed unless the CPU is under heavy load. SQL Server will rarely push the processors that hard, which means they stay slow – sounds good in theory, but in reality, every query takes 70-100% longer than it did pre-virtualization. If you care about query performance, turn this setting off in the hardware BIOS. You can read more about the power-saving CPU issue here.
4. Coordinate VMware reservations and shares with SQL Server settings. VMware vSphere has a great set of options to ensure that a guest OS gets the right amount of resources for its needs. These settings need to be set in concert with SQL Server’s min and max memory settings. There’s plenty of bad advice out on the web saying things like, “Just disable the balloon driver and give SQL Server all the memory” – that’s not right either. There’s a happy medium in letting SQL Server and VMware cooperate to balance resources across multiple guests, but it only works when these settings are aware of each other.
3. Use Resource Pools to track SQL Server licensing. Microsoft’s virtualization licensing for SQL Server is notoriously complex. Starting with SQL Server 2008R2, only Datacenter Edition ($60k/cpu socket) provides unlimited virtualization rights. Enterprise Edition ($30k/cpu socket) provides just four VMs. Tracking these closely with VMware Resource Pools can result in huge cost savings. If SQL Servers are allowed to move to any host in the VMware cluster, then a licensing audit can produce staggering costs.
2. Use VMware HA for high availability. If your users can tolerate a SQL Server outage of 2-3 minutes, VMware HA is much easier to manage than a SQL Server cluster. If your users require less than 30 seconds of downtime, consider implementing a physical SQL Server cluster instead. SQL Server clusters are tricky enough to manage on their own, and doing them inside VMware adds an impractical level of management on servers that can’t be down for more than 30 seconds.
1. Virtualize small SQL Servers first. Start by gaining experience with 1-2 vCPU servers with under 16GB of memory. As the company’s sysadmins grow accustomed to how SQL Server uses CPU and memory in a virtual environment, they’ll be more confident virtualizing larger servers. If the DBAs and sysadmins don’t get along when trying to pin down performance problems on smaller servers, they’re going to be very adversarial when dealing with larger servers.
Wow – That’s a Demanding List!
I know what you’re thinking: “This outside consultant is greedy and expects everybody to dump tons of money into their SQL Servers. He’s asking for the moon. Nobody does this in real life.”
Let’s rewind back to the beginning of the recommendations where I said, “When a company is doing all of these things, odds are they’re going to be very happy with their virtual SQL Servers.” You can certainly skimp on some of these items and still stand a pretty good chance of being happy with your SQL Server performance.
The more items you skimp on, the worse your chances become. If you implement a virtual SQL Server with 32GB of memory trying to cache 250GB of databases on RAID 5 storage, hooked up via 1GB iSCSI, with no prior experience virtualizing SQL Servers of that size, odds are you’re going to be miserable. Users will scream and complain, and you’ll bring in an outsider who will track the problems back to these types of recommendations. Keep my contact info handy.
Our Training Videos: VMware, SANs, and Hardware for SQL Server DBAs – our 5-hour training video series explaining how to buy the right hardware, configure it, and set up SQL Server for the best performance and reliability. Here’s a preview:
A funny thing happened on my way to becoming a judge in the 2011 Exceptional DBA Awards. But to get there, I gotta tell you a story about my dark past.
I used to be a developer. No, wait, it gets worse: I coded web pages in Classic ASP, aka VBscript. My idea of reusable code was stuff that I could copy/paste into multiple web pages. I didn’t have a QA team – our idea of testing was to run it a few times on our own machines, and then keep an eye on it after we deployed it to production. To me, the word exception will always remind me of exception handling, one of the many things I didn’t do well as a developer.
The bad news was that my code sucked, but the good news was that I knew it. I wasn’t destined to be a developer, and I started transitioning into database administration instead. I loved SQL Server, hardware, and performance tuning, so I began interviewing for jobs that would let me focus on that.
Along the way, I had a job interview that didn’t go well. It was a company with offices in multiple states, and part of the interview involved a phone discussion with the head DBA in another state. The DBA asked me, “If you wanted to monitor for exceptions and problems on your SQL Servers, how would you do it?”
I answered immediately without so much as a thought: “I’d buy an alerting package from a vendor.”
The DBA drilled down deeper, asking me how I’d build an alerting system with things like DMVs and SSIS, but I refused to budge. I explained that my code simply sucked, and that it would take me way longer to build a collection utility – and even when it was done, it’d be garbage. The DBA asked if I would use any of the freely available scripts at places like SQLServerCentral.com, and I could hear the DBA’s anger and dissatisfaction through the phone at my replies. I just didn’t want to hassle with rolling my own software.
Needless to say, I didn’t get the job. However, that experience pushed me harder to hone my skills. In my epic post Rock Stars, Normal People, and You, I documented my grueling struggle to take control of my databases, my career, and my life. It was a long, hard road, and it all started with one simple thing:
I spent time in the SQL Server community.
I started reading blogs, watching webcasts, and attending presentations. As I got my confidence up, I realized I could give back too, so I started presenting my own stuff. The more I gave back, the more addictive it became.
I know you’re exceptional too – because you’re here.
When I go out and talk to people at conferences, SQLSaturdays, and user groups, I talk to people about what blogs they read. Most of ’em don’t have time to read blogs. You’re already in the minority just by being here – you’re taking time out of your day to advance your knowledge. You’re not getting paid to read my blog. You’re doing this because you love what you do, and you love to learn. That’s exceptional. It’s outside the norm. Sure, I know, you think you’re just one of thousands here in the online community, but it’s easy to forget that simply by being here, you’re already ahead of the curve. You’re ahead of the tens of thousands of database professionals who never take the time to read blogs, watch webcasts, and attend presentations.
Red Gate just launched their annual Exceptional DBA Awards contest to honor people like you. Yes, you. And I want you to go enter. Stop comparing yourself to the people up on the podium, and start comparing yourself to the people who never even bother to show up to free conferences in their own town. You’re exceptional. Stop thinking of it as blowing your own horn, and start thinking of it as being proud of what you do. I know you love what you do, because you’re here reading this blog.
I’m honored to say that Red Gate invited me to join Brad McGehee, Rodney Landrum, and Steve Jones in judging the 2011 Exceptional DBA Awards. We’re looking for people who don’t just do their jobs, but they go above and beyond – spending time reading blog posts, answering questions online, and giving back to others.
I’m really honored to be a judge because it’s a champagne moment for me. See, several years ago, when I was struggling through that job interview with The DBA? That guy was Rodney Landrum.
He was right to turn me down, because at the time, I wasn’t exceptional. I didn’t read blogs. I didn’t know DMVs well. I didn’t give back to the community. But you, dear reader, are already far ahead of where I was. You’re exceptional, and it’s time you threw your hat in the ring.
Go visit ExceptionalDBA.com today and learn more about this year’s contest.
One of our FreeCon Chicago brainstorming exercises was to talk about what makes a good training session, conference session, or keynote speech. I started it by asking a few questions.
Does a successful session require a packed room? I was so happy to hear the attendees answer, “No.” A packed room has absolutely nothing to do with the success of a session: a packed room has to do with the success of the conference schedulers picking the right room size for a given topic, abstract selection committee picking the right abstract for the audience, and speaker’s marketing ability in getting the word out. You can’t even judge success by the population in the room at the end of the session, either, because many attendees won’t leave mid-session out of sheer politeness.
Does a successful session require demos? The attendees universally answered, “NO!” The best explanation I’ve seen comes from a post Jeremiah shared in our community newsletter: Why how is boring and how why is awesome by Benjamin Pollack. No, most audiences don’t really want to watch you click and type and fix typos, but even if they did, conference rooms are horrible, awful places to watch demos. You can’t see the screen well, you can’t take notes fast enough, and you need a step-by-step reference that you can follow along later anyway. That’s not to say presentations with demos aren’t successful – indeed, they can be. It’s just that demos aren’t required to be successful.
Does a successful session require slides? Again, the answer was simple: “NO!” We talked about some all-demo sessions that were spectacular, Buck Woody’s sessions where all he used was a whiteboard, and panel discussions. We even liked the Actor’s Studio style where a session is nothing more than a very well-conducted interview.
I have presentations that are on both extremes: my Virtualization & SAN Basics presentation is 100% slides, and my Blitz: SQL Server Takeovers presentation is 100% demos. Every now and then, a fellow presenter will come up to me afterwards and say (with more than a little disdain), “I noticed that you didn’t use any (slides/demos). Do attendees ever leave bad feedback about that?” I totally understand their point of view because presenters are used to certain delivery mechanisms, but instead of the tools, we need to focus on the storytelling. It’s a tough concept for us technology people to get because our very business is tools. Instead, we have to take a step back and ask the audience what they’re really here for. At FreeCon, the answer from the attendees was loud and clear.
A successful session requires one thing: engagement. Attendees have to feel that they’re interacting in some small way. They want eye contact from the presenter, but much more than that, they want to feel a sense of belonging and bonding with both the presenter and their fellow audience members. They want a session that engages their brain, shows them something interesting and new, and gives them something to talk about.
Think about how you engage at the ball game. Whether it’s our kids playing soccer or a visit to a baseball/basketball/football/drinking game, we engage. We talk back to the announcer on the loudspeaker, we yell at the players, and we share our feelings with the people sitting next to us. If we’re lucky, we interact directly with the players by catching balls or catching their eye as we sit courtside. We build up rituals like the seventh inning stretch.
Presentations are spectator sports. We pay for tickets (sometimes), root for the home speaker, share our thoughts on Twitter, and hope to catch a thrown t-shirt. Engagement gets harder as audiences get bigger, but it’s still possible. As I walked into my “Tuning T-SQL Step by Step” presentation at Connections Orlando this spring, I realized my session had been moved to the developer track, not the typical SQL Server track. When the big room filled up, I took a show-of-hands poll to see the mix of developers versus database administrators. Since it turned out to be a diverse audience, I engaged the audience throughout the presentation by pitting them against each other. I’d say things like, “Well, you developers know how those DBAs are – they’re control freaks, aren’t they?” I tried to pick on (and promote) both sides evenly so that everyone in the room would feel like I’d taken their side at least once.
If it’s a blowout or a bad session, we vote with our feet. When I first started going to conferences, I heard the experienced veterans say the same thing over and over: “I’m skipping the morning keynotes – they suck.” I understood the motivation – many of us partied late into the night – but in my wide-eyed naïveté, I showed up each morning hoping to see the home team knock it out of the park. Unfortunately, many of the keynotes I’ve attended have just plain sucked.
Which brings me to a question for you: what should we tell new speakers? What makes a good session or keynote?
This week, my calendar is chock full of free SQL Server training sessions! I took this week off to share knowledge with the community – and, uh, pack for SQLCruise Alaska. Here’s what I’m presenting this week:
May 24 – Virtualization & SAN Basics for DBAs
Pittsburgh SQL Server User Group (Brent Ozar)
Rated one of the top 10 sessions at the PASS Summit 2010! These two technologies can make a very big – and very bad – difference in how your SQL Server performs. Wouldn’t it be great if you could get the real, honest lowdown from a virtualization administrator, a SAN administrator, and a DBA? Wouldn’t it be even better if one person had done all three, and could give you the pros and cons of each point of view? That person is Brent Ozar, a Microsoft Certified Master who’s been there and done that. You’ll learn:
- Three things you should NEVER do when virtualizing SQL Server
- Three things you should ALWAYS do when using SQL on a SAN
- Three metrics you should always capture on virtual & SAN-connected SQL Servers
May 25 – SQLCruise Preview: Tuning Queries with Brent, Tim, and Aaron
Can’t join us on SQLcruise Alaska this weekend? Come spend an hour with us for a free sampling! Three of the presenters (Brent Ozar, Tim Ford and Aaron Bertrand) will cover query tuning tips from their SQLcruise presentations.
You’ll learn what to look for in estimated and actual plans, how to apply that knowledge to speed up your query, and get a free tool to make it all easier – SQL Sentry Plan Explorer.
May 25 – SQL Server Performance Tuning for Race Car Drivers (Brent Ozar)
South Florida SQL Server User Group
Times are tough even for the best drivers: Helio Castroneves is dancing for money and Danica Patrick is doing ads for what appears to be an adult services company. Maybe it’s time to switch careers, and Brent has just the thing. Use your hard-earned knowledge of high speeds, million-dollar hardware and surviving disastrous crashes to become a SQL Server performance tuner!
In this session, Brent will show you:
- Why Colin Chapman would check for indexes before adding new ones
- The importance of well-tested safety gear to performance tuning
- Why not monitoring your servers is like overdriving your headlights
- Just like races are lost in the pits, uptime records are lost during maintenance windows
May 26 – Consulting Lines for DBAs
PASS Professional Development Virtual Chapter (Brent Ozar)
Consultants work with technology, but they also deal with a lot of politics. Brent Ozar was a production DBA for years and sat through thousands of meetings. Now that he’s a consultant, he’s figured out how consultants handle political bombs during meetings, and he wants to share his favorite consulting lines with you. You’ll learn how to deal with dangerous developers, miserable managers, and cussing customers using simple lines that won’t blow up in your face.
This morning, the Professional Association for SQL Server announced that the annual Summit will be held in Charlotte, North Carolina. For the last 5 years, it’s been held in Seattle, and PASS members have protested. We wanted the Summit to move around from place to place to make it easier for other people to attend and to let us see some different scenery. (I mean really, how many years can we try to talk our spouses into going to Seattle in the fall when it’s cold and rainy?) This year, it’s moving! I’m so happy to hear this.
You can read the announcement at SQLPass.org.
The Professional Association for SQL Server (PASS) holds a summit every year where thousands of database professionals gather to learn about the latest developments from Microsoft, meet their fellow community members, and drink Jägermeister. This year, there’s a new way to help shape the PASS Summit – you can vote on the sessions you’d like to see.
Here’s the sessions we’re submitting this year along with some personal notes about why we’d like to present ’em.
“And Then It Got Even Worse”… Great Mistakes to Learn From (Panel Discussion- Vote)
Brent says: Nobody calls me when things are going well. When my phone rings, the poop has already hit the fan. My favorite disaster was when we found out that the redundant air conditioners…weren’t. This wouldn’t be so bad except that it was in the middle of summer in South Florida. It was a Sunday, so it took us a while to drive into the office. We tried opening the datacenter doors to help cool things off, but then we realized that since it was Sunday, the office air conditioners were turned off too. Good times. In that story, I’ll talk about why RAID 5 still isn’t enough for serious data protection.
Tim says: I was one of those accidental DBAs everyone talks about 12 years ago. Then things got busy, really busy. Now I find myself going back and fixing all the poor decisions made by the DBA who was learning on the job: Me.
Jeremiah says: I’ve written my share of bad code, recovered downed servers on 2 hours of sleep, and had every SQL Server upgrade go horribly wrong. It’s good to learn from your own mistakes, but it’s even better to learn from someone else’s.
Kendra says: It’s funny, when everything goes terribly wrong, you’ve got to stay very calm but also think fast. More than once I’ve had a situation go south where I thought of a story someone told me– and it saved the day.
BLITZ! The SQL – More One Hour SQL Server Takeovers by Brent (Vote)
Brent says: “You’re the database guy, right? We’ve got this SQL Server that’s been under Johnny’s desk. Everybody’s using some app on here. It’s yours now. Kthxbai.” DAMMIT, that sucks. I hate those moments as a DBA. Suddenly I had a completely new – or rather, completely old and busted – server that I had to manage. No backups, no documentation, no security, all problems. I built my Blitz script to take over servers faster and easier, and last year at the 24 Hours of PASS, I shared it with you in an all-demo session. Today, I’m a consultant, and I have to rapidly assess SQL Servers several times a week. I’ve built a new version of the script that does it harder, better, faster, stronger, and if this session is picked, I’ll give it to you at the Summit.
Tim says: I’ve been using variations on this script for a couple years now. It continues to save my bacon and make me look like a superstar in the office.
Jeremiah says: I had a collection of scripts for years that did something almost like what the Blitz script can do. I can’t say enough great things about this session.
Kendra says: ZOMG, he’s giving this away?
Consolidation is NOT a 4-Letter Word by Tim (Vote)
Tim says: I’ve been given a VM farm to fill with SQL Servers. So how do I decide what instances and databases I’m virtualizing and consolidating? I USE THE MATHS! Dynamic Management Objects, system objects, and the like are all tools I’m using in the process. I’m not going to talk about how to consolidate. This is about how to come up with the numbers to work your load balancing Mojo.
Brent says: I got a four-figure bonus from my boss’s boss after I showed how to consolidate a bunch of crappy old servers onto one brand new server. I saved over half a million dollars in licensing, made my own job easier, and paid off my Jeep. How cool is that?
Jeremiah says: When I was a production DBA, I frequently said “The less I have to manage the better.” Consolidation made my job much easier because I had to worry about less licenses, fewer servers, and less things that can break.
Kendra says: Consolidation is terrifying. What if it all goes horribly wrong in six months, which is when you can’t get more hardware? What you need is a method to figure things out.
How StackOverflow Scales with SQL Server by Brent and Kyle Brandt (Vote)
Brent says: I remember back in 2007 when I went to my first PASS: I loved the session on MySpace’s SQL Server infrastructure. I’ve been helping out with StackOverflow’s databases for a couple of years now, and one day I was just thinking to myself, “Wow, this stuff’s getting pretty big. Maybe we should talk about it at PASS?” I asked Kyle Brandt (Blog – ServerFault – @KyleMBrandt) if he’d like to co-present a session with me about the magic behind the scenes.
Kendra says: This session is so full of win. I’ve worked with Brent and Kyle together and they’ve mastered a complex and dynamic environment. They’ve faced common challenges that happen to many companies, and they’ve managed to conquer the problems without breaking the bank.
No More Bad Dates: Best Practices for Working with Dates and Times by Kendra (Vote)
Brent says: I remember the first time my company grew into multiple time zones. Suddenly GETDATE() on one server didn’t match GETDATE() on another server, and all our reporting tables had to be reworked. Whoops. <sigh> Now what? Do I store it in UTC? Do I change all my server clocks to UTC, or do I use offsets? Why is this stuff so hard?
Jeremiah says: Temporal data has always been tricky to deal with. The new data types make life much easier for DBAs, developers, and end users alike.
Kendra says: After working with TSQL for many years I realized there are a lot of intricacies to dates and times that you don’t see until you’ve mucked things up a bit. It all seems easy from afar, but once you get your hands dirty it’s a different story. Who knew language settings were a big deal for dates? Who knew computed columns with dates were picky? What do you mean, TIMESTAMP isn’t a stamp with a time? I pulled together all the wackiness, gotchas, and oh-look-at-that’s into this talk to save *you* some time.
Performance Through Isolation: How Experienced DBAs Manage Concurrency by Kendra (Vote)
Brent says: WITH (NOLOCK) only gets you so far, buddy. Time to cowboy up.
Tim says: Is ANTI-SERIALIZABLE an isolation level?
Jeremiah says: I like it when I get the rows in wrong the order, or twice, or twice, because of isolation I like it when I get level tricks.
Kendra says: Each time I give this presentation I’m amazed at how important it is to manage transaction isolation properly in an active environment, and how easy it is to misunderstand how things work– I’ll give you steps to manage concurrency that will save you loads of trouble.
Rewrite Your T-SQL for Great Good by Jeremiah (Vote)
Jeremiah says: Rewriting T-SQL is not the most glamorous way to make your database faster, it’s not always the easiest way to make your database faster, but it’s frequently the most rewarding way to make your database faster. Over the course of my career I’ve written a lot of bad T-SQL. I’ve re-written even more bad T-SQL to make applications run faster. Sometimes I even had to rewrite the code in ways that didn’t make sense to me as a developer but it made perfect sense for the database. I learned a lot but I always felt like there should be more information about this topic.
This session focuses on real problems I’ve faced, real patterns I’ve uncovered, and real solutions I’ve used to make things faster.
Brent says: Just because your T-SQL runs doesn’t mean it runs fast. Most of the time, this is completely okay, but every now and then you need to rewrite a working query to make it fly. That’s where Jeremiah comes in: we do this constantly. Learn from people who make a living doing this.
Tim says: So recently when I ran across a trigger that included a WAITFOR DELAY in order to resolve a timing issue with a GUID Primary Key in a child table that may have been bad code? (And unfortunately I’m not making that up for the sake of humor. Sadly they had removed the GUID Primary Key over two years ago but left the unnecessary WAITFOR DELAY. Easily fixed with a couple hyphens. I’m sure Jeremiah will lift heavier in this session.
Kendra says: This isn’t about putting lipstick on a pig– it’s about making your pig glow with health. Jeremiah will teach you more than tricks or how to use flashy features. Instead, he’ll teach you how to systemically improve your application performance.
Rules, Rules, and Rules by Jeremiah (Vote)
Jeremiah says: I like digging into something and figuring out how it works. When I first started working with databases, I thought that this was a pretty easy way to store and retrieve data. The more I learned about databases, the more I became fascinated by the way they worked. As I started digging into databases, I discovered that the most fascinating part of a database wasn’t really the database itself, it was the underlying software. To keep learning and understanding more about how databases work, I dug into the computer science that makes them tick.
There’s a lot of theory out there, some of it’s only applicable when you’re writing a database, but a lot of it can be applied and can help you make your life as a database professional easier. If you’re half as interested in this as I am, you’ll get a kick out of this session.
Brent says: if you like Dr. DeWitt’s keynotes diving into the technology side of databases, but you’re not quite sure how to relate that back to your own job, this is the session for you. Jeremiah likes reading database source code in his spare time, and he can show you how things like drive rotational speeds influence your schema designs.
Kendra says: Jeremiah’s unusual because he’s been a full time developer, a full time DBA, and a full time consultant. This has given him a big-picture view of systems from the spindles to the compiler, but with a practical twist. This is a great talk, and it’ll set you up to engineer better applications.
SELECT Skeletons FROM DBA_Closet by Tim (Vote)
Brent says: Experience is a fancy word for someone who’s made a lot of mistakes. Tim’s experienced, and he’ll talk about his educational opportunities so you can be experienced too – but without all the burn marks.
Tim says: I was the King of Cursors and Prince of Linked Servers when I was learning by successful failing earlier in my career. This public trousering hopefully provides some redemption. If not then it wouldn’t be the first time I’ve made a fool out of myself in public.
Jeremiah says: I can’t wait to learn about all of the skeletons that Tim is going to bring into the open, mainly so I can laugh along with him at the mistakes we’ve both made.
Seven Strategic Discussions About Scale by Kendra (vote)
Brent says: Want to amp up your career? Like reading stories on HighScalability.com? Kendra’s got experience with huge systems and a talent for communicating. If you’re frustrated with a crappy app design, she can help you convince your fellow developers, DBAs, and the people holding the checkbook. It’s really, really hard to find good training on big, high-performance systems, and you can get it for free in this session.
Jeremiah says: The best way to learn about scaling is to do it wrong and then do it right. The next best thing is to learn from someone who’s worked on massive databases and who is willing and able to share that information with you.
Kendra says: After years of experimentation, I’ve figured out how to sell a good idea: honestly, but with great data and timing. For any given issue, there are concrete things you can do to be more persuasive. Here’s how to find the right change for your systems, and the data which will get your message through.
Storage Triage: Why Your Drives Are Slow by Brent (Half-Day Session – Vote)
Brent says: This year, PASS let us submit 4-hour sessions for the first time. I’m really excited about this because there’s some sessions I just can’t cover in an hour. Very often when I’m called into clients, the SAN administrator is getting thrown under the bus. I need to quickly figure out who’s really at fault – is storage slow, or is storage slow because we’ve got memory or query problems? In this session, I’ll explain my diagnosis decisions to help relieve storage pains, and I’ll give attendees a poster with my decision tree.
Jeremiah says: I wish I’d had a session like this when I started learning about storage. Heck, I still wish I could go to a session like this. Storage is a critical component of SQL Server performance – getting it right is key.
Kendra says: Trust me: you want to be friends with your SAN admin. This talk will set you up so you only bring your SAN administrators problems that they can solve, with data that makes sense. If you can do that regularly then you’ll have an ally on your side when things go wrong, and that can really save the day.
The Database is Dead, Long Live the Database by Jeremiah (Vote)
Jeremiah says: We’ve all supported application features that we knew shouldn’t be in a relational database. Sometimes, you just have to suck it up and do what you can to keep that SQL Server running, right? It turns out that there are a lot of other ways to get things done and some of them work a lot better than storing data in SQL Server. This is something that I learned the hard way while performance tuning different applications – you need to pick the right tool for the job. I’ve struggled with trying to make a solution fit into a relational database and perform well. Sometimes, it just doesn’t work out.
You’re going to love learning about the mistakes I’ve made and how I’ve solved them, sometimes in novel ways.
Brent says: You’ve got that one app you keep swearing at because it performs horribly in SQL Server. It uses too much space, it tries shredding XML in every query, or it never gets queried period. Turns out it shouldn’t be in SQL Server to begin with, and Jeremiah can help you see the alternatives. They’re a lot easier (and cheaper) than you think.
The Other Side of the Fence: Lessons Learned from Leaving Home by Jeremiah (Half-Day Session – Vote)
Jeremiah says: I worked at Quest for a while as an Emerging Technology Expert. My job was to take a look at different databases and investigate how they could benefit businesses. I made some mistakes and had to re-learn a lot of things before I could start making sense of things. There’s more to it than learning new features and ways of doing things – by getting outside of my comfort zone, I had to make sure that I really understood how SQL Server operated in order to compare and contrast it to other databases.
Unlike The Database is Dead, Long Live the Database, this talk is all about different features in SQL Server, how they work, and why they got that way.
Brent says: Jeremiah likes to experiment. He’s a SQL Server guy who’s been playing doctor with several other database platforms. I love talking to him about how SQL Server does stuff because he can relate it in terms of how PostgreSQL or Hadoop do it. I don’t have the time to become an expert on those other platforms, but the things he teaches me about them help open my eyes about how I can do things differently in SQL Server.
Kendra says: Jeremiah and I talk about different database platforms often. Working with different platforms is like travel: you understand your home in a deeper way after you’ve been elsewhere.
The Periodic Table of Dynamic Management Objects by Tim (Vote)
Brent says: Tim’s poured a lot of work into making DMVs easier to understand. His introduction for this session says it all – “It’s time to have fun… WITH SCIENCE!” I love this session idea.
Tim says: I’m having a lot of fun with what started as a way for me to exercise my creative demons. We’ll go over the namesake poster and learn some interesting tricks using Dynamic Management Objects. We’ll also play some games to reinforce what you’ve learned. Like the abstract says: “It’s time to have fun… WITH SCIENCE!”
Jeremiah says: I was so excited when Tim swore me to secrecy and showed me an early draft of his poster. There a lot of DMVs and Tim figured out a great way to communicate meaningful information about them all.
Kendra says: You know how you thought Chemistry class was going to be all math, but then you got to light things on fire and blow some things up in the parking lot? You wanna be here for this one.
Top 10 Crimes Against Fault Tolerance by Kendra (Vote)
Brent says: When I first talked to Kendra before SQLCruise 2010, I loved hearing about the scale and flexibility of the servers she was working with. Multi-terabyte databases behind load balancers for constant uptime? Your ideas are intriguing to me and I wish to subscribe to your newsletter. If she’s that serious about data warehouse uptime, I’d love to see her tricks for everyday databases!
Kendra says: This talk is all about the important things that are much easier to do at the beginning. When you’re first designing an application or a service and you’re creating new databases, there are ways you can vastly reduce your recovery time down the road. As time passes, it’s much harder to make changes to add these things in. This is a list you can live by.
Virtualization and SAN Basics for DBAs by Brent (All-Day Pre-Con – Vote)
Brent says: I’ve given this all-day session at SQLbits in the UK and Connections in Orlando to rave reviews, and my clients frequently bring me in to give this same session to their DBAs, VMware admins, and SAN admins. I help get everyone on the same page so they don’t try to throw each other under the bus.
Tim says: Brent has presented on this topic on SQL Cruise and each time I walk away with more things to try back in the “Real World”.
Jeremiah says: Every time I talk to Brent about SAN or virtualization, I come away feeling smarter that I was before.
Kendra says: This session gives you the foundation you need to make it through every day comfortably– it’s like permanent clean underwear. We all need that, don’t we?
Sound Interesting? Vote For Us!
You can vote for the sessions you’d like to see here. Vote before midnight Pacific on May 20th.
If you’d like to get quick SQL Server help, the #SQLHelp hash tag is a fun way to get it. My original “How to Use the #SQLHelp Hash Tag” post hit a couple of years ago, and it’s time for a followup. Read that post first, and then come back here for some basic guidelines.
Don’t use #SQLHelp to promote your blog. Congratulations on writing an informative post, and we’re sure it’s got some useful information in it, but the #SQLHelp hash tag is for people who are asking questions. Unless your blog post was written to answer a question currently live on #SQLHelp, please refrain from tweeting about your blog.
Do answer a #SQLHelp question with a product if that’s the solution. Vendors build products to solve pain points, and sometimes those pain points surface as #SQLHelp questions. If the answer is a product – whether it’s a free one or a paid one – then feel free to mention it and provide a link. If you’ve got personal experience with the product, that’s even better. If you’re a vendor, you might wanna disclose that in your tweet.
Don’t demo #SQLHelp at conferences by saying, “Say hello, #SQLHelp!” Immediately, dozens of users around the world will reply to you, and the #SQLHelp hash tag will become unusable for half an hour or more. Rather than saying Hello World, ask the audience to give you a question, and then post that question on #SQLHelp.
Do suggest that long discussions move to a Q&A web site. Sometimes questions need a lot more detail than we can get in 140 characters. If you notice a discussion turning into a long back-and-forth conversation, helpfully suggest that the questioner read my tips on writing a good question and then create a post on whatever site you prefer.
- StackOverflow.com for T-SQL programming questions
- ServerFault.com for systems administration questions
- DBA.StackExchange.com for DBA-specific questions that might need cross-platform expertise
- SQLServerCentral.com for general SQL Server stuff
Don’t post jobs to #SQLHelp. Use the #SQLJobs hash tag instead.
Do thank people who give you #SQLHelp. This is a group of volunteers who love to lend a helping hand. It’s like getting consulting help for free around the clock. High five ’em if they helped you get through your day easier.
Building an execution plan is a lot like going shopping. Before I leave the house, I need to think about a few things:
- How many items do I need? I take a glance at my grocery list to see roughly how many things I’m shopping for.
- What stores am I going to visit? I can’t get everything from the same store, so I make a list of where I’m going to source everything.
- Am I in a big store or a small store? The bigger the store, the more likely I’m going to pick up more items that I might not have had on my list. My guess of what I need might change if I see cool things I’d like to pick up. The store also might not have everything that I’d like to find.
Armed with this background information, it’s time to make my plan of attack:
- Which store should I visit first? The order is determined by what I’m shopping for. I want to visit my butcher or fishmonger first to see what’s fresh and what’s on sale, and then those purchases determine what I get from the grocery store and wine store.
- In each store, do I need a push cart, a basket, or just my hands? This is influenced by how many items I think I’m going to take to the checkout stand.
- In each store, will I go to specific aisles for what’s on my list, or just hit all the aisles? If I only need a couple of things, I’ll go directly to those aisles, get ’em, and walk out. (When I get rich, I’ll pay the minimart back.) If I have a huge list, then it makes more sense to start at one side of the store and walk through every aisle, crossing items off my list as I pick ’em up.
Why, that’s just like SQL Server’s query processor!
How SQL Server Builds Execution Plans
The query processor looks at your query, reviews the table statistics, and tries to quickly choose an efficient execution plan – just like you choose which stores to visit in what order, and then what aisles to hit inside the store. The first order of business is checking out what
stores tables are involved, and what we need from each one. Let’s take a simple AdventureWorks query joining two tables:
FROM Sales.SalesOrderHeader h
INNER JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
WHERE h.DueDate >= ‘5/15/2011′
AND d.OrderQty > 10
We’re joining between SalesOrderHeader and SalesOrderDetail, which have a parent/child relationship: rows in SalesOrderHeader can have multiple rows in SalesOrderDetail table. We’re filtering on both tables – we’re checking for the header’s DueDate field and the details OrderQuantity field. Which one should we check first? We’ve got two options:
- Check SalesOrderHeader to look up all rows with DueDate >= 5/15/2011, and then look up their matching SalesOrderDetail records to examine their OrderQty, or
- Check SalesOrderDetail to look up all rows with OrderQty > 10, and then look up their matching SalesOrderHeader records to examine their DueDate
Let’s check to see what SQL Server chose in one situation. When reading execution plans, we read from the top right to determine what happens first, and then go left. While this may not seem intuitive at first, keep in mind that it’s designed to increase the job security of the database administrator. You can thank Microsoft later.
In my sample data, it chose to check SalesOrderHeader first – that’s the top right operator in the execution plan. SQL Server determined that this would be the most effective method because neither table had an index on the fields I needed. Either SQL Server was going to have to scan the entire SalesOrderHeader table to check each record’s DueDate or it would need to scan the entire SalesOrderDetail table to check each line item’s OrderQty. In this example, SalesOrderHeader was the smaller table, so it made more sense to scan that one.
Mo Tables, Mo Problems
When I go to the fishmonger and discover crawfish is finally in season, I have to change the rest of my shopping plans. I need to go to the wine store because I don’t keep the right wine pairing for mud bugs. Me being a flexible guy, I can easily rework my entire shopping plan if crawfish happens to be in season, but SQL Server doesn’t have that kind of on-the-fly flexibility. It has to build a query execution plan, and then rigidly adhere to that plan – no matter what it finds in at the fishmonger.
The more tables we add into our query, the more SQL Server has to make guesstimates. When we daisy-chain lots of tables together, SQL Server estimates how many rows it’s going to match in each table, pick the right orders, and reserve memory to do its work. One bad estimate early on in the query execution plan can turn the whole plan sour.
In performance tuning, sometimes I help SQL Server by breaking a ginormous shopping trip into two: I separate a large query into two separate queries. I don’t mean nested SELECT statements in a maelstrom of parenthesis, either – I build a temp table, populate it with the first query’s results, and then join that temp table to another set of results in a second query. Here’s an overly simplified example:
CREATE TABLE #CreditCards (CreditCardID INT)
INSERT INTO #CreditCards
( CreditCardID )
WHERE Hacked = 1
FROM #CreditCards cc
INNER JOIN Sales.SalesOrderHeader h ON cc.CreditCardID = h.CreditCardID
INNER JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
WHERE h.DueDate >= ‘5/15/2011′
AND d.OrderQty > 100
Sometimes by breaking up our shopping trip, SQL Server is able to build a better execution plan for the second query. This is especially effective when I’m dealing with a very large number of tables, yet there’s just a couple of key tables in the query that determine whether we show any results or not. I don’t use this technique proactively – I only try it when I’m consistently running into problems with execution plans that would be better suited separately. Alternate techniques could include index hints or query plan guides.
How SQL Server Chooses An Aisle Seek or a Store Scan
Once we’re inside a particular store (table), we’re faced with another choice: do we jump directly around to just a couple of aisles to find exactly what we want, or do we walk up and down every aisle of the store, picking things up as we go past? SQL Server makes this decision based on statistics about the table (as well as other tables in the query). If it believes it only has to pick up a few items, it’ll choose to do a seek (possibly combined with row lookups). If the number of things we need is greater than the tipping point, then SQL Server does a scan instead.
While the word “seek” has a reputation for performance amongst database administrators, don’t get sidetracked trying to force every query execution plan to use all seeks. Sometimes it’s just more efficient to do a scan – like when we need to pick up several things from every aisle, or every page in the table. The better question is to ask whether we really need something from every aisle. (Erika asks me this question every time I walk into Frys – my credit card can’t handle table scans in there.)
The decision to do a seek or a scan is influenced by our shopping list. Sometimes I can’t exactly make sense of what’s on my list. When Erika wrote down “Frantoia,” that didn’t make sense to me, so I was forced to go up and down the grocery store aisles looking for a product by that name. If she would have said, “WHERE Brand = Frantoia AND ProductCategory = Olive Oil”, then I’d have been able to use my trusty index to seek directly to the olive oil, but I didn’t have that option. Instead, I had to do a scan – there wasn’t enough information in the query for me to use an available index, and I was too lazy to ask for directions.
In addition to searching by fields that aren’t indexed, other things in our query can force SQL Server to do a scan instead of a seek:
Using functions in the WHERE clause – if Erika tells me to buy everything WHERE PriceToday <= (50% * RegularPrice), I’m going to have to walk through the entire store looking at every single price tag. I can’t ask the store for a map of items that are half-off or more, because they simply won’t have a map like that. Likewise, if my SQL Server query gets fancy with the functions, it won’t be sargable.
Bad or out-of-date statistics – SQL Server uses statistics to gauge the contents of the grocery store as a whole. If it believes that the store is like a typical grocery store with only a small section of olive oils, then it’ll jump directly to the olive oil area if we say WHERE ProductCategory = Olive Oil. However, if this store just came under new ownership, and the new owners decided to carry a stunning array of olive oil, SQL Server might be overwhelmed by the amount of data that comes back.
Why I Hate “ORDER BY”
We believe in division of labor in the Ozar house. When I get home from shopping, I deliver the bags into the dining room table. From there, it’s Erika’s job to put things away in the various cupboards and closets. I know that I could make Erika’s job easier if I sorted items as I loaded them into the car, but that just doesn’t make sense. It’s not efficient for me to stop what I’m doing in the middle of the shopping trip, move things around between bags, and get them in exactly the right order for efficient cabinet loading. There’s plenty of space for efficient sorting when I get home.
Likewise, I don’t want my SQL Server sorting data. The more processing that I can offload to web servers, application servers, and thick clients, the better. SQL Server Enterprise Edition is $30k/CPU, but your web/app servers are likely a great deal cheaper, and you’ve probably got much better methods of scaling out your app server tier. We just don’t have a good way of scaling out SQL Server queries until Denali AlwaysOn hits.
More Reading on SQL Server Seeks, Scans, and Statistics
To learn more about the topics in today’s post, check out:
- Gail Shaw’s SQL In the Wild blog – Gail’s posts have two things I love: illustrated examples and a straightforward, easy-to-read style. She has a wide variety of query optimizer information for all levels, beginner to advanced. The easiest way to search her blog for interesting articles is to put in site:sqlinthewild.co.za to filter your Google results for things like just her statistics posts.
- Grant Fritchey’s book on tuning T-SQL – this solid book should be on every DBA’s bookshelf. It covers everything you need to know to make queries go faster, and no background knowledge is required. I’ve reviewed it here.
- Paul White’s blog – this new MVP writes the hottest new blog for senior SQL Server DBAs. I learn something about SQL Server internals every single time I read his work.
- My bookmarks tagged performancetuning – I use Pinboard to save my bookmarks online. Not all of these have to do with tuning T-SQL, but there’s some gems in here.
- Kimberly Tripp’s Microsoft SQL MCM videos – free training on Indexing Strategies, Index Analysis, Statistics, and more.
The tech event community is growing again. Years ago, GeekCruises (now named InSight Cruises) pioneered the traincation concept, and last year SQLCruise brought that theme to the SQL Server community. The new Startup Workaway brings tech founders to Costa Rica for startup sprints – a mix of work and play. Red Gate’s SQL in the City invites database geeks to Los Angeles and London for a day of sessions. If you want to learn and play, there’s a lot of fun options.
Now Denny Cherry, a longtime SQL Server community member, MVP, and author, and his wife Kris have formed a new event: SQL Excursions. The first event is in Napa, California in September. I talked to Denny about launching the new event.
Brent: Congratulations on launching SQL Excursions! What made you take the big step?
Denny: Thanks Brent we are thrilled to be getting SQL Excursions off the ground. Kris and I put SQL Excursions together for a couple of different reasons. I wanted to be able to do some more speaking and teaching which is something which I love to do, and we wanted to put something together which would give spouses / significant others a way to go on the trip and having something fun for everyone to do. As you’ve probably noticed, Kris comes on a lot of my trips with me, and to often she ends up going to the dinners and parties and doesn’t really have anyone to talk to that isn’t a SQL Server person, and apparently she doesn’t find talking about SQL Server all day exciting. We see these events as a way to get some great technology information to the technology folks, and get the significant others to see a small piece of what we do, while giving them some fun events to go to.
Brent: I can see why Kris would love this. What kinds of events is she planning during the day while the SQL Server training happens, and what kind of training are you doing?
Denny: On Thursday and Friday during the day the guests of the attendees will be off on a full day wine tour, which we haven’t set the price for yet (we need to know the interest level before we can set the price). On Saturday there will be another full day wine tour for all the attendees and their guests which will also be an optional day. Tom LaRock and I haven’t set the training schedule yet. We will be putting up a survey with some topic options that we’d like to talk about so we can have the community vote on which of those topics will be covered. The training will all be 300-400 level sessions.
Brent: How’d you pick Napa as the first location?
Denny: We picked Napa as the first location as lots of people love wine, and I’ve never heard of anyone not having a good time in Napa. For people that decide to come to Napa a couple of days early or stay a couple of days after (like Kris and I are doing) there is tons of stuff to do in downtown Napa. There are several extremely good restaurants, as well as several tasting rooms. All this is just a short few minute walk from the hotel that we have selected for our first SQL Excursion.
Brent: I noticed you said walk, and that’s a really good thing. I, for one, love wine tastings, and driving home afterwards isn’t an option!
Denny: Drinking and driving is never an option, no matter what event you are at. The great thing about Napa is that the downtown area has lots of stuff to do right there. For official events which are away from downtown and away from the hotel, we’ll be getting shuttle vans to get everyone to where we are going. If someone wants to venture out on their own Napa is just that short walk or few dollar cab ride away.
Brent: Both you and Tom do a lot of travel – I see you at all the big conferences. With SQL Excursions, this is yet another event – are you still going to all the other events like the PASS Summit and TechEd?
Denny: You are correct we both do a lot of traveling to do presentations and I don’t see SQL Excursions taking away from any of these other conferences. Conferences like PASS, Tech Ed, EMC World, and Connections are a major part of my continuing professional education and I will always do my best to attend, and speak (when they’ll have me), at them.
Brent: Who’s the perfect person to go on SQL Excursions Napa?
Denny: I would say that someone who is a mid to senior level DBA who is looking for two days of solid 300-400 level material while having a great time in Napa with other data professionals. Now you definitely do not need to know anything about wine to come to Napa, god knows I don’t know much about wine. During lunch in addition to learning about SQL Server, we’ll also be having some instructors come in to teach about wine, and how to find a wine that works for you. If any of this sounds like a good time to you, this is definitely an event to look at.
Brent: What do you want your attendees to take away from the event that’s different than typical events?
Denny: Oh course we want our attendees to get some great SQL Server knowledge out of the week. We also want them to have a great social experience that at the larger conferences people aren’t always able to do because they get left behind and lost in the mass of people that are there. At our Napa SQL Excursion it’ll be a small group, so there won’t be any getting left behind with everyone having the option of getting together with the group for dinner after the sessions. We’ve got some great after events that we are still working on getting setup which will make for a great way for the attendees and their guests to mingle and have a great time.
Brent again here. I think this event looks like a lot of fun, and I wish I could attend – but if I add any more travel to my fall schedule, Erika’s going to helpfully arrange my belongings out on the front door. Thankfully Denny’s one of the attendees on this month’s SQLCruise Alaska, and I look forward to talking with him about the event there. As I wrote in my post How to Get Paid to Take a Cruise, anybody can start their own community and training events. What’s stopping you from attending – or hosting – one of these fun events?
You can learn more about SQL Excursions here.
When you start developing a new application how do you pick the database back end? Most people pick what they know/what’s already installed on their system: the tried and true relational database. Let’s face it: nobody is getting fired for using a relational database. They’re safe, well understood, and there’s probably one running in the datacenter right now.
Here’s the catch: if you’re using a relational database without looking at anything else, you’re potentially missing out. Not all databases work well for all workloads. Microsoft SQL Server users are already aware of this; there are two distinct database engines – one for transactional and one for analytical workloads (SQL Server Analysis Services).
When you start a new project, or even add a substantial feature, you should ask yourself [questions about your data]. Here are a few sample questions:
- Why am I storing this data?
- How will my users query the data?
- How will my users use this data?
The Relational Database
There are a lot of reasons to use a relational database. Relational databases became the de facto choice for databases for a number of reasons. In addition to being based on sound mathematical theory and principles, relational databases make it easy to search for specific information, read a select number of columns, and understand structure by querying the metadata stored in the relational database itself.
The self-describing nature of a relational database provides additional benefits. If you’ve created a relational database with referential integrity and schema constraints you are assured that every record in the database is valid. By enforcing data integrity and validity at the data level, you are assured that any data in the database is always correct.
The adoption of SQL as a standard in the mid-1980s finalized the victory of the relational database for the next 25 years. Using SQL it became easy to create ad hoc queries that the original database developers had never dreamed of. The self-describing nature of relational databases combined with SQL’s relatively simple syntax was hoped to make it easy for savvy business users to write their own reports.
In short, relational databases make it easy to know that all data is always correct, query data in many ways, and use it in even more ways. Will all of these benefits, you’d think that people would have no need for a database other than a relational database.
Document databases different from relational databases primarily because of how they store data. Relational databases are based on [relational theory]. While databases differ from relational theory, the important thing to remember is that relational database structure data as rows in tables. Document databases store documents in collections. A document closely resembles a set of nested attributes or, if you’re more like me, you might think of it as a relatively complete object graph. Rather than break an application entity out in to many parts (order header and line items) you store application entities as logical units.
The upside to this is that document databases all developers to create software that reads and writes data in a way that is natural for that particular application. When an order is placed, the order information is saved as a logical and physical unit in the database. When that order is read out during order fulfillment, one order record is read by the order fulfillment application. That record contains all of the information needed.
Unlike relational databases, document databases do not have a restriction that all rows contain the same number of columns. We should store similar objects in the same collection, but there’s no mandate that says the objects have to be exactly the same. The upside of this is that we only need to verify that data is correct at the time it is written. Our database will always contain correct data, but the meaning of “correct” has changed slightly. We can go back and look at historical records and know that any record was valid when it was written. One of the more daunting tasks with a relational database is migrating data to conform to a new schema.
While data flexibility is important, document databases may make it difficult to perform complex queries. Document databases typically do not support what many database developers have come to think of as standard operations. There are no joins or projections. Instead it’s a requirement to move querying logic into the application tier.
Database developers will find the following query to be a familiar way to locate users who have never placed an order.
SELECT u.* FROM users u LEFT JOIN orders o WHERE u.user_id = o.user_id WHERE o.order_id IS NULL;
With a document database a naive approach might be to write queries that retrieve all users and orders and subsequently merge the list of results. A more practical approach is to cache a list of order IDs within the user object to improve look up performance. This seems like a horrible idea to many proponents of relational thinking, but it allows for rapid lookups of data and is considered to be an acceptable substitute for joins in a document database. Finding the users who have never placed an order becomes as simple as looking for users without an orders property. Some document databases support secondary indexes, making it possible to improve lookups.
Document databases are a great fit for situations where an entire object graph will always be retrieved as a single unit. Additionally, document databases make it very easy to model data where most records have a similar core of functionality but some differences may exist between records.
Key/value stores are simple data stores. Data is identified by a key when it is stored and that key is used to retrieve data at some point in the future. While key/value stores have existing for a long time, they have gained popularity in recent years.
Many data operations can be reduced to simple operations based on primary key and do not require additional complex querying and manipulation. In addition, key/value stores lend themselves well to being distributed across many commodity hardware nodes. A great deal has been written about using key/value stores. [Amazon’s Dynamo] is an example of a well documented and much discussed key/value store. Other examples include Apache Cassandra, Riak, and Voldemort.
Key/value stores typically only offer three data access methods:
delete. This means that joins and sorting must be moved out to client applications. The data store’s only responsibility is to serve data as quickly as possible.
Of course, if key/value stores did nothing apart from serve data by primary key, they wouldn’t be terribly popular. What other features do they offer to make the desirable for production use?
It is very easy to scale a key/value store beyond a single server. By increasing the number of available servers, each server in the cluster is responsible for a smaller amount of data. By distributing data, it’s possible to get faster throughput and better data durability than is possible with a monolithic server.
Many key/value stores use a technique known as consistent hashing to divvy up the key space. Using consistent hashing means we can divide our key space into many chunks and distribute responsibility for those many chunks across many servers. Think of it like this: when you go to register in person at an event the alphabet has frequently been divided up into sections at separate tables. Splitting up responsibility for check ins across the alphabet means that, in theory every attendee can be served faster by having multiple volunteers sign them in. Likewise, we can spread responsibility for different keys across different servers and spread the load evenly.
Data is replicated across many servers. Replicating data has several advantages over having a single monolithic, robust, data store. When data is stored on multiple servers the failure of any single server is not catastrophic; data can still be read and written while the outage is solved.
Hinted handoff mechanisms make it easy to handle writing during a server outage. If a server is not available to write data, other servers will pick up the load until the original server (or a replacement) is available again. Writes will be streamed to the server responsible for the data once it comes back online. Much like replication, hinted handoff is a mechanism that helps a distributed key/value store cope with the failure of individual server.
Many distributed databases use a master server to coordinate activity and route traffic. Master/coordinator servers create single points of failure as well as singular bottlenecks in a system. Many distributed key/value databases bypass this problem by using a heterogeneous design that makes all nodes equal. Any server can perform the duties of any other server and communication is accomplished via gossip protocols.
The previous features add resiliency and fault tolerance to key/value data stores. Combining these features makes it possible for any node to serve data from any other node, survive data center problems, and survive hardware failures.
Column-oriented databases store and process data by column rather than row. Although commonly seen in business intelligence, analytics, and decision support systems, column-oriented databases are also seeing use in wide table databases that many have sparse columns, multi-dimensional maps, or be distributed across many nodes. The advantage of a column-oriented approach is that data does not need to be consumed as an entire row – only the necessary columns need to be read from disk.
Column-oriented databases have been around for a long time; both Sybase IQ and Vertica are incumbents, and SQL Server Apollo is Microsoft’s upcoming column store, slated for release in SQL Server Denali. Google’s Bigtable, Apache HBase, and Apache Cassandra are newer entrants into this field and are the subject of this discussion. Bigtable, HBase, and Cassandra are different from existing products in this field: these three systems allow for an unlimited number of columns to be defined and categorized into column families. They also provide additional data model and scalability features.
I have to speak in generalities and concepts here since there are implementation differences between the various column-oriented databases.
Data Model – Row Keys
A row in a column-oriented database is identified by a row key of an arbitrary length. Instead of using system generated keys (GUIDs or sequential integers), column-oriented databases use strings of arbitrary length. It’s up to application developers to create logical key naming schemes. By forcing developers to choose logical key naming schemes, data locality can be guaranteed (assuming keys are ordered).
The original Bigtable white paper mentions using row keys based on the full URL of a page with domain names reversed. For example
com.stackoverflow.blog. Because data is sorted by row key, this scheme makes sure that all data from Stack Overflow is stored in the same location on disk.
Data Model – Columns & Column Families
Column families are an arbitrary grouping of columns. Data in a column family is stored together on disk. It’s a best practice to make sure that all of the column in a column family will be read using similar access patterns.
Column families must be defined during schema definition. By contrast, columns can be defined on the fly while the database is running. This is possible because column families in a column-oriented database are sparse by default; if there are no columns within a column family for a given row key, no data is stored. It’s important to note that different rows don’t need to contain the same number of columns.
Column-oriented databases don’t natively support secondary indexes. Data is written in row key order. However there is no rule that data can’t be written in multiple locations. Disk space is cheap, CPU and I/O to maintain indexes is not.
The lack of secondary indexes may seem like a huge limitation, however it frees application developers from having to worry about how indexes might be maintained across multiple distributed servers in a cluster. Instead, developers can worry about writing and storing data the same way that it needs to be queried.
N.B. Cassandra has secondary indexes as of Cassandra 0.7
Picking the Right Database
Ultimately, picking the right database depends on workload, expertise, and future plans. It’s worth considering one of many options before settling on a relational database or one of many other databases. They all serve different purposes and fill different niches. The decision to store your data one way will have far reaching implications about how data is written, retrieved, and analyzed.