SAN Storage Best Practices for SQL Server
I bet you’re here because you don’t trust your SAN administrator. The SAN admin’s been telling you everything’s fine, and that it must be a SQL Server problem, right?
Odds are, it’s not. Time to learn what SQL Server needs from its storage, and what SAN admins need from us database administrators.
SQL Server Storage Performance Tuning
Whew! We’ve got the SAN controllers, pathing, and Windows set up correctly, and now it’s time to install SQL Server and put our databases on it. Here’s my thoughts on how to do it:
- SQL University Part 1: The Basics of Storage – the basics of data files, log files, and cache.
- SQL University Part 2: Where We Store Stuff – RAID levels, magnetic drives versus solid state, and where to put your data and log files.
- SQL University Part 3: Scary Storage Scenarios – why DBCC is so important in SANs and virtualization.
- SQL Server on a SAN: Dedicated or Shared Drives? – a reader asked for help configuring their blades and NetApp SAN.
- Automated Tiered Storage for Databases – the differences between SAN-level tiering and drive-level tiering, and what you should pick for SQL Server.
- Putting SQL Server Data and Log Files on the Same Drive – sometimes it makes sense, and I explain why.
- SQL Server Setup Checklist – Now that you’ve learned about the basics of SQL Server storage, it’s time to put together a SQL Server. We’re not just going by best practices here – we’re going for real-world maintainable performance. I tell you what changes to make before & after the install, and some of these have a big payoff in storage performance. Make sure to enable Instant File Initialization, for example, and I explain how in the checklist.
SAN Optimization Tips for SQL Server Performance
SAN gear is notoriously difficult to configure, but thankfully, storage vendors put a lot of work into writing good documentation on how to set up their gear for SQL Server. Read this stuff, and believe me, there’s some fantastic tips in here that can make all the difference between good performance and bad.
Here’s the most common vendor document repositories:
- Dell EqualLogic with SQL Server – mix of a marketing and tech specs whitepaper, but good data.
- EMC – EMC hides some of their documentation behind a paywall. You have to prove you have current EMC maintenance before you can access their docs. Boo, EMC. You’ll need to work with your SAN guy to get access to those, but the good news is that there’s still a lot of useful info at that link.
- Hitachi – I haven’t worked with these myself, but clients have found these docs helpful.
- HP LeftHand Documentation for SQL Server – unfortunately, HP doesn’t have the best navigation for their support docs, so this link goes straight to Google, which makes searching HP’s documentation easier.
- IBM SAN Storage Redbooks for SQL Server – IBM calls their best technical documents Redbooks.
- IBM XIV with SQL Server – IBM’s XIV is an unusual kind of SAN.
- NetApp SQL Server Resource Guide – those are NetApp’s favorite documents. For a complete list, check out the NetApp Technical Library. For the basics, check out PDF TR-3696 on SQL Server with NetApp.
Look for document titles that include the words setup, configuration, best practices, guidelines, and so on. These are the least likely to be marketing. If the document isn’t at least 10 pages long, skip it and keep looking – short documents tend to be marketing fluff. The longer documents will tell you the right RAID type to use for data files, log files, and tempdb, plus much more like stripe sizes and cache configurations.
When you find the right one for your storage gear, hand the guidelines to your SAN administrator – that’s your best chance to get the right settings. Ask them to show you the SAN management software to verify each of the settings listed in the manufacturer’s guidelines.
How to Connect SQL Servers to the SAN
The term SAN gets misused a lot because it really means Storage Area Network – the communication pipelines between your server and a magic black box called a SAN controller. That controller is the configurable hardware that manages RAID levels, caching, and more. Here, we’re talking about how we plug your SQL Server into the network (SAN) itself, and how it gets there is called pathing. Here’s my articles on it:
- 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.
Microsoft’s Recommendations for Storage Speed
When you need to go up against the SAN guy to prove there’s a storage problem, get Microsoft in your corner. Here’s Microsoft’s guidelines on storage latency numbers:
- SQLCat Troubleshooting Logs – “With respect to #1 our recommendation for response time on the log device should be in the range of <1ms to 5ms.”
Contact Us for SQL Server & SAN Performance Help
We’re here for you. Brent Ozar Unlimited is a boutique consulting firm focused on understanding your environment and strategy. We partner with you to objectively identify pain points and develop remedies that align to your business goals. Your experience comes first; we share our knowledge and expertise to help you.
Want to get started immediately? We’ve got video training. Our VMware, SANs, and Hardware for SQL Server DBAs Training Video is a 5-hour training video series explaining how to buy the right hardware, configure it, and set up SQL Server for the best performance and reliability. Here’s a preview:
