Blog

We work with SQL Server every day, happily sending and receiving millions of packets of data across the network. Have you stopped to think about what happens when something goes wrong?

The Situation

It’s a regular Tuesday afternoon. Users are happily submitting orders into the shopping cart. After browsing around the website for a while, the users decide that it’s time to buy something. After clicking “Check out now”, the user goes through the check out process, enters their payment information, and then clicks “Buy some stuff!” A few milliseconds later, they get a message that their order was successful and everything is zipping over to their house.

It’s something that we do every day, right? Heck, it’s simple enough that I drew a little diagram:

The Problem

What happens when something goes wrong?

There are a lot of places where things could go wrong – a browser bug could prevent form submission, the shopping cart application could go down, the ordering system could be down for maintenance, or SQL Server might even crash (but you’ve configured a solid HA system, so that ain’t gonna happen).

What’s left to fail? The network.

Everything works as it should – the user clicks “Buy some stuff!” and a message is sent to the application servers. The application servers do their magic, approve the credit card, build an order, and save the order in SQL Server. Immediately after SQL Server acknowledges the write and commits the transaction, but before any confirmation is sent to the user, the top of the rack switch power cycles. Randy, the operations intern, accidentally unplugged the power cord before plugging it back in.

Now What?

This is the part that’s up to you. Sound off in the comments:

• Did the user’s write succeed or fail?
  • What does SQL Server think?
  • What does the application think?
  • Did SQL Server do the right thing?
• What can you do about this sort of thing?
• How can your application handle failures like this?

Storage vendors brag about the IOPS that their hardware can provide. Cloud providers have offered guaranteed IOPS for a while now. It seems that no matter where we turn, we can’t get away from IOPS.

What Are You Measuring?

When someone says IOPS, what are they referring to? IOPS is an acronym for Input/Output Operations Per Second. It’s a measure of how many physical read/write operations a device can perform in one second.

IOPS are relied upon as an arbiter of storage performance. After all, if something has 7,000 IOPS, it’s gotta be faster than something with only 300 IOPS, right?

The answer, as it turns out, is a resounding “maybe.”

Most storage vendors perform their IOPS measurements using a 4k block size, which is irrelevant for SQL Server workloads; remember that SQL Server reads data 64k at a time (mostly). Are you slowly getting the feeling that the shiny thing you bought is a piece of wood covered in aluminum foil?

Those 50,000 IOPS SSDs are really only going to give you 3,125 64KiB IOPS. And that 7,000 IOPS number that Amazon promised you? That’s in 16KiB IOPS. When you scale those numbers to 64KiB IOPS it works out to 1,750 64KiB IOPS for SQL Server RDS.

Latency vs IOPS

What about latency? Where does that fit in?

Latency is a measure of the duration between issuing a request and receiving a response. If you’ve ever played Counter-Strike, or just run ping, you know about latency. Latency is what we blame when we have unpredictable response times, can’t get to google, or when I can’t manage to get a headshot because I’m terrible at shooters.

Why does latency matter for disks?

It takes time to spin a disk platter and it takes time to move the read/write head of a disk into position. This introduces latency into rotational hard drives. Rotational HDDs have great sequential read/write numbers, but terrible random read/write numbers for the simple reason that the laws of physics get in the way.

Even SSDs have latency, though. Within an SSD, a controller is responsible for a finite number of chips. Some SSDs have multiple controllers, some have only one. Either way, a controller can only pull data off of the device so fast. As requests queue up, latency can be introduced.

On busy systems, the PCI-Express bus can even become a bottleneck. The PCI-E bus is shared among I/O controllers, network controllers, and other expansion cards. If several of those devices are in use at the same time, it’s possible to see latency just from access to the PCI-E bus.

What could trigger PCI-E bottlenecks? A pair of high end PCI-E SSDs (TempDB) can theoretically produce more data than the PCI-E bus can transfer. When you use both PCI-E SSDs and Fibre Channel HBAs, it’s easy to run into situations that can introduce random latency into PCI-E performance.

What About Throughput?

Throughput is often measured as IOPS * operation size in bytes. So when you see that a disk is able to perform X IOPS or Y MB/s, you know what that number means – it’s a measure of capability, but not necessarily timeliness. You could get 4,000 MB/s delivered after a 500 millisecond delay.

Although throughput is a good indication of what you can actually expect from a disk under perfect lab test conditions, it’s still no good for measuring performance.

Amazon’s SQL Server RDS promise of 7,000 IOPS sounds great until you put it into perspective. 7,000 IOPS * 16KiB = 112,000 KiB per second – that’s roughly 100MBps. Or, as you or I might call it, 1 gigabit ethernet.

What Does Good Storage Look Like?

Measuring storage performance is tricky. IOPS and throughput are a measurement of activity, but there’s no measure of timeliness involved. Latency is a measure of timeliness, but it’s devoid of speed.

Combining IOPS, throughput, and latency numbers is a step in the right direction. It lets us combine activity (IOPS), throughput (MB/s), and performance (latency) to examine system performance. 

Predictable latency is incredibly important for disk drives. If we have no idea how the disks will perform, we can’t predict application performance and have acceptable SLAs.

In their Systematic Look at EC2 I/O, Scalyr demonstrate that drive latency varies widely in EC2. While these numbers will vary across storage providers, keep in mind that latency is a very real thing and it can cause problems for shared storage and dedicated disks alike.

What Can We Do About IOPS and Latency?

The first step is to make sure we know what the numbers mean. Don’t hesitate to convert the vendor’s numbers into something relevant for your scenario. It’s easy enough to turn 4k IOPS into 64k IOPS or to convert IOPS into MB/s measurements. Once we’ve converted to an understandable metric, we can verify performance using SQLIO and compare the advertised numbers with real world numbers.

But to get the most out of our hardware, we need to make sure that we’re following best practices for SQL Server set up. Once we know that SQL Server is set up well, it’s also important to consider adding memory, carefully tuning indexes, and avoiding query anti-patterns.

Even though we can’t make storage faster, we can make storage do less work. In the end, making the storage do less gets the same results as making the storage faster.

You’re making excuses.

The database application is slow, and you’re throwing something under the bus, but we’ve got bad news. Changing it probably isn’t going to make your server faster. Here’s some of our favorite excuses.

Kendra SAYS, “Your Problem Isn’t GUIDs.”

Unique identifiers may or may not be globally unique, but they are one thing: the universal scapegoat for people who aren’t really sure what’s causing their performance problems.

I’ve met a lot of GUID blamers. I’ve had people introduce myself to me and immediately say, “My application is soooo terrible. There are GUIDs everywhere!” 

I’ve got news: GUIDs aren’t evil, and using them isn’t a sign of pure incompetence. The same thing is true of natural keys— using them doesn’t necessarily mean your database is going to perform terribly. If your application is slow, then yeah, you should consider schema design and key choices as part of the landscape, but being dogmatic about “good” and “bad” types isn’t going to help you figure out what the most effective changes are to improve your performance.

I’d just love to get back the three days of my life I once spent deploying a key change to “get rid of the problem GUIDs” in a schema. Especially since afterwards performance wasn’t noticeably any different.

Brent SAYS, “Your Problem Isn’t the SAN.”

You’ve been measuring Perfmon metrics like Average Disk Queue Length and Disk Seconds/Read, and you’ve been comparing the results to the SQLCAT list of OLTP problems. You keep waving the results at the SAN admin, and he keeps shrugging, saying that everything looks okay on his end.

That’s because it probably is.

You’ve gotta stop thinking that your SAN is going to hand-deliver every byte to you within a millisecond of the request. It’s not gonna happen. It takes millions of dollars to build a fast SAN, but it takes one heck of a lot less to build a server with a buttload of memory. If you’re doing online processing, the answer isn’t faster disks, because even the fastest drives can’t hold a candle to memory. If you can’t cache it in memory, users are going to call it slow. Full stop.

Kendra SAYS, “Stop saying nobody listens to you”

When you say: “Nobody listens to me!” People hear something else. They hear you say: “I’m not a leader.” They hear you say: “I don’t know how to communicate.”

The more you say this, the less and less people want to listen to you. And the less you expect anyone to listen, either. 

You can fix this. Take a step back. List out three recent changes that you’ve recommended that you haven’t been able to get traction on. For each of your examples, consider:

1) Is communication the problem? If you’re relying on emails, that’s usually not going to get the job done. Develop a strategy to communicate more effectively: and yes, this means talking to people face-to-face at times and listening to what others have to say, too.

2) Do you lack technical credibility? This is an area where training can help. Students come to our classes to ask questions about how to effectively approach real world problems.

3) Do you need an independent opinion? Sometimes different teams get into fingerpointing standoffs, and nobody’s sure where they stand with anyone else. We get brought in as consultants to take an independent look at bottlenecks, defuse any blame fires, and set people on a path to solve their pain points as a team.

Leadership isn’t something that you’re entitled to because of your job title. Leadership is something we each must earn– and we’ve gotta work to keep on earning it. You can become influential and you can lead people! You’ve just got to build your skills to do it.

Brent SAYS, “Your Problem Isn’t the DEVELOPERS, EITHER.”

Brent says: I know, it’s trendy to complain about how the developers do stupid stuff. I make jokes about this all the time on DBAreactions.

Thing is, though, they’re shipping stuff.

Useful stuff. Valuable stuff. Stuff the end users actually want. You think they’re being sloppy because they don’t know any better, but the truth is that they don’t get unlimited time to build everything perfectly. Your management wants to ship features out the door and keep moving forward.

When was the last time you, the DBA, shipped something that end users asked for? Did you build it absolutely flawlessly, or did you duct tape it together to get it out the door?

I bet you could take the diamond-in-the-rough from the developers, put some work into tuning the server and the indexes, and make it fly. And if you can’t, if you’re only willing to work with flawless code, I’ve got bad news for you. I’ve worked with those kinds of developers, like the ones at StackOverflow.com, and they don’t need you. (In fact, Stack doesn’t have a full time DBA, and they’re one of the 200 biggest sites on the web. Think about that for a second.)

Last week, I went to summer camp for geeks, and I had a great time! That Conference is a three-day, developer-focused, family-friendly technology conference. Camp was held for the first time in 2012. I heard such great things about it from last year’s campers that I decided to go this year!

The sessions focused on mobile, web, and cloud development. Being a SQL Server geek, I was a bit of a stranger in a strange land. I confess I couldn’t answer any of the .NET Rocks! trivia questions during the prize giveaway at the end, but I still took a ton away from this conference. Here are my five favorites!

#That5K

I love running, and I try to find fellow runners at SQL Saturdays, PASS Summit, and even at our training classes. I saw that there would be a group running each morning of That Conference, so I decided to join them. I’m glad I did! I got to know a couple fellow runners and geeks better. The second day, one person told us it was the first time he’d ever run 3 miles straight, and another person said it was the first time in his life he’d ever woken up with the intent to exercise. Those comments made the early wake-ups worth it. Your physical health ties directly into your mental health and ability to enjoy life – don’t neglect it.

Technology-Agnostic

My primary focus is SQL Server, but when I work with clients, I’m not working with just the DBA. It’s frequently a group – a server admin, a manager, and a handful of developers that work with the system. They may not speak my language, and I’m not fluent in theirs. Most conferences focus on one topic – SQL Server, SharePoint, .NET. Not this one – it was a mix of .NET, PHP, Ruby, Java, mobile development, and more. Thus, this was the perfect conference for me to attend – learning what’s important to developers and speaking their language.

I attended sessions on PHP, Git, DevOps, website development and optimization, PaaS in the cloud, and Azure HA/DR, among other topics. I learned a ton. I also learned what developers care about – and it’s not how long the database backups take each night!

Fellow Campers

I met great people. Everyone that I sat down next to at breakfast or lunch was friendly and willing to chat. The speakers were top-notch.  It’s a great community-focused event where we were encouraged to network and share – and everyone did.

Arduino

Arduino is “an open-source electronics prototyping platform based on flexible, easy-to-use hardware and software.” Let me rephrase that: Arduino lets you make dancing robots.

I had the distinct pleasure of watching Sharon Cichelli give a talk about Arduino. Rather than a dry, boring presentation, she showed us how she learned electronics and this programming language to make a dancing robot. Having never owned an electronics kit or touched a breadboard before, I wasn’t sure I would understand a word. However, in 10 minutes, I was captivated and understanding electronics diagrams. When she wrote simple code to make her robot fret and wiggle, I was in love. The best talks are given by people who are passionate about a topic, and she clearly was. I’m tempted to get a “100 Electronics Projects for Kids” kit and let my inner geek out now!

Open Campfires

The difficulty with every conference is that there are always topics I want to talk about or learn more about that aren’t on the schedule. There are times I want to find a group of people sitting around a table, chatting, and join them. The Open Spaces concept fills that void perfectly. There was a large hall set aside specifically for this. Half of it was tables that anyone could use at any time – to work, to chat, to program. The other half was set up in four circles of chairs. If you wanted to lead a session at any time – whether you had a question you wanted to ask other people, or toss ideas around – all you had to do was put a topic and your name on a post-it note on the board.

The board was filled with topics from “Bitcoin” to “Unblocking Creativity” to “Improving the Workplace”. I sat in a couple of them my first day at lunch and was hooked. People were sharing ideas, confidently talking about what worked for them and what didn’t, and teaching me a ton.

I decided to throw my own in, and put down “Performance Tuning: What and How” on the second day after lunch. I wanted to find out what steps developers took to tune their code, and how they would get to the point of saying, “It’s a database problem.” I also wanted to know what questions they had about SQL Server performance tuning. The worst part about this? It was only an hour. It flew by. Ten or so of us discussed everything from how indexes work to caching, execution plans to baselines.

What really impressed me was people’s willingness to talk. Everyone at that conference could have taught me something. Most people are scared of talking in front of people, so they never submit a session. But when you put them in a small group and let them talk about a topic they care about, they will open up. I learned most from those people, sharing their day-to-day challenges. It was inspiring!

Let’s Have S’mores!

I didn’t think anything could top the sessions and networking, but the conference ended with a s’mores and bacon bar. Perfect!

I can’t wait to go camping at That Conference next year!

Performance tuning is a weird art to learn, because you do it in public. Sure, you may puzzle over a technical problem at your desk or revise code on your own– but when it comes to really tuning hard problems, 99% of the time you’re doing it with other people. That’s the nature of real performance tuning.

Just like learning to dance, it can be awkward to get into performance tuning. What if you miss something obvious? What if the idea you suggest is totally stupid? What if you try to do things that are, frankly, impossible?

You’re worried that they’re all gonna laugh at you.

Everyone has these fears at first. If you keep at it, you’ll get through it.

Stage 1 – The Kid’s Recital: Everyone starts out here. You’ve read some blog posts, you’ve done a little practicing, and now when things get tough, you say, “Hey, what about this!?!?”

People can tell that you’re a little new at this, and maybe you’re a little overeager, but you probably make up for it in excitement. Don’t get stage fright. You know that sometimes people don’t think you’re a total pro, but you’ve got to just keep practicing and getting out there and performing. Go out there and try to do your thing.

You secret power at this stage: Listen to other people. Learn from them as much as you can.

Stage 2 – The Rebecca Black: Eventually, you learn to sing a song that’s pretty catchy. You find a few ways to make something blazing faster. People start to listen to you more and more, and they come to you to ask your opinion more often.

At this stage you just know a few tricks, but they seem powerful. You feel like “This stuff wasn’t so hard! I know everything I need to know!”

The rush is blissful, but usually brief. Suddenly you’re rushing into trouble and trying to tackle too much alone. It turns out that you really know a few tricks, and soon enough you start to make mistakes. Hopefully, it humbles you. (If it doesn’t, well, bad news. Nobody’s gonna wanna work with you if you get stuck in this phase.)

You wonder if you’ll ever be more than a one-hit-wonder.

Stage 3 – The True Karaoke Star: If you keep at it, you’ll get comfortable, and you’ll learn to embrace what you don’t know. You’re used to solving performance problems. You’ve got not just one or two tricks in your bag, but many different techniques, and you know their limits. You’ve got good sources to go to, and you love to question your assumptions.

More importantly, you’ve made it to that golden place where you’re no longer embarrassed, but you’re not over-confident, either. You have your voice and you also listen to people. You don’t panic at the first sign of trouble, and you also know that the show doesn’t have to be all about you– other people can get on stage, too.

Sometimes it Takes a While. Finding the right job and lifestyle where you can grow and learn is the hard part. That probably won’t fall into your lap. It’s OK. Everyone has to start somewhere– and for some of us it takes volunteering, working nights, or taking risks on our career to try something new.

Whichever way you get there, don’t get discouraged. You can be a rock star, too.