Tag Archive: san

Free SAN 101 video from the SSWUG Virtual Conference

I just flew out to Tucson to film my sessions for the upcoming SSWUG Virtual Conference.  It’s a pretty cool setup – SWUG has a full blown TV studio in their offices, and it’s really professional.  (Trust me – I know when something’s unprofessional.)

Invisible Cheezburger

Professor Ozar

The best way for me to explain it is to show you what a session looks like: here’s my SAN 101 presentation from last year.  You can watch it in its entirety for free.  You do need a high-speed internet connection – it doesn’t work well with slow connections like the one here in my hotel room.  This year, they’ve upgraded their cameras to high definition, too, so you can get better views of code-intensive demos.

If you like that SAN 101 presentation, then check out the full list of SQL Server abstracts.  There’s some fantastic speakers in there, top notch people, and it’s a heck of a deal for around $100.  In this economy, that’s the most cost effective way to get trained.  Show your boss my SAN 101 video to demonstrate the quality of what you’re getting for your money, and then point at the full list of sessions.  It pretty much sells itself once you see the video quality.

Register for the SSWUG V-Conference now and use VIP code SPVBOZSP09 for $10 off.  That coupon code can be combined with other discounts, too, like the early bird registration or the alumni registration.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

SQL Server and Cloud Links for the Week

SQL Server Links

Find Missing Indexes in Stored Procedures – Michelle Ufford, aka SQLFool on Twitter, shows how to set up a T-SQL script that will regularly monitor your server for any query plans in the cache that are missing indexes, and store that data in a table for later retrieval.  See, the dynamic management views (DMVs) that store this data don’t stick around forever, and it’s important to grab the data if it’s only available briefly.

Querying dm_exec_query_stats and dm_exec_cached_plans – Continuing with the theme from the above article, Elisabeth Redei writes about similar ways to get performance data from SQL Server without running a trace.

Best Practices for SQL Server on a SAN – Jimmy May talks about new resources from the Microsoft SQL Server Customer Advisory Team’s Mike Ruthruff.

SQL Server and Null Values Revisited – Aaron Alton explains when nulls make sense in tables.

MySQL DBA

The Original MySQL DBA

10 Things You Need to Know About MySQL Backups – “Does the backup use LOCK TABLES?”  What?  Are you serious?  There’s such a thing as a backup solution that locks all your tables while it runs?  After reading this list, all I can think of is McGyver – I bet he would have made a great MySQL DBA.  “I gotta back up this server, and all I’ve got is a coat hanger, two socks and a pound of bacon.  Let’s do this.”

T-SQL Challenge from Adam Machanic – Wanna win a full-blown MSDN subscription?  Got mad T-SQL skillz?  Adam’s running a contest that involves using a single T-SQL statement to concatenate multiple strings from AdventureWorks.  I love reading the comments and trying to reverse-engineer what the person is thinking about doing: for example, one of the commenters asked if indexes could be added.

SQL Server Object Ownership – K. Brian Kelley explains how this concept changes from SQL 2000 to 2005/2008 and gives in-depth scripts to walk through it.

When Index Seeks are Actually Index Scans – Gail Shaw explains that when you’re reading an execution plan, an index seek might still be rolling through all of the records in the index.  Don’t just dismiss “index seek” as everything being all good.

Cloud and Virtualization Links

Nothing especially interesting this week.

The Junk Drawer

Paying Down Your Technical Debt – I love this concept: as you’re building stuff, you’re amassing a technical debt with every shortcut you take.  It’s like a looming credit card debt, and sooner or later you have to pay it down or else it’s going to start costing you real money.  Performance costs money, plain and simple.

Fusion-io Solid State Drive Discussion – I mentioned the Fusion-io drive a while back because it looked like a neat technology – solid state storage via an PCI-Express card.  Their CTO David Flynn calls it a SAN in your hand (catchy) but I would note that SANs have a high level of redundancy, like multiple paths to the storage.  The Fusion-io drive is a single PCI-Express card in a single server, which makes it a very singular point of failure: if your box goes down, your data is offline.  That’s nothing like a SAN, where the data can be accessed from multiple servers (think clustering.)  I’m not saying the Fusion-io isn’t cool – it certainly looks cool – but it’s not a SAN in your hand.  Expect to see more of these types of solutions – OCZ just announced one for around $2k.

How FriendFeed uses MySQL to store schema-less data – when you’re desperate for performance, you start thinking farther and farther out of the box.  This is McGyver too, but in a cool way.

Three Kinds of Meetings – Seth Godin explains the three kinds of meetings: information, discussion, or permission.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

Partition alignment in virtual machines

Partition alignment is a setup trick for disk-intensive apps like Exchange and SQL Server that can have a 30% or more performance impact on  your server.  The setup information is in my SQL Server Setup Checklist, and Jimmy May has more details on the mechanics of partition alignment.

If you run SQL Server in VMware, this still applies to you.  Today, Duncan Epping of Yellow Bricks (a VMware-focused blog) brings news of a new way to test whether your virtual machines have been set up correctly with partition alignment.  VMware admins can run a script on their VMware host, which scans all of their guests and makes sure their partitions are aligned.

The bad news is that it can’t actually fix the partition alignment – the only way to do that right now is to delete the partition, which means a complete backup and restore with a lot of downtime.  The article hints that NetApp is building a utility that will fix partition alignment on VMware guests on the fly.

For more details, read Duncan’s blog entry about VM partition alignment.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

New internal solid state drives are “world’s fastest storage”

World's Fastest Storage?

Fastest Storage in the World?

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 and a one-page pamphlet instruction manual written in Engrish.  TweakTown is not exactly the world’s most trusted source for enterprise-class database hardware, but bear with me for a minute.  And for the record, Fusion-io is based in Utah.

So anyway, here’s the stats that TweakTown (cough) is reporting:

  • 80gb = $2,995, 320gb = $14,995
  • Around 460 MB/s reads (for reference, their raid 5 array of 8 15k drives got around 500 MB/s)
  • Around 350 MB/s writes (reference array was around 370 MB/s)
  • .05 milliseconds read latency (reference array was around 5ms)
  • .02 milliseconds write latency (reference was 2ms)

Okay, it’s quick.

The Drawback: No RAID

TweakTown’s review was nothing short of glowing, but as a SQL Server DBA, I immediately had an issue with it.  Notice that this isn’t a SATA drive – it’s an internal PCI-Express card.  How do I protect against failure?  Sooner or later it’s going to fail, because everything fails, and I want some level of redundancy.  I can plug two of them into a server, but then I have two drives, and I have to implement software RAID.  Ugh.  Let’s say I’m okay with that (which I’m not) and I implement it – what kind of speed hit do I take?

Where the Fusion-IO Drive Makes Sense

If I had a SQL Server with excessively high TempDB load, to the point where TempDB was the biggest bottleneck in the system, I’d buy one of these and put TempDB on it.  After all, if TempDB fails, I haven’t really lost much.  Heck, my SAN admins would probably buy it for me to get my TempDB off the SAN if the load was that bad.

If I had a SQL Server in a colo datacenter, and I needed more storage speed, this would be a fast way to get it without paying more monthly rental fees for space for a SAN.  SANs suck up a lot of rack space, and a couple of these in each database server (although probably the larger size ioDrives) would probably cost less than two years of colo space rental.  Your mileage may vary.

And if I had a million dollars, as the Barenaked Ladies so elegantly said, I’d buy one for my girlfriend.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

Steel Cage Blogmatch Part Two: The SQL

Jason Massie and I had a steel cage blogmatch about whether to configure a SAN with shared or dedicated drives, and we both passed out for exhaustion.

This debate is starting up again but with a slightly different spin: virtualization.  In an environment with Microsoft Hyper-V or VMware ESX, more than one SQL Server may have its virtual drives on the same physical hard drives.

If we have two SQL Servers, are we better off keeping their virtual hard drives on separate physical hard drives, or using one larger pool of hard drives shared between the two?  Microsoft’s technical article “Running SQL Server 2008 in a Hyper-V Environment: Best Practices and Performance Considerations” attempts to answer that burning question with Figure 16 on page 24:

Shared Disks vs Dedicated Disks

Shared Disks vs Dedicated Disks

This graph shows two things:

  1. Nobody spell checked the graph title, and
  2. Dedicated drives were faster.

However, they’re only 3.5% faster on average.  Management of the shared disk approach is much more than 3.5% easier, so I’d have to vote for the shared drive approach, much to Jason Massie’s glee.

Plus, keep in mind that your servers probably won’t all be under full load at all times.  During periods where only one server is under heavy load, that server should achieve higher performance throughput since they’ll have more idle spindles available to them.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

Jimmy May explains Partition Alignment (#sqlpass)

Before attending this session, I’ve always gone to KB article 929491 article on partition alignment.  I thought I kinda sorta understood what was going on, but Jimmy’s presentation drew on the best graphical illustrations to show it.  Not to mention his audio stylings like, “Holy correlated wait stats!”

Get your binoculars out

Get your binoculars out

Partition alignment is one of those hidden performance tweaks that makes a big (10-20-30%) performance impact.  Windows 2008 (and Vista) fixed this, but only for new partitions – if you upgrade an existing Windows 2003 server and don’t blow away the partitions, you’re still affected.

How much will you really be affected? Jimmy’s experiments with unaligned and aligned raid 10 arrays had a 6-disk aligned array outperforming an 8-disk default array.  Whoa – that’s a 30% cost savings.  He’s a hilarious presenter, which was great because he ended up sounding like a weight loss salesperson.

Partition alignment used to be all about the mechanics of hard drives, but today it’s all about RAID stripe sizes and SAN cache.  You can’t just align your partitions at 32kb.  While that might have worked in the days of locally attached physical hard drives, it doesn’t line up with common RAID stripe sizes.  It even matters for virtual machines, Jimmy says, because the virtual hard drive file lines up with the hard drives on the host.

If your vendor says partition alignment doesn’t matter, you have the wrong contact person at your vendor.  I back him up 100% on that.  No exceptions.

For basic disks – disk alignment is performed with diskpart, but you can’t use that for reporting alignment. He gave a WMI script to grab the partition offsets, but again, only for basic disks.

For dynamic disks – For Windows dynamic disks, use dmdiag.exe -v (which means verbose).  The v is required.  For Veritas dynamic disks, use the Veritas tools.  He didn’t drill into this in the presentation.

He also touched on the importance of stripe sizes and file allocation unit sizes.  He didn’t recommend rebuilding just because of 4kb cluster sizes, but if you’re redoing a partition from scratch, use the size your vendor recommends.

The bad news: you can’t fix any of this online. You have to back up your data, blow away the partitions, align them, and restore the data.  But hey, good things come to those who wait.

Jimmy May’s blog on SQL Server storage is a great resource to check out.  I hadn’t seen it before, so I subscribed because he kept swearing that he’s going to write more entries about it.  I’ve gotta hit this guy up to do video podcasts, because he’s hilarious.

To learn how to align partitions, check out my SQL Server setup checklist.

There’s probably some other valuable in there that you might be missing!  I cover a lot of easy ways to get more performance out of your SQL Server.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

Bad storage performance on Amazon EC2 Windows servers

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

I find this too horrifying to comprehend.  I can’t imagine running a database server using a first-generation USB thumb drive for storage, but that’s essentially what this is.

If anybody out there is using Windows on Amazon EC2, I’d be really curious to hear what your SQL Server performance is like relative to your physical in-house servers.  If you’ve got the time, running SQLIO on your EC2 instances would be even better, but it takes several hours to run through my full battery of SQLIO tests.  Running any storage testing utility against an EC2 hard drive would be interesting.

This was not a one-time result – this was a continuous overnight test against an m1.large instance with nothing installed on it.  Every single read test maxed out at around 8 MBs/sec.  Unbelievable.

I’d love to spend more time digging into this, but I’m signing off the computer tonight to start our move up to Michigan.  (I have a linkpost scheduled for Friday already.)  When I get to Michigan, I’ll try a couple of other storage performance test utilities against EC2 instances to see if I’m missing something obvious, and I certainly hope I am.  I’ve never seen performance this bad on a desktop, let alone a server.

Update from Amazon EC2 Support

There’s a thread about slow Amazon EC2 performance for storage on the Amazon forums.  The official answer from Amazon is yes, it’s that slow but they believe it’s normal for SAN storage.  They’re way off base there – I routinely get faster performance out of my home lab gear – but at least they’re watching the forums.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

Storage Performance Testing with SQLIO

SQLIO is probably the worst-named tool in history.  It’s a free storage testing program from Microsoft, but it has absolutely nothing to do with SQL Server whatsoever.  It’s still a great tool though – you can use it to push as much load as possible into your hard drives and storage area networks (SAN).  Learn how to use it in this ten-minute video:

Learn more in my full SQLIO tutorial article.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

Watch a free 10-minute preview of SSWUG videos (update with discount)

You’ve heard me talk about my SSWUG video conference sessions, but you’re not sure whether it’ll work, or whether it’s worth the money?  Well, Chris Shaw and the good folks at SSWUG are giving away free previews to show you how good it looks.  You can watch the first ten minute of my SQLIO session for frrrrrrrreeeeeee:

Yes, I really do talk with my hands, and yes, I use an Apple Macbook Pro.  The stickers are:

More stickers to come – I’m looking forward to getting some at PASS and plastering them all over the Mac.  Erika hates it when I do that because the Mac does indeed look better naked, but I don’t want you guys thinking I’m a graphic designer or something.

Update 10/29 – I just got word that if you use the VIP code “BOZAVIP” when signing up, you get $10 off your registration, bringing it down to $90.

There’s a drawback, though, and I’m going to tell you about it because I believe that honesty is the best policy.  If you use that signup code, I get $5.  If more than ten of you use the code, my cut goes up to $10 per person.  Here’s where the drawback comes in: Erika has already given me permission to spend my SSWUG money as “fun money” on my week-long Caribbean cruise in December.  I might come back with alcohol-induced amnesia or a bad tattoo.  So maybe it’s better for all of us if you don’t use that code.  I’m just putting it out there, your choice.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

SAN Tips for First-Time Users

If you’re thinking about buying your first storage area network, do yourself a favor and go to the Aston Martin dealership first.  Smell the leather – that is, if they’ll let you sit in one – and play with the switchgear to feel how nice and solid it feels.  Pretty nice, huh?

Or maybe that’s not your thing – maybe you’re a boat person.  In that case, swing by Hinckley and check out the T38R, which has a no-fuss, no-sweat power top just like the one in the Aston Martins.  They don’t have as many local dealers as Aston Martin, and you can’t swing into Cabela’s to check out their boats, but trust me, it’s worth the trip.  They’re just gorgeous machines, very elegant and timeless.

After either of these two trips, your first SAN will seem inexpensive by comparison.

Getting into storage area networking is very expensive, and every mistake in SAN technology will cost you and your company six or seven figures.

Want to learn from my many expensive mistakes?  Register for the SSWUG Virtual Conference on SQL Server, and for $100, you can attend my SAN Tips for First-Time Users seminar along with dozens of other SQL Server presentations from experts like Brian Knight, Buck Woody, Chris Shaw, Stephen Wynkoop and more.  I crammed a lot of information into that seminar – so much, in fact, that I had to race through the last half-dozen slides to finish the presentation before the 57 minute limit!

And by the way – the other reason to check out Astons and Hinckleys is because SAN admins make the big bucks.  Might as well start that relationship with the dealer now – if you get into SANs, you’ll be picking up your own Picnic Boat in no time.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube