Merrill Aldrich : performance

Chasing the ISV, or, “That code makes my teeth hurt.” T-SQL Tuesday (ish) #21

merrillaldrich — Wed, 10 Aug 2011 14:00:00 GMT

This month’s T-SQL Tuesday – a blog party dreamed up by sqlblog.com’s Adam Machanic ( blog | @ AdamMachanic ) – is about that code we’ve all written that we don’t really like to think about too often. You know the stuff. I can’t help but imagine the next...(read more)

SCOM, 90 days in, III. Stuff to Add

merrillaldrich — Thu, 03 Mar 2011 06:57:44 GMT

This is the third installment of a series on our deployment of System Center at my workplace, emphasis on SQL Server MP. At this point we’ve got Operations Manager installed, and up and running, and we’ve been able to categorize all the monitored servers...(read more)

SQLSat65, Great Perf Counters Poster from Quest

merrillaldrich — Mon, 28 Feb 2011 16:50:00 GMT

I was fortunate to be able to attend the Vancouver BC SQLSaturday this past weekend, and it was excellent! Great sessions, good facility, well attended. Nice work, and a huge thank you to the volunteers that made that happen. One side perk: I got a copy...(read more)

SCOM, 90 Days In, I

merrillaldrich — Thu, 24 Feb 2011 04:11:51 GMT

At my office we’re about 90 days into our implementation of System Center Operations Manager for Windows Server and SQL Server monitoring. All in all it’s been a good experience, and I’m really excited to have access to this tool. I’ve logged a fair number...(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)

Busting a persistent myth: views are executed "before" enclosing queries

merrillaldrich — Thu, 11 Feb 2010 17:49:00 GMT

To all who are SQL savvy, this is old news, but I wanted to put this tidbit up for you to pass along, as a concise proof, to others that might subscribe to this performance myth. On forums and Stack Overflow, it seems I constantly see this misinformation...(read more)

Poll: Hyperthread or not on 2 x 4 core Nehalem SQL Server?

merrillaldrich — Thu, 14 Jan 2010 22:05:00 GMT

Common wisdom with previous generations of server hardware was that SQL Server might not benefit from hyperthreading or could be degraded at high CPU usage; does anyone have definitive data about Nehalem (non-EX) servers and SQL Server 2005 or 2008? Here...(read more)

Choices in physical storage design – EAV Case Study

merrillaldrich — Tue, 24 Nov 2009 00:30:00 GMT

Partitioning for Fun and Profit Over the past year or so I’ve been playing with a toy SQL monitoring application, just to sharpen my dev skills a bit. Part of the app is a SQL database to store historical performance data, in order to do trending. Fooling...(read more)

Wrinkly Entropy

merrillaldrich — Fri, 20 Nov 2009 23:44:00 GMT

So, found this incredible freeware program on Codeplex with a really simple UI to handle performance optimization for SQL Server: I was gonna post the link here but I lost it, and now the site seems to have been taken down... OK, I am obviously lying....(read more)

Trick Question -- Part Quattro

merrillaldrich — Wed, 18 Nov 2009 05:49:00 GMT

Spoiler: TPH is an evil trap In the last installment I pleaded for the equal treatment of the database schema and object model when implementing an application with a database, on the grounds that the failure of either means the failure of the whole system....(read more)

Trick Question -- Part Trois

merrillaldrich — Mon, 16 Nov 2009 20:41:00 GMT

This is the third part of a series ( Part 1 , Part 2 ) thinking out loud about the decision making around data access for applications. Once you've considered how tightly bound your application code can safely be to tables, I would like to put two related...(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.

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.