Search results matching tag 'SAN'

Is the SAN dying???

RickHeiges — Wed, 12 Dec 2012 00:55:00 GMT

Is the SAN dying?

The reason that I ask this question is that MSFT has unleashed technologies this year that point in that direction

Always ON Availability Groups shuns shared storage
Windows 2012 has Storage Replication Technology that does not require a SAN
Windows 2012 has Hyper-V Replica Technology that does not require a SAN
PDW v2 continues to reinforce the approach to avoid shared storage

I'm not saying that SAN technology does not have its place or does not have benefits inherent to the beast. I'm just pointing out that MSFT has made investments in technology that diminish the need for SANs.

Thoughts?

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 is shared – VMs or consolidated SQL Servers and shared storage – if you want to do a big project, like, say, drive to Vegas, you better fill the car with gas. Drive back and forth to work every day? Gas. Same for storage.”

This was a light-bulb-above-my-head moment.

Now that everything is consolidated onto shared infrastructure, all the way down to complete servers, the way we think about funding IT projects has to change too. It used to be that if you wanted to do a project, you would enumerate what the systems would cost, then price and go buy them. It was like this: this new project will need a bulldozer and an excavator, and maybe a Super-Zooper-Flooper-Do, let’s buy them for the project, and then they will arrive on a truck and we will install them, and the project will move forward. Many people are still thinking this way, but it’s now officially backward. We don’t buy discrete items for projects anymore, we buy a slice of shared infrastructure. And planning for that infrastructure has to change, or you will be, as many organizations are, forever, endlessly, exasperatingly short of it.

Gas Up Early

Imagine you and your friends are cruising down the road on a beautiful day, and someone decides you need to, simply MUST drive to Southern California. Do you at that point look around at each other and say “OK, who has gas money?” Perhaps. But hopefully not if you run a large business.

Worse, do you just start driving that direction, and when you get down to 1/8 of a tank, then ask everyone in the car? Again, maybe, but not too many people travel this way who are over 25. I think, anyway.

So the obvious question is, and I see this in many companies, why do we pile projects onto shared infrastructure like SAN storage and VM clusters without planning what infrastructure will be required to take us where we want to go? Answer: the organizations haven’t finished shifting their thinking. They think, hey presto, now we don’t need to buy those unique pieces of equipment any more, we just get “free” VMs and databases and storage from that magic bottomless pool. But that’s only the first stage. They haven’t realized yet, at an organizational level, who fills that pool of resources up, and how quickly, and how much it costs.

Watch the Gauge

Part of the difficulty is there’s no single “gas gauge” to tell an organization how the shared infrastructure is doing – you need some clever, forward thinking administrators to do that, and they, in turn need some tools. Further, it’s pretty hard today to estimate what “slice” of shared infrastructure a project will need, and how or whether to literally charge back for that resource. That means you have one arm making plans for all the places the organization will drive, with no idea how much gas is in the tank, and perhaps another arm with its eye on the fuel level, but which doesn’t know what the travel plans are. If you just start driving, at some point someone’s going to be standing by the side of the road with a thumb out and a gas can.

And here’s another gotcha – you can’t, from a practical point of view, keep on filling this tank one gallon at a time, while always near empty. It’s not safe or economical. Do you really want to buy disks or shared servers and try to install them monthly? Weekly?

So start thinking about your servers and storage as a commodity, and do it now. Try to get your organization to make this simple shift – we don’t buy pieces of equipment for projects anymore. We buy a platform, then estimate how much more of that platform we need for all upcoming work, and to sustain growth, then implement it.

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 in vivid detail the problems with sending compressed data into a de-duplicating appliance. And these guys are both absolutely right. Everything in their posts is correct, and I would ask that, if you haven’t, you please read them before mine:

First, Brent Ozar:

http://www.brentozar.com/archive/2009/11/why-dedupe-is-a-bad-idea-for-sql-server-backups/

(And, may I say, well done on the Numero Uno Google result for that post. Very nice!)

Next Denny Cherry:

http://itknowledgeexchange.techtarget.com/sql-server/sql-backup-compression-and-backup-dedup-are-mortal-enemies/

(A very respectable #3 on the Google-ometer.)

Now, I’m not kidding. These guys know their stuff, and they are right. Stop reading right now.

Still here? Ok, now come closer.

Closer.

Shh.

I studied this whole thing very carefully, and I do it anyway.

While it’s true that de-duplication works poorly with compressed data, and if you compare the de-dupe ratios for “usual” uncompressed files with the de-dupe ratios for compressed files, the compressed data looks very, very bad. But there’s even more to this story, so much more that we decided to, in a limited way, stuff the compressed files into our DDR anyway.

Here’s why:

Both SQL Server backups and file compression are a deterministic process. If you back up the same database twice, and it has the same data pages in it, and those pages are largely unchanged, then the backup files will be substantially the same. This is true if you compress both files with the same algorithm and settings, too – the data in the compressed files will be largely identical. It will not be like any OTHER files on your network, but the two files will be similar to one another.

If you change a small percentage of the data pages in the data file, that will still be true: a compressed backup of the database on, say, Monday will be mostly the same as a compressed backup of the same database, with modest changes, on Tuesday.

What that means is that if I have a 1 TB database, which I do, that produces a 250 GB compressed backup file, and that database receives mainly incremental changes from day to day or week to week, then each successive backup will be similar to the previous one. And if I copy them into a de-duplicating store (at least the one I have to work with) then, while the first file will be basically 100% net new data, the second will de-dupe against the first. It’s not as effective as other types of files, but it does help. Let’s say, for argument, that I get 75% de-duplication of only the two files, instead of the normal 85%+ across many instances of other files, I am still getting 75% de-duplication, and that can be very useful.

Useful how? Well, we have SAN replication married to our de-duplicating store for offsite backup and disaster recovery. That means that each night I have to transmit a LOT of SQL backup data across a WAN to another site. What’s a lot? For me, that just means the pipe is small and the data is much bigger. And that process would go a lot faster if, somehow, by magic, a whole lot of the data were already at the other end of the pipe before I start.

See where I’m going with this? With de-duplicated files, as days and weeks pass, each time we replicate new files from one site to the other, a whole lot of the data is already there at the other site. We only have to transmit the net new data. Even if that’s only 50% (a very poor performance number for de-duplicated storage in most people’s minds) that’s still cutting the data in half. Which is pretty good. Plus it’s compressed, which helps every other aspect of the backup story.

So we have what I think is a good compromise, born out by internal testing:

Keeping compressed SQL Backups in de-duplicated storage indefinitely, as a replacement for tapes, is impractical. It’s just too expensive. So we keep the SQL Backups in there only for the purpose of DR, and we have a pretty aggressive purge schedule to be rid of old files. The sweet spot seems to be to keep only a week or two.
We use tapes too, for archival purposes, and they have longer retention.
We back up to local (DAS or SAN) disks first at the SQL Server and then copy into the de-duplicating store, so that the backup process performs well and isn’t bottlenecked at the network or at the speed the appliance can receive the files. So backups go to disk, then get copied into the de-dupe store, cancel against whatever is in there, and then it replicates them off site.

This is not a cheap setup, but it works great. I love it. That 250 GB file I mentioned is available at my other site in a couple of hours, because it’s always mostly there already. Your mileage may vary depending on all the specifics of the technology you have, and, as I said, Brent and Denny are right.

* Professional driver on a closed course; don’t try this at home; no animals were de-duped in the production of this post.

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 experience with a real implementation. Over the past four months or so, our IT group at work has deployed a new disk array incorporating enterprise-class fibre channel SSDs for database functions, and I am happy to report it’s been a success. I do have a few nuggets that I learned along the way, which might be useful if you are contemplating a move like this. Some of these tips I’ve talked about before, but they are doubly true in the rarefied (read: expensive) air surrounding SAN SSD’s. You really don’t want to sink this kind of money and not get the maximum possible return!

Project

In the Fall our organization faced a large data migration to a second datacenter, to enhance our disaster recovery and business continuity position. This entailed the purchase of some new infrastructure, and for various reasons the group decided a storage array including SSD’s made strategic sense for the next several years. I had had one eye on SSD’s when they started becoming cost-effective enough to make it into the smaller scale TPC benchmarks – indicating the cost/performance ratio was starting to make sense. Our SAN admin had also been watching them too, for all kinds of reasons, and I think I might have seen her drooling a little when this notion came up.

Anyway, we were able to obtain an EMC Symmetrix-class array with three tiers of storage, from SSD’s at the high end to 15k Fibre Channel disks to SATA disks. The array was incorporated into our existing SAN, and attached to a combination of new and repurposed servers, to create production environments for both Oracle and SQL Server. We tested the complete environment as pre-production for several weeks, mainly to validate the IO performance of this new architecture relative to what we had been running, and then have progressively cut production systems over to the equipment.

I can say that it’s performed really well – the Oracle and SQL Server systems that have SSD storage on the new array definitely saw a net boost in performance. Most of the time, our Oracle DBA’s report that they see about a 2/3 increase in performance, with some exceptions. I am running a data warehouse function on the SSD’s, and I can say we shaved perhaps 25% off of our ETL/load window by moving to the new hardware. At the same time, I would like to caution that, though it might seem like these things are supernatural in terms of performance, they are not a make-all-your-problems-disappear silver bullet. They are dramatically faster for some particular tasks, but not for everything.

Method

With help from EMC, EMC professional services consultants and our internal staff, we went through this overall process to design and deploy:

We came up with a general size and performance profile of all the existing systems we planned to move and/or consolidate onto the new array. Importantly, we got the data from real performance stats collected off our existing servers and arrays, so we could describe to EMC what the real IO requirements looked like, as well as the capacity needed. IO usually drives the cost of storage more than capacity in the database world, and we had to take pains to make sure our execs and other parties didn’t get sidetracked into looking only at capacity (so many TB) instead of performance.
EMC came back with a general design showing how many of what type of disks would be in the array. That design set most of our budget parameters :-). The new array ended up being sort of mid-range, with 46 200 GB STEC Fibre Channel SSD’s, 124 450 GB 15k RPM FC Disks, and 36 1TB 7200 RPM SATA disks.
While the array was in the manufacturing and testing queue at EMC, we did a deep dive into the specifics of how to arrange the data on all the available devices. We took about a month’s worth of performance counters from all our existing storage and mashed that into aggregate IO targets for the new servers on the new array. EMC professional services then took that data and proposed a design showing exactly how the LUNs and files would physically lay out. We did a couple of back-and-forth iterations before settling on the final design.
The basic guiding principle around the SSDs was to use them for the files that would benefit most: the read-intensive ones. We literally found the read-intensive files or disks on our existing systems from the IO counters and placed those, only, on the SSD tier. The rest of the files are on conventional 15K FC disks. All the SSD’s are set up as RAID5 (reads being the priority) and all the 15K disks are mirrored pairs, for performance.
Then The Beast, as I like to call it, was delivered, put together and health-checked by EMC.
We moved our pre-production data to the new system, and ran automated performance tests for about a month, just so we knew how to expect this thing to act.
Finally, we moved production systems over one at a time.

Take-Aways

I learned a lot going through this process – and it was really fun, to boot. Here are some basic ideas to think about if you are looking at SSDs in SAN:

If you don’t have a system for gathering IO performance counters on a rolling basis I strongly recommend, as I have before, getting or building one. This capability is vital to right-size your infrastructure and spend money wisely. I think it’s most cost-effective just to buy a monitoring system (and put your valuable working hours into something business-specific that can’t be purchased), but if you can’t afford a monitor, it’s not very hard to build a basic counter-collection system. Worst case, it is possible to log this data with perfmon, but that takes extra effort and can push your schedule out, as you have to gather the data when needed rather than just having it already in a repository.
Resist the idea that SSD’s are just so miraculously fast that it’ll be like RAM, or make all your performance worries disappear. In fact they solve mainly one problem – an important problem, but one only. They avoid the penalty of random access to disks, which requires mechanical moving parts to get to some region of a disk, then pass the data on the disk past the disk head. If you suffer from that problem, they will help – a lot. If not, then you should dig deeper and see if SSD’s will really be an advantage for your workload. Writes might not be that much faster, nor sequential reads, depending on the details. You might be best off with a hybrid solution that uses two kinds of storage, and it’s worth considering a design like that. Be realistic about their true performance characteristics and set expectations appropriately.
You still have to RAID the devices together. SSD’s can fail just as disks can fail, and if you are running a production system then you will want the redundancy. That obviously has a cost impact. On the other hand, the arithmetic might go like this: if you need, say, 48 disks in RAID 1+0 to get good performance for an existing server (and those disks are probably mostly empty) but SSD’s could get you the same performance (imaginary numbers here) with only 6 or 8 devices in RAID5, then maybe the budget starts to look attractive.

Still, it was really fun last week when I spied our little data warehouse doing over 7,900 IOPS! Sweet!

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 systems, and a lot of smart people helping me, so here's a SAN 101 crib sheet for DBAs who may be new to this.

SAN Basics

SAN stands for Storage Area Network, like LAN or WAN. The first thing of note is that it's a network, and not a device. Often it's a fiber-optic network, for speed and bandwidth, but sometimes it can be a copper network. It's easy to lose sight of the fact that its a network, because it's not what we all think of as "the network," and because the storage admins are usually the only ones that play there. Hence the colloquial term "a SAN" referring to one storage device. One device on the network is really a Storage Array (SA?), not a Storage Array Network (SAN). Tomato, tom-AH-to, I suppose, but it's important in larger environments to talk about one array separately from the storage network itself.
Because it's a network, there's a network protocol for devices to communicate. Usually that is FCP for fiber or iSCSI for copper (with exceptions.) The protocols really send SCSI commands, the same kind many hard disk devices use within a single server. That means, for practical purposes, that devices a server "sees" on the SAN look and act like hard disks rather than network devices. There can be switches in a SAN, and so on, just like other networks.
Servers typically connect to a fiber SAN with one or two (maybe more) Host Bus Adapters (HBAs). An HBA just acts as an interpreter, making the devices visible on the storage network appear to the host bus, transparently, as if they were disks. So, if you are building such a setup, you'll have a server with a couple of HBAs, those are connected to a switch or two on the fiber network, which is then connected to one or more storage arrays. If a chunk of storage on one of the arrays is allocated for your server, then you see that storage space as if you'd stuck a hard disk in. Partition, format, write. Simple!

Rule 1: There is no Magic

Storage vendors do have a reputation (they are selling things, after all) to pitch their stuff in a way that makes it seem like all your performance worries will vanish in some kind of magic fiber-optic {poof}. Not so, I'm afraid. Enterprise arrays are good, but they are still subject to the laws of physics. The disks in an array are still disks, the cache is just a cache. This is good for the DBA, in a way, because if you know how to size direct attached storage by spindle count and IOPS, then - whooee! - it's not really that different. There are things that help performance some, like a huge write cache with write re-ordering and fault tolerance, but you don't have to throw out what you know and start over. Lots of random IO still means you need lots of random IO capacity, which still means disks.

Rule 2: Performance Costs More than Space

The fundamental mistake I have seen is to hand a SAN administrator (or your SAN vendor) a spec about how much space you need for your data. That is a recipe for disaster. What is important is what the performance profile is, and, just as with plain ol' disks, IOPS will determine what the hardware has to look like in an array. And you want to do that before you get the price quote, because the performance, not the space, will drive the cost. Ask for a terabyte? You might get one 1 TB SATA disk. That won't be happy at all.

Rule 3: Yes, Direct Attached Storage is Cheaper ... But

OK, so there's a whole economics problem to be aware of. SAN storage is expensive, sometimes very expensive. In some circumstances, it's still worth it, but the price tag is undeniable. Examples: if you have the same amount of storage (or in the SQL world, amount of storage performance) in direct attached disks, and in SAN storage, the SAN flavor is going to cost a lot more up front. It's pretty easy to spend $1,000 per disk for 15k fiber channel disk drives in an array.

However, with real IT in real organizations of any size, things are more complicated - there isn't one, unchanging and monolithic system. In fact, things, servers, storage requirements, circumstances, applications all change at a breakneck pace. What we do, most of the time, is try to accomodate change and keep things "up." That is life for many IT pros. So it is not cheaper to have piles and piles of unutilized or overspec'd direct attached storage sitting around, married to single servers. If you had to buy twice what you needed, where's the savings? When you buy into a SAN what you are buying is, or should be, flexibility. The flexibility to use it fully, all the time, and to change the storage allocation for all your servers without physically scrapping storage hardware and buying new. If you have a SAN, and it's not providing that value, then you probably wasted your money.

So, if you have one relatively static database server and you're looking at storage options, direct-attached might save you a pile of money. Don't build a SAN for performance around a couple of servers -- there's no compelling performance-per-dollar argument. On the other hand, if you find that you look around your enterprise and see tens or hundreds of servers where there's a huge waste factor for storage in some places, and full disks in others, there's your SAN opportunity. This server over here is low on disk space? OK, let's just add some. That one had too much, and we're wasting space? Take some away. Storage over here is too slow? Migrate it to a set if disks with more performance.

In other words:

Best case: one SQL Server or cluster with correct direct attached storage, fully utilized. Nice, but this is the real world!

Next best: a heterogeneous, changing environment that uses SAN storage to best advantage

Worse: a collection of over-spec'd DAS hardware that is under 50% utilized, but can't realistically be changed. It seemed cheap at the time, but there's so darn much of it!

Worst: a big, expensive, under-utilized SAN

Rule 4: You Need a Good Relationship with the SAN Admin

I've blogged about this before, but suffice it to say that bad communication with the SAN admin = FAIL. SQL Server often has unique and demanding IO requirements that don't go away just because you have a fancy array. You have to be able to work that out with the storage admins, if you have them, or the vendor, if you are in a smaller shop. Together you will have to talk through the need to separate logs, data and backups, and what the performance profile of each "virtual" disk system needs to be, backed by perf counter data, to prevent the SAN nightmare: "We spent our $5,000,000 and the VP wants to know why it's SLOW."

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 moving SQL services and touching everything, it seems to be easier to make the consolidation argument to application owners/stakeholders and get that work done at the same time. So it's looking like consolidation and relocation in the same effort. Hopefully it'll be a win squared.

But to come to the point: this collection of projects has led to the purchase of a sizeable new disk array (one chunk of the new storage is even solid state - yee haw!) and that means serious performance and capacity planning. I currently use Spotlight on SQL Server Enterprise to monitor and collect stats about our servers, and it has a nice, automated repository for historical trending baked right in.

What's that? You don't collect performance stats from your servers over time? Stop burning valuable minutes reading this silly blog and go get (or even build, if you must) a tool! Now! BinGoogle, in no particular order: Idera, Quest, SQL Sentry, Red Gate, and work from there. Sheesh!

I happen to have Spotlight available out of the whole group of monitoring products, so I thought I'd share some techniques I have used to project storage requirements from a pile of existing servers onto a new, consolidated set of clusters. The same techniques work for other calculations, like CPU use and so on, I just happen to be laser-focused on storage at the moment. The same techniques probably also work with minor variation against any tool that collects this perf counter data.

The Spotlight repository I have set up is a SQL database that ends up containing hourly performance counter samples for about 30 days. The list of available counters is quite comprehensive, but for this exercise I need the ones the SAN engineers will need to design the LUN layout on our new array. That is:

IO operations per second, per disk

Bandwidth required (quantity of data read and written per disk, rather than number of operations)

Also handy, Spotlight provides Disk Queue values for past time periods, so you can get some idea of which existing disks in your environment are busy, or maybe too busy. We're also able to cross-reference this with the performance counters from our exising SAN arrays, as a double-check.

Chunking the Spotlight Repository Data

The Spotlight repository schema is fairly easy to understand, especially if you take a peek at some of the provided reporting procs that come with the product. In essence, there's a table full of performance counter samples, and then ancillary tables that list your servers ("monitored objects") and friendly names of counters ("statistic_names," "statistic_keys"). A series of joins will provide a simple output of time, server, and counter value. I went ahead and encapsulated this in a view, so that I could use it for a variety of specific queries:

CREATE VIEW [dbo].[reportingPerfData] 
AS 
  SELECT a.timecollected, 
         ds.datasource_name, 
         mo.monitored_object_name, 
         sc.statistic_class_name, 
         sn.statistic_name, 
         sk.statistic_key_value, 
         a.raw_value 
  FROM   spotlight_perfdata a 
         INNER JOIN spotlight_stat_classes sc 
           ON a.statistic_class_id = sc.statistic_class_id 
         INNER JOIN spotlight_stat_names sn 
           ON a.statistic_name_id = sn.statistic_name_id 
         INNER JOIN spotlight_datasources ds 
           ON sc.datasource_id = ds.datasource_id 
         INNER JOIN spotlight_monitored_objects mo 
           ON a.monitored_object_id = mo.monitored_object_id 
         INNER JOIN spotlight_stat_keys sk 
           ON a.statistic_key_id = sk.statistic_key_id
GO

The only tricky bit I found is that some of this is built on the EAV model (labeled rows in a generic table with a variant value column, instead of separate, clearly named columns). EAV just implies the first thing you have to write is a pivot to get the values you need into appropriate columns, cast to the right types. I wish Quest hadn't set it up this way, but it's a small peeve, and just having the data at all is great.

I'm looking for disk stats, so I have a second query that is built on the first, but limits results to disk performance, and pivots the counter values into separate columns. Again, defining this in a view facilitates reuse:

CREATE VIEW dbo.reportingdiskperfdata 
AS 
  SELECT   [Year], 
           [Month], 
           [Day], 
           daypart, 
           [Hour], 
           [Minute], 
           timecollected, 
           [server], 
           drive, 
           SUM(pctbusy)             AS pctbusy, 
           SUM(readspersec)         AS readspersec, 
           SUM(writespersec)        AS writespersec, 
           SUM(mbyteswrittenpersec) AS mbyteswrittenpersec, 
           SUM(mbytesreadpersec)    AS mbytesreadpersec, 
           SUM(iopersec)            AS iopersec, 
           SUM(queuelength)         AS queuelength 
  FROM     (SELECT YEAR(timecollected)                       AS [Year], 
                   MONTH(timecollected)                      AS [Month], 
                   DAY(timecollected)                        AS [Day], 
                   CASE 
                     WHEN 0 <= DATEPART(HOUR,timecollected) 
                          AND DATEPART(HOUR,timecollected) < 6 
                     THEN 1 
                     WHEN 6 <= DATEPART(HOUR,timecollected) 
                          AND DATEPART(HOUR,timecollected) < 12 
                     THEN 2 
                     WHEN 12 <= DATEPART(HOUR,timecollected) 
                          AND DATEPART(HOUR,timecollected) < 18 
                     THEN 3 
                     WHEN 18 <= DATEPART(HOUR,timecollected) 
                          AND DATEPART(HOUR,timecollected) < 24 
                     THEN 4 
                   END AS daypart, 
                   DATEPART(HOUR,timecollected)              AS [Hour], 
                   DATEPART(MINUTE,timecollected)            AS [Minute], 
                   timecollected, 
                   monitored_object_name                     AS [server], 
                   statistic_name, 
                   CAST(statistic_key_value AS VARCHAR(100)) AS drive, 
                   CASE 
                     WHEN statistic_name = 'pctbusy' 
                     THEN CAST(raw_value AS FLOAT) 
                     ELSE 0.0 
                   END AS pctbusy, 
                   CASE 
                     WHEN statistic_name = 'readspersec' 
                     THEN CAST(raw_value AS FLOAT) 
                     ELSE 0.0 
                   END AS readspersec, 
                   CASE 
                     WHEN statistic_name = 'writespersec' 
                     THEN CAST(raw_value AS FLOAT) 
                     ELSE 0.0 
                   END AS writespersec, 
                   CASE 
                     WHEN statistic_name = 'byteswrittenpersec' 
                     THEN CAST(raw_value AS FLOAT) / (1024 * 1024) 
                     ELSE 0.0 
                   END AS mbyteswrittenpersec, 
                   CASE 
                     WHEN statistic_name = 'bytesreadpersec' 
                     THEN CAST(raw_value AS FLOAT) / (1024 * 1024) 
                     ELSE 0.0 
                   END AS mbytesreadpersec, 
                   CASE 
                     WHEN statistic_name = 'iopersec' 
                     THEN CAST(raw_value AS FLOAT) 
                     ELSE 0.0 
                   END AS iopersec, 
                   CASE 
                     WHEN statistic_name = 'queuelength' 
                     THEN CAST(raw_value AS FLOAT) 
                     ELSE 0.0 
                   END AS queuelength 
            FROM   dbo.reportingperfdata 
            WHERE  datasource_name = 'windows' 
                   AND statistic_class_name = 'logicaldisks' 
                   AND statistic_name IN ('readspersec', 
                                          'pctbusy','writespersec','byteswrittenpersec', 
                                           
                                          'bytesreadpersec', 
                                          'iopersec','queuelength' 
                                          
                                         ) 
                   AND timecollected >= DATEADD(DAY,-30,(SELECT MAX(timecollected) 
                                                         FROM   dbo.spotlight_perfdata))
  ) AS pivotperfvals 
  GROUP BY [Year], 
           [Month], 
           [Day], 
           daypart, 
           [Hour], 
           [Minute], 
           timecollected, 
           [server], 
           drive 

GO

If you look closely, there are two other transformations: as the values are pivoted out into labeled columns and there is an expansion of the timecollected value into a hierarchy of year / month / date / time, and four time periods per day ("daypart," meaning early morning, morning, afternoon, evening) along the lines of an Analysis Services / BI hierarchy. This is just to facilitate reporting later with an Excel Pivot Chart.

With these two views we are almost to the point where we've made information out of our data.

From History to Plans

I am doing relocation, but also consolidation. That means that in many cases two or more disks from the performance history I have will need to be added together to form a projection of how busy a new, consolidated LUN will be, and then what performance will be required of the disk array for that LUN.

In order to get to the final numbers, I next made a small mapping table right in the Spotlight repository database, and used it to map all the existing disks from all the existing servers onto the planned layout of our new SQL Server clusters.

CREATE TABLE dbo.diskmigrationplan ( 
  existingserver VARCHAR(128), 
  existingdrive  VARCHAR(128), 
  newserver      VARCHAR(128), 
  newdrive       VARCHAR(128)) 

INSERT INTO dbo.diskmigrationplan 
           (existingserver, 
            existingdrive, 
            newserver, 
            newdrive) 
SELECT DISTINCT [server],
               drive, 
                [server], 
                drive 
FROM   dbo.reportingdiskperfdata 
WHERE  drive NOT IN ('c:','d:','q:','x:') 

GO

This is just a simple, four-column table that lists the existing server name, existing disk and the planned server name and planned disk. Its purpose is to allow creation of a query that can first sum the performance counters for separate disks that will be consolidated, and then average the performance of the two disks together, over time, to estimate the demand on the consolidated disk. Changing the mapping changes the plan for consolidation, so I can fine tune the old-to-new server plans.

Once I have that mapping table filled in, then edited to relate existing disks to new disks, I can get to a query that will show the projected demand in the new server environment:

-- Sum the counter values from old servers/drives to new servers/drives 
CREATE VIEW reportingdiskperfprojection 
AS 
  SELECT   [Year], 
           [Month], 
           [Day], 
           daypart, 
           [Hour], 
           [Minute], 
           timecollected, 
           drive = mp.newserver + ' ' + mp.newdrive, 
           pctbusy = SUM(pctbusy), 
           readspersec = SUM(readspersec), 
           writespersec = SUM(writespersec), 
           mbyteswrittenpersec = SUM(mbyteswrittenpersec), 
           mbytesreadpersec = SUM(mbytesreadpersec), 
           iopersec = SUM(iopersec), 
           queuelength = SUM(queuelength) 
  FROM     dbo.reportingdiskperfdata pd 
           INNER JOIN dbo.diskmigrationplan mp 
             ON mp.existingserver = pd.[server] 
                AND mp.existingdrive = pd.drive 
  GROUP BY [Year], 
           [Month], 
           [Day], 
           daypart, 
           [Hour], 
           [Minute], 
           timecollected, 
           mp.newserver + ' ' + mp.newdrive 

GO

Chart it Out

I've been able to use the output from this last view in two reporting techniques. In order to make a friendly, detailed picture of the performance profile of each disk, I plugged Excel 2007's Pivot Chart straight into the view. This allows me to examine each disk and look at a graph of each counter over the whole time span, to get an idea of when the disk is busy, and how peak usage compares to average usage, etc. It's as simple as clicking the Pivot Chart button, linking the data source for the chart up to the view in SQL Server, then pulling the data columns into the standard pivot table rows / columns / values areas.

Aggregate

Lastly, while the charts showing each disk are handy, it's pretty time consuming to plow through each disk and jot down estimates. Instead, I thought a final bit of SQL work could give me an overview of target numbers for all the disks. The trick here is how to aggregate the results. I don't care so much when a disk is idle, so periods of low use just don't really matter - but they do have to be excluded. I also don't care about spikes or outliers on the high end of the spectrum. One thing I took away from the "engineering lite" classes I had in Architecture school: when you design a building air conditioning system, you don't design around the very hottest days that might happen (that'd be a waste of money). You design around the average of most of the hot days, so that the system keeps the building cool practically all the time, but you haven't wasted money with extra capacity just for the one hottest day in five years.

In order to do this, I need to take the consolidated data for each planned disk (that is, add up the perf counters for disks that will land on consolidated servers) and then compute the average and max values for the normal busy periods. With row_number() ranking, this is pretty easy to do: I have a query rank the 720 available samples of a counter for the whole month in descending order, then from that list I discard the top 5 or so to eliminate spikes or outliers, and then average together the next 100 samples. I'm sure there's a statistics rule and a term for this, like "difference of mean sum square deviation over moving time average," but here I have to confess to knowing practically nothing about statistics beyond what I put in layman's terms above :-).

The query for one such aggregation:

SELECT   rankedioperseccounters.drive, 
         AVG(iopersec) AS avgiopersec, 
         MAX(iopersec) AS maxiopersec 
FROM    (SELECT timecollected, 
                 drive, 
                 [rank] = Row_number() OVER(PARTITION BY drive ORDER BY iopersec DESC), 
                 iopersec 
         FROM   dbo.reportingdiskperfprojection) AS rankedioperseccounters 
WHERE    [rank] BETWEEN 5 AND 105 
GROUP BY drive

Then, a beastly looking query can do them all at once. This is just that same query repeated for each metric:

-- Top 101 of 720 counter values from all samples, clipping max 5 
SELECT iopersec.drive, 
       avgiopersec, 
       maxiopersec, 
       avgmbytesreadpersec, 
       maxmbytesreadpersec, 
       avgmbyteswrittenpersec, 
       maxmbyteswrittenpersec, 
       avgpctbusy, 
       maxpctbusy, 
       avgqueuelength, 
       maxqueuelength 
FROM   (SELECT   rankedioperseccounters.drive, 
                 AVG(iopersec) AS avgiopersec, 
                 MAX(iopersec) AS maxiopersec 
        FROM     (SELECT timecollected, 
                         drive, 
                         [rank] = Row_number() 
                                    OVER(PARTITION BY drive ORDER BY iopersec DESC), 
                         iopersec 
                  FROM   dbo.reportingdiskperfprojection) AS rankedioperseccounters 
        WHERE    [rank] BETWEEN 5 AND 105 
        GROUP BY drive 
       ) AS iopersec 
       LEFT JOIN (SELECT   rankedmbytesreadperseccounters.drive, 
                           AVG(mbytesreadpersec) AS avgmbytesreadpersec, 
                           MAX(mbytesreadpersec) AS maxmbytesreadpersec 
                  FROM     (SELECT timecollected, 
                                   drive, 
                                   [rank] = Row_number() 
                                              OVER(PARTITION BY drive ORDER BY mbytesreadpersec DESC), 
                                   mbytesreadpersec 
                            FROM   dbo.reportingdiskperfprojection) AS rankedmbytesreadperseccounters 
                  WHERE    [rank] BETWEEN 5 AND 105 
                  GROUP BY drive
                 ) AS mbytesreadpersec 
         ON iopersec.drive = mbytesreadpersec.drive 
       LEFT JOIN (SELECT   rankedmbyteswrittenperseccounters.drive, 
                           AVG(mbyteswrittenpersec) AS avgmbyteswrittenpersec, 
                           MAX(mbyteswrittenpersec) AS maxmbyteswrittenpersec 
                  FROM     (SELECT timecollected, 
                                   drive, 
                                   [rank] = Row_number() 
                                              OVER(PARTITION BY drive ORDER BY mbyteswrittenpersec DESC), 
                                   mbyteswrittenpersec 
                            FROM   dbo.reportingdiskperfprojection) AS rankedmbyteswrittenperseccounters 
                  WHERE    [rank] BETWEEN 5 AND 105 
                  GROUP BY drive 
                 ) AS mbyteswrittenpersec 
         ON iopersec.drive = mbyteswrittenpersec.drive 
       LEFT JOIN (SELECT   rankedpctbusycounters.drive, 
                           AVG(pctbusy) AS avgpctbusy, 
                           MAX(pctbusy) AS maxpctbusy 
                  FROM     (SELECT timecollected, 
                                   drive, 
                                   [rank] = Row_number() 
                                              OVER(PARTITION BY drive ORDER BY pctbusy DESC), 
                                  pctbusy 
                            FROM   dbo.reportingdiskperfprojection) AS rankedpctbusycounters 
                  WHERE    [rank] BETWEEN 5 AND 105 
                  GROUP BY drive
                 ) AS pctbusy 
         ON iopersec.drive = pctbusy.drive 
       LEFT JOIN (SELECT   rankedqueuelengthcounters.drive, 
                           AVG(queuelength) AS avgqueuelength, 
                           MAX(queuelength) AS maxqueuelength 
                  FROM     (SELECT timecollected, 
                                   drive, 
                                   [rank] = Row_number() 
                                              OVER(PARTITION BY drive ORDER BY queuelength DESC), 
                                   queuelength 
                            FROM   dbo.reportingdiskperfprojection) AS rankedqueuelengthcounters 
                  WHERE    [rank] BETWEEN 5 AND 105 
                  GROUP BY drive
                 ) AS queuelength 
         ON iopersec.drive = queuelength.drive

Voila! Real figures for moving a large collection of existing sprawl to a few, hopefully tidy clusters. Wish me luck!

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.

Finally, test the IO performance of the LUN before putting it into production, and make sure it'll deliver what you require. This can be as simple as a stress-testing script or a utility like SQLIOSim, combined with Performance Monitor's physical disk counters.

SAN vs. Disk Arrays: It goes a long way to be slightly more specific!

Linchi Shea — Fri, 26 Jun 2009 21:45:00 GMT

In the SQL Server communities, it's common to hear people talking about HP SAN, EMC SAN, 3Par SAN, and so on as if there were such things as HP SAN, EMC SAN, etc.

Technically, SAN stands for Storage Area Network, but can be, and has been, used in two different ways. First, outside the storage communities, people often view everything beyond the drive at the OS level as the SAN with no regard to how that SAN is architected or configured as long as that drive is presented from some kind of SAN infrastructure. Typically, this is the way SQL Server professionals talk about SAN.

Within the storage communities, the interpretation is often different. To a storage engineer, there is a distinction between SAN and disk arrays: SAN is the network fabric that is made up of switches that provide your host a point-to-point path/link to the disks on some disk arrays. Disk arrays are the storage devices where physical disks are pooled together and managed by sophisticated software and additional controller hardware.

I personally have tried consciously to stick to the second interpretation, and refrained from using terms such as HP SAN or EMC SAN, primarily because this type of speaking adds no value, but introduces inaccuracies, and can be potentially misleading.

First of all, even if you interpret SAN broadly, it’s still probably wrong, or inaccurate, to speak of EMC SAN or IBM SAN, for instance, because it’s highly unlikely that the SAN environment is entirely made up of the EMC or IBM devices (I have not seen one anyway). The disk arrays may be from EMC or IBM, but the switches are often from Broacade or Cisco, or a mix of switches from different vendors. SAN is not a monolithic piece.

Although SAN in terms of the switch fabric can be a limiting factor, especially when it comes to throughput, disk arrays are often where the disk I/O performance is determined, especially when it comes to disk I/O latency. Identifying from what disk array a LUN is carved automatically puts us in a better position in our performance analysis.

Even if we stop being ‘picky’ and interpret EMC SAN to mean an EMC disk array behind a SAN fabric, the phrase is not specific enough to add as much value as identifying it to be an EMC DMX-3 disk array, for instance. Note that there can be many different models, makes, and versions of disk arrays behind a SAN, and their performance characteristics can be vastly different. It does not help to say that your drive is presented from an EMC SAN because two drives presented from the same SAN can perform very differently even with the same number of drives, same type of drives, and same RAID configuration. Many factors of the disk array heavily influence the performance of LUNs carved from the disk array.

Although different LUNs can be carved from the same disk array and these LUNs can be configured to have different performance characteristics, identifying the disk array allows one to understand why two LUNs of the same configuration have different performance characteristics, or at least gives one an opportunity to look for more information, and make the conversations easier with the storage folks.

Saying that a drive is from an EMC SAN is simply too generic to be useful, even if we are willing to ignore the fact that there is no such thing as an EMC SAN.

Performance impact: file fragmentation and SAN – Part V

Linchi Shea — Mon, 29 Dec 2008 21:36:00 GMT

SQL Server workloads

So far, the discussions in all the previous posts (1, 2, 3, and 4) on the performance impact of file fragmentation on a drive presented from a high-end enterprise-class disk array are related to disk I/O workloads. Ultimately, you want to know how file fragmentation may impact your SQL Server workloads.

In this post, I share with you some results from running three SQL Server workloads: (1) database backups, (2) checkpoints, and (3) table scans. These SQL Server workloads were run in the same three test scenarios as used in all the previous four posts:

· Non-fragmented. The database files were created on an empty drive,

· 2MB fragments. The database files were created on a drive whose free space had been fragmented into non-contiguous chunks with each chunk being contiguous and 2MB in size,

· 128KB fragments. The database files were created on a drive whose free space had been fragmented into non-contiguous chunks with each chunk being contiguous and 128KB in size,

The same database was used in all the tests. The database was about 9.5GB in size, and the table on which table scan was performed had 10 million rows and was about 4GB in size. DBCC DROPCLEANBUFFERS was run prior to each table scan.

The following chart is a summary of the SQL Server workload test results:

Clearly, file fragmentation did not have any significant performance impact on these SQL Server workloads. Because the performance of these workloads is dependent on the disk I/O throughput, we could have predicted this from the results of our disk I/O tests as reported in the previous four posts. However, it’s still comforting to see the prediction validated with real data points.

This concludes this series of posts on the performance impact of file fragmentation on a drive that is presented from a high-end enterprise class disk array. Overall, the impact was significantly less than what we have seen on a traditional directly attached disk drive. For I/O throughput, there was little to no impact. For I/O latency (or response time), file fragmentation can cause some I/O request to take much longer to complete.

In a future post(s), I’ll explore whether the same holds true with a drive presented from a lower end disk array.

Performance impact: file fragmentation and SAN – Part IV

Linchi Shea — Mon, 22 Dec 2008 16:34:00 GMT

Lies, damned lies, and statistics!

If you have read my three previous posts (1, 2, 3), you may walk away with an impression that on a drive presented from a high-end enterprise class disk array, Windows file fragmentation does not have a significant performance impact. And I’ve given you empirical data—oh yeah, statistics—to support that impression.

But that is not the whole story! No, I didn’t lie to you. The numbers I presented were solid. It’s just that the story is not yet finished.

In these previous posts on the performance impact of file fragmentation, I presented the I/O throughput data as the evidence. The arguments were valid, especially we did see file fragmentation causing the I/O throughput to degrade in a directly attached storage. But I/O throughput is but one I/O performance metric, and it is not enough to look at the I/O throughput alone.

Let me start with an analogy. So suppose we have an eight-lane super highway going from New York City to Los Angles. As we pumping (okay, driving) more cars from NYC to LA, we take measure at a checkpoint in LA to find out how many cars are crossing that checkpoint every hour, i.e. we are measuring the throughput of the super highway. Now, instead of building the eight-lane super highway straight from NYC to LA, we have it take a detour via Boston. At that same checkpoint in LA, we again measure the throughput. Everything else being equal, we should get the same throughput.

However, for a given car, the trip from NYC to LA would take a lot longer on this detoured highway.

An I/O path is similar to a super highway. While its throughput is an important measure, how long it takes for an I/O request to complete—I/O latency or response time—is also an important measure. The question is, will file fragmentation take your I/O traffic for a detour?

Indeed, empirical test data show that when a file is severely fragmented, the maximum I/O latency of large sequential reads and writes (e.g. 256KB reads and writes) can suffer significantly. The following chart shows the impact of file fragmentation on the maximum I/O latency. The data were obtained from the same tests whose throughputs were reported in Part III of this series of posts.

Clearly, when the test file was fragmented into numerous 128KB disconnected pieces, some of the 256KB reads suffered terribly latency degradation. And if your applications happen to be issuing these I/Os, you would most likely experience a performance degradation regardless whether the I/O path can maintain the same I/O throughput.

Having some valid statistics may seem to add force to an argument, which makes it so much easier to be misleading if the whole story is not told, and technically everything is valid, and nobody is lying. By the way, this is a trick often employed by the vendors, who tend to conveniently ignore the bad news, or intentionally bury it with statistics on the good news. In my book, that would be lies, damned lies, and statistics.