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.

Testing Your Storage Throughput

Start by getting a rough idea of your storage performance compared to other storage – and what SQL Server needs:

Testing with the free CrystalDiskMark – this is the easy button of storage tests.

How to Test Your Storage with SQLIO – this isn’t as easy as CrystalDiskMark, but it’s more accurate and more powerful.

Microsoft Fast Track Data Warehouse Reference Architecture – if you think the title is long, wait til you try to read the entire post. Actually, don’t – just do a control-F in that doc to find the phrase “Designed with a Balanced Hardware Approach” and read until you hit the “Updating the SMP Reference Calculator Spreadsheet for a new Hardware Configuration.” You’ll understand SQL Server’s Maximum Consumption Rate – basically, you need to be able to pull at least 200MB/sec of data per core to keep SQL Server busy. Otherwise, your SQL Server is just sitting around twiddling its thumbs at 5-20% CPU while queries are running – and while that sounds like a good thing, it’s not, because you’re paying licensing by CPU.

If you’re not getting at least 200MB/sec of storage throughput, it’s time to dig in and find the bottleneck. Keep reading.

There are updated guides for SQL Server 2016, as well (warning: PDFs)

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.

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.
Tom’s Hardware Hard Drive Charts – when you’ve got your storage speed test results, compare them to how fast a single hard drive goes.

How SQL Server Uses Storage

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

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.
Analyzing What SQL Server Caches by Jonathan Allen – this query looks at the contents of your server’s memory and shows what database is being cached.
Analyzing What SQL Server Accesses by David Pless – this DMV query checks the read and write latency by database file.
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.

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 doesn’t 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.

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 server performance tuning class.