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?

Well, maybe – but to find out, you’re going to have to crack open some books – or blog posts, at least. This page is my favorite resources for:

  • How storage works
  • How SQL Server connects to storage (pathing)
  • How SQL Server uses storage
  • Vendor-specific SAN best practices for SQL Server
  • General SAN best practices for SQL Server
  • Getting more help from Brent Ozar Unlimited

How Storage Works

Before we dive into how SQL Server works with storage, you might want to learn how storage works in general. This isn’t required, but having a good foundation will help as we get into the more advanced stuff.

How SQL Server Connects to Storage (Pathing)

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.

How SQL Server Uses Storage

The relationship between memory (cache), data files, log files, and TempDB is like your Facebook status – it’s complicated.

Vendor-Specific SAN Best Practices for SQL Server

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:

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.

General SAN Best Practices for SQL Server

If you can’t find guidance for your particular make/model of storage, let’s talk about general best practices that work with most types of storage.

  • 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.
  • SQLCat Troubleshooting Logs - Microsoft’s SQL Customer Advisory Team says, “With respect to #1 our recommendation for response time on the log device should be in the range of <1ms to 5ms.”

Getting Help from Brent Ozar Unlimited

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. Learn more about our SQL Critical Care®, or check out our SAN training videos.

css.php