Selecting, testing, and benchmarking storage for SQL Server.
43 associated posts42 primary posts
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.…
We've got a client that does big batch jobs every day, loading hundreds of gigabytes of data or more in short bursts. They were frustrated with slow performance on the batch jobs, and after we performed our SQL Critical Care® with 'em, it was really clear that their hardware was the bottleneck. They were using…
One of the big reasons you spend big money on server-quality hardware is to get cool stuff to make administration easier. Each hardware vendor provides their own software tools - Dell includes OpenManage, IBM includes Director, and HP includes their System Management Homepage.
To illustrate how it works without violating anybody's NDAs, I picked up a used HP DL380 off eBay to use as a demo.
Everyone wants to make sure they're getting the best performance out of their solid state storage. If you're like a lot of people, you want to make sure you're getting what you paid for, but how do you know for sure that the drive is performing well?Watch that AverageThe first way to monitor performance it…
Developers frequently make a big mistake: they tune out on discussions of storage performance and IO. Don't fall into this trap! Understanding how storage impacts SQL Server performance helps you tune the right parts of your code and focus on what really matters. In this 30 minute video, Microsoft Certified Master Kendra Little demonstrates why developers need to understand the IO impact on writes and reads in SQL Server. She'll show you how simple configuration changes can sometimes save hundreds of hours of developer time.
Let's bust the buzzword bubble. Big Data is a sexy problem to have, so everybody's claiming it. I'm sick of people using this phrase to gloss over their real challenges. You have a Small Server problem. If you haven't bought a new server in the last two years, you're not allowed to complain about the…
Let's step back and take a look at the big picture. (Today, I'm writing for beginners, so you advanced gurus can go ahead and close the browser now. I'm going to simplify things and leave a lot out in order to get some main points across. Don't well-actually me.)
Microsoft SQL Server databases are stored on disk in two files: a data file and a log file.
I’m a bit of a researcher. When I want to know about a topic, I start off by doing a lot of reading. After I take a lot in, I want to make sure I understand the details. At that point, I try to think like a scientist. I come up with ways to test what I’m learning and see it in action. My process isn’t revolutionary, but it goes something like this:
Wanna get your storage learn on? VMware has a well-laid-out explanation of the pros and cons of different ways to connect to shared storage. The guide covers the four storage protocols, but let's get you a quick background primer first. iSCSI, NFS, FC, and FCoE Basics iSCSI means you map your storage over TCPIP. You…
Buying SAN gear is hard work. You have to compare vendors based on performance, capacity, reliability, ease of implementation, quality of support, and of course, price. When I'm working with a client buying new storage, here's the questions I have them ask each prospective storage vendor. Q: If we buy SSD, SAS, and SATA drives,…
I've written about how to test your SAN's performance with SQLIO, but I'll be honest with you: that's the hard way. It takes knowledge and time, and you only have one of those. (I'll be charitable and not tell you which one.) Instead, let's get seat-of-the-pants numbers for your storage. Go to the CrystalDiskMark download…
Geeks love duct tape. Sure, we pride ourselves on building a rock-solid solution the right way, but when the brown stuff hits the moving metal stuff, we love to show off our ingenuity. We carry our money in duct tape wallets, wear duct tape shirts, practice ductigami, and hang our duct tape from custom metal brackets.
Database administrators want just one kind of storage: fast.
Companies want just one kind of storage: cheap.
Solid state drives (SSDs) have gotten a lot of press because they can be dramatically faster than magnetic hard drives. They use flash memory instead of spinning magnetic platters. Since any bit of memory can be accessed at any time without moving a hard drive's head around, SSD random access is insanely fast. How fast…
In part 2 of this series, I cover magnetic hard drives, solid state drives, and RAID arrays. I explain the pros and cons of each, and I discuss their relative speeds.
In part 1 of a multi-part series, I talk about what SQL Server stores, why your data and transaction files are usually best kept on separate drive arrays, and why cache doesn't always help.
Storage Area Networks (SANs) can do multipathing, but what does that really mean? I explain the differences between failover and performance load balancing, and talk about why active/active multipathing isn't what it seems.
In the beginning, God made drives directly attached to our computers. Over time, man decided to get jiggy with it and use Storage Area Networks. Learn why pathing is so important to these two systems.
I don't talk about hardware much here, but an SSD review hit Engadget this morning and made me take notice. Fusion-io gave their new 80gb ioDrive to TweakTown for a review, and - Wait, hang on. Don't close the browser yet. I know, everything about this post reeks of some tiny manufacturer with borked products…
This week I presented at the SSWUG Virtual Conference on how to benchmark your storage performance with Microsoft's SQLIO utility. Last week I talked about running SQL Server in the cloud on Amazon EC2. Take those two things together, and we've got storage benchmarking on Amazon EC2 servers:
Maximum write speed: 68 MBs/sec
Maximum read speed: 8 MBs/sec
