New Project Starting. Got Gas?

merrillaldrich — Mon, 10 Sep 2012 19:32:02 GMT

“Storage is just like gasoline,” said a fellow DBA at the office the other day. This DBA, Mike is his name, is one of the smartest people I know, so I pressed him, in my subtle and erudite way, to elaborate. “Um, whut?” I said. “Yeah. Now that everything...(read more)

Scandalous II: Shh! I am De-duplicating Compressed Backups

merrillaldrich — Sat, 23 Apr 2011 05:01:50 GMT

This is part II of two Scandalous posts . Watch, mouth agape, as I run with scissors, right up against prevailing wisdom! Unfollow me now, before it’s too late! Here’s the thing. There are two really outstanding posts out there on the ‘tubez that explain...(read more)

T-SQL Tuesday #004: Real World SSD’s

merrillaldrich — Tue, 09 Mar 2010 04:22:00 GMT

A contribution for T-SQL Tuesday #004 , hosted by the illustrious Mike Walsh! In the past few weeks I had some correspondence with Kendal Van Dyke leading up to his SQL Saturday presentation on SSDs, and he got me fired up to share a little of my team’s...(read more)

SAN 101 for the DBA

merrillaldrich — Tue, 16 Feb 2010 22:45:00 GMT

As will become apparent from this post, I am no Storage Admin. My apologies for offending the sensibilities of those who know this topic better than I do! I get asked occasionally about placing SQL Server data on SAN storage, and I've done it with a few...(read more)

Using Historical Perf Counter Data For Storage Planning

merrillaldrich — Thu, 29 Oct 2009 23:42:00 GMT

Lately I'm faced with a fairly ambitious data center move, and at the same time with an initiative to consolidate sprawling SQL Servers onto centralized clusters. It's a chunk of work, but these two notions have fit together pretty well: as long as we're...(read more)

SAN Disk Array Performance: Beware LUN Concatenation

merrillaldrich — Sun, 26 Jul 2009 23:17:00 GMT

I'd like to pass along a couple of tips for those new to using SAN storage for SQL Server. SAN Storage is quite expensive, and doubly so if your storage doesn't deliver on the performance front. SAN disk arrays are not magic, and sadly they don't just automagically perform well, marketing to the contrary. These are some items I have found helpful in configuring them:

Create and maintain a good relationship with your SAN admin. You need to know the details of how the storage is laid out for your SQL Server, and how to describe what you need in a way that she can understand. Don't treat it like a black box.
Find out about the disk offset / alignment issue. I won't cover that here, as it's well documented elsewhere. Just be sure to use DISKPART and the ALIGN parameter on Windows 2003 or earlier.
When you spec anything that is demanding performance-wise, to your SAN administrator, don't just ask for a quantity of space -- include a performance threshold in IO's per second. The number of physical disks required in the array, just as with direct attached storage, will most likely be driven by your performance needs and not the capacity you ask for. DB's that need a lot of disks on DAS also need a lot of disks in a SAN attached array, for all the same reasons. (Here's a great recent post about performance-centric design from Rod Colledge.)

With larger LUNs, it's common practice to build them by ganging together smaller groups of disks - for example, if you need 24 disks, they might be organized first into LUNs of 6 disks each, then grouped into the 24-disk set (sometimes called a "meta-LUN"). The details of how that is done are vital to performance.

For the LUN spec, it's fairly straightforward if you have an existing system that you can use as a benchmark (even if it's underperforming). Do project the quantity of storage that you'll need, but don't stop there -- use Perfmon to record the physical disk counters describing IO per second disk performance with the system under load. If the existing system's disk performance isn't adequate, apply a multiplier to get to the requirements for your new LUN. Provide the SAN administrator the quantity of storage, the IO measure in Reads and Writes per Second and the bandwidth you need in MB per second. It often happens that the IO performance measures are the limiting factor, and that they are limited by physical disk hardware, implying that the SAN Admin will have to provide more disks than would be required for raw capacity.

Next, be aware of the issue with Concatenated LUNs vs. Striped Luns. To take the example of the 24 disk LUN above, there are two ways to gang together those groups of 6 disks: striping and concatenation. Concatenation will chain them together in sequence, such that the first six disks work in concert to provide the first 1/4 of the total storage, then when those are full, the second set provides the next 1/4 of the storage, and so on. That may be OK for another app, like file storage, but this is exactly the wrong effect for a database! If you imagine those flashing busy lights on the disk array, it means that as you start using the LUN, the first six disks will light up and the others will be essentially idle. The resulting IO performance will be about that of a 6 disk array, despite all the empty space provided.

Striping, on the other hand, means that the data will be "banded" across all the disks right from the start, and you'll get the corresponding performance as all the disks work together. This is essential, or else the large quantity of (expensive!) disks is basically wasted.

Worth noting: our EMC arrays' management software issues an ominous message when creating a striped meta-LUN like this, warning that it could take a long time, maybe more than a week, to stripe. The key here is that, as far as I can tell, that only applies if you are dealing with LUNs that already have data on them, and that need to be reorganized. Striping a large, empty volume of space doesn't really take that long.