Using SQL Server on a SAN doesn’t always guarantee fast performance. Here’s some of my tips from over the years.
How to Understand & Test Your SAN
- SAN Multipathing Part 1 – What Are Paths? – I start by explaining a little history of storage and how we got to the messy black boxes we have today.
- SAN Multipathing Part 2 – What Multipathing Does – I’ll give you a hint: active/active multipathing isn’t as cool as you think.
- Finding Your SAN Bottlenecks with SQLIO – the name SQLIO is so misleading, because this free utility from Microsoft helps you troubleshoot bad performance no matter what servers you’re using.
How to Configure SQL Server on SANs
- Putting SQL Server Data and Log Files on the Same Drive – sometimes it makes sense, and I explain why.
- SQL Server Partitioning – it isn’t the answer to everything, and I explain when to use it.
- SQL Server on a SAN: Dedicated or Shared Drives? – a reader asked for help configuring their blades and NetApp SAN.
- Steel Cage Blogmatch: How to Configure a SAN – I argue with Jason Massie of StatisticsIO.com about the right way to configure a SAN for performance.
How to Configure Windows for SANs
- Partition Alignment by Jimmy May – this easy configuration tweak gives you 10-30% faster storage performance, but there’s a catch. It can only be configured when the volume is first created – otherwise you have to blow away your data, configure the volume again, and restore your data. Get it right the first time with help from Jimmy May.
- Partition Alignment in Virtual Machines – yes, it matters for VMs too.
My Presentations on Storage Area Networks
- Getting Started with SQLIO – video presentation at SQLServerPedia.
- SQL Server on SANs slide deck – just the PowerPoint deck, not video or audio, at SlideShare.net.
What are your thoughts on carving up direct attached storage. We are setting up a 15 drive sql cluster. Would it be best to make one big RAID10 and then carve up virtual drives(all within the RAID controller, not windows) or would it be better to carve up 4 or 6 drives for data, a couple of drives for tempdb, etc, etc.
The answer is always that it depends. Try setting up one 14-drive RAID 10 array and run SQLIO tests on it. Then try smaller arrays and see how they perform. I’ve seen some RAID controllers that max out throughput within a fairly small number of spindles. For example, I worked with one system that got the same throughput speeds with a 6-drive RAID 10 array that it got with a 12-drive RAID 10 array. The controller just maxed out its performance and couldn’t get beyond that point. Therefore, I got better performance out of two 6-drive arrays in that particular system. Hope that helps!